Connect Superset to ClickHouse

Apache Superset is an open-source data exploration and visualization platform written in Python. Superset connects to ClickHouse using a Python driver provided by ClickHouse. Let's see how it works...

1. Install the Driver

1. Superset uses the clickhouse-connect driver to connect to ClickHouse. The details of clickhouse-connect are at https://pypi.org/project/clickhouse-connect/ and it can be installed with the following command:

   pip install clickhouse-connect 

2. Start (or restart) Superset.

2. Connect Superset to ClickHouse

1. Within Superset, select Data from the top menu and then Databases from the drop-down menu. Add a new database by clicking the + Database button:

   

2. In the first step, select ClickHouse Connect as the type of database:

   

3. In the second step, enter:

• The HOST and PORT: typical ports are 8443 when using TLS, or 8123 when not using TLS, but these are configurable by your ClickHouse administrator. Port 443 is used in the example, as this is the port that ClickHouse Playground (a publicly available server hosting several example datasets) is listening on.

• The DATABASE NAME: out of the box there is a database named default, use the name of any existing database.

• The USERNAME and PASSWORD: out of the box the username is default. Your ClickHouse administrator will provide the password to use. If you are connecting to the ClickHouse Playground, the username is play and the password is left blank.

• The DISPLAY NAME: this can be any name you prefer. If you will be connecting to multiple ClickHouse databases then make the name more descriptive.

  

4. Click the CONNECT and then FINISH buttons to complete the setup wizard, and you should see your database in the list of databases.

3. Add a Dataset

1. To interact with your ClickHouse data with Superset, you need to define a dataset. From the top menu in Superset, select Data, then Datasets from the drop-down menu.

2. Click the button for adding a dataset. Select your new database as the datasource and you should see the tables defined in your database. For example, the PlayGround default database has a table named covid:

   

3. Click the ADD button at the bottom of the dialog window and your table appears in the list of datasets. You are ready to build a dashboard and analyze your ClickHouse data!

4. Creating charts and a dashboard in Superset

If you are familiar with Superset, then you will feel right at home with this next section. If you are new to Superset, well...it's like a lot of the other cool visualization tools out there in the world - it doesn't take long to get started, but the details and nuances get learned over time as you use the tool.

1. You start with a dashboard. From the top menu in Superset, select Dashboards. Click the button in the upper-right to add a new dashboard. The following dashboard is named Covid-19 Dashboard:

   

2. To create a new chart, select Charts from the top menu and click the button to add a new chart. You will be shown a lot of options. The following example shows a Big Number chart using the covid dataset from the CHOOSE A DATASET drop-down:

   

3. You need to add a metric to a Big Number. The column named DATA and the section named Query with a METRIC field show a red warning because they are not defined yet. To add a metric, click Add metric and a small dialog window appears:

   

4. The following example uses the SUM metric, found on the the SIMPLE tab. It sums the values of the new_confirmed column:

   

5. To view the actual number, click the RUN QUERY button:

   

6. Click the SAVE button to save the chart, then select Covid-19 Dashboard under the ADD TO DASHBOARD drop-down, then SAVE & GO TO DASHBOARD saves the chart and adds it to the dashboard:

   

7. That's it. Building dashboards in Superset based on data in ClickHouse opens up a whole world of blazing fast data analytics!