CockroachDB Integration with Superset

What is Superset ??

Superset is a fast, lightweight modern web application that is loaded with options that make it easy for users of all skill sets to explore and visualize their data, from simple pie charts to highly detailed deck.gl geospatial charts. so, in simple words its used for analyzing your data.

Why Integrate it with CockroachDB ??

CockraochDB is a highly available & reliable database which makes consistency as a priority and is best suited for all your OLTP needs.However, to analyze this data you can use any intelligent visualization tools. One of such easy lightweight tool is Apache Superset. Together with Superset & CockroachDB now you have a Datastore with analytical capabilities.

Installing Superset with docker

Pre-requisite:Docker
There seems to be multiple ways to install superset. I wanted to try out the installation using Docker as its the most recommended approach. You can follow the Installation instructions from their docs.
See below images for reference.

Note: superset is not officially supported in Windows as of 04/23, So, if you need to test this out, quickly spin up a linux/mac instance on hyperV or something similar.

Integrating Superset with cockroachDB

Pre-requisite: Working instance of CockroachDB and its connection parameters.
CockroachDB has multiple offerings & you can use any of these to integrate with superset. I used the serverless instance of CRDB here for the exercise.
For folks who are familiar with Cockroach workload, I loaded my serverless instance with MOVR workload
According to Superset documentation to integrate Superset with cockroachDB, Superset requires a python DB-API database driver and a SQL Alchemy dialect to be installed for cockroachDB. Now, lets see how to install the driver.

Installing the database driver to support cockroachDB

1.Create requirements-local.txt

# From the repo root...
touch ./docker/requirements-local.txt

2.Add the cockroachdb driver

echo "cockroachdb" >> ./docker/requirements-local.txt

3.Rebuild your local docker image with new driver

docker-compose build --force-rm

4.Start Superset via Docker Compose

docker-compose -f docker-compose-non-dev.yml pull
docker-compose -f docker-compose-non-dev.yml up

Now, superset should have all the necessary packages installed for connecting with CockroachDB.

Adding Cockroach DB as a data store in Superset

1.Go to Superset UI at localhost:8088 and login.
2.On the right top corner, next to settings you'll see a + icon. click on it & select Data and navigate to connect database. See below image for reference.

1. In the window you'll be prompted to Select a database to connect and here you'll need to go to the Supported Databases and choose cockroachDB. See below image

In second screen after selecting cockroachDB, you need to provide the connection details. Enter the primary Credentials, select Basic and enter the SQLAlCHEMY URI which should be formatted specifically for cockroach DB. See below on how to format it.

cockroachdb://root:pass@{hostname}:{port}/{database}?sslmode=verify-full

example: cockroachdb://sue:samplepassword@sue-sampletest-host:26257/movr?sslmode=verify-full

As I'm using a secure serverless cluster,I'm required to provide my root.crt in the Security section, under Root Certificate. So, I copied the root cert which is by default located in the $HOME/.postgresql/root.crt.

4.Once the connection is successful, you'll see CockroachDB in the Superset Databases

Creating Dashboards in Superset

Creating Dashboards in Superset is super simple, you can follow their docs for creating your first dashboard.

I created a bunch of dashboards for my dataset, see below image for reference.

Yay !! There you go, you have now successfully integrated cockroachDB with Superset and created some dashboards as well.