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 asdate_trunc('month', x); parts withtoYear(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 JOINwhen 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),argMaxandcountIfare fine inexpression.sql. - Built for the hot tier. Give the ClickHouse tables a low
costand 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_namelike any table.
Next Steps
- Datasources
- Semantic Routing, one model across a warehouse and a hot tier