ClickHouse表引擎 5.MergeTree 家族其它引擎

clickhouse

发布日期: 2021-06-13

文章字数: 5.8k

阅读时长: 25 分

阅读次数:

1. ReplacingMergeTree

1.1 介绍

MergeTree 虽然可以设置主键，但是主键没有唯一约束的作用，即使多行主键相同的数据一样能够写入成功，但是在某些场景下，我们希望表中不存在主键重复的数据。
ReplacingMergeTree 就是为这种场景设计的，它能够在分区合并时删除重复数据
创建 ReplacingMergeTree 语法

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name(
  name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  ...
) ENGINE = ReplacingMergeTree(var)
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

其中 var 是选填参数，会指定一个 UInt、Date或 DateTime 类型的段作为版本号，这个参数确定了去重时使用的算法。

1.2 使用示例

准备数据

id	code	create_time
A01	102	2021-06-03 10:04:35
A01	101	2021-06-03 10:05:35
A02	104	2021-06-03 10:06:35
A02	103	2021-06-03 10:07:35
A03	105	2021-06-03 10:08:35
A03	105	2021-06-03 10:09:35

创建测试表并插入数据

-- 创建表
CREATE TABLE t_merge_replace
(
  `id` String,
  `code` UInt8,
  `create_time` DateTime
)
ENGINE = ReplacingMergeTree
partition by toYYYYMM(create_time)
ORDER BY (id,code)
primary key id;

-- 建表时的 order by 是删除数据的判断依据，在这张表中，会依据id 和 code 两个字段进行去重

-- 插入数据
insert into t_merge_replace values
('A01',102,'2021-06-03 10:04:35'),
('A01',101,'2021-06-03 10:05:35'),
('A02',104,'2021-06-03 10:06:35'),
('A02',103,'2021-06-03 10:07:35'),
('A03',105,'2021-06-03 10:08:35'),
('A03',105,'2021-06-03 10:09:35');

执行分区合并删除重复数据

-- 手动合并分区
optimize table t_merge_replace;

-- 查看去重后的数据
 select * from t_merge_replace;

┌─id──┬─code─┬─────────create_time─┐
│ A01 │  101 │ 2021-06-03 10:05:35 │
│ A01 │  102 │ 2021-06-03 10:04:35 │
│ A02 │  103 │ 2021-06-03 10:07:35 │
│ A02 │  104 │ 2021-06-03 10:06:35 │
│ A03 │  105 │ 2021-06-03 10:09:35 │
└─────┴──────┴─────────────────────┘

-- 经过对比可以发现，并没有根据 primary key 去重，
-- 而是根据 id,code 组合进行去重，从而只删除了 A03 其中的一条数据

不同分区数据去重

从上面的例子中可以看到去重效果还不错，但是当插入不同分区的重复数据时发现并不是想象的那样

-- 插入新的数据
insert into t_merge_replace values
('A01',102,'2021-07-03 10:04:35'),
('A01',101,'2021-07-03 10:05:35');

-- 查询数据
 select * from t_merge_replace;

┌─id──┬─code─┬─────────create_time─┐
│ A01 │  101 │ 2021-06-03 10:05:35 │
│ A01 │  102 │ 2021-06-03 10:04:35 │
│ A02 │  103 │ 2021-06-03 10:07:35 │
│ A02 │  104 │ 2021-06-03 10:06:35 │
│ A03 │  105 │ 2021-06-03 10:09:35 │
└─────┴──────┴─────────────────────┘
┌─id──┬─code─┬─────────create_time─┐
│ A01 │  101 │ 2021-07-03 10:05:35 │
│ A01 │  102 │ 2021-07-03 10:04:35 │
└─────┴──────┴─────────────────────┘

-- 按照 前面的规则（根据 id,code 去重）
-- 那么当手动合并分区的时候，因为新曾部分数据，导致会有重复数据被删掉，
-- 但是手动合并分区发现并不是这样

-- 合并分区
 optimize table t_merge_replace;
-- 查询数据
 select * from t_merge_replace;

┌─id──┬─code─┬─────────create_time─┐
│ A01 │  101 │ 2021-07-03 10:05:35 │
│ A01 │  102 │ 2021-07-03 10:04:35 │
└─────┴──────┴─────────────────────┘
┌─id──┬─code─┬─────────create_time─┐
│ A01 │  101 │ 2021-06-03 10:05:35 │
│ A01 │  102 │ 2021-06-03 10:04:35 │
│ A02 │  103 │ 2021-06-03 10:07:35 │
│ A02 │  104 │ 2021-06-03 10:06:35 │
│ A03 │  105 │ 2021-06-03 10:09:35 │
└─────┴──────┴─────────────────────┘

-- 可以看到数据 （A01,101,A02,102）依旧重复
-- 原因是 ReplacingMergeTree 是以分区为单位删除重复数据的，不同分区内的重复数据依旧是无法去重的

1.3 版本号的用法

-- 创建表
CREATE TABLE t_merge_replace2
(
  `id` String,
  `code` UInt8,
  `create_time` DateTime
)
ENGINE = ReplacingMergeTree(create_time)
partition by toYYYYMM(create_time)
ORDER BY (id)
primary key id;

-- 建表时的 order by 是删除数据的判断依据，在这张表中，会依据id 和 code 两个字段进行去重

-- 插入数据
insert into t_merge_replace2 values
('A01',102,'2021-06-03 10:04:35'),
('A01',101,'2021-06-03 10:05:35'),
('A02',104,'2021-06-03 10:06:35'),
('A02',103,'2021-06-03 10:07:35'),
('A03',105,'2021-06-03 10:08:35'),
('A03',105,'2021-06-03 10:09:35');

-- 手动合并分区
optimize table t_merge_replace2;

-- 查看数据
 select * from t_merge_replace2;

┌─id──┬─code─┬─────────create_time─┐
│ A01 │  101 │ 2021-06-03 10:05:35 │
│ A02 │  103 │ 2021-06-03 10:07:35 │
│ A03 │  105 │ 2021-06-03 10:09:35 │
└─────┴──────┴─────────────────────┘

-- 可以看到每个 id 的 create_time 最大的一条数据被保留下来

1.4 总结

使用 order by 排序键作为判断数据是否重复的依据
只有在分区合并时触发删除重复数据的逻辑
以分区为单位删除重复数据，不同分区内的重复数据不会去重
进行数据去重时，因为分区内的数据已经按照order by 进行排序了，所以能够找到重复的数据
去重策略有联众
1. 如果没有设置版本号（var）保留同一组中最后一行数据
2. 如果设置了版本号（var）保留同一组数据中保留var 字段取值最大的一行数据

2. SummingMergeTree

2.1 介绍

SummingMergeTree是为了只关心汇总结果，不关心明细数据，且汇总条件固定不会随意修改的场景而设计的。
SummingMergeTree能够在合并分区时按照预先定义的条件汇总聚合数据，将同一组下的多行数据合并成一行，这样既减少了数据量，有降低了查询汇总数据时的消耗。

2.2 排序键与主键

在 MergeTree 分区内数据会按照 order by （排序键）表达式排序，主键索引也会按照 primary key（主键）表达式取值并排序，而 order by 可以取代主键，在一般情形下，只需要定义 order by 即可，此时排序键与主键相同，即数据排序与主键索引相同。

如果需要同时定义排序键与主键通常只有一种情况，那就是希望排序键与主键不同，这种情况通常只在定义 SummingMergeTree 和 AggregratingMergeTree 时才会出现，这是因为，这两种表引擎的数据聚合都是根据排序键进行的，主键与聚合的条件定义分离，为修改聚合条件留下更大的空间。

-- 假设现有一张数据表，表中有 A,B,C,D,E,F 六个字段，如果按照 A,B,C汇总则条件为：
order by (A,B,C)

-- 上面这样定义表，主键也会被定义为 A,B,C 
-- 假如此时业务上只会根据 A 字段进行过滤，应该只使用 A 字段作为主键，那么就需要更换一种定义形式：
order by (A,B,C)
primary key A

-- 如果同时声明了排序键和主键，那么 MergeTree 会强制要求 主键 为 排序键 的前缀
-- 即如下定义是错误的
order by (A,B,C)
primary key B

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

当业务发生变化，只需要根据需要增减排序键改变聚合规则即可，相对于不可修改的主键，便利了很多。

-- 减少排序键
alter table table_name modify ordery by (A,B)

2.3 使用介绍


CREATE TABLE [IF NOT EXISTS] [db_name.]table_name(
  name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  ...
) ENGINE = SummingMergeTree((col1,col2,...))
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

col1,col2,… 是选填项，为 columns 参数值，用于设置除主键外的其它数值类型字段，以指定被 sum 汇总的列字段，若不指定此字段，会将所有非主键的数值类型字段进行汇总
ORDER BY 排序键，也作为数据聚合的依据
PRIMARY KEY 主键，主键必须作为排序键的前缀

2.4 使用示例

准备数据

id	code	num1	num2	create_time
A01	C01	102	10.3	2021-06-03 10:04:35
A01	C01	101	5.7	2021-06-03 10:05:35
A01	C02	104	9.7	2021-06-03 10:06:35
A02	C02	103	5.8	2021-06-03 10:07:35
A02	C02	105	3.2	2021-06-03 10:08:35
A02	C03	105	1.3	2021-06-03 10:09:35

创建测试表并插入数据

-- 建表
CREATE TABLE db_merge.t_merge_sum (
  `id` String,
  `code` String,
  `num1` UInt32,
  `num2` Float32,
  `create_time` DateTime
) ENGINE = SummingMergeTree()
partition by toYYYYMM(create_time)
ORDER BY (id,code)
primary key id;

-- 插入测试数据
insert into db_merge.t_merge_sum values
('A01','C01',102,9.3,'2021-06-03 10:04:35'),
('A01','C01',101,5.7,'2021-06-03 10:05:35'),
('A01','C02',104,9.7,'2021-06-03 10:06:35'),
('A02','C02',103,5.8,'2021-06-03 10:07:35'),
('A02','C02',105,3.2,'2021-06-03 10:08:35'),
('A02','C03',105,1.3,'2021-06-03 10:09:35');

-- 强制合并操作
optimize table db_merge.t_merge_sum final;

-- 查看数据
 select * from db_merge.t_merge_sum;

┌─id──┬─code─┬─num1─┬─num2─┬─────────create_time─┐
│ A01 │ C01  │  203 │   15 │ 2021-06-03 10:04:35 │
│ A01 │ C02  │  104 │  9.7 │ 2021-06-03 10:06:35 │
│ A02 │ C02  │  208 │    9 │ 2021-06-03 10:07:35 │
│ A02 │ C03  │  105 │  1.3 │ 2021-06-03 10:09:35 │
└─────┴──────┴──────┴──────┴─────────────────────┘

-- 可以看到 A01,C01 等多行数据汇总成了一行，而不再汇总范围的 create_time 取了每组的第一行的值
-- 与 ReplacingMergeTree 相同，聚合数据只是在同一分区内，不同分区的数据无法进行聚合汇总

嵌套类型数据聚合

SummingMergeTree 同样支持嵌套类型的字段，在使用嵌套类型段时，需要被 sum汇总的字段名称必须以map 后缀结尾，

-- 建表
CREATE TABLE db_merge.t_merge_sum_nested
(
    `id` String,
    `nestMap` Nested(id UInt32, key UInt32, val UInt32),
    `create_time` DateTime
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(create_time)
ORDER BY id;

-- 插入测试数据
insert into db_merge.t_merge_sum_nested values
('A01',  [1,1,2], [10,20,30],[10,11,12],'2021-06-03 10:04:35'),
('A01',  [3,3,4], [40,50,60],[13,14,15],'2021-06-03 10:05:35'),
('A02',  [5,5,6], [70,80,90],[16,17,18],'2021-06-03 10:06:35');

-- 强制合并操作
optimize table db_merge.t_merge_sum final;

-- 查看聚合结果数据
 select * from db_merge.t_merge_sum_nested;

┌─id──┬─nestMap.id─┬─nestMap.key───┬─nestMap.val───┬─────────create_time─┐
│ A01 │ [1,2,3,4]  │ [30,30,90,60] │ [21,12,27,15] │ 2021-06-03 10:04:35 │
│ A02 │ [5,6]      │ [150,90]      │ [33,18]       │ 2021-06-03 10:06:35 │
└─────┴────────────┴───────────────┴───────────────┴─────────────────────┘

在使用嵌套类型的时候，也支持使用复合 key 作为聚合条件为了使用复合 key ，在嵌套类型的字段中，除第一个字段外，任何以 key，id 或 type为后缀结尾的字段，都将和第一个字段组成复合 key。如将上面的 key 改为 Key，就会以 id 和 Key 作为聚合条件。

2.5 总结

用 order by 排序键作为聚合数据的条件 key
只有在分区合并的时候才会触发合并逻辑
以分区为单位聚合数据，合并分区时，同一分区内相同排序键的数据将会被合并汇总，不同分区间的数据不会被汇总
如果在定义引擎时指定了 columns 参数值，则会汇总被指定的列字段，若不指定此字段，会将所有非主键的数值类型字段进行汇总
在进行数据汇总时，因为分区内的数据已经进行了 order by 排序，所以能够找到相邻具有相同聚合 key 的数据。
在进行数据汇总时，同一分区内多行相同聚合 key 的数据会合并成一行，对于非聚合字段，会取第一行数据的值
支持嵌套结构，但列明字段必须以 Map 结尾，嵌套类型中，默认以第一个字段作为聚合 key，除第一个字段外，任何以Key、ID或Type为后缀的字段，都将与第一个字段一起组成复合 key。

3. AggregatingMergeTree

数据仓库领域有一个十分常见的模型，数据立方体，它通过以空间换时间的方式提升查询性能，将需要聚合的数据预先算出来，并将结果保存，在需要进行聚合查询时，直接查询结果

3.1 介绍

AggregatingMergeTree 跟数据立方体有些相似，它能够在合并分区时，按照预先定义的条件聚合数据。
AggregatingMergeTree 会根据预先定义的聚合函数计算数据并通过二进制的格式存入表中。
AggregatingMergeTree 将同一分组下的多行数据聚合成一行，即减少了数据又降低了后续聚合查询的开销。
AggregatingMergeTree 相当于是SummingMergeTree的升级版，二者有许多设计思路是一致的，比如排序键与主键的设计。

3.2 定义方式

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name(
  name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  name3 AggregateFunction(Function,type),
  ...
) ENGINE = AggregatingMergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

AggregatingMergeTree 没有额外设置的参数，合并分区时在每个数据分区内，会按照order by 聚合数据，而做哪种聚合操作会通过AggregateFunction 数据类型实现。

3.3 使用介绍

AggregatingMergeTree 是 clickhouse提供的一种特殊数据类型，它能够以二进制的形式存储中间状态结果。
AggregatingMergeTree 使用方式也有些特殊，对于AggregateFunction 类型的列，数据的查询与写入都与平常的列不同，
AggregatingMergeTree 在写入数据时需要调用对应的State函数，而在查询时，需要调用对应的 Merge 函数。
示例如下：

-- 建表
CREATE TABLE db_merge.t_merge_agg (
  `id` String,
  `city` String,
  `code` AggregateFunction(uniq,String), -- 聚合字段 等同于 UNIQ(code)
  `value` AggregateFunction(sum,UInt32), -- 聚合字段 等同于 SUM(value)
  `create_time` DateTime
) ENGINE = AggregatingMergeTree()
partition by toYYYYMM(create_time)
ORDER BY (id,city) -- 等同于 group by id,city
primary key id;


-- 插入数据
-- 需要调用 UNIQ对应的 uniqState 方法 和 sum 对应的 sumState 方法，并使用 insert select 语法
insert into table db_merge.t_merge_agg
select 'A01','shenzhen',uniqState('code1'),sumState(toUInt32(100)),'2021-06-04 15:27:22'
union all 
select 'A01','shenzhen',uniqState('code2'),sumState(toUInt32(200)),'2021-06-04 15:28:22';

-- 查询数据
-- 需要调用 UNIQ对应的 uniqMrege 方法 和 sum 对应的 sumMerge 方法

select id,city,uniqMerge(code),sumMerge(value) from db_merge.t_merge_agg group by id,city;

┌─id──┬─city─────┬─uniqMerge(code)─┬─sumMerge(value)─┐
│ A01 │ shenzhen │               2 │             300 │
└─────┴──────────┴─────────────────┴─────────────────┘

AggregatingMergeTree 更常用的方式是结合物化视图使用，将它作为物化视图的表引擎。

物化视图使用AggregatingMergeTree 表引擎用于特定场景的数据查询，比 MergeTree 的性能更高，

使用示例如下：


-- 先建立明细数据表
CREATE TABLE db_merge.t_merge_basic (
  `id` String,
  `value` UInt32, 
  `create_time` DateTime
) ENGINE = MergeTree()
partition by toYYYYMM(create_time)
ORDER BY id;

-- 使用 MergeTree 表作为底表，存储明细数据，对外提供实时查询的能力
-- 创建物化视图
CREATE MATERIALIZED VIEW db_merge.t_merge_basic_view
ENGINE = AggregatingMergeTree()
partition by month
ORDER BY (id) 
as
select 
  id,
  toYYYYMM(create_time) as month,
  sumState(value) as value
from db_merge.t_merge_basic
group by id,month;

drop VIEW db_merge.t_merge_basic_view;

-- 向明细数据表增加数据

insert into db_merge.t_merge_basic values
('A01',102,'2021-06-03 10:04:35'),
('A01',101,'2021-06-03 10:05:35'),
('A02',104,'2021-06-03 10:06:35'),
('A02',103,'2021-06-03 10:07:35'),
('A03',105,'2021-06-03 10:08:35'),
('A03',105,'2021-06-03 10:09:35');

-- 查询明细表数据
select * from db_merge.t_merge_basic;

┌─id──┬─value─┬─────────create_time─┐
│ A01 │   102 │ 2021-06-03 10:04:35 │
│ A01 │   101 │ 2021-06-03 10:05:35 │
│ A02 │   104 │ 2021-06-03 10:06:35 │
│ A02 │   103 │ 2021-06-03 10:07:35 │
│ A03 │   105 │ 2021-06-03 10:08:35 │
│ A03 │   105 │ 2021-06-03 10:09:35 │
└─────┴───────┴─────────────────────┘

-- 查询物化视图数据

select id,month,sumMerge(value) as value from db_merge.t_merge_basic_view group by id,month;

┌─id──┬──month─┬─value─┐
│ A01 │ 202106 │   203 │
│ A02 │ 202106 │   207 │
│ A03 │ 202106 │   210 │
└─────┴────────┴───────┘

3.4 总结

用 order by 排序键作为聚合数据的条件 key
只有在分区合并的时候才会触发合并逻辑
以分区为单位聚合数据，合并分区时，同一分区内相同排序键的数据将会被聚合计算，不同分区间的数据不会被计算
在进行数据汇总时，因为分区内的数据已经进行了 order by 排序，所以能够找到相邻具有相同聚合 key 的数据
在进行数据聚合时，同一分区内多行相同聚合 key 的数据会合并成一行，对于非主键非聚合字段，会取第一行数据的值
使用 AggregateFunction 定义聚合函数与聚合字段
AggregateFunction 类型的字段使用二进制存储，在写入数据时需要调用对应的State函数，而在查询时，需要调用对应的 Merge 函数。
AggregatingMergeTree 通常作为物化视图的引擎表，与 MergeTree 配合使用

4. CollapsingMergeTree

4.1 介绍

海量数据的更新与删除，通常是一个让人头疼的问题，一种最符合常规思维的逻辑是先找到要修改数据的文件，接着修改文件，删除或修改有变化的数据，然而在大数据领域，或者说对于clickhouse 这类高性能分析性数据库而言，对数据源文件的修改是一件代价非常昂贵的操作，相较于直接修改源文件，更多时候会将删除与修改操作，转换为新增操作，即以增代删。

CollapsingMergeTree 就是一种通过以增代删的思路，支持行级数据修改和删除的表引擎。
CollapsingMergeTree 通过一个标记位 sign 来标识该行记录的状态，如果 sign 为 1 表示该行数据有效，如果 sign 为 -1 表示数据需要被删除
CollapsingMergeTree 合并分区时，同一分区内sign 标记为 1 和-1 的一组数据会被抵消删除
CollapsingMergeTree 以 order by 作为后续判断数据是否重复的唯一标准。
CollapsingMergeTree 支持对一行数据进行更新与删除操作。

4.2 使用介绍


CREATE TABLE [IF NOT EXISTS] [db_name.]table_name(
  name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  ...
) ENGINE = CollapsingMergeTree(sign) -- sign 用于指定一个 Int8 类型的标识位字段
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]


-- 建表示例
CREATE TABLE db_merge.t_merge_collapse (
  `id` String,
  `code` UInt32,
  `create_time` DateTime,
  `sign` Int8
) ENGINE = CollapsingMergeTree(sign)
partition by toYYYYMM(create_time)
ORDER BY id;


-- 插入测试数据

insert into db_merge.t_merge_collapse values
('A01',100,'2021-06-04 17:21:01',1),
('A02',200,'2021-06-04 17:22:01',1),
('A03',300,'2021-06-04 17:23:01',1),
('A04',400,'2021-06-04 17:24:01',1);

-- 查询数据
select * from db_merge.t_merge_collapse;

┌─id──┬─code─┬─────────create_time─┬─sign─┐
│ A01 │  100 │ 2021-06-04 17:21:01 │    1 │
│ A02 │  200 │ 2021-06-04 17:22:01 │    1 │
│ A03 │  300 │ 2021-06-04 17:23:01 │    1 │
│ A04 │  400 │ 2021-06-04 17:24:01 │    1 │
└─────┴──────┴─────────────────────┴──────┘


-- 修改一条数据 
insert into db_merge.t_merge_collapse values
('A01',500,'2021-06-04 17:21:01',1)

-- 删除一条数据
insert into db_merge.t_merge_collapse values
('A02',200,'2021-06-04 17:22:01',-1);

-- 强制合并查看结果

optimize table db_merge.t_merge_collapse;

-- 查看数据
select * from db_merge.t_merge_collapse;

┌─id──┬─code─┬─────────create_time─┬─sign─┐
│ A01 │  500 │ 2021-06-04 17:21:01 │    1 │
│ A03 │  300 │ 2021-06-04 17:23:01 │    1 │
│ A04 │  400 │ 2021-06-04 17:24:01 │    1 │
└─────┴──────┴─────────────────────┴──────┘

4.3 折叠规则

CollapsingMergeTree 折叠数据时遵循下面的规则：

当 sign = 1 与 sign = -1 一样多时：

- 最后一行是 <font color='green'>sign = 1</font>：保留第一行 <font color='red'>sign = -1</font> 和最后一行 <font color='green'>sign = 1</font>
- 最后一行是 <font color='red'>sign = -1</font>：什么也不保留

当sign = 1 比 sign = -1 多一行时：

保留最后一行 sign = 1

当sign = -1 比 sign = 1 多一行时：

保留第一行sign = -1

其余情况，clickhouse 会打印警告信息，但是不会报错，查询结果未可预知。

4.4 注意事项

数据折叠不是事实触发，而是在分区合并时触发，在分区合并之前，还能够查询到旧数据，这种情况有两种解决办法：

在查询前进行强制分区合并，但这种方法效率很低，生产环境慎用。
改变查询方式，如下示例：


select 
  id,sum(code),count(code),avg(code) 
from db_merge.t_merge_collapse 
group by id

-- 修改为：

select 
  id,sum(code * sign),count(code * sign),avg(code * sign) 
from  db_merge.t_merge_collapse 
group by id 
having sum(sign) > 0;

只有相同分区内的数据可以被折叠，通常来说，修改或删除的数据都处于同一分区，所以这不是很严重的问题
CollapsingMergeTree对写入数据的顺序有着严格的要求，
- 如果按照正常顺序写入，先写入 sign = 1 再写入 sign = -1 数据能够正常折叠
- 如果将写入顺序掉转，先写入sign = -1 再写入 sign = 1 则不能够折叠

这是因为CollapsingMergeTree 的处理机制引起的，它要求sign = 1 和 sign = -1 数据相邻，而分区内数据基于 order by 排序，要按照sign = 1 和 sign = -1 数据相邻只能严格按照顺序写入。

如果数据时单线程写入就会很好控制写入顺序，但想处理大量数据往往是多线程写入，这时就不太好控制写入顺序了这种情况下CollapsingMergeTree的工作机制就会出现问题，还好clickhouse 提供了VersionedCollapsingMergeTree 表引擎来解决这个问题。

5. VersionedCollapsingMergeTree

5.1 介绍

VersionedCollapsingMergeTree 表引擎的作用于CollapsingMergeTree完全相同
VersionedCollapsingMergeTree 与CollapsingMergeTree不同之处在于其对写入顺序没有要求
VersionedCollapsingMergeTree 通过版本号来实现这一特性
VersionedCollapsingMergeTree 在定义版本号字段（ver）之后，会自动将 ver 作为排序条件增加到order by 末端，所以无论写入顺序如何，在折叠处理时都能回到正确的顺序。

5.2 使用示例

建表语法

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name(
  name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
  ...
) ENGINE = VersionedCollapsingMergeTree(sign,ver)
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

使用示例

-- 建表
CREATE TABLE db_merge.t_merge_version_collapse (
  `id` String,
  `code` UInt32,
  `create_time` DateTime,
  `sign` Int8,
  `ver` UInt8
) ENGINE = VersionedCollapsingMergeTree(sign,ver)
partition by toYYYYMM(create_time)
ORDER BY id; -- 会按照 order by id,ver desc 排序

删除数据示例


-- 插入数据
insert into db_merge.t_merge_version_collapse values ('A01',100,'2021-06-04 17:21:01',-1,1);
insert into db_merge.t_merge_version_collapse values ('A01',200,'2021-06-04 17:21:01',1,1);

-- 查看数据

select * from db_merge.t_merge_version_collapse;

┌─id──┬─code─┬─────────create_time─┬─sign─┬─ver─┐
│ A01 │  200 │ 2021-06-04 17:21:01 │    1 │   1 │
└─────┴──────┴─────────────────────┴──────┴─────┘
┌─id──┬─code─┬─────────create_time─┬─sign─┬─ver─┐
│ A01 │  100 │ 2021-06-04 17:21:01 │   -1 │   1 │
└─────┴──────┴─────────────────────┴──────┴─────┘
-- 强制合并后查看数据
optimize table db_merge.t_merge_version_collapse final;

-- 查看数据
select * from db_merge.t_merge_version_collapse;
Query id: 80e69d05-b279-4f7a-9f1a-3640628fa2a8

Ok.

0 rows in set. Elapsed: 0.003 sec.

更新数据测试

-- 更新数据
insert into db_merge.t_merge_version_collapse values ('A01',100,'2021-06-04 17:21:01',-1,1);
insert into db_merge.t_merge_version_collapse values ('A01',200,'2021-06-04 17:21:01',1,1);
insert into db_merge.t_merge_version_collapse values ('A01',300,'2021-06-04 17:21:01',1,1);

-- 强制合并后查看数据
optimize table db_merge.t_merge_version_collapse final;

-- 查看数据
select * from db_merge.t_merge_version_collapse;

┌─id──┬─code─┬─────────create_time─┬─sign─┬─ver─┐
│ A01 │  300 │ 2021-06-04 17:21:01 │    1 │   1 │
└─────┴──────┴─────────────────────┴──────┴─────┘

6. MergeTree表引擎之间的关系

6.1 继承关系

MergeTree 表引擎下面有 6 个变种MergeTree 表引擎。
这 7 种表引擎主要区别在于，Merge 合并数据的逻辑上。
在具体实现逻辑部分，7 中 Merge 共用一个主体，在触发 merge动作时，它们分别实现了自己的合并逻辑。
除了 MergeTree 之外，其它 6 个变种的 Merge 逻辑都是建立在MergeTree 之上的，并且均继承于MergingSortedBlockInputStream。
MergingSortedBlockInputStream 的主要作用是按照order by 的规则保持新分区数据的有序性，其它 6 个变种在此基础上各有所长，或去除重复数据或进行汇总操作。
从继承角度看7 中 MergeTree 主要区别在于Merge 逻辑部分，所以特殊逻辑之后再 Merge 合并时触发。

6.2 组成关系

ReplicatedMergeTree 跟普通的 MergeTree 有什么关系呢？

ReplicatedMergeTree 在 MergeTree 的基础上增加了分布式协调处理的能力，借助 Zookeeper 的消息广播功能，实现了副本之间的数据同步。
ReplicatedMergeTree 系列可以用组合关系来理解
7 种 MergeTree 引擎前面加上 Replicated 又能组合出 7 种新的引擎，这些引擎拥有副本协同能力

hnbian

https://www.hnbian.cn/posts/f1dd16d2.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 hnbian !

olap clickhouse

ClickHouse表引擎 6.外部存储类型表引擎

1. 介绍除了 MergeTree 家族的表引擎之外，clickhouse 还支持外部存储、内存、日志等多个种类的表引擎，每个种类的表引擎下面都对应多种表引擎。外部存储系列的表引擎：能够直接读取其他系统的数据，clickhouse 只需

2021-06-20 clickhouse

olap clickhouse

ClickHouse表引擎 4.MergeTree 多路径存储

1. 多路径存储策略介绍 ClickHouse 19.15 版本之前，Merge 支持支单路径存储，所有数据都被写入 config.xml 配置的 path 指定的路径下，即使挂载多块磁盘也无法有效利用这些空间为了解决这个痛点，19.15

2021-06-05 clickhouse

olap clickhouse

ClickHouse表引擎 5.MergeTree 家族其它引擎

1. ReplacingMergeTree

1.1 介绍

1.2 使用示例

1.3 版本号的用法

1.4 总结

2. SummingMergeTree

2.1 介绍

2.2 排序键与主键

2.3 使用介绍

2.4 使用示例

2.5 总结

3. AggregatingMergeTree

3.1 介绍

3.2 定义方式

3.3 使用介绍

3.4 总结

4. CollapsingMergeTree

4.1 介绍

4.2 使用介绍

4.3 折叠规则

4.4 注意事项

5. VersionedCollapsingMergeTree

5.1 介绍

5.2 使用示例

6. MergeTree表引擎之间的关系

6.1 继承关系

6.2 组成关系

你的赏识是我前进的动力