Skip to content

ClickHouse connector

ClickHouse is reached over its HTTP interface. Each statement is one request; results come back as JSONCompact by default, or as a true Arrow stream when the build has the Arrow feature.

Every request carries three settings so results are comparable with the other engines: session_timezone=UTC, 64-bit integers unquoted, and the database as a query parameter. There is no connection pool — one keep-alive HTTP agent, with a 10 s connect and 60 s request timeout. Gated by exec-http, plus exec-http-arrow for the Arrow path.

Connection profile

datasources:
  ch:
    type: clickhouse
    uri: http://ch.internal:8123
    username: default
    password: ${CLICKHOUSE_PASSWORD}
    database: analytics

host:/port: work instead of uri:, and the endpoint is then built as http://<host>:<port>.

Parameters

Key Type Required Default Notes
type string yes — clickhouse
uri string yes* — *Or host:. The full endpoint including scheme.
host string yes* — *Or uri:. Used to synthesize http://<host>:<port> — always http, so an HTTPS endpoint needs an explicit uri:.
port int no 8123 Only used when the endpoint is synthesized from host:.
username string no — Sent as the X-ClickHouse-User header.
password string no — Sent as the X-ClickHouse-Key header. Prefer ${VAR}.
database string no — Sent as a query parameter on every request.
default bool no false See connection profiles.

Parsed and ignored on this connector: schema, catalog, sslmode, sslrootcert (TLS comes from the URL scheme, not from these keys), arrow_flight_port, account, role, warehouse, compat_mode.

HTTPS needs a TLS-capable build

The exec-http feature alone links an HTTP client without TLS, so an https:// endpoint fails in a build that has only exec-http. Published binaries and the Python wheel ship exec-all and are fine; a hand-built binary needs --features exec-all (or any build that also includes exec-snowflake / exec-databricks, which bring the TLS stack in).

Arrow-native results

With exec-http-arrow, the same endpoint is asked for ArrowStream and batches decode as a true lz4-framed stream — no configuration change in the profile. See Arrow.

One conversion happens for you: ClickHouse's Arrow writer exports Date as a bare UInt16 (epoch days) and DateTime as a bare UInt32 (epoch seconds), so the adapter runs a DESCRIBE (query) first — type inference only, no execution — and re-types those columns to Date32/Timestamp. Date32 and DateTime64 already export correctly.

Limitations

  • No cume_dist. ClickHouse's window-function list has percent_rank but not cume_dist, so a metric using it is refused at compile time for this dialect rather than substituted.
  • UNION is emitted as UNION DISTINCT, because ClickHouse rejects a bare UNION unless union_default_mode is set — handled by the compiler, nothing to configure.
  • Left and full joins are emitted with join_use_nulls = 1 appended, because ClickHouse's default fills join misses with 0 instead of NULL — also handled for you, and the reason a ClickHouse result agrees with DuckDB.

Verify the connection

$ dosi query --model model.yaml \
    --metrics revenue --group-by orders.status --execute --connection ch

Troubleshooting

Message Cause and fix
connection "x" (clickhouse) is missing required field "uri" Give uri: or host:.
HTTP 401: <body> / HTTP 403: <body> Credentials or grants — the body is ClickHouse's own message.
HTTP 404: <body> on an https:// URL Usually the TLS caveat above: an exec-http-only build cannot speak HTTPS.
cannot reach server: <e> Host, port or network; check that the HTTP interface (8123) is the port in the profile, not the native one (9000).
bad JSONCompact response: <e> The endpoint answered something that is not a ClickHouse result — a proxy or the wrong port.
bad DESCRIBE response: <e> / bad ArrowStream response: <e> Arrow path only; the server's response could not be decoded.

Reference