TUNANのBlogHomeArchivesTagsCategories
ClickHouse MergeTree系列引擎
|ClickHouse
Word count: 6.9k|Reading time: 26 min

目录

  1. MergeTree表引擎
  2. ReplacingMergeTree表引擎
  3. SummingMergeTree表引擎
  4. Aggregatingmergetree表引擎
  5. CollapsingMergeTree表引擎
  6. VersionedCollapsingMergeTree表引擎
  7. GraphiteMergeTree表引擎

在所有的表引擎中，最为核心的当属MergeTree系列表引擎，这些表引擎拥有最为强大的性能和最广泛的使用场合。对于非MergeTree系列的其他引擎而言，主要用于特殊用途，场景相对有限。而MergeTree系列表引擎是官方主推的存储引擎，支持几乎所有ClickHouse核心功能。

MergeTree表引擎

MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘，且数据片段不可修改。为了避免片段过多，ClickHouse会通过后台线程，定期合并这些数据片段，属于相同分区的数据片段会被合成一个新的片段。这种数据片段往复合并的特点，也正是合并树名称的由来。

MergeTree作为家族系列最基础的表引擎，主要有以下特点：

  • 存储的数据按照主键排序：允许创建稀疏索引，从而加快数据查询速度
  • 支持分区，可以通过PRIMARY KEY语句指定分区字段。
  • 支持数据副本
  • 支持数据采样

建表语法

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(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]
  • ENGINE：ENGINE = MergeTree()，MergeTree引擎没有参数
  • ORDER BY：排序字段。比如ORDER BY (Col1, Col2)，值得注意的是，如果没有指定主键，默认情况下 sorting key(排序字段)即为主键。如果不需要排序，则可以使用ORDER BY tuple()语法，这样的话，创建的表也就不包含主键。这种情况下，ClickHouse会按照插入的顺序存储数据。必选。
  • PARTITION BY：分区字段，可选。
  • PRIMARY KEY：指定主键，如果排序字段与主键不一致，可以单独指定主键字段。否则默认主键是排序字段。可选。
  • SAMPLE BY：采样字段，如果指定了该字段，那么主键中也必须包含该字段。比如SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID))。可选。
  • TTL：数据的存活时间。在MergeTree中，可以为某个列字段或整张表设置TTL。当时间到达时，如果是列字段级别的TTL，则会删除这一列的数据；如果是表级别的TTL，则会删除整张表的数据。可选。
  • SETTINGS：额外的参数配置。可选。

建表示例

CREATE TABLE emp_mergetree (
  emp_id UInt16 COMMENT '员工id',
  name String COMMENT '员工姓名',
  work_place String COMMENT '工作地点',
  age UInt8 COMMENT '员工年龄',
  depart String COMMENT '部门',
  salary Decimal32(2) COMMENT '工资'
  )ENGINE=MergeTree()
  ORDER BY emp_id
  PARTITION BY work_place
  ;
 -- 插入数据 
INSERT INTO emp_mergetree 
VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_mergetree 
VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000); 

-- 查询数据
-- 按work_place进行分区
cdh04 :) select * from emp_mergetree;

SELECT *
FROM emp_mergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

查看一下数据存储格式，可以看出，存在三个分区文件夹，每一个分区文件夹内存储了对应分区的数据。

[root@cdh04 emp_mergetree]# pwd
/var/lib/clickhouse/data/default/emp_mergetree
[root@cdh04 emp_mergetree]# ll
总用量 16
drwxr-x--- 2 clickhouse clickhouse 4096 9月  17 17:45 1c89a3ba9fe5fd53379716a776c5ac34_3_3_0
drwxr-x--- 2 clickhouse clickhouse 4096 9月  17 17:44 40d45822dbd7fa81583d715338929da9_1_1_0
drwxr-x--- 2 clickhouse clickhouse 4096 9月  17 17:45 a6155dcc1997eda1a348cd98b17a93e9_2_2_0
drwxr-x--- 2 clickhouse clickhouse    6 9月  17 17:43 detached
-rw-r----- 1 clickhouse clickhouse    1 9月  17 17:43 format_version.txt

进入一个分区目录查看

640

  • checksums.txt：校验文件，使用二进制格式存储。它保存了余下各类文件(primary. idx、count.txt等)的size大小及size的哈希值，用于快速校验文件的完整性和正确性。

  • columns.txt：列信息文件，使用明文格式存储。用于保存此数据分区下的列字段信息，例如

    [root@cdh04 1c89a3ba9fe5fd53379716a776c5ac34_3_3_0]# cat columns.txt
    columns format version: 1
    6 columns:
    `emp_id` UInt16
    `name` String
    `work_place` String
    `age` UInt8
    `depart` String
    `salary` Decimal(9, 2)

  • count.txt：计数文件，使用明文格式存储。用于记录当前数据分区目录下数据的总行数

  • primary.idx：一级索引文件，使用二进制格式存储。用于存放稀疏索引，一张MergeTree表只能声明一次一级索引，即通过ORDER BY或者PRIMARY KEY指定字段。借助稀疏索引，在数据查询的时能够排除主键条件范围之外的数据文件，从而有效减少数据扫描范围，加速查询速度。

  • 列.bin：数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储某一列的数据。由于MergeTree采用列式存储，所以每一个列字段都拥有独立的.bin数据文件，并以列字段名称命名。

  • 列.mrk2：列字段标记文件，使用二进制格式存储。标记文件中保存了.bin文件中数据的偏移量信息

  • partition.dat与minmax_[Column].idx：如果指定了分区键，则会额外生成partition.dat与minmax索引文件，它们均使用二进制格式存储。partition.dat用于保存当前分区下分区表达式最终生成的值，即分区字段值；而minmax索引用于记录当前分区下分区字段对应原始数据的最小和最大值。比如当使用EventTime字段对应的原始数据为2020-09-17、2020-09-30，分区表达式为PARTITION BY toYYYYMM(EventTime)，即按月分区。partition.dat中保存的值将会是2019-09，而minmax索引中保存的值将会是2020-09-17 2020-09-30。

注意点

  • 多次插入数据，会生成多个分区文件

    -- 新插入两条数据
    cdh04 :) INSERT INTO emp_mergetree
    VALUES (5,'robin','北京',35,'财务部',50000),(6,'lilei','北京',38,'销售事部',50000);

    -- 查询结果
    cdh04 :) select * from emp_mergetree;
    ┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
    │      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
    └────────┴──────┴────────────┴─────┴────────┴──────────┘
    ┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
    │      5 │ robin │ 北京       │  35 │ 财务部   │ 50000.00 │
    │      6 │ lilei │ 北京       │  38 │ 销售事部 │ 50000.00 │
    └────────┴───────┴────────────┴─────┴──────────┴──────────┘
    ┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
    │      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
    │      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
    └────────┴──────┴────────────┴─────┴────────┴──────────┘
    ┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
    │      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
    └────────┴──────┴────────────┴─────┴──────────┴──────────┘

    可以看出，新插入的数据新生成了一个数据块，并没有与原来的分区数据在一起，我们可以执行optimize命令，执行合并操作

    -- 执行合并操作
    cdh04 :) OPTIMIZE TABLE emp_mergetree PARTITION '北京';
    -- 再次执行查询
    cdh04 :) select * from emp_mergetree;                  
    SELECT *
    FROM emp_mergetree

    ┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
    │      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
    │      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
    └────────┴──────┴────────────┴─────┴────────┴──────────┘
    ┌─emp_id─┬─name──┬─work_place─┬─age─┬─depart───┬───salary─┐
    │      3 │ bob   │ 北京       │  33 │ 财务部   │ 50000.00 │
    │      5 │ robin │ 北京       │  35 │ 财务部   │ 50000.00 │
    │      6 │ lilei │ 北京       │  38 │ 销售事部 │ 50000.00 │
    └────────┴───────┴────────────┴─────┴──────────┴──────────┘
    ┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
    │      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
    └────────┴──────┴────────────┴─────┴──────────┴──────────┘

    执行上面的合并操作之后，会新生成一个该分区的文件夹，原理的分区文件夹不变。

  • 在MergeTree中主键并不用于去重，而是用于索引，加快查询速度

    -- 插入一条相同主键的数据
    INSERT INTO emp_mergetree
    VALUES (1,'sam','杭州',35,'财务部',50000);
    -- 会发现该条数据可以插入，由此可知，并不会对主键进行去重

ReplacingMergeTree表引擎

上文提到MergeTree表引擎无法对相同主键的数据进行去重，ClickHouse提供了ReplacingMergeTree引擎，可以针对相同主键的数据进行去重，它能够在合并分区时删除重复的数据。值得注意的是，ReplacingMergeTree只是在一定程度上解决了数据重复问题，但是并不能完全保障数据不重复。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = ReplacingMergeTree([ver])
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]
  • [ver]：可选参数，列的版本，可以是UInt、Date或者DateTime类型的字段作为版本号。该参数决定了数据去重的方式。
  • 当没有指定[ver]参数时，保留最新的数据；如果指定了具体的值，保留最大的版本数据。

建表示例

CREATE TABLE emp_replacingmergetree (
  emp_id UInt16 COMMENT '员工id',
  name String COMMENT '员工姓名',
  work_place String COMMENT '工作地点',
  age UInt8 COMMENT '员工年龄',
  depart String COMMENT '部门',
  salary Decimal32(2) COMMENT '工资'
  )ENGINE=ReplacingMergeTree()
  ORDER BY emp_id
  PRIMARY KEY emp_id
  PARTITION BY work_place
  ;
 -- 插入数据 
INSERT INTO emp_replacingmergetree
VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_replacingmergetree
VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);

注意点

当我们再次向该表插入具有相同主键的数据时，观察查询数据的变化

INSERT INTO emp_replacingmergetree
VALUES (1,'tom','上海',25,'技术部',50000);
-- 查询数据，由于没有进行合并，所以存在主键重复的数据
cdh04 :) select * from emp_replacingmergetree;                                        

SELECT *
FROM emp_replacingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
-- 执行合并操作
optimize table emp_replacingmergetree final;
-- 再次查询，相同主键的数据，保留最近插入的数据，旧的数据被清除
cdh04 :) select * from emp_replacingmergetree;       

SELECT *
FROM emp_replacingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘

从上面的示例中可以看出，ReplacingMergeTree是支持对数据去重的，那么是根据什么进行去重呢？答案是：ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY。我们在看一个示例：

CREATE TABLE emp_replacingmergetree1 (
  emp_id UInt16 COMMENT '员工id',
  name String COMMENT '员工姓名',
  work_place String COMMENT '工作地点',
  age UInt8 COMMENT '员工年龄',
  depart String COMMENT '部门',
  salary Decimal32(2) COMMENT '工资'
  )ENGINE=ReplacingMergeTree()
  ORDER BY (emp_id,name) -- 注意排序key是两个字段
  PRIMARY KEY emp_id     -- 主键是一个字段
  PARTITION BY work_place
  ;
 -- 插入数据 
INSERT INTO emp_replacingmergetree1
VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_replacingmergetree1
VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);

再次向该表中插入相同emp_id和name的数据，并执行合并操作，再观察数据

-- 插入数据
INSERT INTO emp_replacingmergetree1
VALUES (1,'tom','上海',25,'技术部',50000),(1,'sam','上海',25,'技术部',20000);
-- 执行合并操作
optimize table emp_replacingmergetree1 final;
-- 再次查询，可见相同的emp_id和name数据被去重，而形同的主键emp_id不会去重
-- ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY
cdh04 :) select * from emp_replacingmergetree1;                                                                           
SELECT *
FROM emp_replacingmergetree1

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ sam  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

至此，我们知道了ReplacingMergeTree是支持去重的，并且是按照ORDERBY排序键为基准进行去重的。细心的你会发现，上面的重复数据是在一个分区内的，那么如果重复的数据不在一个分区内，会发生什么现象呢？我们再次向上面的emp_replacingmergetree1表插入不同分区的重复数据

-- 插入数据
INSERT INTO emp_replacingmergetree1
VALUES (1,'tom','北京',26,'技术部',10000);
-- 执行合并操作
optimize table emp_replacingmergetree1 final;
-- 再次查询
-- 发现  1 │ tom  │ 北京       │  26 │ 技术部 │ 10000.00
-- 与    1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00
-- 数据重复，因为这两行数据不在同一个分区内
-- 这是因为ReplacingMergeTree是以分区为单位删除重复数据的。
-- 只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除
cdh04 :) select * from emp_replacingmergetree1;       

SELECT *
FROM emp_replacingmergetree1

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 技术部 │ 10000.00 │
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ sam  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 50000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

总结

  • 如何判断数据重复

    ReplacingMergeTree在去除重复数据时，是以ORDERBY排序键为基准的，而不是PRIMARY KEY。

  • 何时删除重复数据

    在执行分区合并时，会触发删除重复数据。optimize的合并操作是在后台执行的，无法预测具体执行时间点，除非是手动执行。

  • 不同分区的重复数据不会被去重

    ReplacingMergeTree是以分区为单位删除重复数据的。只有在相同的数据分区内重复的数据才可以被删除，而不同数据分区之间的重复数据依然不能被剔除。

  • 数据去重的策略是什么

    如果没有设置[ver]版本号，则保留同一组重复数据中的最新插入的数据；如果设置了[ver]版本号，则保留同一组重复数据中ver字段取值最大的那一行。

  • optimize命令使用

    一般在数据量比较大的情况，尽量不要使用该命令。因为在海量数据场景下，执行optimize要消耗大量时间

​

SummingMergeTree表引擎

该引擎继承了MergeTree引擎，当合并 SummingMergeTree 表的数据片段时，ClickHouse 会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值，即如果存在重复的数据，会对对这些重复的数据进行合并成一条数据，类似于group by的效果。

推荐将该引擎和 MergeTree 一起使用。例如，将完整的数据存储在 MergeTree 表中，并且使用 SummingMergeTree 来存储聚合数据。这种方法可以避免因为使用不正确的主键组合方式而丢失数据。

如果用户只需要查询数据的汇总结果，不关心明细数据，并且数据的汇总条件是预先明确的，即GROUP BY的分组字段是确定的，可以使用该表引擎。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = SummingMergeTree([columns]) -- 指定合并汇总字段
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

CREATE TABLE emp_summingmergetree (
  emp_id UInt16 COMMENT '员工id',
  name String COMMENT '员工姓名',
  work_place String COMMENT '工作地点',
  age UInt8 COMMENT '员工年龄',
  depart String COMMENT '部门',
  salary Decimal32(2) COMMENT '工资'
  )ENGINE=SummingMergeTree(salary)
  ORDER BY (emp_id,name) -- 注意排序key是两个字段
  PRIMARY KEY emp_id     -- 主键是一个字段
  PARTITION BY work_place
  ;
 -- 插入数据 
INSERT INTO emp_summingmergetree
VALUES (1,'tom','上海',25,'技术部',20000),(2,'jack','上海',26,'人事部',10000);
INSERT INTO emp_summingmergetree
VALUES (3,'bob','北京',33,'财务部',50000),(4,'tony','杭州',28,'销售事部',50000);

当我们再次插入具有相同emp_id,name的数据时，观察结果

INSERT INTO emp_summingmergetree
VALUES (1,'tom','上海',25,'信息部',10000),(1,'tom','北京',26,'人事部',10000);
cdh04 :) select * from emp_summingmergetree;
-- 查询
SELECT *
FROM emp_summingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
-- 执行合并操作
optimize table emp_summingmergetree final;
cdh04 :) select * from emp_summingmergetree;       
-- 再次查询，新插入的数据 1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 
-- 原来的数据 ：        1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00
-- 这两行数据合并成：    1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00
SELECT *
FROM emp_summingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 北京       │  26 │ 人事部 │ 10000.00 │
│      3 │ bob  │ 北京       │  33 │ 财务部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00 │
│      2 │ jack │ 上海       │  26 │ 人事部 │ 10000.00 │
└────────┴──────┴────────────┴─────┴────────┴──────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart───┬───salary─┐
│      4 │ tony │ 杭州       │  28 │ 销售事部 │ 50000.00 │
└────────┴──────┴────────────┴─────┴──────────┴──────────┘

注意点

要保证PRIMARY KEY expr指定的主键是ORDER BY expr 指定字段的前缀，比如

-- 允许
ORDER BY (A,B,C) 
PRIMARY KEY A  
-- 会报错
--  DB::Exception: Primary key must be a prefix of the sorting key
ORDER BY (A,B,C) 
PRIMARY KEY B

这种强制约束保障了即便在两者定义不同的情况下，主键仍然是排序键的前缀，不会出现索引与数据顺序混乱的问题。

总结

  • SummingMergeTree是根据什么对两条数据进行合并的

    用ORBER BY排序键作为聚合数据的条件Key。即如果排序key是相同的，则会合并成一条数据，并对指定的合并字段进行聚合。

  • 仅对分区内的相同排序key的数据行进行合并

    以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合Key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。

  • 如果没有指定聚合字段，会怎么聚合

    如果没有指定聚合字段，则会按照非主键的数值类型字段进行聚合

  • 对于非汇总字段的数据，该保留哪一条

    如果两行数据除了排序字段相同，其他的非聚合字段不相同，那么在聚合发生时，会保留最初的那条数据，新插入的数据对应的那个字段值会被舍弃

    -- 新插入的数据:        1 │ tom  │ 上海       │  25 │ 信息部 │ 10000.00 
    -- 最初的数据 ：        1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00

    -- 聚合合并的结果：      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00

Aggregatingmergetree表引擎

该表引擎继承自MergeTree，可以使用 AggregatingMergeTree 表来做增量数据统计聚合。如果要按一组规则来合并减少行数，则使用 AggregatingMergeTree 是合适的。AggregatingMergeTree是通过预先定义的聚合函数计算数据并通过二进制的格式存入表内。

与SummingMergeTree的区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则可以指定各种聚合函数。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

CREATE TABLE emp_aggregatingmergeTree (
  emp_id UInt16 COMMENT '员工id',
  name String COMMENT '员工姓名',
  work_place String COMMENT '工作地点',
  age UInt8 COMMENT '员工年龄',
  depart String COMMENT '部门',
  salary  AggregateFunction(sum,Decimal32(2)) COMMENT '工资'  
  )ENGINE=AggregatingMergeTree()
  ORDER BY (emp_id,name) -- 注意排序key是两个字段
  PRIMARY KEY emp_id     -- 主键是一个字段
  PARTITION BY work_place;

对于AggregateFunction类型的列字段，在进行数据的写入和查询时与其他的表引擎有很大区别，在写入数据时，需要调用*State函数；而在查询数据时，则需要调用相应的*Merge函数。对于上面的建表语句而言，需要使用sumState函数进行数据插入

-- 插入数据，
-- 注意：需要使用INSERT…SELECT语句进行数据插入
INSERT INTO TABLE emp_aggregatingmergeTree
SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(10000,2));
INSERT INTO TABLE emp_aggregatingmergeTree
SELECT 1,'tom','上海',25,'信息部',sumState(toDecimal32(20000,2));
-- 查询数据
SELECT 
  emp_id,
  name , 
  sumMerge(salary) 
FROM emp_aggregatingmergeTree
GROUP BY emp_id,name;
-- 结果输出
┌─emp_id─┬─name─┬─sumMerge(salary)─┐
│      1 │ tom  │         30000.00 │
└────────┴──────┴──────────────────┘

上面演示的用法非常的麻烦，其实更多的情况下，我们可以结合物化视图一起使用，将它作为物化视图的表引擎。而这里的物化视图是作为其他数据表上层的一种查询视图。

AggregatingMergeTree通常作为物化视图的表引擎，与普通MergeTree搭配使用。

-- 创建一个MereTree引擎的明细表
-- 用于存储全量的明细数据
-- 对外提供实时查询
CREATE TABLE emp_mergetree_base (
  emp_id UInt16 COMMENT '员工id',
  name String COMMENT '员工姓名',
  work_place String COMMENT '工作地点',
  age UInt8 COMMENT '员工年龄',
  depart String COMMENT '部门',
  salary Decimal32(2) COMMENT '工资'
  )ENGINE=MergeTree()
  ORDER BY (emp_id,name)
  PARTITION BY work_place
  ;
  
-- 创建一张物化视图
-- 使用AggregatingMergeTree表引擎
CREATE MATERIALIZED VIEW view_emp_agg
ENGINE = AggregatingMergeTree()
PARTITION BY emp_id
ORDER BY (emp_id,name)
AS SELECT
     emp_id,
     name,
     sumState(salary) AS salary
FROM emp_mergetree_base
GROUP BY emp_id,name;

-- 向基础明细表emp_mergetree_base插入数据
INSERT INTO emp_mergetree_base
VALUES (1,'tom','上海',25,'技术部',20000),
(1,'tom','上海',26,'人事部',10000);

-- 查询物化视图
SELECT 
  emp_id,
  name , 
  sumMerge(salary) 
FROM view_emp_agg
GROUP BY emp_id,name;
-- 结果
┌─emp_id─┬─name─┬─sumMerge(salary)─┐
│      1 │ tom  │         30000.00 │
└────────┴──────┴──────────────────┘

CollapsingMergeTree表引擎

CollapsingMergeTree就是一种通过以增代删的思路，支持行级数据修改和删除的表引擎。它通过定义一个sign标记位字段，记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除。当CollapsingMergeTree分区合并时，同一数据分区内，sign标记为1和-1的一组数据会被抵消删除。

每次需要新增数据时，写入一行sign标记为1的数据；需要删除数据时，则写入一行sign标记为-1的数据。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = CollapsingMergeTree(sign)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

建表示例

上面的建表语句使用CollapsingMergeTree(sign)，其中字段sign是一个Int8类型的字段

CREATE TABLE emp_collapsingmergetree (
  emp_id UInt16 COMMENT '员工id',
  name String COMMENT '员工姓名',
  work_place String COMMENT '工作地点',
  age UInt8 COMMENT '员工年龄',
  depart String COMMENT '部门',
  salary Decimal32(2) COMMENT '工资',
  sign Int8
  )ENGINE=CollapsingMergeTree(sign)
  ORDER BY (emp_id,name)
  PARTITION BY work_place;

使用方式

CollapsingMergeTree同样是以ORDER BY排序键作为判断数据唯一性的依据。

-- 插入新增数据,sign=1表示正常数据
INSERT INTO emp_collapsingmergetree 
VALUES (1,'tom','上海',25,'技术部',20000,1);

-- 更新上述的数据
-- 首先插入一条与原来相同的数据(ORDER BY字段一致),并将sign置为-1
INSERT INTO emp_collapsingmergetree 
VALUES (1,'tom','上海',25,'技术部',20000,-1);

-- 再插入更新之后的数据
INSERT INTO emp_collapsingmergetree 
VALUES (1,'tom','上海',25,'技术部',30000,1);

-- 查看一下结果
cdh04 :) select * from emp_collapsingmergetree ;

SELECT *
FROM emp_collapsingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │   -1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
-- 执行分区合并操作
optimize table emp_collapsingmergetree;
-- 再次查询，sign=1与sign=-1的数据相互抵消了，即被删除
cdh04 :) select * from emp_collapsingmergetree ;

SELECT *
FROM emp_collapsingmergetree

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00 │    1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘

注意点

  • 分区合并

    分数数据折叠不是实时的，需要后台进行Compaction操作，用户也可以使用手动合并命令，但是效率会很低，一般不推荐在生产环境中使用。

    当进行汇总数据操作时，可以通过改变查询方式，来过滤掉被删除的数据

    SELECT 
        emp_id, 
        name, 
        sum(salary * sign)
    FROM emp_collapsingmergetree
    GROUP BY 
        emp_id, 
        name
    HAVING sum(sign) > 0

    只有相同分区内的数据才有可能被折叠。其实，当我们修改或删除数据时，这些被修改的数据通常是在一个分区内的，所以不会产生影响。

  • 数据写入顺序

    值得注意的是：CollapsingMergeTree对于写入数据的顺序有着严格要求，否则导致无法正常折叠。

    -- 建表
    CREATE TABLE emp_collapsingmergetree_order (
      emp_id UInt16 COMMENT '员工id',
      name String COMMENT '员工姓名',
      work_place String COMMENT '工作地点',
      age UInt8 COMMENT '员工年龄',
      depart String COMMENT '部门',
      salary Decimal32(2) COMMENT '工资',
      sign Int8
      )ENGINE=CollapsingMergeTree(sign)
      ORDER BY (emp_id,name)
      PARTITION BY work_place
      ; 
      
    -- 先插入需要被删除的数据，即sign=-1的数据
    INSERT INTO emp_collapsingmergetree_order 
    VALUES (1,'tom','上海',25,'技术部',20000,-1);
    -- 再插入sign=1的数据
    INSERT INTO emp_collapsingmergetree_order 
    VALUES (1,'tom','上海',25,'技术部',20000,1);
    -- 查询表
    SELECT *
    FROM emp_collapsingmergetree_order

    ┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
    │      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │    1 │
    └────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
    ┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
    │      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │   -1 │
    └────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘
    -- 执行合并操作
    optimize table emp_collapsingmergetree_order;
    -- 再次查询表
    -- 旧数据依然存在
    SELECT *
    FROM emp_collapsingmergetree_order;
    ┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┐
    │      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │   -1 │
    │      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │    1 │
    └────────┴──────┴────────────┴─────┴────────┴──────────┴──────┘

    如果数据的写入程序是单线程执行的，则能够较好地控制写入顺序；如果需要处理的数据量很大，数据的写入程序通常是多线程执行的，那么此时就不能保障数据的写入顺序了。在这种情况下，CollapsingMergeTree的工作机制就会出现问题。但是可以通过VersionedCollapsingMergeTree的表引擎得到解决。

VersionedCollapsingMergeTree表引擎

上面提到CollapsingMergeTree表引擎对于数据写入乱序的情况下，不能够实现数据折叠的效果。VersionedCollapsingMergeTree表引擎的作用与CollapsingMergeTree完全相同，它们的不同之处在于，VersionedCollapsingMergeTree对数据的写入顺序没有要求，在同一个分区内，任意顺序的数据都能够完成折叠操作。

VersionedCollapsingMergeTree使用version列来实现乱序情况下的数据折叠。

建表语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

可以看出：该引擎除了需要指定一个sign标识之外，还需要指定一个UInt8类型的version版本号。

建表示例

CREATE TABLE emp_versioned (
  emp_id UInt16 COMMENT '员工id',
  name String COMMENT '员工姓名',
  work_place String COMMENT '工作地点',
  age UInt8 COMMENT '员工年龄',
  depart String COMMENT '部门',
  salary Decimal32(2) COMMENT '工资',
  sign Int8,
  version Int8
  )ENGINE=VersionedCollapsingMergeTree(sign, version)
  ORDER BY (emp_id,name)
  PARTITION BY work_place
  ;
  
  -- 先插入需要被删除的数据，即sign=-1的数据
INSERT INTO emp_versioned 
VALUES (1,'tom','上海',25,'技术部',20000,-1,1);
-- 再插入sign=1的数据
INSERT INTO emp_versioned 
VALUES (1,'tom','上海',25,'技术部',20000,1,1);
-- 在插入一个新版本数据
INSERT INTO emp_versioned 
VALUES (1,'tom','上海',25,'技术部',30000,1,2);

-- 先不执行合并，查看表数据
cdh04 :) select * from emp_versioned;

SELECT *
FROM emp_versioned

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00 │    1 │       2 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │    1 │       1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘
┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 20000.00 │   -1 │       1 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘

-- 获取正确查询结果
SELECT 
    emp_id, 
    name, 
    sum(salary * sign)
FROM emp_versioned
GROUP BY 
    emp_id, 
    name
HAVING sum(sign) > 0;

-- 手动合并
optimize table emp_versioned;

-- 再次查询
cdh04 :) select * from emp_versioned;

SELECT *
FROM emp_versioned

┌─emp_id─┬─name─┬─work_place─┬─age─┬─depart─┬───salary─┬─sign─┬─version─┐
│      1 │ tom  │ 上海       │  25 │ 技术部 │ 30000.00 │    1 │       2 │
└────────┴──────┴────────────┴─────┴────────┴──────────┴──────┴─────────┘

可见上面虽然在插入数据乱序的情况下，依然能够实现折叠的效果。之所以能够达到这种效果，是因为在定义version字段之后，VersionedCollapsingMergeTree会自动将version作为排序条件并增加到ORDER BY的末端，就上述的例子而言，最终的排序字段为ORDER BY emp_id,name，version desc。

GraphiteMergeTree表引擎

该引擎用来对 Graphite数据进行’瘦身’及汇总。对于想使用CH来存储Graphite数据的开发者来说可能有用。

如果不需要对Graphite数据做汇总，那么可以使用任意的CH表引擎；但若需要，那就采用 GraphiteMergeTree 引擎。它能减少存储空间，同时能提高Graphite数据的查询效率。

Author: Tunan
Link: http://yerias.github.io/2020/12/03/clickhouse/4/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
ClickHouse