Ch Query

Querying ClickHouse

Use the MCP connection for exploration rather than your own client in a script: it is already connected, it runs as a read-only user, and it needs no passwords or dependencies. A real driver (clickhouse-connect, clickhouse-driver) is for pulling data into a notebook, not for looking around.

1. Three things that make numbers wrong

Assume ReplacingMergeTree until you have checked. It is the default choice for mutable data, and it means old row versions sit next to new ones until the engine merges the parts — whenever it feels like it. A plain count() or sum() over such a table over-reports. Options:

-- correct but expensive: the engine collapses duplicates on the fly
SELECT count() FROM shop.prices_by_day FINAL WHERE date >= today() - 30

-- cheaper: collapse by hand on the sorting key
SELECT product_id, argMax(price, date) AS price
FROM shop.prices_by_day
WHERE date BETWEEN '2026-08-01' AND '2026-08-31'
GROUP BY product_id

Check which engine you are actually dealing with before deciding:

SELECT name, engine, partition_key, sorting_key,
       formatReadableQuantity(total_rows), formatReadableSize(total_bytes)
FROM system.tables WHERE database = 'shop' AND name = 'prices_by_day'

If the answer contains neither FINAL nor a group-by on the sorting key, the figure is probably inflated. Say so out loud rather than handing over the number.

Partitioning is almost always by date. A date filter is mandatory or the whole history is read. Put it in PREWHERE, not WHERE:

SELECT product_id, sum(qty)
FROM shop.stocktaking
PREWHERE date_time >= today() - 30      -- prunes partitions before other columns are read
WHERE source = 'shop'
GROUP BY product_id

Never SELECT *. ClickHouse is columnar: the columns you name are exactly what comes off disk. SELECT * on a wide fact table can be hundreds of gigabytes.

2. Look before you query

1. Structure first, query second. DESCRIBE, or better the system.tables query above — the sorting key is the list of columns that are cheap to filter on.
2. Try it on one day with LIMIT 100. Confirm the columns are what you think and the join does not multiply rows. Only then widen the period.
3. Estimate before running anything heavy: EXPLAIN ESTIMATE SELECT ... shows the parts and rows to be read.
4. When you hand over the result, state the period and the deduplication method. "12,340 orders" without "for 2026-08-01..08-26, deduplicated with argMax on product_id" is a useless number.

Where the biggest tables are, on a server you do not know:

SELECT database || '.' || name AS t, engine,
       formatReadableSize(total_bytes) AS size,
       formatReadableQuantity(total_rows) AS rows
FROM system.tables
WHERE database NOT IN ('system', 'information_schema', 'INFORMATION_SCHEMA')
ORDER BY total_bytes DESC LIMIT 20

Worth doing once per server and writing down in the project's CLAUDE.md — and worth feeding into ~/.config/clickhouse-kit/heavy_tables.json, which is what makes the ch-guard hook able to warn you.

3. What else is easy to get wrong

• Joins with a big table on the right. ClickHouse loads the right-hand side into memory in full. Big table on the left; on the right, a dictionary or a filtered subquery.
• dictGet instead of joining a lookup table, when a dictionary exists: dictGet('shop.dict_products', 'category_id', product_id).
• Different servers are different servers. In a cluster of several ClickHouse installations, the same table name may exist on one and not on another, with different grants. Do not assume replicas.
• Backup twins. Names like orders_backup, prices_before_migration_42 are pre-migration copies, not live data. Read the full name before counting on it.
• MCP output is truncated and the read-only user usually has row and memory limits. For real extracts use a driver in the notebook; MCP is for exploration.
• Mutations are not transactions. ALTER TABLE ... UPDATE/DELETE is asynchronous and rewrites parts; it is not a substitute for an INSERT with the right partition.

4. The guard hook

If this plugin's hooks are installed, a query that trips one of the rules above raises a question before it runs rather than after. It is ask, never deny — you can always continue. When the flagged pattern is deliberate, make the first line of the query:

-- ch-guard: ok comparing against last month's finance report, duplicates are expected

Bypasses are appended to ~/.config/clickhouse-kit/ch-guard.log, so the reason survives.

5. Personal data

A query that legitimately touches customer tables will return names, addresses and phone numbers. The pii_guard hook flags that in the output. Aggregate or mask before those rows reach an article, a ticket, a wiki page or a chat message — the guard adds a note, it cannot undo a copy-paste.