Metabase
Metabase is a Business Intelligence (BI) tool.
This document shows how to set up Metabase 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
$ 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 YugabyteDB using ysqlsh
Run the following command to connect to YugabyteDB using the YSQL shell:
$ ./bin/ysqlsh
ysqlsh (11.2)
Type "help" for help.
yugabyte=#
Create a database
yugabyte=# CREATE DATABASE yb-demo;
yugabyte=# GRANT ALL ON DATABASE yb-demo to yugabyte;
yugabyte=# \c yb-demo;
Create schema and load data
First create the 4 tables necessary to store the data:
yugabyte=# \i 'schema.sql';
Now load the data into the tables:
yugabyte=# \i 'data/products.sql'
yugabyte=# \i 'data/users.sql'
yugabyte=# \i 'data/orders.sql'
yugabyte=# \i 'data/reviews.sql'
3. Download and configure Metabase
Detailed steps for setting up Metabase are available in the Metabase documentation. 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 YSQL API endpoint at localhost:5433.
4. Run complex queries with Metabase
Detailed steps on how to use Metabase are available in the Metabase documentation. For this doc, you 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, and so on
Click Ask a Question > Custom Query. Choose the database you just set up, and enter the SQL queries noted in the Retail Analytics section.