ClickHouse dashboards with Rill: instant, operational BI at scale

Fast databases deserve fast dashboards

At Rill, we love fast databases, because Rill dashboards require sub-second performance to deliver the immersive user experience we’ve designed. Until now, we’ve exclusively relied on DuckDB and Apache Druid to deliver that performance. Today, we’re excited to announce our live connector for ClickHouse, one of the world’s fastest open-source database engines.

With this connector, it’s possible to build interactive, exploratory dashboards instantly on top of ClickHouse tables. Unlike other data sources we support, the ClickHouse connection is live: no data ingestion or orchestration is necessary.

In this post, we’ll describe how this connector came to be, why Rill dashboards are a natural fit for ClickHouse’s architecture, and how you can evaluate this connector for yourself.

Meeting ClickHouse’s creator at FOSDEM

In the first week of February, we presented a talk about Aerodynamic Data Models at FOSDEM, a gathering for thousands of technology leaders and open-source enthusiasts. In the audience was Alexey Milovidov, creator and CTO of ClickHouse, who posed a thought-provoking question:

 "Why not scale up Rill using ClickHouse?"

On the train ride back to Amsterdam, Alexey suggested running Rill on top of a ClickHouse-powered data set of Wikipedia traffic. After a couple of days, we had working proof of concept interactively exploring a staggering 480 billion records that we’d loaded into ClickHouse.

We were astonished by ClickHouse's efficiency, achieving sub-second load times for the dashboard, even when analyzing an entire year's worth of data.

We shared our results with Alexey, and following a social media post from one of his colleagues (below) we had a surge of interest in our Discord community and in private messages regarding a ClickHouse connector for Rill.

So the Rill engineering team fast-tracked finishing this connector and getting this merged into our 0.41 release which dropped this past week. (And even before this official release, we’ve been thrilled to see ClickHouse experts try out Rill for themselves).

Under the hood of Rill’s ClickHouse connector

Our live connector for ClickHouse differs significantly from traditional connections we support. It enables users to perform OLAP queries directly on the datastore, eliminating the need to transfer data to our embedded, in-memory engine. This capability is reserved for databases that can deliver interactive query performance at scale and support a high level of concurrency.

The integration follows a straightforward workflow:

1. Ingest & model data in ClickHouse: Developers can use ClickHouse to manage data ingestion as they are doing today. Rill dashboards operate best on tables that adhere to the One Big Table data modeling approach, with timestamps as a partition key, which in turn column-oriented engines like ClickHouse deliver extraordinarily high performance on.
2. Develop metrics in Rill Developer: After downloading and launching Rill Developer, users can connect to ClickHouse tables. Users can then autogenerate and refine the aggregate measures and dimensions, defined in SQL and YAML code, which are at the heart of Rill’s exploratory dashboards. The code developed in Rill Developer defines both a metrics layer and a dashboard layer.
3. Share embedded dashboards in Rill Cloud: Deploying and sharing dashboards can be done by connecting Rill Cloud to a Github project along with encrypted credentials. Security policies, including the ability to create partner-filtered, embedded dashboards based on users' roles or attributes (RBAC), can be enforced for deployed dashboards.

Four steps to cloud dashboards on ClickHouse tables

Step 1 - Install Rill

curl https://rill.sh | sh

Step 2 - Launch Rill with a live connection to a ClickHouse cluster

Rill supports connections with either ClickHouse local or ClickHouse clusters. Commands for each are shared below.‍

# Connecting to clickhouse local

rill start --db-driver clickhouse --db "clickhouse://localhost:9000"

# Connecting to clickhouse cluster

 rill start --db-driver clickhouse --db "<clickhouse://<host>:<port>?username=<username<&password=<pass>>" 

‍

This launches Rill Developer in a browser and displays all the existing ClickHouse tables on the left-hand navigation bar. See docs for further details.

Step 3 - Auto-generate a dashboard with one-click

Select any table to get an instant preview of the table and click on Autogenerate Dashboard. Rill uses OpenAI to analyze the table structure and generate an initial dashboard with a set of suggested domain specific metrics that you can edit and customize.

Step 4 - Deploy and share the dashboard with colleagues

Behind the scenes, the dashboard generated is backed by BI-as-code in the project directory. You can easily commit to a Github repo to deploy.

rill deploy --prod-db-driver clickhouse --prod-db-dsn <clickhouse_dsn>


Future directions: enabling interactive data modeling

The current Rill integration to ClickHouse focuses on live connections without supporting data modeling. Our next milestone will further enhances the performance and introduce data modeling capabilities, coupled with live previews, to enrich the overall ClickHouse table to Rill dashboard journey.

We encourage you to check out Rill's ClickHouse connector yourself, and join our community of users in our Discord channel to ask questions or share your feedback.