ClickHouse

clickhouse_query('<catalog>', '<clickhouse sql>')

Unlike every other database catalog, a ClickHouse catalog is not addressed as catalog.schema.table. You pass ClickHouse the SQL you want it to run, and the result comes back as a table you can join with any other catalog.

So this does not work, and is the first thing everyone tries:

SELECT * FROM my_clickhouse.default.users   -- ✗

Submit it and you get a 400 naming the statement that does work, with your own catalog and table already in it. This is the equivalent:

SELECT * FROM clickhouse_query('my_clickhouse', 'SELECT * FROM default.users');

SELECT p.name, c.views
FROM   clickhouse_query('events',
         'SELECT product_id, count(*) AS views
            FROM analytics.page_views
           WHERE day >= today() - 30
        GROUP BY product_id') AS c
JOIN   sales_pg.public.products AS p ON p.id = c.product_id
ORDER BY c.views DESC
LIMIT 20;

What you need

Field	Notes
Host	Bare hostname — no scheme, no port, no path
HTTP port	8443 for TLS, 8123 for plaintext
TLS	On by default
Database	`default` unless you say otherwise
User	A read-only user is enough
Password	Goes to the secrets manager, never stored in the clear

The credential travels as ClickHouse's X-ClickHouse-User / X-ClickHouse-Key headers, not in a URL, so it never appears in a query log or an error message.

Why a function and not `catalog.db.table`

Because the alternative is slow in a way you would not see until it hurt.

ClickHouse answers over its HTTP interface, and an HTTP response is not seekable — nothing can push a filter or a projection into it. A view per table would therefore transfer the whole table for every WHERE, then filter locally. Measured on 3 million rows:


Transferred as rows, filtered and aggregated locally	11.5s, buffered in memory
Aggregated on ClickHouse, one row returned	0.38s

Thirty times, and the difference between fitting in memory and not. Making the fast path the only path is deliberate: a view would have been the first thing everyone reached for, and the slow one.

So: put the filtering, the grouping and the aggregation inside the function call. Join the small result with your other catalogs outside it.

What this means in practice

Results are buffered. Whatever the inner query returns has to fit in the query's memory. Aggregate first; do not SELECT * a billion rows.
Tables still appear in the catalog. The Tables page, autocomplete, the semantic layer and MCP all list your ClickHouse tables and columns — the catalog reads system.tables and system.columns — so you can see what is there before writing the query.
Only the catalog's configured database is listed, not every database on the server.
The call is read-only. INSERT, ALTER, DROP and friends inside the string are refused before the request leaves us.
FORMAT is added for you. Leave it off; FORMAT Parquet is appended, and any other FORMAT is rejected because the response has to be Parquet.

Types

Parquet is ClickHouse's own encoder, so the awkward ones survive intact:

ClickHouse	Arrives as
`LowCardinality(String)`	`VARCHAR`
`Decimal(18, 4)`	`DECIMAL(18,4)`, exact
`DateTime64(3)`	`TIMESTAMP WITH TIME ZONE`
`Array(String)`	`VARCHAR[]`
`UUID`	`UUID`

Known limitation: error messages

A ClickHouse SQL error currently surfaces as an HTTP status — for example HTTP Error: HTTP GET error on '…' (HTTP 404 Not Found) — rather than ClickHouse's own explanation. The message is lost in transit: the response carries it, and the Parquet reader that fetches it does not surface a failed body.

If a call fails and the SQL looks right, run the same statement against ClickHouse directly to see the real error. Fixing this properly means re-issuing the failed query to capture its message, which has to happen inside the execution task rather than in the API, and is tracked as follow-up work.

If a service idles

A ClickHouse Cloud service that has been idle takes tens of seconds to wake, and the first request while it is waking fails at the transport level. Metadata collection retries and waits up to a minute for exactly this reason, and a refresh that still fails records the error against the catalog rather than showing it as empty — if your Tables page shows no tables for a ClickHouse catalog, hit Refresh once the service is awake.

Cost

Each call is one query on your ClickHouse. Behind the scenes the Parquet reader makes two requests (a HEAD then a GET), and ClickHouse would execute the query for both — so every call sets use_query_cache=1 with a short TTL, making the second one a cache hit. Measured: 400,000 rows read without it, 200,001 with. Queries that cannot be cached — anything containing now(), or reading a system table — run twice by design rather than failing.

The wire protocols do not work

For the record, since it is the obvious thing to try: ClickHouse's MySQL (9004) and PostgreSQL (9005) compatibility interfaces cannot be used as ordinary database catalogs. DuckDB's scanner extensions open a transaction before doing anything, and ClickHouse has no transactions — the connection succeeds and then every statement fails with Transactions are not supported (NOT_IMPLEMENTED). Adding one as a MySQL catalog fails validation for that reason, not because of your credentials.

PostgreSQL & MySQL BigQuery