Skip to main content

ClickHouse

Overview​

ClickHouse is an open-source, column-oriented OLAP database management system known for its ability to perform real-time analytical queries on large-scale datasets. Its architecture is optimized for high performance, leveraging columnar storage and advanced compression techniques to speed up data reads and significantly reduce storage costs. ClickHouse's efficiency in query execution, scalability, and ability to handle even petabytes of data makes it an excellent choice for real-time analytic use cases.

Rill supports connecting to an existing ClickHouse instance and using it as an OLAP engine to power Rill dashboards built against external tables. This is particularly useful when working with extremely large datasets (hundreds of GBs or even TB+ in size).

Rill on ClickHouse

Supported versions​

Rill supports connecting to ClickHouse v22.7 or newer versions.

Connection string (DSN)​

Rill is able to connect to ClickHouse using the ClickHouse Go Driver. An appropriate connection string (DSN) will need to be set through the connector.clickhouse.dsn property in Rill.

A very simple example might look like the following:


connector.clickhouse.dsn="clickhouse://<hostname>:<port>?username=<username>&password=<password>"

Check your port

In most situations, the default port is 9440 for TLS and 9000 when not using TLS. However, it is worth double checking the port that your ClickHouse instance is configured to use when setting up your connection string.

DSN properties

For more information about available DSN properties and setting an appropriate connection string, please refer to ClickHouse's documentation.

Connecting to ClickHouse Cloud​

If you are connecting to an existing ClickHouse Cloud instance, you can retrieve connection details about your instance by clicking on the Connect tab from within the admin settings navigation page. This will provide relevant information, such as the hostname, port, and username being used for your instance that you can then use to construct your DSN.

ClickHouse Cloud connection string

Because ClickHouse Cloud requires a secure connection over https, you will need to pass in secure=true and skip_verify=true as additional URL parameters as part of your https URL (for your DSN):


connector.clickhouse.dsn="https://<hostname>:<port>?username=<username>&password=<password>&secure=true&skip_verify=true"

Need help connecting to ClickHouse?

If you would like to connect Rill to an existing ClickHouse instance, please don't hesitate to contact us. We'd love to help!

Setting the default OLAP connection​

You'll also need to update the olap_connector property in your project's rill.yaml to change the default OLAP engine to ClickHouse:


olap_connector: clickhouse

note

For more information about available properties in rill.yaml, see our project YAML documentation.

Interested in using multiple OLAP engines in the same project?

Please see our Using Multiple OLAP Engines page.

Reading from multiple schemas​

Rill supports reading from multiple schemas in ClickHouse from within the same project in Rill Developer and all accessible tables (given the permission set of the underlying user) should automatically be listed in the left-hand tab, which can then be used to create dashboards.

ClickHouse multiple schemas

Configuring Rill Developer​

When using Rill for local development, there are two options to configure Rill to enable ClickHouse as an OLAP engine:

  • You can set connector.clickhouse.dsn in your project's .env file or try pulling existing credentials locally using rill env pull if the project has already been deployed to Rill Cloud
  • You can pass in connector.clickhouse.dsn as a variable to rill start directly (e.g. rill start --env connector.clickhouse.dsn=...)
Getting DSN errors in dashboards after setting .env?

If you are facing issues related to DSN connection errors in your dashboards even after setting the connection string via the project's .env file, try restarting Rill using the rill start --reset command.

Configuring Rill Cloud​

When deploying a ClickHouse-backed project to Rill Cloud, you have the following options to pass the appropriate connection string to Rill Cloud:

  • Use the rill env configure command to set connector.clickhouse.dsn after deploying the project
  • If connector.clickhouse.dsn has already been set in your project .env, you can push and update these variables directly in your cloud deployment by using the rill env push command
info

Note that you must cd into the Git repository that your project was deployed from before running rill env configure.

Additional Notes​

  • At the moment, we do not officially support modeling with ClickHouse. If this is something you're interested in, please contact us.
  • For dashboards powered by ClickHouse, measure definitions are required to follow standard ClickHouse SQL syntax.
  • Because string columns in ClickHouse can theoretically contain arbitrary binary data, if your column contains invalid UTF-8 characters, you may want to first cast the column by applying the toValidUTF8 function (see ClickHouse documentation) before reading the table into Rill to avoid any downstream issues.