clickhouse

# ClickHouse Database Expert

Comprehensive ClickHouse knowledge base for working with high-performance columnar OLAP databases.

## When to Invoke This Skill

Use this skill when:
- Designing ClickHouse schemas (tables, partitions, ORDER BY)
- Choosing table engines (MergeTree family decision tree)
- Writing and optimizing ClickHouse SQL queries
- Managing ClickHouse clusters (replication, sharding)
- Debugging query performance or merge issues
- Setting up backups and monitoring
- Integrating ClickHouse with Kafka, S3, or other systems
- Operating ClickHouse on Kubernetes

## What is ClickHouse?

ClickHouse is a columnar OLAP database designed for real-time analytics on large datasets.

**Key Characteristics:**
- **Columnar storage**: Read only needed columns (10-100x faster than row stores for analytical queries)
- **MergeTree engine family**: Automatic background merges for data organization
- **SQL dialect with extensions**: Arrays, tuples, lambdas, specialized functions
- **Append-first design**: Optimized for high-volume inserts, not point updates

## Golden Rules

1. **Always use MergeTree** (except tiny dimensions → Memory engine)
2. **Sort key = query filter**: ORDER BY defines data layout on disk
3. **Partition by time**: For TTL and efficient DROP PARTITION operations
4. **Avoid mutations**: Use INSERT + new data instead of UPDATE/DELETE
5. **Monitor merges**: Background merges impact performance significantly

## Quick Start Examples

### Minimal Working Schema

```sql
-- Basic events table with best practices
CREATE TABLE events (
    timestamp DateTime,
    user_id UInt32,
    event_type LowCardinality(String),
    session_id UUID,
    metadata String,
    revenue Decimal(18, 2) DEFAULT 0
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)  -- Monthly partitions
ORDER BY (user_id, timestamp)     -- Data layout matches query pattern
SETTINGS index_granularity = 8192;
```

### Common Query Patterns

```sql
-- Effective time range filter
SELECT * FRO