# ClickHouse

> The clickhouse source: HTTP or native, TLS, ClickHouse Cloud and SSH bastions.

ClickHouse, self-hosted or ClickHouse Cloud: the `clickhouse` source, from the `clickhouse` package. Declare the package once in `dependencies.yml`; DRE installs it on demand:

dependencies.yml

```yaml
plugins:
  - clickhouse
```

### Profile fields

Set in `profiles.yml`, under `connections:`, in a target with `type: clickhouse`.

Field

Default

Notes

`driver`

`http`

The protocol: http (the default) or native, ClickHouse’s own TCP protocol. One of `http`, `native`.

`host`

`localhost`

Server host name.

`port`

Server port (default 8123, 8443 with secure, 9000 native, 9440 native with secure)

`user`

`default`

User name.

`password`

Password. Secret: use `env_var()`.

`schema`

`default`

The ClickHouse database to use (dbt’s name; `database` also works)

`secure`

`false`

Connect with TLS (ClickHouse Cloud needs it)

`verify`

`true`

Check the server’s TLS certificate.

`ca_cert`

CA certificate to trust for the server’s TLS certificate (PEM), when it isn’t from a public CA. A file path; `~/` is your home directory.

`client_cert`

Client certificate (PEM), for mutual TLS. A file path; `~/` is your home directory.

`client_cert_key`

The client certificate’s private key (PEM) A file path; `~/` is your home directory.

`server_host_name`

The host name to expect on the server’s TLS certificate, when it isn’t `host`

`connect_timeout`

`10s`

How long to wait for a connection (`30s`, `2m`, or seconds) A duration (`30s`, `2m`) or seconds.

`send_receive_timeout`

`300s`

How long to wait on a read or write to the server (`5m`, or seconds) A duration (`30s`, `2m`) or seconds.

`compression`

How results are compressed on the wire: lz4 (the default), zstd, none, or for http gzip, deflate or br. One of `lz4`, `zstd`, `none`, `gzip`, `deflate`, `br`.

`custom_settings`

ClickHouse settings for every query, e.g. max\_execution\_time: 300.

`retries`

`3`

How many times to try connecting again after a temporary error (0: never)

`ssh`

Reach the server through an SSH bastion (a block of settings) Secret: use `env_var()`.

## Notes

Field names and values are dbt-clickhouse’s, so a dbt profile can be copied across: `schema` names the ClickHouse database (`database` also works, and must match `schema` when both are set). The fields dbt-clickhouse only uses to build models (`cluster`, `database_engine`, `use_lw_deletes`, `local_suffix`, …) are accepted and ignored.

Field

Notes

`driver`

`http` (the default) or `native`, ClickHouse’s own TCP protocol. HTTP sessions survive a failed statement; see below.

`host`, `port`

The port follows `driver` and `secure` unless set: 8123, 8443 with `secure`, 9000 native, 9440 native with `secure`.

`user`, `password`

`default` and no password unless set.

`schema`

The database unqualified table names are read from (default `default`).

`secure`, `verify`

TLS, and whether the server’s certificate is checked (default yes).

`ca_cert`

A CA certificate (PEM) to trust, for a server whose certificate isn’t from a public CA.

`client_cert`, `client_cert_key`

Mutual TLS.

`server_host_name`

The name to expect on the server’s certificate, when it isn’t `host` (a tunnel, an IP address).

`connect_timeout`, `send_receive_timeout`

Defaults `10s` and `300s`; a duration or seconds.

`compression`

`lz4` (the default), `zstd` or `none`; over HTTP also `gzip`, `deflate` or `br`.

`custom_settings`

ClickHouse settings sent with every query, e.g. `max_execution_time`, `max_memory_usage`.

`retries`

How many times to try connecting again after a temporary error (default 3).

`ssh`

Reach the server through an SSH bastion, with the [postgres source’s settings](https://getdre.com/docs/plugin-postgres/).

```yaml
connections:
  ch:
    targets:
      dev:        # a local server, e.g. clickhouse/clickhouse-server in Docker
        type: clickhouse
        user: default
        schema: analytics
      prod:       # ClickHouse Cloud
        type: clickhouse
        host: abc123.eu-west-1.aws.clickhouse.cloud
        secure: true
        user: dre_reader
        password: "{{ env_var('DRE_SECRET_CLICKHOUSE_PASSWORD') }}"
        schema: analytics
        custom_settings:
          max_execution_time: 300
```

Capabilities: `sessions`, `check`, `load`, `read_only`.

-   **Sessions.** Every statement of a report’s connection runs in one ClickHouse session, so a `CREATE TEMPORARY TABLE` or `SET` in a `tab: false` query is there for later tabs. Over HTTP that’s a `session_id`. Over native it’s one held connection, which ClickHouse closes after a failed statement: the temp tables and settings go with it, and the next statement says so.
-   **check** (`dre validate --live`) runs `EXPLAIN`, which analyses a `SELECT` without running it. ClickHouse can only `EXPLAIN` a `SELECT`; any other statement is parsed (`EXPLAIN AST`), not analysed.
-   **load** creates a temporary table on the session and sends the rows as one native insert.
-   **Inlined lookups** spell `'` and `\` with backslashes, as ClickHouse reads them (it has no `chr()`, which other sources get). This needs DRE 0.5.1 and clickhouse 1.0.1; with an older DRE, give a lookup holding those characters `load: temp_table` in its YAML.
-   **Read-only.** An unmanaged report’s session runs with ClickHouse’s `readonly = 2`: writes and DDL on tables are refused, `SET` and temp tables still work, and no statement can lower it. `dre query` uses `readonly = 1`, which also refuses `SET`, temp tables and the table functions that reach outside the database (`url`, `file`, `s3`, `remote`, …). The profile’s `custom_settings` apply either way.
-   **Stopping a run** kills the running query on the server (`KILL QUERY` by its query ID), not only the request.

### Types

ClickHouse follows the [rule every warehouse source follows](https://getdre.com/docs/plugins/#types-from-warehouses):

-   `Nullable(T)`, `LowCardinality(T)` and `SimpleAggregateFunction(f, T)` read as `T`.
-   Integers up to 64 bits are integers; `UInt64` is a `Decimal(20, 0)`, so it stays exact. `Int128`, `UInt128`, `Int256` and `UInt256` are exact text.
-   `Decimal(P, S)` is a decimal up to 38 digits; wider is exact text.
-   `Date` and `Date32` are dates. `DateTime` and `DateTime64` are UTC timestamps (ClickHouse stores instants), to the microsecond, or the nanosecond for `DateTime64(7)` to `(9)`.
-   `Array`, `Tuple` (named: a JSON object in its declared order), `Map`, `Nested`, `JSON`, `Variant`, `Dynamic` and the geo types are compact JSON text on one line.
-   `Enum` values are their names. `UUID`, `IPv4`, `IPv6`, intervals and `Time` (it can pass 24 hours) are text. `FixedString` loses its NUL padding, and is hex when it isn’t text.

The package is written in Go, on ClickHouse’s official Go driver (`clickhouse-go`).

## Try it locally

ClickHouse is open source, so a report can be tried against a server in Docker:

```bash
docker run -d -p 8123:8123 -p 9000:9000 -e CLICKHOUSE_USER=reports -e CLICKHOUSE_PASSWORD=reports-pass clickhouse/clickhouse-server
```

With `user: reports`, `password: "{{ env_var('DRE_SECRET_CLICKHOUSE_PASSWORD') }}"` and the variable set to `reports-pass`, `select 1 as connection_check` in the [tutorial](https://github.com/get-dre/dre/blob/v0.5.1/examples/tutorial/) project checks the connection; `dre validate --live <report>` checks every statement before a run.

`invalid-connection-setting` needs the profile repaired before connecting. `connection-failed` with “the port may be for the other protocol” means `driver` and `port` disagree (8123 and 8443 are HTTP; 9000 and 9440 native). See [profiles](https://getdre.com/docs/reference-profiles/), [error codes](https://getdre.com/docs/reference-error-codes/), [connection](https://getdre.com/docs/glossary/#connection) and [lookup](https://getdre.com/docs/glossary/#lookup).

[Edit page](https://github.com/get-dre/dre/edit/master/docs/plugin-clickhouse.md)

[Previous  
Snowflake](https://getdre.com/docs/plugin-snowflake/)[Next  
csv and delimited](https://getdre.com/docs/plugin-csv/)