在ClickHouse中，表引擎决定了数据如何读取和存储、支持哪些查询以及怎么支持、是否并行数据访问、是否支持索引、是否支持多线程、是否支持数据复制。
本文将通过实际案例一步一步讲述ClickHouse的表引擎，跟着本文步骤一步一步来做吧。
ClickHouse表引擎总共分为四个系列，即：Log系列，MergeTree系列，Integration系列和Special系列，其中还包含两种特殊的引擎Replicated和Distributed。
一、MergeTree系列
1. MergeTree
MergeTree系列是适用于高负载任务的最通用和功能最强大的表引擎，也是官方主推的存储引擎，支持几乎所有ClickHouse核心功能。
MergeTree表引擎主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据TTL等。
MergeTree虽然有主键索引，但是其主要作用是加速查询，并不具有唯一约束语义。
假如有如下建表语句：主键为(id, create_time)，按照id, create_time排序，数据按照 create_time分区，并保留最近3个月
1CREATE TABLE test_merge_tree (
2 id UInt32,
3 name String default null,
4 create_time Date
5) ENGINE = MergeTree()
6 PARTITION BY create_time
7 ORDER BY (id, create_time)
8 PRIMARY KEY (id, create_time)
9 TTL create_time + INTERVAL 3 MONTH
10 SETTINGS index_granularity=8192;
写入几条数据：这里写入几条，其中有几条是主键重复的数据
1insert into test_merge_tree values(1, 'xiaoming','2024-03-05');
2insert into test_merge_tree values(1, 'xiaohua','2024-03-05');
3insert into test_merge_tree values(2, 'xiaomao','2024-03-06');
4insert into test_merge_tree values(2, 'xiaogou','2024-03-06');
查询一下表数据：
1select * from test_merge_tree;
2------------------------------
3┌─id─┬─name─────┬─create_time─┐
4│ 1 │ xiaoming │ 2024-03-05 │
5└────┴──────────┴─────────────┘
6┌─id─┬─name────┬─create_time─┐
7│ 1 │ xiaohua │ 2024-03-05 │
8└────┴─────────┴─────────────┘
9┌─id─┬─name────┬─create_time─┐
10│ 2 │ xiaogou │ 2024-03-06 │
11└────┴─────────┴─────────────┘
12┌─id─┬─name────┬─create_time─┐
13│ 2 │ xiaomao │ 2024-03-06 │
14└────┴─────────┴─────────────┘
可以看到：数据并没有按照主键去重。
由于MergeTree采用类似LSM tree的结构，很多存储层处理逻辑直到Compaction期间才会发生，所以我们手动触发一下compaction。
1optimize table test_merge_tree final;
再来查询一下数据：
1select * from test_merge_tree;
2------------------------------
3┌─id─┬─name────┬─create_time─┐
4│ 2 │ xiaomao │ 2024-03-06 │
5│ 2 │ xiaogou │ 2024-03-06 │
6└────┴─────────┴─────────────┘
7┌─id─┬─name─────┬─create_time─┐
8│ 1 │ xiaoming │ 2024-03-05 │
9│ 1 │ xiaohua │ 2024-03-05 │
10└────┴──────────┴─────────────┘
我们发现仍然有4条数据。所以，可以得出结论：MergeTree虽然有主键索引，但是其主要作用是加速查询，而不具有主键唯一约束的作用。
2. ReplacingMergeTree
为了解决上述提到的MergeTree相同主键无法去重的问题，ClickHouse提供了ReplacingMergeTree引擎，专门用来做去重。
首先来建一张ReplacingMergeTree引擎的表：
1CREATE TABLE test_replacing_merge_tree (
2 id UInt32,
3 name String default null,
4 create_time Date
5) ENGINE = ReplacingMergeTree()
6 PARTITION BY create_time
7 ORDER BY (id, create_time)
8 PRIMARY KEY (id, create_time)
9 TTL create_time + INTERVAL 3 MONTH
10 SETTINGS index_granularity=8192;
写入几条数据：
1insert into test_replacing_merge_tree values(1, 'xiaoming','2024-03-05');
2insert into test_replacing_merge_tree values(1, 'xiaohua','2024-03-05');
3insert into test_replacing_merge_tree values(2, 'xiaomao','2024-03-06');
4insert into test_replacing_merge_tree values(2, 'xiaogou','2024-03-06');
查询一下表数据：
1select * from test_replacing_merge_tree;
2-----------------------------------
3┌─id─┬─name─────┬─create_time─┐
4│ 1 │ xiaoming │ 2024-03-05 │
5└────┴──────────┴─────────────┘
6┌─id─┬─name────┬─create_time─┐
7│ 2 │ xiaogou │ 2024-03-06 │
8└────┴─────────┴─────────────┘
9┌─id─┬─name────┬─create_time─┐
10│ 1 │ xiaohua │ 2024-03-05 │
11└────┴─────────┴─────────────┘
12┌─id─┬─name────┬─create_time─┐
13│ 2 │ xiaomao │ 2024-03-06 │
14└────┴─────────┴─────────────┘
可以看到，数据仍然没有去重，我们再来手动触发一下Compaction：
1optimize table test_replacing_merge_tree final;
再次查询一下数据：
1select * from test_replacing_merge_tree;
2-------------------------------------
3┌─id─┬─name────┬─create_time─┐
4│ 1 │ xiaohua │ 2024-03-05 │
5└────┴─────────┴─────────────┘
6┌─id─┬─name────┬─create_time─┐
7│ 2 │ xiaogou │ 2024-03-06 │
8└────┴─────────┴─────────────┘
这次我们发现确实去重了。但是需要注意以下限制：
① 在没有彻底optimize之前，可能无法达到主键去重的效果，比如部分数据已经被去重，而另外一部分数据仍旧有主键重复；
② 在分布式场景下，相同primary key的数据可能被sharding到不同节点上，不同shard间可能无法去重
③ optimize是后台动作，无法预测在什么时候进行Compaction
④ 数据量大的时候，手动执行optimize在要消耗大量时间
所以，ReplacingMergeTree可以用于数据最终去重，但是无法解决我们查询过程中数据重复的问题。
3. CollapsingMergeTree
对于以上ReplacingMergeTree的问题，ClickHouse提供了CollapsingMergeTree来消除这个限制。
该引擎要求在建表语句中指定一个标记列Sign，这个sign字段名也可以自定义，不需要必须是sign。后台Compaction时会将主键相同、Sign相反的行进行折叠。Sign=1的行称之为状态行，Sign=-1的行称之为取消行。
要新增状态时，写入一行状态行；需要删除状态时，则写入一行取消行。在后台Compaction时，状态行与取消行会自动做折叠（删除）处理。而尚未进行Compaction的数据，状态行与取消行同时存在。
因此为了能够达到主键折叠（删除）的目的，需要业务层进行以下改造处理：
1① 执行删除操作需要写入取消行，而取消行中需要包含与原始状态行一样的数据（Sign列除外）。所以在应用层需要记录原始状态行的值，或者在执行删除操作前先查询数据库获取原始状态行；
2
3② 由于后台Compaction时机无法预测，在发起查询时，状态行和取消行可能尚未被折叠；另外，ClickHouse无法保证primary key相同的行落在同一个节点上，不在同一节点上的数据无法折叠。因此在进行count(*)、sum(col)等聚合计算时，可能会存在数据冗余的情况。为了获得正确结果，业务层需要改写SQL，将`count()、sum(col)`分别改写为`sum(Sign)、sum(col * Sign)`。
还是用一个例子来说明吧，首先建一张CollapsingMergeTree引擎的表：
1CREATE TABLE test_collapsing_merge_tree (
2 id UInt32,
3 num_of_apple UInt32 default null,
4 create_time Date,
5 sign Int8
6) ENGINE = CollapsingMergeTree(sign)
7 PARTITION BY create_time
8 ORDER BY (id, create_time)
9 PRIMARY KEY (id, create_time)
10 TTL create_time + INTERVAL 3 MONTH
11 SETTINGS index_granularity=8192;
写入几行数据：
1-- 插入状态行，注意sign一列的值为1
2insert into test_collapsing_merge_tree values(1, 10,'2024-03-05',1);
3-- 插入取消行，注意sign一列的值为-1
4insert into test_collapsing_merge_tree values(1, 10,'2024-03-05',-1);
5-- 插入一行主键相同的新状态行
6insert into test_collapsing_merge_tree values(1, 20,'2024-03-05',1);
查询一下：
1select * from test_collapsing_merge_tree;
2---------------------------------------
3┌─id─┬─num_of_apple─┬─create_time─┬─sign─┐
4│ 1 │ 10 │ 2024-03-05 │ 1 │
5└────┴──────────────┴─────────────┴──────┘
6┌─id─┬─num_of_apple─┬─create_time─┬─sign─┐
7│ 1 │ 20 │ 2024-03-05 │ 1 │
8└────┴──────────────┴─────────────┴──────┘
9┌─id─┬─num_of_apple─┬─create_time─┬─sign─┐
10│ 1 │ 10 │ 2024-03-05 │ -1 │
11└────┴──────────────┴─────────────┴──────┘
可以看到未Compaction之前，状态行与取消行共存。所以我们需要改写一下sql：
1SELECT
2 id,
3 sum(num_of_apple * sign) AS num_of_apple
4FROM test_collapsing_merge_tree
5GROUP BY id
6HAVING sum(sign) > 0;
7-----------------------
8┌─id─┬─num_of_apple─┐
9│ 1 │ 20 │
10└────┴──────────────┘
可以看到，这样可以得到正确的结果。我们手动触发一下Compaction看看：
1optimize table test_collapsing_merge_tree final;
再来查询一下数据：
1select * from test_collapsing_merge_tree;
2----------------------------------
3┌─id─┬─num_of_apple─┬─create_time─┬─sign─┐
4│ 1 │ 20 │ 2024-03-05 │ 1 │
5└────┴──────────────┴─────────────┴──────┘
可以看到状态行、取消行已经被折叠，只剩下最新的一行状态行。
CollapsingMergeTree虽然解决了主键相同的数据即时删除的问题，但是状态持续变化且多线程并行写入情况下，状态行与取消行位置可能乱序，导致无法正常折叠。比如下面的例子：
首先还是建一个跟上面一样的表结构：
1CREATE TABLE test_collapsing_merge_tree_no_order (
2 id UInt32,
3 num_of_apple UInt32 default null,
4 create_time Date,
5 sign Int8
6) ENGINE = CollapsingMergeTree(sign)
7 PARTITION BY create_time
8 ORDER BY (id, create_time)
9 PRIMARY KEY (id, create_time)
10 TTL create_time + INTERVAL 3 MONTH
11 SETTINGS index_granularity=8192;
然后写入几条数据：
1-- 先插入取消行，注意sign一列的值为-1
2insert into test_collapsing_merge_tree_no_order values(1, 10,'2024-03-05',-1);
3-- 再插入状态行，注意sign一列的值为1
4insert into test_collapsing_merge_tree_no_order values(1, 10,'2024-03-05',1);
手动触发一下Compaction：
1optimize table test_collapsing_merge_tree_no_order final;
查询一下：
1┌─id─┬─num_of_apple─┬─create_time─┬─sign─┐
2│ 1 │ 10 │ 2024-03-05 │ -1 │
3│ 1 │ 10 │ 2024-03-05 │ 1 │
4└────┴──────────────┴─────────────┴──────┘
可以看到即使Compaction之后也无法进行主键折叠，状态行和取消行的数据仍旧都存在。
4. VersionedCollapsingMergeTree
为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree表引擎在建表语句中新增了一列version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且version相同、sign相反的行，在Compaction时会被删除。
与CollapsingMergeTree类似， 为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。
首先还是来建一张VersionedCollapsingMergeTree引擎表：
1CREATE TABLE test_versioned_collapsing_merge_tree (
2 id UInt32,
3 num_of_apple UInt32 default null,
4 create_time Date,
5 sign Int8,
6 version UInt8
7) ENGINE = VersionedCollapsingMergeTree(sign,version)
8 PARTITION BY create_time
9 ORDER BY (id, create_time)
10 PRIMARY KEY (id, create_time)
11 TTL create_time + INTERVAL 3 MONTH
12 SETTINGS index_granularity=8192;
写入几条数据：
1-- 先插入取消行，注意sign一列的值为-1，version=1
2insert into test_versioned_collapsing_merge_tree values(1, 10,'2024-03-05',-1,1);
3-- 再插入状态行，注意sign一列的值为1，version=1
4insert into test_versioned_collapsing_merge_tree values(1, 10,'2024-03-05',1,1);
5-- 再插入一行主键相同的新状态行，sign=1，version=2
6insert into test_versioned_collapsing_merge_tree values(1, 20,'2024-03-05',1,2);
为了获取正确的结果，仍然需要改写sql：
1SELECT
2 id,
3 sum(num_of_apple * sign) AS num_of_apple
4FROM test_versioned_collapsing_merge_tree
5GROUP BY id
6HAVING sum(sign) > 0;
手动触发一下Compaction：
1optimize table test_versioned_collapsing_merge_tree final;
再次查询一下：
1select * from test_versioned_collapsing_merge_tree;
2----------------------------------
3┌─id─┬─num_of_apple─┬─create_time─┬─sign─┬─version─┐
4│ 1 │ 20 │ 2024-03-05 │ 1 │ 2 │
5└────┴──────────────┴─────────────┴──────┴─────────┘
可以看到即使取消行与状态行乱序写入，仍然可以被正确折叠。
5. SummingMergeTree
ClickHouse提供了SummingMergeTree来支持对主键列进行预先聚合。
在后台Compaction时，会将主键相同的多行进行sum求和，然后使用一行数据替换，从而降低存储空间占用，提升聚合计算性能。
首先建个表吧：
1CREATE TABLE summtt
2(
3 key UInt32,
4 value UInt32
5)
6ENGINE = SummingMergeTree()
7ORDER BY key
插入几条数据：
1-- 插入数据
2INSERT INTO summtt Values(1,1),(1,3),(2,2)
查询一下数据：
1select * from summtt;
2--------------------
3┌─key─┬─value─┐
4│ 1 │ 4 │
5│ 2 │ 2 │
6└─────┴───────┘
可以看到数据已经被预先聚合，如果没有聚合，需要手动触发一下compaction。
6. AggregatingMergeTree
SummingMergeTree对非主键列进行sum聚合，而ClickHouse也提供另一种引擎：AggregatingMergeTree，他可以指定各种聚合函数。
AggregatingMergeTree的语法比较复杂，需要结合物化视图或ClickHouse的特殊数据类型AggregateFunction一起使用。在insert和select时，也有独特的写法和要求：写入时需要使用-State语法，查询时使用-Merge语法。
示例一：配合物化视图使用
先建一张明细表：
1CREATE TABLE visits
2(
3 UserID UInt64,
4 CounterID UInt8,
5 StartDate Date,
6 Sign Int8
7)
8ENGINE = CollapsingMergeTree(Sign)
9ORDER BY UserID;
插入几条数据：
1INSERT INTO visits VALUES(0, 0, '2024-03-06', 1);
2INSERT INTO visits VALUES(1, 1, '2024-03-07', 1);
建立物化视图，对明细表进行预先聚合：
1CREATE MATERIALIZED VIEW visits_agg_view
2ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
3AS SELECT
4 CounterID,
5 StartDate,
6 sumState(Sign) AS Visits,
7 uniqState(UserID) AS Users
8FROM visits
9GROUP BY CounterID, StartDate;
查询物化视图：
1SELECT
2 StartDate,
3 sumMerge(Visits) AS Visits,
4 uniqMerge(Users) AS Users
5FROM visits_agg_view
6GROUP BY StartDate
7ORDER BY StartDate;
示例二：配合AggregateFunction使用
先一张明细表：
1CREATE TABLE detail_table
2( CounterID UInt8,
3 StartDate Date,
4 UserID UInt64
5) ENGINE = MergeTree()
6PARTITION BY toYYYYMM(StartDate)
7ORDER BY (CounterID, StartDate);
写入几条数据：
1INSERT INTO detail_table VALUES(1, '2024-03-05', 1);
2INSERT INTO detail_table VALUES(2, '2024-03-04', 1);
建一张聚合表：
1CREATE TABLE agg_table
2( CounterID UInt8,
3 StartDate Date,
4 UserID AggregateFunction(uniq, UInt64)
5) ENGINE = AggregatingMergeTree()
6PARTITION BY toYYYYMM(StartDate)
7ORDER BY (CounterID, StartDate);
导入明细表数据到聚合表中：
1INSERT INTO agg_table
2select CounterID, StartDate, uniqState(UserID)
3from detail_table
4group by CounterID, StartDate
从聚合表中查询数据：
1SELECT uniqMerge(UserID) AS state
2FROM agg_table
3GROUP BY CounterID, StartDate;
二、 Integration系列
这种引擎主要用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。这里举一些常见的例子：
1. Hive
1CREATE TABLE test.test_orc
2(
3 `f_tinyint` Int8,
4 `f_smallint` Int16,
5 `f_int` Int32,
6 `f_integer` Int32,
7 `f_bigint` Int64,
8 `f_float` Float32,
9 `f_double` Float64,
10 `f_decimal` Float64,
11 `f_timestamp` DateTime,
12 `f_date` Date,
13 `f_string` String,
14 `f_varchar` String,
15 `f_bool` Bool,
16 `f_binary` String,
17 `f_array_int` Array(Int32),
18 `f_array_string` Array(String),
19 `f_array_float` Array(Float32),
20 `f_array_array_int` Array(Array(Int32)),
21 `f_array_array_string` Array(Array(String)),
22 `f_array_array_float` Array(Array(Float32)),
23 `day` String
24)
25ENGINE = Hive('thrift://localhost:9083', 'test', 'test_orc')
26PARTITION BY day
2. Jdbc
1CREATE TABLE jdbc_table
2(
3 `int_id` Int32,
4 `int_nullable` Nullable(Int32),
5 `float` Float32,
6 `float_nullable` Nullable(Float32)
7)
8ENGINE JDBC('jdbc:mysql://localhost:3306/?user=root&password=root', 'test', 'test')
3. Hdfs
1CREATE TABLE hdfs_engine_table
2(
3 name String, value UInt32
4)
5ENGINE=HDFS('hdfs://hdfs1:9000/other_storage', 'TSV')
4. Kafka
1-- 创建kafka表
2CREATE TABLE source
3(
4 `ts` DateTime,
5 `tag` String,
6 `message` String
7)
8ENGINE = Kafka()
9SETTINGS kafka_broker_list = '10.10.80.70:6667,10.10.80.71:6667,10.10.80.72:6667',
10 kafka_topic_list = 'tag',
11 kafka_group_name = 'clickhouse',
12 kafka_format = 'JSONEachRow',
13 kafka_skip_broken_messages = 1,
14 kafka_num_consumers = 2
15
16-- 创建ClickHouse表
17CREATE TABLE target
18(
19 `ts` DateTime,
20 `tag` String
21)
22ENGINE = MergeTree()
23PARTITION BY toYYYYMM(ts)
24ORDER BY tag
25
26-- 将kafka数据导入ClickHouse
27CREATE MATERIALIZED VIEW source_mv TO target
28AS SELECT ts, tag FROM source;
三、Log系列
Log系列表引擎功能相对简单，主要用于快速写入小表，一次性读出的场景。
Log系列包括以下几种引擎：
① TinyLog
不支持并发读取数据文件，格式简单，适合用来做临时表。
② StripLog
支持并发读取数据文件。将所有列存储在同一个大文件中，减少了文件个数。
③ Log
支持并发读取数据文件。每个列会单独存储在一个独立文件中。
他们的共同点是：
① 数据被顺序追加到磁盘上
② 不支持delete、update
③ 不支持index
④ 不支持原子性写
⑤ insert会阻塞select操作
四、Special系列
Special系列的表引擎，是为了特定场景而定制的。比如Memory，Buffer，Dictionary，File，URL等等。
五、总结
ClickHouse提供了丰富多样的表引擎供我们使用。本文主要讲述了ClickHouse的表引擎，同时对于MergeTree系列表引擎进行了详细对比和示例。
比如Log系列用来做小表数据分析、MergeTree系列用来做大数据量分析，而Integration系列用于外部数据集成，除了这些表引擎之外，ClickHouse还提供了Replicated、Distributed等高级表引擎，以提供更好的可靠性保证和分布式能力。
往期推荐