ClickHouse

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

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.

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:

• 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:

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 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, error codes, connection and lookup.