clickhouse-iolisted
Install: claude install-skill Izangi2714/claude-code-python-stack
# ClickHouse Analytics Patterns
ClickHouse-specific patterns for high-performance analytics and data engineering.
## When to Activate
- Designing ClickHouse table schemas (MergeTree engine selection)
- Writing analytical queries (aggregations, window functions, joins)
- Optimizing query performance (partition pruning, projections, materialized views)
- Ingesting large volumes of data (batch inserts, Kafka integration)
- Implementing real-time dashboards or time-series analytics
## Table Design Patterns
### MergeTree Engine (Most Common)
```sql
CREATE TABLE events_analytics (
date Date,
event_id String,
user_id String,
event_type String,
value Float64,
created_at DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, user_id, event_type)
SETTINGS index_granularity = 8192;
```
### ReplacingMergeTree (Deduplication)
```sql
CREATE TABLE user_events (
event_id String,
user_id String,
event_type String,
timestamp DateTime,
properties String
) ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, event_id, timestamp);
```
### AggregatingMergeTree (Pre-aggregation)
```sql
CREATE TABLE stats_hourly (
hour DateTime,
dimension String,
total_value AggregateFunction(sum, Float64),
total_count AggregateFunction(count, UInt32),
unique_users AggregateFunction(uniq, String)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, dimension);
```
## P