ClickHouse 实时分析引擎入门与优化

# ClickHouse 实时分析引擎入门与优化 ## 为什么选择 ClickHouse ClickHouse 是列式存储的 OLAP 数据库，单机即可实现亿级数据的秒级查询，非常适合日志分析、用户行为分析和实时报表。 ## 建表与引擎选择 ```sql -- 典型的事件分析表 CREATE TABLE events ( event_date Date, event_time DateTime, user_id UInt64, event_type LowCardinality(String), page_url String, country LowCardinality(String), device LowCardinality(String), duration_ms UInt32, properties String -- JSON 字符串 ) ENGINE = MergeTree() PARTITION BY toYYYYMM(event_date) ORDER BY (event_type, user_id, event_time) TTL event_date + INTERVAL 6 MONTH SETTINGS index_granularity = 8192; ``` ## 物化视图实时聚合 ```sql -- 每小时 UV/PV 聚合视图 CREATE MATERIALIZED VIEW hourly_stats ENGINE = SummingMergeTree() PARTITION BY toYYYYMM(hour) ORDER BY (hour, event_type, country) AS SELECT toStartOfHour(event_time) AS hour, event_type, country, count() AS pv, uniqExact(user_id) AS uv, avg(duration_ms) AS avg_duration FROM events GROUP BY hour, event_type, country; -- 查询时自动使用物化视图 SELECT hour, sum(pv) as total_pv, sum(uv) as total_uv FROM hourly_stats WHERE hour >= '2024-01-01' GROUP BY hour ORDER BY hour; ``` ## 查询优化技巧 ```sql -- 使用 PREWHERE 替代 WHERE（列式存储优化） SELECT user_id, event_type, event_time FROM events PREWHERE event_date = '2024-06-15' WHERE event_type = 'page_view' LIMIT 100; -- 使用 approximate 函数加速 SELECT uniqHLL12(user_id) AS approx_uv, -- 近似 UV，误差 = today() - 7; -- 避免 SELECT * -- 列式存储中，查询的列越少越快 ``` ## 数据导入 ```bash # 从 CSV 批量导入 clickhouse-client --query="INSERT INTO events FORMAT CSVWithNames" < events.csv # 从 Kafka 实时消费 CREATE TABLE events_queue ( data String ) ENGINE = Kafka() SETTINGS kafka_broker_list = 'kafka:9092', kafka_topic_list = 'events', kafka_group_name = 'clickhouse_consumer', kafka_format = 'JSONEachRow'; ``` ClickHouse 的写入性能也非常出色，单机每秒可写入数百万行，配合 Buffer 引擎可以进一步优化小批量写入场景。