ClickHouse 分布式表数据去重

场景

有一个部署在两台机器上的 ClickHouse 集群，里面有张分布式表。一部分数据存在机器 A 的 Shard 表上，一部分数据在机器 B 的 Shard 表上。访问任意一台机器，都可以看到全量的表数据。（更多细节略过）

这张分布式表里有一些重复数据，想要去重。怎么处理呢？

思路

创建一张临时的分布式表，从原表中查出去重后的数据，插入到临时表中。清空原表数据，然后再从临时表插回原表。

SQL

下面用具体的 SQL 举例子。

// 设定：
//     1. 数据库是 hello_db
//     2. 正式表是 hello
//     3. 正式 Shard 表是 helloShard
//     4. 临时表是 hello_temp
//     5. 临时 Shard 表是 hello_tempShard
//     6. 根据 timestamp 和 id 的组合做去重

// 创建临时表
create table if not exists hello_tempShard on cluster '{cluster}' (`id` Int64, `name` String, `meta` String, `timestamp` Int64, `dates` Array(Int64)) ENGINE = MergeTree PARTITION BY toYYYYMM(toDateTime(toUInt32(timestamp))) ORDER BY (timestamp, id)

// 清空临时表
truncate table hello_tempShard on cluster '{cluster}'

// 创建分布式临时表
create table if not exists hello_temp on cluster '{cluster}' (`id` Int64, `name` String, `meta` String, `timestamp` Int64, `dates` Array(Int64)) ENGINE = Distributed('{cluster}', hello_db, hello_tempShard, xxHash64(id))

// 正式表去重导临时表
insert into hello_temp select id, any(name), any(meta), timestamp, any(dates) from hello group by (id, timestamp) 

// 清空正式表
truncate table helloShard on cluster '{cluster}'

// 临时表导正式表
insert into hello select * from hello_temp

// 删除临时表
drop table if exists hello_temp on cluster '{cluster}'
drop table if exists hello_tempShard on cluster '{cluster}'

参考链接：https://clickhouse.tech/docs/zh/engines/table-engines/special/distributed/