【ClickHouse】表引擎详解

ClickHouse表引擎一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交，根据场景组合使用。最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。对于大多数正式的任务，应该使用MergeTree族中的引擎。

【ClickHouse】表引擎详解

1.日志引擎系列--Log系列

这些引擎是为了需要写入许多小数据量（少于一百万行）的表的场景而开发的，相对简单，主要用于快速写入小表（1百万行左右的表），然后全部读出的场景。

共性是：

数据被顺序append写到磁盘上。
不支持delete、update。
不支持index。
不支持原子性写。
insert会阻塞select操作。

区别是：

TinyLog：不支持并发读取数据文件，查询性能较差；格式简单，适合用来暂存中间数据。
Log：支持并发读取数据文件，查询性能比TinyLog好；每个列会单独存储在一个独立文件中。并发读取，写入操作则阻塞读取和其它写入。Log 引擎适用于临时数据，write-once 表以及测试或演示目的。
StripeLog：支持并发读取数据文件，查询性能比TinyLog好；将所有列存储在同一个大文件中，减少了文件个数。

1.1TinyLog

最简单的表引擎，用于将数据存储在磁盘上。每列都存储在单独的压缩文件中。写入时，数据将附加到文件末尾。不支持索引。

并发数据访问不受任何限制：如果同时从表中读取并在不同的查询中写入，则读取操作将抛出异常。如果同时写入多个查询中的表，则数据将被破坏。

这种表引擎的典型用法是 write-once：首先只写入一次数据，然后根据需要多次读取。查询在单个流中执行。此引擎适用于相对较小的表（建议最多1,000,000行）。

是最简单的表轻量引擎（最多约100万行）, 一写多读的应用场景。同时读写会损害数据
TinyLog 表经常作为中间表，用于数据的微批量处理. 语法中无需携带任何参数
它将数据保存到磁盘. 每个字段都以单独压缩文件形式保存. 当写入数据时, 数据追加到文件的末尾

创建表的样例：

CREATE  TABLE  [ IF  NOT  EXISTS ]  [ db .] table_name  [ ON  CLUSTER  cluster ] 
（
   name1  [ type1 ]  [ DEFAULT | MATERIALIZED | ALIAS  expr1 ]  [ TTL  expr1 ]，
   name2  [ type2 ]  [ DEFAULT | MATERIALIZED | ALIAS  expr2 ]  [ TTL  expr2 ]，
  	 	 	... 
  INDEX index_name1  expr1的 TYPE  TYPE1 （...） 粒度 值1 ，
  INDEX  index_name2  表达式2  TYPE  TYPE2 （...） 粒度 值2 
） ENGINE  =  TinyLog();

1.2Log

Log与 TinyLog 的不同之处在于，"标记" 的小文件与列文件存在一起。这些标记写在每个数据块上，并且包含偏移量，这些偏移量指示从哪里开始读取文件以便跳过指定的行数。这使得可以在多个线程中读取表数据。对于并发数据访问，可以同时执行读取操作，而写入操作则阻塞读取和其它写入。如果写入表失败，则该表将被破坏，并且从该表读取将返回错误。

CREATE  TABLE  [ IF  NOT  EXISTS ]  [ db .] table_name  [ ON  CLUSTER  cluster ] 
（
   name1  [ type1 ]  [ DEFAULT | MATERIALIZED | ALIAS  expr1 ]  [ TTL  expr1 ]，
   name2  [ type2 ]  [ DEFAULT | MATERIALIZED | ALIAS  expr2 ]  [ TTL  expr2 ]，
  	 	 	... 
  INDEX index_name1  expr1的 TYPE  TYPE1 （...） 粒度 值1 ，
  INDEX  index_name2  表达式2  TYPE  TYPE2 （...） 粒度 值2 
） ENGINE  =  Log();

1.3StripeLog

StripeLog 引擎将所有列存储在一个文件中。对每一次 Insert 请求，ClickHouse 将数据块追加在表文件的末尾，逐列写入。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    column1_name [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    column2_name [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = StripeLog

ClickHouse 为每张表写入以下文件：

data.bin — 数据文件。
index.mrk — 带标记的文件。标记包含了已插入的每个数据块中每列的偏移量。
StripeLog 引擎不支持 ALTER UPDATE 和 ALTER DELETE 操作。

2.Special系列

Special系列的表引擎，大多是为了特定场景而定制的。这里挑选几个简单介绍，不做详述。

Memory：将数据存储在内存中，重启后会导致数据丢失。查询性能极好，适合于对于数据持久性没有要求的1亿一下的小表。在ClickHouse中，通常用来做临时表。
Buffer：为目标表设置一个内存buffer，当buffer达到了一定条件之后会flush到磁盘。
File：直接将本地文件作为数据存储。
Null：写入数据被丢弃、读取数据为空。

2.1Memory

Memory 引擎以未压缩的形式将数据存储在内存中。数据完全以读取时获得的形式存储。在简单查询上达到最大生产率（超过10 GB /秒），因为没有磁盘读取，不需要解压缩或反序列化数据。（值得注意的是，在许多情况下，与 MergeTree 引擎的性能几乎一样高）。从这张表中读取是很轻松的。并发数据访问是同步的。锁范围小：读写操作不会相互阻塞。不支持索引。阅读是并行化的。

重新启动服务器时，表中的数据消失，表将变为空。通常，使用此表引擎是不合理的。

2.2Merge

重新启动服务器时，表中的数据消失，表将变为空。通常，使用此表引擎是不合理的。（值得注意的是，在许多情况下，与 MergeTree 引擎的性能几乎一样高）。
锁范围小：读写操作不会相互阻塞。不支持索引。阅读是并行化的。换句话说，从这张表中读取是很轻松的。并发数据访问是同步的。
以未压缩的形式将数据存储在内存中。数据完全以读取时获得的形式存储。
Merge 引擎本身不存储数据，但可用于同时从任意多个其他的表中读取数据。读是自动并行的，不支持写入。
Merge 引擎的一个典型应用是可以像使用一张表一样使用大量的 TinyLog 表。

Merge 引擎的参数：一个数据库名和一个用于匹配表名的正则表达式。

ENGINE=Merge(db, 'regex')

2.3Distributed

分布式引擎本身不存储数据, 但可以在多个服务器上进行分布式查询。读是自动并行的。
读取时，远程服务器表的索引（如果有的话）会被使用。
远程服务器不仅用于读取数据，还会对尽可能数据做部分处理。
分布式引擎参数：服务器配置文件中的集群名，远程数据库名，远程表名，数据分片键（可选）

ENGINE=Distributed(cluster, db, table[, sharding_key])

远程服务器不仅用于读取数据，还会对尽可能数据做部分处理。分片是指包含数据不同部分的服务器（要读取所有数据，必须访问所有分片）。副本是存储复制数据的服务器（要读取所有数据，访问任一副本上的数据即可）。配置了副本，读取操作会从每个分片里选择一个可用的副本。可配置负载平衡算法（挑选副本的方式）。如果跟服务器的连接不可用，则在尝试短超时的重连。如果重连失败，则选择下一个副本，依此类推。如果跟所有副本的连接尝试都失败，则尝试用相同的方式再重复几次。该机制有利于系统可用性，但不保证完全容错：如有远程服务器能够接受连接，但无法正常工作或状况不佳。

3.MergeTree系列--合并树引擎系列

Log、Special、Integration主要用于特殊用途，场景相对有限。MergeTree系列才是官方主推的存储引擎，支持几乎所有ClickHouse核心功能。

3.1MergeTree

MergeTree表引擎主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据TTL等。MergeTree支持所有ClickHouse SQL语法，但是有些功能与MySQL并不一致，比如在MergeTree中主键并不用于去重MergeTree 引擎系列的基本理念如下：当有巨量数据要插入到表中时，需要高效地一批批写入数据片段，并希望这些数据片段在后台按照一定规则合并。应对表的并发访问，我们使用多版本机制。换言之，当同时读和更新表时，数据从当前查询到的一组片段中读取。没有冗长的的锁。插入不会阻碍读取。对表的读操作是自动并行的。

特点：

存储的数据按主键排序。
允许使用分区（在指定了主键的情况下）。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。
支持数据副本。
支持数据采样。


CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

存储

表由按主键排序的数据片段组成。
当数据被插入到表中时，会分成数据片段并按主键的字典序排序。
不同分区的数据会被分成不同的片段，ClickHouse 在后台合并数据片段以便更高效存储。不会合并来自不同分区的数据片段。这个合并机制并不保证相同主键的所有行都会合并到同一个数据片段中。

主键与排序键

稀疏索引让你能操作有巨量行的表。因为这些索引是常驻内存（RAM）的。
ClickHouse 不要求主键唯一。所以，你可以插入多条具有相同主键的行。
主键中列的数量并没有明确的限制。
长的主键会对插入性能和内存消耗有负面影响。
默认情况下主键跟排序键相同。指定一个跟排序键（用于排序数据片段中行的表达式）不一样的主键（用于计算写到索引文件的每个标记值的表达式）是可以的。这种情况下，主键表达式元组必须是排序键表达式元组的一个前缀。

如下建表DDL所示，test_tbl的主键为(id, create_time)，并且按照主键进行存储排序，按照create_time进行数据分区，数据保留最近一个月。

CREATE TABLE test_tbl (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = MergeTree()
   PARTITION BY create_time
     ORDER BY  (id, create_time)
     PRIMARY KEY (id, create_time)
     TTL create_time + INTERVAL 1 MONTH
     SETTINGS index_granularity=8192;
     
insert into test_tbl values(0, '2019-12-12', null);
insert into test_tbl values(0, '2019-12-12', null);
insert into test_tbl values(1, '2019-12-13', null);
insert into test_tbl values(1, '2019-12-13', null);
insert into test_tbl values(2, '2019-12-14', null);
查询数据： 可以看到虽然主键id、create_time相同的数据只有3条数据，但是结果却有5行。
select count(*) from test_tbl;
┌─count()─┐
│       5 │
└─────────┘

select * from test_tbl;
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
由于MergeTree采用类似LSM tree的结构，很多存储层处理逻辑直到Compaction期间才会发生。因此强制后台compaction执行完毕，再次查询，发现仍旧有5条数据。
optimize table test_tbl final;


select count(*) from test_tbl;
┌─count()─┐
│       5 │
└─────────┘

select * from test_tbl;
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘

3.2ReplacingMergeTree

该引擎和MergeTree的不同之处在于它会删除具有相同主键的重复项。适用于在后台清除重复数据以节省空间，但不保证不存在重复。数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，因此你无法预先作出计划。有一些数据可能仍未被处理。尽管你可以调用 OPTIMIZE 语句发起计划外的合并，但请不要指望使用它，因为 OPTIMIZE 语句会引发对大量数据的读和写。

合并的时候，ReplacingMergeTree 从所有具有相同主键的行中选择一行留下：如果 ver 列未指定，选择最后一条。如果 ver 列已指定，选择 ver 值最大的版本。

虽然ReplacingMergeTree提供了主键去重的能力，但是仍旧有以下限制：

在没有彻底optimize之前，可能无法达到主键去重的效果，比如部分数据已经被去重，而另外一部分数据仍旧有主键重复。
在分布式场景下，相同primary key的数据可能被sharding到不同节点上，不同shard间可能无法去重。
optimize是后台动作，无法预测具体执行时间点。
手动执行optimize在海量数据场景下要消耗大量时间，无法满足业务即时查询的需求。

因此ReplacingMergeTree更多被用于确保数据最终被去重，而无法保证查询过程中主键不重复。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

案例：

CREATE TABLE test_tbl_replacing (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = ReplacingMergeTree()
   PARTITION BY create_time
     ORDER BY  (id, create_time)
     PRIMARY KEY (id, create_time)
     TTL create_time + INTERVAL 1 MONTH
     SETTINGS index_granularity=8192;

-- 写入主键重复的数据
insert into test_tbl_replacing values(0, '2019-12-12', null);
insert into test_tbl_replacing values(0, '2019-12-12', null);
insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(2, '2019-12-14', null);

-- 查询，可以看到未compaction之前，主键重复的数据，仍旧存在。
select count(*) from test_tbl_replacing;
┌─count()─┐
│       5 │
└─────────┘

select * from test_tbl_replacing;
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘


-- 强制后台compaction：
optimize table test_tbl_replacing final;


-- 再次查询：主键重复的数据已经消失。
select count(*) from test_tbl_replacing;
┌─count()─┐
│       3 │
└─────────┘

select * from test_tbl_replacing;
┌─id─┬─create_time─┬─comment─┐
│  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
└────┴─────────────┴─────────┘

3.3CollapsingMergeTree

ClickHouse实现了CollapsingMergeTree来消除ReplacingMergeTree的功能限制。该引擎要求在建表语句中指定一个标记列Sign，后台Compaction时会将主键相同、Sign相反的行进行折叠，也即删除。

CollapsingMergeTree将行按照Sign的值分为两类：Sign=1的行称之为状态行，Sign=-1的行称之为取消行。

每次需要新增状态时，写入一行状态行；需要删除状态时，则写入一行取消行。

在后台Compaction时，状态行与取消行会自动做折叠（删除）处理。而尚未进行Compaction的数据，状态行与取消行同时存在。

因此为了能够达到主键折叠（删除）的目的，需要业务层进行适当改造：

执行删除操作需要写入取消行，而取消行中需要包含与原始状态行主键一样的数据（Sign列除外）。所以在应用层需要记录原始状态行的值，或者在执行删除操作前先查询数据库获取原始状态行。
由于后台Compaction时机无法预测，在发起查询时，状态行和取消行可能尚未被折叠；另外，ClickHouse无法保证primary key相同的行落在同一个节点上，不在同一节点上的数据无法折叠。因此在进行count(*)、sum(col)等聚合计算时，可能会存在数据冗余的情况。为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。

CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

-- 插入状态行，注意sign一列的值为1
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1);

-- 插入一行取消行，用于抵消上述状态行。注意sign一列的值为-1，其余值与状态行一致；
-- 并且插入一行主键相同的新状态行，用来将PageViews从5更新至6，将Duration从146更新为185.
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);

-- 查询数据：可以看到未Compaction之前，状态行与取消行共存。
SELECT * FROM UAct;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

-- 为了获取正确的sum值，需要改写SQL： 
-- sum(PageViews) => sum(PageViews * Sign)、 
-- sum(Duration) => sum(Duration * Sign)
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘


-- 强制后台Compaction
optimize table UAct final;

-- 再次查询，可以看到状态行、取消行已经被折叠，只剩下最新的一行状态行。
select * from UAct;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

3.4VersionedCollapsingMergeTree

为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且Version相同、Sign相反的行，在Compaction时会被删除。

与CollapsingMergeTree类似，为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。

CREATE TABLE UAct_version
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8,
    Version UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID;


-- 先插入一行取消行，注意Signz=-1, Version=1
INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, -1, 1);
-- 后插入一行状态行，注意Sign=1, Version=1；及一行新的状态行注意Sign=1, Version=2，将PageViews从5更新至6，将Duration从146更新为185。
INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);


-- 查询可以看到未compaction情况下，所有行都可见。
SELECT * FROM UAct_version;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘


-- 为了获取正确的sum值，需要改写SQL： 
-- sum(PageViews) => sum(PageViews * Sign)、 
-- sum(Duration) => sum(Duration * Sign)
SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct_version
GROUP BY UserID
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘


-- 强制后台Compaction
optimize table UAct_version final;


-- 再次查询，可以看到即便取消行与状态行位置乱序，仍旧可以被正确折叠。
select * from UAct_version;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         6 │      185 │    1 │       2 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

3.5SummingMergeTree

ClickHouse通过SummingMergeTree来支持对主键列进行预先聚合。在后台Compaction时，会将主键相同的多行进行sum求和，然后使用一行数据取而代之，从而大幅度降低存储空间占用，提升聚合计算性能。

值得注意的是：

ClickHouse只在后台Compaction时才会进行数据的预先聚合，而compaction的执行时机无法预测，所以可能存在部分数据已经被预先聚合、部分数据尚未被聚合的情况。因此，在执行聚合计算时，SQL中仍需要使用GROUP BY子句。
在预先聚合时，ClickHouse会对主键列之外的其他所有列进行预聚合。如果这些列是可聚合的（比如数值类型），则直接sum；如果不可聚合（比如String类型），则随机选择一个值。
通常建议将SummingMergeTree与MergeTree配合使用，使用MergeTree来存储具体明细，使用SummingMergeTree来存储预先聚合的结果加速查询。

CREATE TABLE summtt
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key

-- 插入数据
INSERT INTO summtt Values(1,1),(1,2),(2,1)

-- compaction前查询，仍存在多行
select * from summtt;
┌─key─┬─value─┐
│   1 │     1 │
│   1 │     2 │
│   2 │     1 │
└─────┴───────┘

-- 通过GROUP BY进行聚合计算
SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘

-- 强制compaction
optimize table summtt final;

-- compaction后查询，可以看到数据已经被预先聚合
select * from summtt;
┌─key─┬─value─┐
│   1 │     3 │
│   2 │     1 │
└─────┴───────┘


-- compaction后，仍旧需要通过GROUP BY进行聚合计算
SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│   2 │          1 │
│   1 │          3 │
└─────┴────────────┘

3.6AggregatingMergeTree

AggregatingMergeTree也是预先聚合引擎的一种，用于提升聚合计算的性能。与 SummingMergeTree的区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则可以指定各种聚合函数。

AggregatingMergeTree的语法比较复杂，需要结合物化视图或ClickHouse的特殊数据类型AggregateFunction一起使用。在insert和select时，也有独特的写法和要求：写入时需要使用-State语法，查询时使用-Merge语法。

示例一：配合物化视图使用。

CREATE TABLE visits
(
    UserID UInt64,
    CounterID UInt8,
    StartDate Date,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

-- 对明细表建立物化视图，该物化视图对明细表进行预先聚合
-- 注意：预先聚合使用的函数分别为： sumState, uniqState。对应于写入语法<agg>-State.
CREATE MATERIALIZED VIEW visits_agg_view
ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
AS SELECT
    CounterID,
    StartDate,
    sumState(Sign)    AS Visits,
    uniqState(UserID) AS Users
FROM visits
GROUP BY CounterID, StartDate;

-- 插入明细数据
INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);

-- 对物化视图进行最终的聚合操作
-- 注意：使用的聚合函数为 sumMerge， uniqMerge。对应于查询语法<agg>-Merge.
SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM visits_agg_view
GROUP BY StartDate
ORDER BY StartDate;

-- 普通函数 sum, uniq不再可以使用
-- 如下SQL会报错： Illegal type AggregateFunction(sum, Int8) of argument 
SELECT
    StartDate,
    sum(Visits),
    uniq(Users)
FROM visits_agg_view
GROUP BY StartDate
ORDER BY StartDate;

示例二：配合特殊数据类型AggregateFunction使用。

-- 建立明细表
CREATE TABLE detail_table
(   CounterID UInt8,
    StartDate Date,
    UserID UInt64
) ENGINE = MergeTree() 
PARTITION BY toYYYYMM(StartDate) 
ORDER BY (CounterID, StartDate);

-- 插入明细数据
INSERT INTO detail_table VALUES(0, '2019-11-11', 1);
INSERT INTO detail_table VALUES(1, '2019-11-12', 1);

-- 建立预先聚合表，
-- 注意：其中UserID一列的类型为：AggregateFunction(uniq, UInt64)
CREATE TABLE agg_table
(   CounterID UInt8,
    StartDate Date,
    UserID AggregateFunction(uniq, UInt64)
) ENGINE = AggregatingMergeTree() 
PARTITION BY toYYYYMM(StartDate) 
ORDER BY (CounterID, StartDate);

-- 从明细表中读取数据，插入聚合表。
-- 注意：子查询中使用的聚合函数为 uniqState， 对应于写入语法<agg>-State
INSERT INTO agg_table
select CounterID, StartDate, uniqState(UserID)
from detail_table
group by CounterID, StartDate

-- 不能使用普通insert语句向AggregatingMergeTree中插入数据。
-- 本SQL会报错：Cannot convert UInt64 to AggregateFunction(uniq, UInt64)
INSERT INTO agg_table VALUES(1, '2019-11-12', 1);

-- 从聚合表中查询。
-- 注意：select中使用的聚合函数为uniqMerge，对应于查询语法<agg>-Merge
SELECT uniqMerge(UserID) AS state 
FROM agg_table 
GROUP BY CounterID, StartDate;

4.Integration系列

该系统表引擎主要用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。

Kafka：将Kafka Topic中的数据直接导入到ClickHouse。
MySQL：将Mysql作为存储引擎，直接在ClickHouse中对MySQL表进行select等操作。
JDBC/ODBC：通过指定jdbc、odbc连接串读取数据源。
HDFS：直接读取HDFS上的特定格式的数据文件；

4.1Kafka

自动跟踪传递的消息，因此组中的每条消息仅计算一次。如果要获取数据两次，则使用另一个组名创建表的副本。组是灵活的并在群集上同步
发布或订阅数据流。
组织容错存储。
处理流可用。

建表样例：

CREATE TABLE [IF NOT EXISTS] [db.] table_name [ON CLUSTER cluster]
		（
    		name1 [type1] [DEFAULT | MATERIALIZED | ALIAS expr1]，
   		 name2 [type2] [DEFAULT | MATERIALIZED | ALIAS expr2]，
  			  ...
		）ENGINE = Kafka（）
		设置
    		kafka_broker_list ='host：port'，
  		kafka_topic_list ='topic1，topic2，...'，
    		kafka_group_name ='group_name'，
    		kafka_format ='data_format'[，]
   		[kafka_row_delimiter ='delimiter_symbol'，]
    		[kafka_schema =''，]
    		[kafka_num_consumers = N，]
   		[kafka_skip_broken_messages = N]

实例：

CREATE TABLE ODS.table_name
    (`EventDate` Date, `CounterID` UInt32, `UserID` UInt32) 
     ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/table_name2', '{replica}') 
     PARTITION BY toYYYYMM(EventDate) 
     ORDER BY (CounterID, EventDate, intHash32(UserID)) 
     SAMPLE BY intHash32(UserID) 
     SETTINGS index_granularity = 8192

注：8192:主键索引粒度就是每隔8192个单位数据，是1个block，主键会每隔8192个单位，取主键列的数据，同时记录这是第几个block，查询的时候，如果有索引，就通过索引定位到是哪个block，然后找到这个block对应的数据文件

4.2HDFS

从HDFS上同步数据，可以参考下面链接，由于小白没有实践，就不写了！

https://clickhouse.tech/docs/zh/sql-reference/table-functions/hdfs/

【ClickHouse】表引擎详解

1.日志引擎系列--Log系列

1.1TinyLog

1.2Log

1.3StripeLog

2.Special系列

2.1Memory

2.2Merge

2.3Distributed

3.MergeTree系列--合并树引擎系列

3.1MergeTree

3.2ReplacingMergeTree

3.3CollapsingMergeTree

3.4VersionedCollapsingMergeTree

3.5SummingMergeTree

3.6AggregatingMergeTree

4.Integration系列

4.1Kafka

4.2HDFS

Mysql数据库表定期备份的实现详解

mysql的innodb数据库引擎详解

SQL Server 表的管理_关于数据增删查改的操作的详解（案例代码）

SQL Server 表的管理_关于事务的处理的详解（案例代码）

MySQL存储引擎以及MyISAM与InnoDB的区别详解

MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】

基于SQL2005 SQL2008 表结构信息查询升级版的详解(含外键信息)

MySQL 5.7双主同步部分表的实现过程详解

详解MySQL导出指定表中的数据的实例

C#中哈希表(HashTable)用法实例详解(添加/移除/判断/遍历/排序等)