ClickHouse
ClickHouse (opens in a new tab) is a fast and resource efficient open-source database (opens in a new tab) for real-time applications and analytics.
Prerequisites
- The hostname for the ClickHouse (opens in a new tab) database server
- The username/password (opens in a new tab) for the ClickHouse (opens in a new tab) database server
Setup
Manual
Add the following to a .env file in your Cube project:
CUBEJS_DB_TYPE=clickhouse CUBEJS_DB_HOST=my.clickhouse.host CUBEJS_DB_NAME=my_clickhouse_database CUBEJS_DB_USER=clickhouse_user CUBEJS_DB_PASS=**********Environment Variables
| Environment Variable | Description | Possible Values | Required |
|---|---|---|---|
CUBEJS_DB_HOST | The host URL for a database | A valid database host URL | ✅ |
CUBEJS_DB_PORT | The port for the database connection | A valid port number | ❌ |
CUBEJS_DB_NAME | The name of the database to connect to | A valid database name | ✅ |
CUBEJS_DB_USER | The username used to connect to the database | A valid database username | ✅ |
CUBEJS_DB_PASS | The password used to connect to the database | A valid database password | ✅ |
CUBEJS_DB_CLICKHOUSE_READONLY | Whether the ClickHouse user has read-only access or not | true, false | ❌ |
CUBEJS_DB_CLICKHOUSE_COMPRESSION | Whether the ClickHouse client has compression enabled or not | true, false | ❌ |
CUBEJS_DB_MAX_POOL | The maximum number of concurrent database connections to pool. Default is 20 | A valid number | ❌ |
CUBEJS_CONCURRENCY | The number of concurrent queries to the data source | A valid number | ❌ |
Pre-Aggregation Feature Support
When using pre-aggregations with ClickHouse, you have to define indexes in pre-aggregations. Otherwise, you might get the following error: ClickHouse doesn't support pre-aggregations without indexes.
count_distinct_approx
Measures of type count_distinct_approx can not be used in pre-aggregations when using ClickHouse as a source database.
rollup_join
You can use rollup_join pre-aggregations to join data from ClickHouse and other data sources inside Cube Store.
Alternatively, you can leverage ClickHouse support for integration table engines (opens in a new tab) to join data from ClickHouse and other data sources inside ClickHouse. To do so, define table engines in ClickHouse and connect your ClickHouse as the only data source to Cube.
Pre-Aggregation Build Strategies
To learn more about pre-aggregation build strategies, head here.
| Feature | Works with read-only mode? | Is default? |
|---|---|---|
| Batching | ✅ | ✅ |
| Export Bucket | ✅ | - |
By default, ClickHouse uses batching to build pre-aggregations.
Batching
No extra configuration is required to configure batching for ClickHouse.
Export Bucket
Clickhouse driver only supports using AWS S3 for export buckets.
AWS S3
For improved pre-aggregation performance with large datasets, enable export bucket functionality by configuring Cube with the following environment variables:
Ensure the AWS credentials are correctly configured in IAM to allow reads and writes to the export bucket in S3.
CUBEJS_DB_EXPORT_BUCKET_TYPE=s3 CUBEJS_DB_EXPORT_BUCKET=my.bucket.on.s3 CUBEJS_DB_EXPORT_BUCKET_AWS_KEY=<AWS_KEY> CUBEJS_DB_EXPORT_BUCKET_AWS_SECRET=<AWS_SECRET> CUBEJS_DB_EXPORT_BUCKET_AWS_REGION=<AWS_REGION>SSL
To enable SSL-encrypted connections between Cube and ClickHouse, set the CUBEJS_DB_SSL environment variable to true. For more information on how to configure custom certificates, please check out Enable SSL Connections to the Database.
Additional Configuration
You can connect to a ClickHouse database when your user's permissions are restricted (opens in a new tab) to read-only, by setting CUBEJS_DB_CLICKHOUSE_READONLY to true.
You can connect to a ClickHouse database with compression enabled, by setting CUBEJS_DB_CLICKHOUSE_COMPRESSION to true.