ClickHouse
Jitsu supports ClickHouse as a destination. For more information about ClickHouse see docs.
Configuration#
ClickHouse destination config consists of following schema:
#We recommend to set up Clickhouse TLS config separately from destinations.
#That allows sharing TLS config between destinations and makes possible to configure secure connection with configurator UI
clickhouse:
tls_config:
maincert: path_to_crt_file
cert2: path_to_crt_file2
#Destinations config
destinations:
clickhouse_1:
type: clickhouse
clickhouse:
dsns:
- "https://username:password@host1:8443/mydb?read_timeout=5m&timeout=5m&enable_http_compression=1&tls_config=maincert"
- "https://username:password@host2:8443/mydb?read_timeout=5m&timeout=5m&enable_http_compression=1&tls_config=maincert"
- "https://username:password@host3:8443/mydb?read_timeout=5m&timeout=5m&enable_http_compression=1&tls_config=maincert"
db: mydb
cluster: mycluster
engine:
raw_statement: "ENGINE = ReplacingMergeTree(_timestamp) ORDER BY (event_id)" #Optional
nullable_fields:
- middle_name
- event_description
partition_fields:
- function: toYYYYMMDD
field: _timestamp
- field: event_type
order_fields:
- function: intHash32
field: id
primary_keys:
- event_id #Must be equal to server.unique_id_fieldFields#
| Field (*required) | Type | Description | Default value |
|---|---|---|---|
| dsns* | string array | Array of connection strings. Must contain at least one connection string. | - |
| db* | string | Database name. | - |
| cluster* | string | Required if dsns count more than 1. Run SELECT * from system.clusters to get the list of all available clusters | - |
| engine | object | Tables engine configuration. | see below |
| tls | object | TLS configuration. Map of cert names and paths to cert files. Cert names will be used in tls_config query parameter in dsns. | - |
If engine wasn't provided default one (depends on cluster configuration) will be used:
#if ClickHouse single server (dsns = 1)
ReplacingMergeTree(_timestamp) PARTITION BY toYYYYMM(_timestamp) ORDER BY ($server.unique_id_field)
#if ClickHouse cluster (dsns > 1)
ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/$db/$tablename', '{replica}', _timestamp) PARTITION BY toYYYYMM(_timestamp) ORDER BY ($server.unique_id_field)$server.unique_id_field - is a global configuration of events unique ID depends on type of Jitsu JS SDK.
For JS SDK 2.0: server.unique_id_field = event_id
For the oldest one: server.unique_id_field = eventn_ctx_event_id
Engine#
| Field (*required) | Type | Description | Default value |
|---|---|---|---|
| raw_statement | string | Engine raw statement which will be used in CREATE TABLE db.name (a type,b type) $raw_statement. Must begin with 'ENGINE='. Could include ORDER BY, PARTITION BY, etc. | - |
| nullable_fields | string array | Array of nullable fields. All fields are created as non-null by default. | - |
| partition_fields | field object array | Array of field objects will be used in PARTITION BY ($partition_fields) statement | toYYYYMM(_timestamp) |
| order_fields | field object array | Array of field objects will be used in ORDER BY ($order_fields) statement | $server.unique_id_field |
| primary_keys | string array | Array of field names will be used in PRIMARY KEY ($primary_keys) statement | - |
Please note, if raw_statement exists than config keys partition_fields, order_fields,primary_keys will be skipped.
Field object#
| Field (*required) | Type | Description | Default value |
|---|---|---|---|
| function | string | ClickHouse function name which will be applied to the field (e.g. toYYYYMMDD, intHash32) | - |
| field* | string | Field name | - |