# Ch Query

> Query ClickHouse without getting an inflated number or melting a production node: deduplication on ReplacingMergeTree, partition pruning with PREWHERE, column pruning, join order, and how to explore an unfamiliar schema first. Use when asked to "count this from the data", "how many orders/sales/events", "pull that out of ClickHouse", "write a query", "what tables are there", "check this hypothesis against the data", "why is this query hanging", "optimise this query", and before any analysis that starts with "how many do we have". Not for designing tables and migrations, not for A/B test analysis.

- Skill: `viderstar/ch-query` (Agent Skill)
- Install (CLI): `npx skillmds@latest add viderstar/ch-query`
- Raw SKILL.md: https://api.skillmd.com/api/skills/viderstar/ch-query/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: ViderStar (https://skillmd.com/u/viderstar)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/viderstar/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:

```sql
-- 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:

```sql
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`:

```sql
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:

```sql
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:

```sql
-- 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.