Two ways to visualize ClickHouse data using Apache Superset

Apache Superset is a powerful BI tool that provides ways to view and explore data. It is also gaining popularity among ClickHouse users.

We will cover 2 ways to install Superset and then show how to connect to your first ClickHouse database from Superset. The code examples are based on Ubuntu 18.04, Superset 1.1.0, and clickhouse-sqlalchemy 0.1.6.

Method 1: Python virtual environment

The first method installs Superset directly on your host. We will first create a Python virtual environment. The following are commonly used commands.

python3 -m venv clickhouse-sqlalchemy
. clickhouse-sqlalchemy/bin/activate
pip install --upgrade pi

Install and start Superset

Install dependency packages

sudo apt-get install build-essential libssl-dev libffi-dev python-dev python-pip libsasl2-dev libldap2-dev

Commands for handling Superset connections to ClickHouse. Might need to be tweaked slightly depending on your environment.

export FLASK_APP=superset
pip install apache-superset
superset db upgrade
superset fab create-admin
superset load_examples
superset init

Install clickhouse-sqlalchemy driver

pip install clickhouse-sqlalchemy

clickhouse-driver version must be 0.2.0 or higher.

pip freeze |grep clickhouse
clickhouse-driver==0.2.0
clickhouse-sqlalchemy==0.1.6

Start Superset and log in

It’s time to start Superset. Run the following command:

superset run -p 8088 --with-threads --reload --debugger

Browse to localhost:8088

You will see the login screen as shown below. Enter the administrator login name and password you defined during Superset installation (for example, admin/secret).

Method 2: Run Superset using Docker Compose

If you don't want to worry about Python versions, virtual environments, and pip. You can use docker.

First install docker and docker-compose.

Installation complete, check the version.

$ docker --version
Docker version 19.03.4, build 9013bf583a
$ docker-compose --version
docker-compose version 1.29.1, build c34c88b2

Install superset using docker-compose

git clone https://github.com/apache/superset
cd superset
touch ./docker/requirements-local.txt
echo "clickhouse-driver>=0.2.0" >> ./docker/requirements-local.txt
echo "clickhouse-sqlalchemy>=0.1.6" >> ./docker/requirements-local.txt
docker-compose -f docker-compose-non-dev.yml up

After successful operation, the browser accesses localhost:8088

The default login/password is admin/admin.

Connecting to ClickHouse

Regardless of the installation method you choose, you can now connect to your first ClickHouse database.

Once you log in, you'll see a screen with your recent work and your current dashboard. Select the Data tab in the upper right corner, then select Database. A page will appear containing your current database connections. Press the + Database button to add a new database.

Enter the following values:

Database name: clickhouse-public
SQLALCHEMY URL: clickhouse+native://demo:[email protected]/default?secure=true

Altinity.Cloud is a public dataset site.

Press the Test Connection button. Save the link after success.

Connection Process

The connection uses SQLAlchemy, a common API for connecting to ClickHouse and many other databases.

SQLAlchemy connections use a specialized URL format that supports multiple drivers. To connect to ClickHouse, you need to provide a URL similar to the one we showed before:

clickhouse+native://demo:[email protected]/default?secure=true

There are two main protocols for connecting to ClickHouse, native TCP and HTTP.

It is recommended to use raw TCP.

clickhouse+native://<user>:<password>@<host>:<port>/<database>[?options…]

Configure Superset

We have successfully connected to ClickHouse. Now we will use Superset to build a dashboard.

First, let's create the dataset. Select clickhouse-public as the connection, then select schema default and table ontime.

Once you have a dataset, creating your first chart is simple. Just click on the dataset name on the datasets page. Superset will switch to a screen to define a chart, as shown below.

Creating a Time Series Chart

Publish charts on dashboards.

Select the DASHBOARD button to add the created chart.

The above is the whole process of Apache Superset visualization of ClickHouse data.

This concludes this article on two ways to visualize ClickHouse data using Apache Superset. For more information about Apache Superset visualization of ClickHouse, please search for previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!