ClickHouse Adapter

Generate ClickHouse SQL from your model, typically for a hot tier in front of the warehouse.

What the adapter emits

  • Identifiers in `backticks`; bare column names in expressions are lowercased.
  • Date literals as toDate('2026-01-01'); grain truncation as date_trunc('month', x); parts with toYear(x), toMonth(x), toDayOfWeek(x).
  • CTEs for the per-fact subqueries (no temp tables), window functions where a decorator needs them, and a FULL OUTER JOIN when a query blends measures from two facts.

Configuration

clickhouse_hot:
  adapter: clickhouse
  name: ClickHouse Hot Tier
  tier: hot
  protocol: https
  host: abc123.us-east-1.aws.clickhouse.cloud
  port: 8443
  database: analytics
  username: analyst
  # password: never here; zsql deploy strips it if present

Connection fields

The server never connects to ClickHouse: it has no database driver in it, and the only keys the deployed model keeps are adapter, tier and the name. Your application runs the statement it gets back, with its own credentials.

zsql on this machine does connect, read-only, for zsql catalog, zsql sample and zsql run. It connects over HTTP: username and a password, or a url when host and port cannot say it; --schema on a catalog read names the database. Secrets go in .zsql or ZSQL_DS_<KEY>_<FIELD>, never in datasources.yml; zsql deploy strips password from the file if present. See Connecting to a warehouse.

Notes

  • Expressions are ClickHouse SQL. uniqExact, quantile(0.5)(x), argMax and countIf are fine in expression.sql.
  • Built for the hot tier. Give the ClickHouse tables a low cost and a date partition covering what they hold, so the planner routes recent-data queries here and falls back to the warehouse otherwise. See Semantic Routing.
  • Views work. A view or materialized view is referenced by physical_name like any table.

Next Steps