CockroachDB Integration with Superset
Tags: cockroachdb, superset, database, analytics
Published: 2023-04-26 21:13:24

## 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](https://superset.apache.org/docs/installation/installing-superset-using-docker-compose).
See below images for reference.

![docker-terminal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q4vd3x01ntxdgqyw5s5t.png)

![docker-container](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3vnze1gwgbu7g6mddeeo.png)

![superset-intro](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c959gjsh8uuij3vc7iqq.png)

_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](https://superset.apache.org/docs/databases/installing-database-drivers) 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.

![susperset-UI](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gz79zbiaxmsnz7dwxbz0.png)
3. 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

![select-db](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0zhote8rlk3frnhmik6c.png)

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
```
![Connect-db](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3789qjomh69vmle3ko7x.png)

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`.

![adv-sec](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g2ne28ath0mxbgveepz8.png)

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

![integration-db](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xc7tj4vhoxtj8lvhc5e6.png)

### Creating Dashboards in Superset

Creating Dashboards in Superset is super simple, you can follow their [docs](https://superset.apache.org/docs/creating-charts-dashboards/creating-your-first-dashboard) for creating your first dashboard. 

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

![dashboard-sue](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kh9ebsh6qqm8k892ztyp.png)

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