基于idempotency_id字段的ClickHouse数据去重问题咨询

方案一：正确使用ReplacingMergeTree（澄清误区：字符串主键完全支持） #

你之前提到无法用ReplacingMergeTree是误解，ClickHouse的ReplacingMergeTree完全支持字符串类型作为主键字段。通过将idempotency_id设为主键，MergeTree会在后台合并分区时自动去重，默认保留最后插入的那条数据。

建表示例：

CREATE TABLE user_events
(
    event_time DateTime,
    clicks UInt32,
    idempotency_id String
)
ENGINE = ReplacingMergeTree()
PRIMARY KEY idempotency_id
ORDER BY idempotency_id;

• 导入数据后，可手动触发合并（可选，后台会自动执行）：OPTIMIZE TABLE user_events FINAL;
• 查询时无需额外去重逻辑，直接查询即可获取去重后的数据：

SELECT * FROM user_events;

方案二：使用AggregateMergeTree预聚合去重 #

如果需要灵活保留任意一条对应idempotency_id的数据，可用AggregateMergeTree结合any聚合函数实现预聚合去重。

建表示例：

CREATE TABLE user_events_agg
(
    idempotency_id String,
    event_time AggregateFunction(any, DateTime),
    clicks AggregateFunction(any, UInt32)
)
ENGINE = AggregateMergeTree()
PRIMARY KEY idempotency_id
ORDER BY idempotency_id;

• 导入数据时需用聚合函数写入：

INSERT INTO user_events_agg
SELECT
    idempotency_id,
    anyState(event_time),
    anyState(clicks)
FROM raw_user_events
GROUP BY idempotency_id;

• 查询时展开聚合结果：

SELECT
    idempotency_id,
    anyMerge(event_time) AS event_time,
    anyMerge(clicks) AS clicks
FROM user_events_agg
GROUP BY idempotency_id;

方案三：导入阶段预处理去重 #

在数据写入ClickHouse前完成去重，避免后续查询或合并的开销。比如用awk工具按idempotency_id去重，保留最后出现的条目：

awk '{arr[$4]=$0} END{for(i in arr) print arr[i]}' raw_data.csv > deduplicated_data.csv

再将去重后的文件导入：

INSERT INTO user_events FORMAT CSV
FROM INFILE 'deduplicated_data.csv';

方案四：优化查询阶段ROW_NUMBER的内存问题 #

如果必须在查询阶段去重，优化写法降低内存消耗：

• 先按idempotency_id分区，避免全局排序的内存开销
• 配合设置合理的内存参数

优化后的查询语句：

SELECT event_time, clicks, idempotency_id
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY idempotency_id ORDER BY event_time DESC) AS rn
    FROM user_events
    SETTINGS max_memory_usage = 10000000000 -- 调整内存限制为10GB
)
WHERE rn = 1;

• 若为分布式表，优先让分片内完成去重再合并结果，减少跨分片数据传输。

内容的提问来源于stack exchange，提问作者sparkle