How to Connect Superset with ClickHouse: A Step‑by‑Step Guide

If you’re looking to connect Superset to ClickHouse, you’ve landed in the right place. Both tools are open‑source, but they serve very different purposes: ClickHouse excels at ultra‑fast columnar analytics, while Superset provides a browser‑based UI for building dashboards. Marrying the two lets you explore massive datasets without writing endless SQL scripts. Below you’ll find everything you need—from system requirements to troubleshooting tips—so you can get the integration up and running without a hitch.

Connecting Superset to ClickHouse: What You Need to Know

Before diving into code, take a moment to understand the moving parts. Superset talks to databases through SQLAlchemy, a Python ORM that requires a driver for each backend. For ClickHouse, the most common driver is clickhouse‑sqlalchemy, which itself depends on the native clickhouse‑driver. Your Superset instance must be able to reach the ClickHouse server over the network, and the ClickHouse user you create needs read‑only access to the tables you intend to visualize.

Prerequisites and Environment Setup

• Superset version: 2.0 or newer (the driver works best with recent releases).
• ClickHouse server: 22.8 or later; the default TCP port is 9000, HTTP port 8123.
• Python environment: Superset runs on Python 3.8+; ensure you have pip available.
• Network access: Superset’s host must be able to resolve the ClickHouse hostname and reach the appropriate ports.

If any of these items are missing, set them up first. Installing ClickHouse on a Linux box is straightforward—just follow the official installation guide. For Superset, the quick‑start Docker image is a solid baseline for testing.

Installing the ClickHouse SQLAlchemy Driver

The driver isn’t bundled with Superset, so you’ll add it manually. Open a terminal on the machine where Superset lives and run:

pip install clickhouse-sqlalchemy

This command pulls both clickhouse-sqlalchemy and its dependency clickhouse-driver. If you’re using a virtual environment (highly recommended), activate it before installing. After the installation finishes, verify the import works:

python -c "import clickhouse_sqlalchemy"

Any import error at this stage usually points to a mismatched Python version or a missing system library such as libaio. Installing the missing package and re‑running the pip command resolves most issues.

Creating a ClickHouse User for Superset

Security best practice dictates that Superset shouldn’t connect as the default ClickHouse admin. Instead, create a dedicated read‑only account:

CREATE USER superset WITH PASSWORD 'your_strong_password';
GRANT SELECT ON *.* TO superset;

Adjust the password policy to match your organization’s standards. You can also limit the user to specific databases by replacing *.* with mydb.*. Remember to note the exact hostname (or IP) and port you’ll use in the next step.

Adding ClickHouse as a Database Source in Superset

Log into Superset as an admin, then navigate to Data → Databases → + Database. In the “SQLAlchemy URI” field, paste a connection string that follows this pattern:

clickhouse+native://superset:your_strong_password@clickhouse-host:9000/default

Replace clickhouse-host with the actual server address, and adjust the database name if you aren’t using the default schema. After entering the URI, click “Test Connection.” Superset will attempt to open a native TCP connection; a green success banner means the driver is correctly installed and the ClickHouse user has the necessary rights.

If the test fails, double‑check the following:

• Network firewalls or security groups blocking port 9000.
• Typos in the username or password.
• ClickHouse’s listen_host setting, which may restrict connections to localhost only.

Configuring Tables and Metrics

Once the connection is verified, Superset will list all ClickHouse tables in the chosen schema. You can import them individually or use the “Import all” button for bulk registration. When you open a table in the “Explore” view, you’ll notice that some advanced functions—like arrayJoin or groupArray—are not automatically recognized as metrics. To make the most of ClickHouse’s columnar strengths, define custom metrics using raw ClickHouse expressions:

{
"metric_name": "sum_requests",
"expression": "sum(requests)"
}

Saving these definitions lets you treat them like any other metric in Superset’s chart builder.

Performance Tips for Large Datasets

ClickHouse shines when you push aggregation down to the server. In Superset, enable “Run query in async mode” to avoid blocking the UI while the database processes billions of rows. Additionally, consider setting a default max_rows_to_read limit in the ClickHouse server configuration; this prevents runaway queries from overwhelming your dashboard users.

Common Pitfalls and How to Fix Them

Even with careful setup, you might run into a few hiccups:

• SSL/TLS errors: If your ClickHouse instance is behind an HTTPS reverse proxy, use the clickhouse+http dialect and add verify=False only for testing—never in production.
• Time‑zone mismatches: ClickHouse stores timestamps in UTC by default. Superset’s UI assumes the browser’s local time, so you may see shifted values unless you explicitly convert time zones in your SQL or set the timezone parameter in the connection string.
• Missing functions: Some ClickHouse functions aren’t part of the SQLAlchemy dialect. When that happens, write a SELECT statement manually in the “SQL Lab” tab and reference it in a virtual dataset.

FAQ

Can I use the HTTP port (8123) instead of the native TCP port?

Yes. Switch the dialect to clickhouse+http and adjust the URI accordingly. This is handy when you only have HTTP access through a load balancer.

Do I need to restart Superset after installing the driver?

Restarting the Superset web server (or the Docker container) is recommended. The new driver is loaded at startup, so a simple refresh won’t be enough.

Is it safe to expose ClickHouse to the internet for Superset?

Generally, no. Keep ClickHouse behind a VPN or internal network, and use firewall rules to restrict access to the Superset host only. If you must expose it, enforce TLS and strong authentication.

How do I handle schema changes in ClickHouse?

Superset caches table metadata. After adding or dropping columns, use the “Refresh Metadata” button in the database settings to sync the schema.