ClickHouse

ClickHouse is a fast and resource efficient open-source database for real-time applications and analytics.

Prerequisites

The hostname for the ClickHouse database server
The username/password for the ClickHouse 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 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.

Custom headers

The ClickHouse driver supports forwarding custom HTTP headers on every request to the ClickHouse server. This is useful when requests pass through a proxy or gateway that expects additional headers (e.g., for routing or tracing). See the ClickHouse JavaScript client configuration for more details. Custom headers can’t be configured via environment variables. Instead, use the driver_factory configuration option to pass a headers object to the driver:

from cube import config

@config('driver_factory')
def driver_factory(ctx: dict) -> dict:
  return {
    'type': 'clickhouse',
    'headers': {
      'X-Custom-Header': 'value',
      'X-Routing-Group': 'analytics'
    }
  }

module.exports = {
  driverFactory: ({ dataSource }) => ({
    type: "clickhouse",
    headers: {
      "X-Custom-Header": "value",
      "X-Routing-Group": "analytics"
    }
  })
};

In multitenant deployments, you can use the security context to pass per-tenant headers, for example to forward a user token from the API request down to ClickHouse:

from cube import config

@config('driver_factory')
def driver_factory(ctx: dict) -> dict:
  security_context = ctx['securityContext']
  return {
    'type': 'clickhouse',
    'headers': {
      'X-Custom-User-Token': security_context['token']
    }
  }

module.exports = {
  driverFactory: ({ securityContext }) => ({
    type: "clickhouse",
    headers: {
      "X-Custom-User-Token": securityContext.token
    }
  })
};

Additional Configuration

You can connect to a ClickHouse database when your user’s permissions are restricted 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.

Connect to data

Users & Permissions

SSO & Identity Providers

Deployment

Monitoring

AI

Account

Prerequisites

Setup

Manual

Environment Variables

Pre-Aggregation Feature Support

`count_distinct_approx`

`rollup_join`

Pre-Aggregation Build Strategies

Batching

Export Bucket

AWS S3

SSL

Custom headers

Additional Configuration

​Prerequisites

​Setup

​Manual

​Environment Variables

​Pre-Aggregation Feature Support

​count_distinct_approx

​rollup_join

​Pre-Aggregation Build Strategies

​Batching

​Export Bucket

​AWS S3

​SSL

​Custom headers

​Additional Configuration

Prerequisites

Setup

Manual

Environment Variables

Pre-Aggregation Feature Support

`count_distinct_approx`

`rollup_join`

Pre-Aggregation Build Strategies

Batching

Export Bucket

AWS S3

SSL

Custom headers

Additional Configuration