clickhouse-patternslisted
Install: claude install-skill Mattakushi432/Claude-Code-Skills-Custom-DevTools-Pack
# ClickHouse Patterns
## MergeTree Engines
```sql
-- ReplacingMergeTree — deduplicate on merge
CREATE TABLE events (
event_date Date,
user_id UInt64,
event_type String,
properties String, -- JSON as string
created_at DateTime DEFAULT now(),
version UInt64 DEFAULT toUnixTimestamp(now())
) ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, event_type);
-- AggregatingMergeTree — pre-aggregate on merge
CREATE TABLE page_views_agg (
date Date,
page String,
views AggregateFunction(count),
uniq_users AggregateFunction(uniq, UInt64)
) ENGINE = AggregatingMergeTree()
ORDER BY (date, page);
-- SummingMergeTree — sum numeric columns on merge
CREATE TABLE revenue_daily (
date Date,
user_id UInt64,
revenue Decimal(18, 2)
) ENGINE = SummingMergeTree(revenue)
ORDER BY (date, user_id);
```
## Materialized Views
```sql
-- Real-time aggregation pipeline
CREATE MATERIALIZED VIEW hourly_stats
ENGINE = SummingMergeTree()
ORDER BY (hour, page)
AS SELECT
toStartOfHour(created_at) AS hour,
page,
count() AS views,
uniqExact(user_id) AS unique_users
FROM page_events
GROUP BY hour, page;
-- Populate from existing data
INSERT INTO hourly_stats
SELECT toStartOfHour(created_at) AS hour, page,
count(), uniqExact(user_id)
FROM page_events GROUP BY hour, page;
```
## Dictionaries (Key-Value Lookup)
```sql
-- Flat dictionary from PostgreSQL
CREATE DICTIONARY user_dict (