ClickHouse 介绍
目录
简介
ClickHouse 是一个用于 联机分析处理(OLAP) 的列式数据库管理系统(DBMS)。它由 Yandex 公司于 2016 年开源,主要用于处理海量数据的快速分析查询。
发展历史
- 2009年:Yandex 开始开发 ClickHouse,用于解决 Metrica 分析系统中的性能瓶颈
- 2016年:ClickHouse 正式开源,采用 Apache 2.0 许可证
- 2021年:ClickHouse, Inc. 成立,成为独立公司运营
- 至今:已成为业界领先的开源 OLAP 数据库之一
核心定位
ClickHouse 专为以下场景设计:
- 宽表分析:处理包含数百列的宽表
- 实时分析:支持亚秒级查询响应
- 高吞吐量:每秒可处理数十亿行数据
- 压缩存储:高效的数据压缩算法
核心特性
1. 列式存储
ClickHouse 采用列式存储架构,数据按列而非按行存储:
优势:
- 减少 I/O:查询时只读取需要的列
- 高效压缩:同类型数据压缩率更高
- 向量化执行:利用 SIMD 指令加速计算
2. 数据压缩
ClickHouse 支持多种压缩算法:
| 压缩算法 | 压缩率 | 解压速度 | 适用场景 |
|---|---|---|---|
| LZ4 | 低 | 最快 | 实时性要求高 |
| ZSTD | 中 | 快 | 通用场景 |
| ZSTD(1-22) | 高 | 中等 | 存储敏感场景 |
| NONE | 无 | 最快 | 已压缩数据 |
3. 索引机制
ClickHouse 使用稀疏索引和二级索引:
4. 向量化执行
ClickHouse 使用向量化执行引擎,利用 CPU 的 SIMD(单指令多数据)指令集:
架构设计
整体架构
查询处理流程
表引擎架构
数据分区架构
数据模型
基本数据类型
数值类型
| 类型 | 大小 | 范围 | 说明 |
|---|---|---|---|
| UInt8 | 1字节 | 0-255 | 无符号8位整数 |
| UInt16 | 2字节 | 0-65535 | 无符号16位整数 |
| UInt32 | 4字节 | 0-4294967295 | 无符号32位整数 |
| UInt64 | 8字节 | 0-18446744073709551615 | 无符号64位整数 |
| Int8 | 1字节 | -128-127 | 有符号8位整数 |
| Int16 | 2字节 | -32768-32767 | 有符号16位整数 |
| Int32 | 4字节 | -2147483648-2147483647 | 有符号32位整数 |
| Int64 | 8字节 | -9223372036854775808-9223372036854775807 | 有符号64位整数 |
| Float32 | 4字节 | - | 单精度浮点数 |
| Float64 | 8字节 | - | 双精度浮点数 |
| Decimal | 可变 | - | 高精度小数 |
字符串类型
| 类型 | 说明 |
|---|---|
| String | 可变长度字符串 |
| FixedString(N) | 固定长度N的字符串 |
| UUID | UUID类型 |
时间类型
| 类型 | 说明 |
|---|---|
| Date | 日期,精度到天 |
| DateTime | 日期时间,精度到秒 |
| DateTime64 | 日期时间,支持亚秒精度 |
特殊类型
| 类型 | 说明 |
|---|---|
| Enum8 | 8位枚举 |
| Enum16 | 16位枚举 |
| Array(T) | 数组类型 |
| Tuple(T1, T2, …) | 元组类型 |
| Map(K, V) | 键值对类型 |
| Nested | 嵌套结构 |
| Nullable(T) | 可空类型 |
| LowCardinality(T) | 低基数类型优化 |
表结构设计示例
-- 创建用户行为分析表
CREATE TABLE user_events
(
-- 主键和分区键
event_date Date,
event_time DateTime,
-- 用户信息
user_id UInt64,
user_type Enum8('guest' = 0, 'registered' = 1, 'vip' = 2),
-- 事件信息
event_type String,
event_category LowCardinality(String),
page_url String,
referrer_url String,
-- 设备信息
device_type Enum8('desktop' = 0, 'mobile' = 1, 'tablet' = 2),
os_name LowCardinality(String),
browser_name LowCardinality(String),
-- 位置信息
country LowCardinality(String),
city LowCardinality(String),
-- 业务指标
session_id String,
duration_seconds UInt32,
page_views UInt16,
-- 数组类型
tags Array(String),
product_ids Array(UInt64)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, event_time)
SETTINGS index_granularity = 8192;
表引擎
MergeTree 系列
MergeTree 是 ClickHouse 中最核心的表引擎系列,支持数据分区、索引、副本等功能。
1. MergeTree
基础表引擎,支持数据分区和索引。
CREATE TABLE orders
(
order_id UInt64,
user_id UInt64,
order_date Date,
order_time DateTime,
product_id UInt64,
quantity UInt32,
price Decimal(18, 2),
status LowCardinality(String)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, user_id, order_id)
SETTINGS index_granularity = 8192;
参数说明:
PARTITION BY:分区键,按月分区ORDER BY:排序键,决定数据在分区内的排序方式PRIMARY KEY:主键,默认与 ORDER BY 相同SETTINGS index_granularity:索引粒度,默认8192行
2. ReplacingMergeTree
支持去重的 MergeTree,根据 ORDER BY 键进行去重。
CREATE TABLE orders_unique
(
order_id UInt64,
user_id UInt64,
order_date Date,
order_time DateTime,
product_id UInt64,
quantity UInt32,
price Decimal(18, 2),
status LowCardinality(String),
version UInt64
)
ENGINE = ReplacingMergeTree(version)
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_id)
SETTINGS index_granularity = 8192;
特点:
- 根据 ORDER BY 键去重
- 保留 version 值最大的记录
- 去重在合并时执行,不是实时的
3. SummingMergeTree
预聚合表引擎,自动对数值列进行求和。
CREATE TABLE daily_sales
(
date Date,
product_id UInt64,
category LowCardinality(String),
region LowCardinality(String),
sales_amount Decimal(18, 2),
sales_count UInt32,
order_count UInt32
)
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, product_id, category, region)
SETTINGS index_granularity = 8192;
特点:
- 自动对非主键的数值列求和
- 适合预聚合场景
4. AggregatingMergeTree
支持自定义聚合函数的表引擎。
CREATE TABLE user_daily_stats
(
date Date,
user_id UInt64,
-- 聚合状态列
page_views AggregateFunction(sum, UInt64),
unique_pages AggregateFunction(uniq, String),
session_duration AggregateFunction(avg, UInt64)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, user_id)
SETTINGS index_granularity = 8192;
-- 插入聚合数据
INSERT INTO user_daily_stats
SELECT
toDate(event_time) as date,
user_id,
sumState(page_views) as page_views,
uniqState(page_url) as unique_pages,
avgState(duration_seconds) as session_duration
FROM user_events
GROUP BY date, user_id;
-- 查询聚合结果
SELECT
date,
user_id,
sumMerge(page_views) as page_views,
uniqMerge(unique_pages) as unique_pages,
avgMerge(session_duration) as avg_session_duration
FROM user_daily_stats
GROUP BY date, user_id;
5. CollapsingMergeTree
通过 sign 字段实现增量更新和删除。
CREATE TABLE orders_collapsing
(
order_id UInt64,
user_id UInt64,
order_date Date,
product_id UInt64,
quantity UInt32,
price Decimal(18, 2),
sign Int8 -- 1表示插入,-1表示删除
)
ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_id)
SETTINGS index_granularity = 8192;
使用方式:
- 插入:sign = 1
- 删除:插入相同主键的记录,sign = -1
- 更新:先删除(sign = -1),再插入(sign = 1)
6. VersionedCollapsingMergeTree
CollapsingMergeTree 的改进版,支持并发写入。
CREATE TABLE orders_versioned
(
order_id UInt64,
user_id UInt64,
order_date Date,
product_id UInt64,
quantity UInt32,
price Decimal(18, 2),
sign Int8,
version UInt64
)
ENGINE = VersionedCollapsingMergeTree(sign, version)
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_id)
SETTINGS index_granularity = 8192;
Log 系列
Log 系列引擎适用于临时数据和小批量数据写入。
-- TinyLog:最简单的引擎,无索引
CREATE TABLE events_tinylog
(
event_time DateTime,
event_type String,
user_id UInt64
)
ENGINE = TinyLog();
-- StripeLog:支持并发读取
CREATE TABLE events_stripelog
(
event_time DateTime,
event_type String,
user_id UInt64
)
ENGINE = StripeLog();
-- Log:支持并发写入
CREATE TABLE events_log
(
event_time DateTime,
event_type String,
user_id UInt64
)
ENGINE = Log();
集成系列
Kafka 引擎
CREATE TABLE kafka_events
(
event_time DateTime,
event_type String,
user_id UInt64,
event_data String
)
ENGINE = Kafka()
SETTINGS
kafka_broker_list = 'kafka1:9092,kafka2:9092',
kafka_topic_list = 'events',
kafka_group_name = 'clickhouse_consumer',
kafka_format = 'JSONEachRow',
kafka_num_consumers = 3;
-- 创建物化视图实时消费Kafka数据
CREATE TABLE events
(
event_time DateTime,
event_type String,
user_id UInt64,
event_data String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);
CREATE MATERIALIZED VIEW events_consumer TO events
AS SELECT * FROM kafka_events;
MySQL 引擎
CREATE TABLE mysql_users
(
id UInt32,
name String,
email String,
created_at DateTime
)
ENGINE = MySQL('mysql-host:3306', 'database', 'users', 'user', 'password');
-- 查询MySQL数据
SELECT * FROM mysql_users WHERE id = 123;
S3 引擎
CREATE TABLE s3_logs
(
timestamp DateTime,
level String,
message String
)
ENGINE = S3('https://bucket.s3.amazonaws.com/logs/*.gz', 'CSV', 'timestamp DateTime, level String, message String');
特殊系列
物化视图
-- 创建物化视图
CREATE MATERIALIZED VIEW daily_sales_mv
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, product_id)
AS SELECT
order_date,
product_id,
sumState(quantity) as total_quantity,
sumState(price * quantity) as total_amount
FROM orders
GROUP BY order_date, product_id;
-- 查询物化视图
SELECT
order_date,
product_id,
sumMerge(total_quantity) as total_quantity,
sumMerge(total_amount) as total_amount
FROM daily_sales_mv
GROUP BY order_date, product_id;
字典表
-- 创建字典
CREATE DICTIONARY user_dict
(
user_id UInt64,
user_name String,
user_level UInt8,
register_date Date
)
PRIMARY KEY user_id
SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 USER 'default' PASSWORD '' DB 'default' TABLE 'users'))
LIFETIME(MIN 300 MAX 600)
LAYOUT(HASHED());
-- 使用字典
SELECT
event_time,
user_id,
dictGet('user_dict', 'user_name', user_id) as user_name,
dictGet('user_dict', 'user_level', user_id) as user_level
FROM user_events
LIMIT 10;
查询语言
ClickHouse 使用类 SQL 查询语言,支持大多数标准 SQL 语法。
基本查询
-- 简单查询
SELECT * FROM orders WHERE order_date >= '2024-01-01' LIMIT 10;
-- 聚合查询
SELECT
product_id,
count() as order_count,
sum(quantity) as total_quantity,
sum(price * quantity) as total_amount,
avg(price) as avg_price
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY product_id
ORDER BY total_amount DESC
LIMIT 100;
-- HAVING 子句
SELECT
user_id,
count() as order_count,
sum(price * quantity) as total_spent
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY user_id
HAVING total_spent > 10000
ORDER BY total_spent DESC;
JOIN 操作
-- INNER JOIN
SELECT
o.order_id,
o.user_id,
u.user_name,
o.product_id,
o.quantity,
o.price
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
WHERE o.order_date >= '2024-01-01';
-- LEFT JOIN
SELECT
u.user_id,
u.user_name,
count(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
AND o.order_date >= '2024-01-01'
GROUP BY u.user_id, u.user_name;
-- RIGHT JOIN
SELECT
p.product_id,
p.product_name,
count(o.order_id) as order_count
FROM orders o
RIGHT JOIN products p ON o.product_id = p.product_id
AND o.order_date >= '2024-01-01'
GROUP BY p.product_id, p.product_name;
窗口函数
-- 计算用户累计消费
SELECT
user_id,
order_date,
order_id,
amount,
sum(amount) OVER (
PARTITION BY user_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as cumulative_amount
FROM orders
WHERE order_date >= '2024-01-01';
-- 计算排名
SELECT
user_id,
total_spent,
rank() OVER (ORDER BY total_spent DESC) as rank_by_spent,
dense_rank() OVER (ORDER BY total_spent DESC) as dense_rank_by_spent
FROM (
SELECT
user_id,
sum(price * quantity) as total_spent
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY user_id
);
数组操作
-- 数组创建和操作
SELECT
[1, 2, 3, 4, 5] as arr1,
arrayJoin([1, 2, 3]) as value,
arraySum([1, 2, 3, 4, 5]) as sum,
arrayAvg([1, 2, 3, 4, 5]) as avg,
arrayFilter(x -> x > 2, [1, 2, 3, 4, 5]) as filtered,
arrayMap(x -> x * 2, [1, 2, 3, 4, 5]) as mapped,
has([1, 2, 3, 4, 5], 3) as contains,
indexOf([1, 2, 3, 4, 5], 3) as index;
-- 数组聚合
SELECT
user_id,
groupArray(event_type) as event_types,
groupUniqArray(event_type) as unique_event_types,
groupArray(order_date) as order_dates
FROM user_events
GROUP BY user_id;
条件表达式
-- CASE WHEN
SELECT
order_id,
quantity,
CASE
WHEN quantity < 10 THEN 'small'
WHEN quantity < 100 THEN 'medium'
ELSE 'large'
END as order_size
FROM orders;
-- if 函数
SELECT
order_id,
if(quantity > 100, 'bulk', 'regular') as order_type
FROM orders;
-- multiIf 函数
SELECT
order_id,
multiIf(
quantity > 1000, 'enterprise',
quantity > 100, 'business',
'personal'
) as customer_type
FROM orders;
时间函数
-- 时间转换和计算
SELECT
event_time,
toDate(event_time) as date,
toYear(event_time) as year,
toMonth(event_time) as month,
toDayOfMonth(event_time) as day,
toHour(event_time) as hour,
toMinute(event_time) as minute,
toStartOfDay(event_time) as day_start,
toStartOfWeek(event_time) as week_start,
toStartOfMonth(event_time) as month_start,
toStartOfQuarter(event_time) as quarter_start,
toStartOfYear(event_time) as year_start,
dateDiff('day', toDateTime('2024-01-01'), event_time) as days_since,
addDays(event_time, 7) as next_week,
subtractDays(event_time, 7) as last_week
FROM user_events
LIMIT 10;
集群与复制
集群架构
副本配置
<!-- config.xml -->
<clickhouse>
<zookeeper>
<node>
<host>zk1</host>
<port>2181</port>
</node>
<node>
<host>zk2</host>
<port>2181</port>
</node>
<node>
<host>zk3</host>
<port>2181</port>
</node>
</zookeeper>
<remote_servers>
<my_cluster>
<shard>
<replica>
<host>node1</host>
<port>9000</port>
</replica>
<replica>
<host>node2</host>
<port>9000</port>
</replica>
</shard>
<shard>
<replica>
<host>node3</host>
<port>9000</port>
</replica>
<replica>
<host>node4</host>
<port>9000</port>
</replica>
</shard>
</my_cluster>
</remote_servers>
</clickhouse>
分布式表
-- 创建本地表
CREATE TABLE orders_local ON CLUSTER my_cluster
(
order_id UInt64,
user_id UInt64,
order_date Date,
product_id UInt64,
quantity UInt32,
price Decimal(18, 2)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, order_id)
SETTINGS index_granularity = 8192;
-- 创建分布式表
CREATE TABLE orders_distributed ON CLUSTER my_cluster
(
order_id UInt64,
user_id UInt64,
order_date Date,
product_id UInt64,
quantity UInt32,
price Decimal(18, 2)
)
ENGINE = Distributed(my_cluster, default, orders_local, rand());
-- 查询分布式表
SELECT
product_id,
count() as order_count,
sum(quantity) as total_quantity,
sum(price * quantity) as total_amount
FROM orders_distributed
WHERE order_date >= '2024-01-01'
GROUP BY product_id
ORDER BY total_amount DESC
LIMIT 100;
分片策略
ClickHouse 支持多种分片策略:
-- 1. 随机分片
ENGINE = Distributed(cluster, database, table, rand());
-- 2. 哈希分片
ENGINE = Distributed(cluster, database, table, intHash32(user_id));
-- 3. 跳数一致性哈希分片
ENGINE = Distributed(cluster, database, table, jumpConsistentHash(user_id, 2));
-- 4. 自定义分片
ENGINE = Distributed(cluster, database, table, sharding_key);
数据复制流程
性能优化
查询优化
1. 使用合适的索引
-- 好的索引设计
CREATE TABLE events
(
event_date Date,
event_time DateTime,
user_id UInt64,
event_type String,
page_url String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date) -- 按月分区
ORDER BY (event_date, user_id, event_time) -- 合理的排序键
PRIMARY KEY (event_date, user_id); -- 主键
-- 利用索引的查询
SELECT * FROM events
WHERE event_date = '2024-01-15'
AND user_id = 12345
AND event_time BETWEEN '2024-01-15 10:00:00' AND '2024-01-15 11:00:00';
2. 避免全表扫描
-- 避免:全表扫描
SELECT * FROM events WHERE event_type = 'page_view';
-- 推荐:先过滤再聚合
SELECT
event_type,
count() as cnt
FROM events
WHERE event_date >= '2024-01-01'
GROUP BY event_type;
3. 使用物化视图
-- 预聚合物化视图
CREATE MATERIALIZED VIEW hourly_stats_mv
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_hour)
ORDER BY (event_hour, event_type)
AS SELECT
toStartOfHour(event_time) as event_hour,
event_type,
count() as event_count
FROM events
GROUP BY event_hour, event_type;
-- 查询物化视图
SELECT
event_hour,
event_type,
sum(event_count) as total_count
FROM hourly_stats_mv
WHERE event_hour >= toDateTime('2024-01-01 00:00:00')
GROUP BY event_hour, event_type;
4. 使用 PREWHERE
-- PREWHERE 先过滤,再读取其他列
SELECT
user_id,
event_type,
page_url
FROM events
PREWHERE event_date = '2024-01-15'
WHERE event_type = 'page_view';
存储优化
1. 选择合适的压缩算法
-- 高压缩率场景
CREATE TABLE compressed_data
(
date Date,
data String
)
ENGINE = MergeTree()
ORDER BY date
SETTINGS
compression_codec = 'ZSTD(19)'; -- 高压缩率
-- 实时性要求高的场景
CREATE TABLE realtime_data
(
timestamp DateTime,
metric_name String,
metric_value Float64
)
ENGINE = MergeTree()
ORDER BY (timestamp, metric_name)
SETTINGS
compression_codec = 'LZ4'; -- 快速压缩
2. 使用 LowCardinality 类型
-- 优化低基数列
CREATE TABLE events_optimized
(
event_date Date,
event_time DateTime,
user_id UInt64,
event_type LowCardinality(String), -- 低基数优化
country LowCardinality(String), -- 低基数优化
device_type LowCardinality(String) -- 低基数优化
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, event_time);
3. 合理设置分区
-- 按月分区
PARTITION BY toYYYYMM(event_date)
-- 按天分区(数据量大时)
PARTITION BY toDate(event_date)
-- 按业务维度分区
PARTITION BY (toYYYYMM(event_date), event_type)
写入优化
1. 批量写入
-- 推荐:批量插入
INSERT INTO events VALUES
('2024-01-15 10:00:00', 'page_view', 12345, '/home'),
('2024-01-15 10:00:01', 'click', 12345, '/product/123'),
('2024-01-15 10:00:02', 'page_view', 67890, '/about');
-- 推荐:从文件批量导入
INSERT INTO events FORMAT CSVWithNames
FROM 'events.csv';
2. 使用 Buffer 表
-- 创建 Buffer 表
CREATE TABLE events_buffer
(
event_time DateTime,
event_type String,
user_id UInt64,
page_url String
)
ENGINE = Buffer(default, events, 16, 10, 100, 10000, 1000000, 10000000, 100000000);
-- 数据会自动刷新到目标表
3. 异步插入
-- 启用异步插入
SET async_insert = 1;
SET wait_for_async_insert = 0;
INSERT INTO events VALUES ('2024-01-15 10:00:00', 'page_view', 12345, '/home');
配置优化
<!-- config.xml -->
<clickhouse>
<!-- 内存配置 -->
<max_memory_usage>10000000000</max_memory_usage>
<max_memory_usage_for_user>8000000000</max_memory_usage_for_user>
<!-- 并发配置 -->
<max_concurrent_queries>100</max_concurrent_queries>
<max_concurrent_queries_for_user>50</max_concurrent_queries_for_user>
<!-- 线程配置 -->
<background_pool_size>16</background_pool_size>
<background_schedule_pool_size>16</background_schedule_pool_size>
<!-- 合并配置 -->
<max_bytes_to_merge_at_max_space_in_pool>1048576000</max_bytes_to_merge_at_max_space_in_pool>
<number_of_free_entries_in_pool_to_lower_max_size_of_merge>8</number_of_free_entries_in_pool_to_lower_max_size_of_merge>
<!-- 缓存配置 -->
<mark_cache_size>5368709120</mark_cache_size>
<uncompressed_cache_size>8589934592</uncompressed_cache_size>
</clickhouse>
应用场景
1. 用户行为分析
-- 用户漏斗分析
WITH funnel_steps AS (
SELECT
user_id,
groupArray(event_type) as events,
groupArray(event_time) as times
FROM user_events
WHERE event_date >= '2024-01-01'
GROUP BY user_id
HAVING length(events) >= 4
)
SELECT
countIf(has(events, 'view_home')) as step1_home,
countIf(has(events, 'view_product')) as step2_product,
countIf(has(events, 'add_to_cart')) as step3_cart,
countIf(has(events, 'purchase')) as step4_purchase
FROM funnel_steps;
2. 实时监控告警
-- 异常检测
SELECT
toStartOfMinute(event_time) as minute,
count() as event_count,
avg(event_count) OVER (
ORDER BY minute
ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
) as avg_count,
event_count / avg(event_count) OVER (
ORDER BY minute
ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
) as ratio
FROM user_events
WHERE event_date >= today()
GROUP BY minute
HAVING ratio > 2.0
ORDER BY minute;
3. A/B 测试分析
-- A/B 测试效果对比
SELECT
experiment_group,
count(DISTINCT user_id) as user_count,
countIf(event_type = 'purchase') as purchase_count,
purchase_count / user_count as conversion_rate,
avg(purchase_amount) as avg_purchase_amount
FROM (
SELECT
u.user_id,
u.experiment_group,
e.event_type,
o.amount as purchase_amount
FROM users u
LEFT JOIN user_events e ON u.user_id = e.user_id
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.experiment_date >= '2024-01-01'
)
GROUP BY experiment_group;
4. 留存分析
-- 用户留存分析
WITH user_cohorts AS (
SELECT
user_id,
toDate(min(event_time)) as cohort_date
FROM user_events
WHERE event_date >= '2024-01-01'
GROUP BY user_id
),
user_activities AS (
SELECT
c.user_id,
c.cohort_date,
toDate(e.event_time) as activity_date,
dateDiff('day', c.cohort_date, toDate(e.event_time)) as day_number
FROM user_cohorts c
INNER JOIN user_events e ON c.user_id = e.user_id
)
SELECT
cohort_date,
count(DISTINCT user_id) as cohort_size,
countIf(day_number = 0) as day0,
countIf(day_number = 1) as day1,
countIf(day_number = 7) as day7,
countIf(day_number = 30) as day30,
round(day1 / cohort_size * 100, 2) as day1_retention,
round(day7 / cohort_size * 100, 2) as day7_retention,
round(day30 / cohort_size * 100, 2) as day30_retention
FROM user_activities
GROUP BY cohort_date
ORDER BY cohort_date;
5. 时间序列分析
-- 时间序列预测(移动平均)
SELECT
date,
revenue,
avg(revenue) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as ma7,
avg(revenue) OVER (
ORDER BY date
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
) as ma30
FROM (
SELECT
toDate(order_date) as date,
sum(price * quantity) as revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY date
)
ORDER BY date;
最佳实践
1. 表设计原则
主键选择
- 选择高基数列:避免使用低基数列作为主键
- 查询友好:主键应该包含常用查询条件
- 数据分布均匀:避免数据倾斜
-- 好的设计
ORDER BY (event_date, user_id, event_time)
-- 不好的设计
ORDER BY (event_type) -- 低基数
ORDER BY (user_id) -- 没有包含时间维度
分区策略
- 按时间分区:最常用的分区方式
- 分区大小适中:每个分区 10-100GB
- 避免过多分区:分区过多影响性能
-- 推荐:按月分区
PARTITION BY toYYYYMM(event_date)
-- 不推荐:按天分区(数据量不大时)
PARTITION BY toDate(event_date)
2. 查询编写规范
使用 LIMIT
-- 好的做法
SELECT * FROM large_table WHERE condition LIMIT 1000;
-- 避免:无限制查询
SELECT * FROM large_table WHERE condition;
避免 SELECT *
-- 好的做法
SELECT user_id, event_type, event_time FROM events;
-- 避免:查询所有列
SELECT * FROM events;
使用合适的聚合函数
-- 好的做法:使用 uniqExact 精确去重
SELECT uniqExact(user_id) FROM events;
-- 好的做法:使用 uniq 近似去重(大数据量)
SELECT uniq(user_id) FROM events;
3. 数据导入最佳实践
使用批量导入
# 使用 clickhouse-client 批量导入
clickhouse-client --query="INSERT INTO events FORMAT CSVWithNames" < events.csv
# 使用 HTTP 接口批量导入
cat events.csv | curl 'http://localhost:8123/?query=INSERT%20INTO%20events%20FORMAT%20CSVWithNames' --data-binary @-
使用 INSERT SELECT
-- 从其他表导入
INSERT INTO events_archive
SELECT * FROM events
WHERE event_date < '2024-01-01';
-- 从外部表导入
INSERT INTO events
SELECT * FROM s3('s3://bucket/events/*.csv', 'CSV', 'event_time DateTime, event_type String, user_id UInt64');
4. 监控与维护
系统表监控
-- 查询当前执行的查询
SELECT * FROM system.processes WHERE query NOT LIKE '%system%';
-- 查看表大小
SELECT
database,
table,
formatReadableSize(sum(bytes)) as size,
sum(rows) as rows
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY size DESC;
-- 查看分区信息
SELECT
partition,
sum(rows) as rows,
formatReadableSize(sum(bytes_on_disk)) as size
FROM system.parts
WHERE table = 'events' AND active
GROUP BY partition
ORDER BY partition DESC;
定期维护
-- 手动触发合并
OPTIMIZE TABLE events PARTITION '202401' FINAL;
-- 删除旧分区
ALTER TABLE events DROP PARTITION '202301';
-- 修改表设置
ALTER TABLE events MODIFY SETTING max_bytes_to_merge_at_once = 1073741824;
5. 安全配置
<!-- users.xml -->
<clickhouse>
<users>
<!-- 只读用户 -->
<readonly>
<password>readonly_password</password>
<networks>
<ip>::/0</ip>
</networks>
<profile>readonly</profile>
<quota>default</quota>
<databases>
<database_name>analytics</database_name>
</databases>
</readonly>
<!-- 管理员用户 -->
<admin>
<password_sha256_hex>...</password_sha256_hex>
<access_management>1</access_management>
<networks>
<ip>10.0.0.0/8</ip>
</networks>
<profile>default</profile>
<quota>default</quota>
</admin>
</users>
<profiles>
<readonly>
<readonly>1</readonly>
<allow_ddl>0</allow_ddl>
</readonly>
</profiles>
</clickhouse>
总结
ClickHouse 是一个高性能的列式 OLAP 数据库,具有以下核心优势:
- 极致性能:列式存储、向量化执行、高效压缩
- 简单易用:类 SQL 查询语言、丰富的表引擎
- 高可用性:支持副本、分片、分布式查询
- 灵活扩展:支持水平扩展、多种存储后端
- 丰富生态:支持多种数据源集成
ClickHouse 适用于以下场景:
- 用户行为分析
- 实时监控告警
- A/B 测试分析
- 留存分析
- 时间序列分析
- 日志分析
- BI 报表
通过合理的设计和优化,ClickHouse 可以在处理海量数据时提供亚秒级的查询响应,是现代数据分析平台的重要组件。
3366



被折叠的 条评论
为什么被折叠?



