ClickHouse

Columnar OLAP analytics with ClickHouse.

Quick Start

docker run -p 8123:8123 -d clickhouse/clickhouse-server
clickhouse-client

When to Use

• High-volume analytics and reporting
• Aggregations over large event datasets
• Timeseries and log analysis
• Dashboards and BI backends

Best Practices

Schema

• Use columnar MergeTree engines
• Choose the right engine (MergeTree, Replacing, Summing)
• Use LowCardinality for low-cardinality strings
• Pick types deliberately (DateTime, UInt, Float64)

Partitioning & Order

• Partition by time for retention and scans
• Set ORDER BY to match filter/sort patterns
• Use TTL for data lifecycle
• Keep partitions balanced in size

Querying

• Aggregate with GROUP BY; use materialized views
• Filter early; avoid SELECT * on wide tables
• Use sampling for very large results
• Leverage `argMax`, `uniq`, and `quantile` functions

Operations

• Monitor disk, CPU, and query performance
• Use `system.query_log` for diagnostics
• Back up parts and metadata
• Distribute with Distributed tables when needed

Dependencies

docker run -p 8123:8123 -d clickhouse/clickhouse-server
# Python: pip install clickhouse-connect

Examples

-- Columnar table with TTL and partition
CREATE TABLE events (
  ts DateTime,
  user_id UInt64,
  action LowCardinality(String),
  value Float64
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (user_id, ts)
TTL toDateTime(ts) + INTERVAL 90 DAY;

-- Aggregation query
SELECT
  toDate(ts) AS day,
  uniq(user_id) AS active_users,
  sum(value) AS total
FROM events
WHERE ts >= now() - INTERVAL 7 DAY
GROUP BY day
ORDER BY day;

-- Quantiles and argMax
SELECT
  quantile(0.95)(value) AS p95,
  argMax(action, ts) AS last_action
FROM events;

import clickhouse_connect

client = clickhouse_connect.get_client(host="localhost", port=8123)
rows = client.query("SELECT count() FROM events").result_rows
print(rows)

Step-by-Step

1. Choose the MergeTree engine and table layout.

2. Set partitioning and ORDER BY for access patterns.

3. Add TTL for retention.

4. Write aggregations with the right functions.

5. Add materialized views for hot aggregations.

6. Monitor query_log and disk.

7. Tune max_threads and memory.

8. Back up and plan scaling.

Validation

1. Aggregations return correct results

2. Queries scan minimal partitions

3. TTL removes expired data

4. Query latency within budget at scale

5. Backups restore correctly

Troubleshooting

• Slow queries: check partitions scanned and ORDER BY.
• High disk: adjust TTL and partition size.
• Memory errors: lower max_memory_usage or optimize queries.