# ClickHouse

[ClickHouse](https://clickhouse.com) is a fast and resource efficient
[open-source database](https://github.com/ClickHouse/ClickHouse) for real-time
applications and analytics.

## Prerequisites

- The hostname for the [ClickHouse][clickhouse] database server
- The [username/password][clickhouse-docs-users] for the
  [ClickHouse][clickhouse] database server

## Setup

### Manual

Add the following to a `.env` file in your Cube project:

```dotenv
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 |
| ---------------------------------- | ----------------------------------------------------------------------------------- | ------------------------- | :------: |
| <EnvVar>CUBEJS_DB_HOST</EnvVar>                   | The host URL for a database                                                         | A valid database host URL |    ✅    |
| <EnvVar>CUBEJS_DB_PORT</EnvVar>                   | The port for the database connection                                                | A valid port number       |    ❌    |
| <EnvVar>CUBEJS_DB_NAME</EnvVar>                   | The name of the database to connect to                                              | A valid database name     |    ✅    |
| <EnvVar>CUBEJS_DB_USER</EnvVar>                   | The username used to connect to the database                                        | A valid database username |    ✅    |
| <EnvVar>CUBEJS_DB_PASS</EnvVar>                   | The password used to connect to the database                                        | A valid database password |    ✅    |
| <EnvVar>CUBEJS_DB_CLICKHOUSE_READONLY</EnvVar>    | Whether the ClickHouse user has read-only access or not                             | `true`, `false`           |    ❌    |
| <EnvVar>CUBEJS_DB_CLICKHOUSE_COMPRESSION</EnvVar> | Whether the ClickHouse client has compression enabled or not                        | `true`, `false`           |    ❌    |
| <EnvVar>CUBEJS_DB_MAX_POOL</EnvVar>               | The maximum number of concurrent database connections to pool. Default is `20`      | A valid number            |    ❌    |
| <EnvVar>CUBEJS_CONCURRENCY</EnvVar>               | The number of [concurrent queries][ref-data-source-concurrency] to the data source  | A valid number            |    ❌    |

[ref-data-source-concurrency]: /product/configuration/concurrency#data-source-concurrency

## Pre-Aggregation Feature Support

When using [pre-aggregations][ref-preaggs] with ClickHouse, you have to define
[indexes][ref-preaggs-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`][ref-schema-ref-types-formats-countdistinctapprox] can
not be used in pre-aggregations when using ClickHouse as a source database.

### `rollup_join`

You can use [`rollup_join` pre-aggregations][ref-preaggs-rollup-join] to join
data from ClickHouse and other data sources inside Cube Store.

Alternatively, you can leverage ClickHouse support for [integration table
engines](https://clickhouse.com/docs/en/engines/table-engines#integration-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

<InfoBox>

To learn more about pre-aggregation build strategies, [head
here][ref-caching-using-preaggs-build-strats].

</InfoBox>

| Feature       | Works with read-only mode? | Is default? |
| ------------- | :------------------------: | :---------: |
| Batching      |             ✅             |     ✅      |
| Export Bucket |             ✅             |      -      |

By default, ClickHouse uses [batching][self-preaggs-batching] to build
pre-aggregations.

### Batching

No extra configuration is required to configure batching for ClickHouse.

### Export Bucket

<WarningBox>

  Clickhouse driver **only** supports using AWS S3 for export buckets.

</WarningBox>

#### AWS S3

For [improved pre-aggregation performance with large
datasets][ref-caching-large-preaggs], enable export bucket functionality by
configuring Cube with the following environment variables:

<InfoBox>

  Ensure the AWS credentials are correctly configured in IAM to allow reads and
  writes to the export bucket in S3.

</InfoBox>

```dotenv
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
<EnvVar>CUBEJS_DB_SSL</EnvVar> environment variable to `true`. For more information on how to
configure custom certificates, please check out [Enable SSL Connections to the
Database][ref-recipe-enable-ssl].

## 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][clickhouse-docs-js-config] for more details.

Custom headers can't be configured via environment variables. Instead, use the
[`driverFactory`][ref-config-driverfactory] configuration option to pass a `headers`
object to the driver:

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

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

```javascript
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][clickhouse-readonly] to read-only, by setting
<EnvVar>CUBEJS_DB_CLICKHOUSE_READONLY</EnvVar> to `true`.

You can connect to a ClickHouse database with compression enabled, by setting
<EnvVar>CUBEJS_DB_CLICKHOUSE_COMPRESSION</EnvVar> to `true`.

[clickhouse]: https://clickhouse.tech/
[clickhouse-docs-users]:
  https://clickhouse.tech/docs/en/operations/settings/settings-users/
[clickhouse-docs-js-config]: https://clickhouse.com/docs/integrations/javascript#configuration
[clickhouse-readonly]: https://clickhouse.com/docs/en/operations/settings/permissions-for-queries#readonly
[ref-config-driverfactory]: /product/configuration/reference/config#driver_factory
[ref-sec-ctx]: /product/auth/context
[ref-caching-using-preaggs-build-strats]:
  /product/caching/using-pre-aggregations#pre-aggregation-build-strategies
[ref-recipe-enable-ssl]:
  /product/configuration/recipes/using-ssl-connections-to-data-source
[ref-caching-large-preaggs]:
  /product/caching/using-pre-aggregations#export-bucket
[ref-schema-ref-types-formats-countdistinctapprox]: /product/data-modeling/reference/types-and-formats#count_distinct_approx
[self-preaggs-batching]: #batching
[ref-preaggs]: /product/caching/using-pre-aggregations
[ref-preaggs-indexes]: /product/data-modeling/reference/pre-aggregations#indexes
[ref-preaggs-rollup-join]: /product/data-modeling/reference/pre-aggregations#rollup_join
