一 MergeTree
建表示例(这里只讲解几个核心参数):
create table default.order_merge_tree(
id UInt32,
order_id String,
order_no String,
total_amount Decimal(16,2),
create_time Datetime
) engine =MergeTree()
order by (id,sku_id)
partition by toYYYYMMDD(create_time)
primary key (id);
1 语句解析:
①.(必填)ENGINE
- 引擎名和参数。 ENGINE = MergeTree()
. MergeTree
引擎没有参数
②.(必填)ORDER BY
— 排序键,可以是一组列的元组或任意的表达式
- 例如:
ORDER BY (order_id, create_time)
。如果没有使用PRIMARY KEY
显式指定的主键,ClickHouse 会使用排序键作为主键 - 如果不需要排序,可以使用
ORDER BY tuple()
③.(选填)PARTITION BY
— 分区键
- 要按月分区,可以使用表达式
toYYYYMM(create_time)
,这里的create_time
是一个 Date 类型的列, 分区名的格式会是"YYYYMM"
- 分区的好处是降低扫描范围提升速度,不填写默认就使用一个分区
④.(选填)PRIMARY KEY
-主键,作为数据的一级索引,但是不是唯一约束,和其他数据库区分
- 如果要 选择与排序键不同的主键,在这里指定,可选项,
- 默认情况下主键跟排序键(由
ORDER BY
子句指定)相同。 - 大部分情况下不需要再专门指定一个
PRIMARY KEY
注意:PRIMARY KEY必须为ORDER BY的前缀
比如ORDER BY (order_id
, create_time
),那主键需要是(order_id
)或 (order_id
, create_time
)
2.分区合并讲解
这里我们是根据年月日设置分区的,年月日相同的数据会在一个分区中,这样查询的时候就会降低查询范围
新的数据写入会有临时分区产生,系统自动帮我们根据分区键加入已有分区
二 去重合并树ReplacingMergeTree
1.介绍
① 是MergeTree的拓展,该引擎和 MergeTree 的不同之处在它会删除【排序键值】相同重复项,根据OrderBy字段,如果OrderBy是两个字段,那么两个字段完全相同才会去重
② 数据的去重只会在数据合并期间进行。合并会在后台一个不确定的时间进行,因此你无法预先作出计划
③ 有一些数据可能仍未被处理,尽管可以调用 OPTIMIZE 语句发起计划外的合并,但请不要依靠它,因为 OPTIMIZE 语句会引发对数据的大量读写
④ 因此,ReplacingMergeTree 适用于在后台清除重复的数据以节省空间,但是它不保证没有重复的数据出现
⑤ 如果是有多个分区表,只在分区内部进行去重,不会跨分区
2.建表示例
create table default.order_relace_merge_tree(
id UInt32,
sku_id String,
out_trade_no String,
total_amount Decimal(16,2),
create_time Datetime
) engine =ReplacingMergeTree(id)
order by (sku_id)
partition by toYYYYMMDD(create_time)
primary key (sku_id);
3.总结
-
如何判断数据重复
- 在去除重复数据时,是以ORDER BY排序键为基准的,而不是PRIMARY KEY
- 若排序字段为两个,则两个字段都相同时才会去重
-
何时删除重复数据
- 在执行分区合并时触发删除重复数据,optimize的合并操作是在后台执行的,无法预测具体执行时间点,除非是手动执行
-
不同分区的重复数据不会被去重
- ReplacingMergeTree是以分区为单位删除重复数据的,在相同的数据分区内重复的数据才会被删除,而不同数据分区之间的重复数据依然不能被删除的
-
删除策略
- ReplacingMergeTree() 填入的参数为版本字段,重复数据就会保留版本字段值最大的。
- 如果不填写版本字段,默认保留插入顺序的最后一条数据
三 聚合引擎SummingMergeTree
1.建表示例
create table default.order_summing_merge_tree(
id UInt32,
sku_id String,
out_trade_no String,
total_amount Decimal(16,2),
create_time Datetime
) engine =SummingMergeTree(total_amount)
order by (id,sku_id)
partition by toYYYYMMDD(create_time)
primary key (id);
2.使用说明
-
SummingMergeTree是根据什么对数据进行合并的
- 【ORBER BY排序键相同】作为聚合数据的条件Key的行中的列进行汇总,将这些行替换为包含汇总数据的一行记录
-
跨分区内的相同排序key的数据是否会进行合并**
- 以数据分区为单位来聚合数据,同一数据分区内相同ORBER BY排序键的数据会被合并汇总,而不同分区之间的数据不会被汇总
-
如果没有指定聚合字段,会怎么聚合
- 如果没有指定聚合字段,则会用非维度列,且是数值类型字段进行聚合
-
对于非汇总字段的数据,该保留哪一条
- 如果两行数据除了【ORBER BY排序键】相同,其他的非聚合字段不相同,在聚合时会【保留最初】的那条数据,新插入的数据对应的那个字段值会被舍弃
-
在合并分区的时候按照预先定义的条件聚合汇总数据,将同一分区下的【相同排序】的多行数据汇总合并成一行,既减少了数据行节省空间,又降低了后续汇总查询的开销
好了,三种常用的MergeTree系列到此结束了,谢谢大家