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;
![](https://img-blog.csdnimg.cn/img_convert/753b657928ba2ce74624d9f44d3b566d.png)
![](https://img-blog.csdnimg.cn/img_convert/f8c6d5355b066d8757bab8a9fd911a2d.png)
三、插入数据到ReplacingMergeTree引擎的表
插入数据到表tbl_test_replacingmergetree_users
使用SQL语句插入数据:
insert into tbl_test_replacingmergetree_users select* from tbl_test_mergetree_users where id<=5;
![](https://img-blog.csdnimg.cn/img_convert/f3cb0022513800be9b54c95b122586b4.png)
查询表中全量数据。
![](https://img-blog.csdnimg.cn/img_convert/b8dc45f2bfc99ca90e0000e2c1bb8fcf.png)
插入重复数据(使用lastLoginDT来区分数据插入的先后顺序):
insert intotbl_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;
![](https://img-blog.csdnimg.cn/img_convert/e6be88cbde10469bf371f304957b15da.png)
再次查询表中全量数据:
select* from tbl_test_replacingmergetree_users order by id,lastLoginDT;
明显看到id是1、2、3的数据各有两条,说明目前已经存在3条重复数据,且新增的3条数据的lastLoginDT都是2019-12-04 14:55:56。
![](https://img-blog.csdnimg.cn/img_convert/396a987e7e31ec0f4d10752316d9b784.png)
编辑现在使用optimize命令执行合并操作,使表中主键id字段的重复数据由现在的6条变成3条:
optimize table tbl_test_replacingmergetree_users final;
![](https://img-blog.csdnimg.cn/img_convert/8719535fe9db397d6be6a2f9f1875a07.png)
再次查询
select* from tbl_test_replacingmergetree_users;
发现主键id字段为1、2、3的重复数据已经合并了,且合并后保留了最后一次插入的3条数据,因为最后插入的3条记录的时间是2019-12-04 14:55:56。
![](https://img-blog.csdnimg.cn/img_convert/3d71680179a4c24759a4900852ca9272.png)
四、删除表
drop table tbl_test_replacingmergetree_users;