clickhouse

ClickHouse OLAP AI Skill Guide

Overview & Engine Architecture

ClickHouse is a columnar OLAP DBMS optimized for high-ingest analytics. MergeTree-family engines store data sorted by primary key; partitions prune scans; background merges compact parts. Agents design ORDER BY for filter/range patterns, avoid finalizing huge SELECT *, and prefer batch inserts over tiny single-row writes.

Insert batches -> parts on disk
      -> MergeTree merges
      -> SELECT with partition + primary-key pruning

When to use this skill

• Event/metrics analytics at high cardinality and volume
• Real-time-ish dashboards over wide denormalized facts
• Replacing slower row-store aggregations for append-heavy data

Operational directives

1. Choose ORDER BY for the most selective filters and ranges you actually query.
2. Partition by time (e.g. month) - not by high-cardinality ids.
3. Insert in large batches; tiny inserts create part storms.
4. Use FINAL sparingly (ReplacingMergeTree) - prefer dedupe in ETL or argMax.
5. Set quotas/timeouts for ad-hoc users on shared clusters.

Table + query example

CREATE TABLE events.page_views
(
  event_date Date,
  event_time DateTime,
  user_id UInt64,
  path LowCardinality(String),
  duration_ms UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (path, user_id, event_time)
TTL event_date + INTERVAL 180 DAY;

INSERT INTO events.page_views
SELECT * FROM input('event_date Date, event_time DateTime, user_id UInt64, path String, duration_ms UInt32')
FORMAT Parquet;

SELECT path, count() AS views, avg(duration_ms)
FROM events.page_views
WHERE event_date >= today() - 7 AND path = '/pricing'
GROUP BY path;

Useful introspection

SHOW CREATE TABLE events.page_views;
SELECT * FROM system.query_log ORDER BY event_time DESC LIMIT 20;
EXPLAIN indexes = 1
SELECT count() FROM events.page_views WHERE path = '/pricing';

Common failures

Symptom	Cause	Fix
Too many parts	small inserts / bad partitions	batch; fix PARTITION BY
Full scan	ORDER BY mismatch	rewrite order; projections
Memory limit	huge GROUP BY	approx functions; limit cardinality
Mutation lag	heavy ALTER UPDATE/DELETE	redesign for append; lightweight deletes carefully