ClickHouse Analytics

Extremely fast for analytical queries.

Installation¶

docker run -d --name ch -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server

Table¶

CREATE TABLE events (
    event_date Date, 
    user_id UInt64, 
    event_type String
) ENGINE=MergeTree() 
PARTITION BY toYYYYMM(event_date) 
ORDER BY (event_type,event_date);

Queries¶

SELECT event_type, count(), uniq(user_id) 
FROM events 
WHERE event_date>='2024-01-01' 
GROUP BY event_type 
ORDER BY count() DESC;

• 10-100x faster than PG for analytics
• Columnar format
• 10x compression
• SQL compatible

ClickHouse for Analytics¶

OLTP=PostgreSQL, OLAP=ClickHouse.