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 theirdocs.
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 withCockroach workload, I loaded my serverless instance withMOVR workload
According to Supersetdocumentation 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 thecockroachdb 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 pulldocker-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 atlocalhost:8088 and login.
2.On the right top corner, next tosettings you'll see a+ icon. click on it & selectData and navigate toconnect database. See below image for reference.

1. In the window you'll be prompted toSelect a database to connect and here you'll need to go to theSupported Databases and choosecockroachDB. See below image

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

cockroachdb://root:pass@{hostname}:{port}/{database}?sslmode=verify-fullexample: cockroachdb://sue:samplepassword@sue-sampletest-host:26257/movr?sslmode=verify-full

As I'm using a secure serverless cluster,I'm required to provide myroot.crt in theSecurity section, underRoot 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 seeCockroachDB in the SupersetDatabases

Creating Dashboards in Superset

Creating Dashboards in Superset is super simple, you can follow theirdocs 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.