ClickHouse的ReplacingMergeTree深入了解
为了解决MergeTree相同主键无法去重的问题，ClickHouse提供了ReplacingMergeTree引擎，用来对主键重复的数据进行去重。
删除重复数据可以使用optimize命令手动执行，这个合并操作是在后台运行的，且无法预测具体的执行时间。
在使用optimize命令执行合并时，如果表数据量过大，会导致耗时很长，此时表将是不可用的，因为optimize会通过读取和写入大量数据来完成合并操作。
ReplacingMergeTree适合在后台清除重复数据以节省空间，但不能保证不存在重复数据。在没有彻底optimize之前，可能无法达到主键去重的效果，比如部分数据已经被去重，而另外一部分数据仍旧存在主键重复的情况。在分布式场景下，相同主键的数据可能被分片到不同节点上，不同分片间无法去重。ReplacingMergeTree更多的被用于确保数据最终被去重，而无法保证查询过程中主键不重复。
一、创建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参数 |
ReplacingMergeTree([ver])中的ver参数是可选的，指带有版本的列，这个列允许使用UInt*、Date或DateTime类型。ReplacingMergeTree在合并时会把具有相同主键的所有行仅保留一个。如果不指定ver参数则保留最后一次插入的数据。 |
二、创建ReplacingMergeTree引擎的表
创建ReplacingMergeTree引擎的本地表tbl_test_replacing_mergetree_users
CREATE TABLE tbl_test_replacingmergetree_users (
id UInt64,
email String,
username String,
gender UInt8,
birthday Date,
mobile FixedString(13),
pwd String,
regDT DateTime,
lastLoginDT DateTime,
lastLoginIP String
) ENGINE=ReplacingMergeTree(id) partition by toYYYYMMDD(regDT) order by id settings index_granularity=8192;
三、插入数据到ReplacingMergeTree引擎的表
插入数据到表tbl_test_replacingmergetree_users
使用SQL语句插入数据：
insert into tbl_test_replacingmergetree_users select * from tbl_test_mergetree_users where id<=5;
查询表中全量数据。
插入重复数据（使用lastLoginDT来区分数据插入的先后顺序）：
insert into tbl_test_replacingmergetree_users(id,email,username,gender,birthday,mobile,pwd,regDT,lastLoginIP,lastLoginDT) select id,email,username,gender,birthday,mobile,pwd,regDT,lastLoginIP,now() as lastLoginDT from tbl_test_mergetree_users where id<=3;
再次查询表中全量数据：
select * from tbl_test_replacingmergetree_users order by id,lastLoginDT;明显看到id是1、2、3的数据各有两条，说明目前已经存在3条重复数据，且新增的3条数据的lastLoginDT都是2019-12-04 14:55:56。
编辑现在使用optimize命令执行合并操作，使表中主键id字段的重复数据由现在的6条变成3条：
optimize table tbl_test_replacingmergetree_users final;
再次查询
select * from tbl_test_replacingmergetree_users;发现主键id字段为1、2、3的重复数据已经合并了，且合并后保留了最后一次插入的3条数据，因为最后插入的3条记录的时间是2019-12-04 14:55:56。
四、删除表
drop table tbl_test_replacingmergetree_users;