Metabase

Metabase is an extremly easy-to-use Business Intelligence (BI) tool. It bills itself as the easy, open source way for everyone in your company to ask questions and learn from data. This page shows how Metabase can be setup to integrate with YugabyteDB’s PostgreSQL compatible API.

1. Start local cluster

Follow Quick Start instructions to run a local YugabyteDB cluster. Test YugabyteDB’s PostgreSQL compatible YSQL API as documented so that you can confirm that you have a PostgresSQL compatible service running on localhost:5433.

2. Load data

Download the sample schema

  1. $ wget https://raw.githubusercontent.com/yugabyte/yb-sql-workshop/master/query-using-bi-tools/schema.sql

Download the sample data

  1. $ wget https://github.com/yugabyte/yb-sql-workshop/raw/master/query-using-bi-tools/sample-data.tgz
  1. $ tar zxvf sample-data.tgz
  1. $ ls data/
  1. orders.sql    products.sql    reviews.sql    users.sql

Connect to YugabyteDB using ysqlsh

You can do this as shown below.

  1. $ ./bin/ysqlsh
  1. ysqlsh (11.2)
  2. Type "help" for help.
  4. yugabyte=#

Create a database

  1. yugabyte=# CREATE DATABASE yb-demo;
  1. yugabyte=# GRANT ALL ON DATABASE yb-demo to yugabyte;
  1. yugabyte=# \c yb-demo;

Create schema and load data

First create the 4 tables necessary to store the data.

  1. yugabyte=# \i 'schema.sql';

Now load the data into the tables.

  1. yugabyte=# \i 'data/products.sql'
  1. yugabyte=# \i 'data/users.sql'
  1. yugabyte=# \i 'data/orders.sql'
  1. yugabyte=# \i 'data/reviews.sql'

3. Download and configure Metabase

Detailed steps for setting up Metabase are available here. The following are the minimal setup steps for getting started.

  1. $ wget http://downloads.metabase.com/v0.30.4/metabase.jar
  1. $ java -jar metabase.jar

Go to http://localhost:3000 to configure your Metabase server and point it to the YSQL API endpoint at localhost:5433.

4. Run complex queries with Metabase

Detailed steps on how to use Metabase are available here. For this doc, we will specifically focus on asking questions that require RDBMS capabilities.

  • Filter data using WHERE clauses
  • Join data between tables
  • Perform data aggregation using GROUP BY
  • Use built-in functions such as SUM, MIN, MAX, etc.

You can click on Ask a Question -> Custom Query. Choose the database we just setup, and enter the SQL queries noted in the Retail Analytics section.