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 YugaByte DB’s PostgreSQL compatible API.
1. Start Local Cluster with YSQL API Enabled
Follow Quick Start instructions to run a local YugaByte DB cluster. Test YugaByte DB’s PostgreSQL compatible YSQL API as documented so that you can confirm that you have a PostgresSQL compatible service running on
2. Load Data
Download the Sample Schema
$ wget https://raw.githubusercontent.com/YugaByte/yb-sql-workshop/master/query-using-bi-tools/schema.sql
Download the Sample Data
$ wget https://github.com/YugaByte/yb-sql-workshop/raw/master/query-using-bi-tools/sample-data.tgz
$ tar zxvf sample-data.tgz
$ ls data/
orders.sql products.sql reviews.sql users.sql
Connect to YugaByte DB using psql
You can do this as shown below.
$ ./bin/psql -p 5433 -U postgres
psql (10.3, server 10.4) Type "help" for help. postgres=#
Create a Database
postgres=> CREATE DATABASE yb_demo;
postgres=> GRANT ALL ON DATABASE yb_demo to postgres;
postgres=> \c yb_demo;
Create Schema and Load Data
First create the 4 tables necessary to store the data.
postgres=> \i 'schema.sql';
Now load the data into the tables.
postgres=> \i 'data/products.sql'
postgres=> \i 'data/users.sql'
postgres=> \i 'data/orders.sql'
postgres=> \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.
$ wget http://downloads.metabase.com/v0.30.4/metabase.jar
$ java -jar metabase.jar
Go to http://localhost:3000 to configure your Metabase server and point it to the YugaByte DB PostgreSQL API endpoint at
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.