如何在ClickHouse中对超大Log引擎单列表去重并转换引擎？

我有一个采用Log引擎的超大单列表addresses_tmp，数据示例如下：

SELECT * FROM addresses_tmp LIMIT 5

   ┌─address──────────────────────────────────┐
1. │ 18a0a8bdcbd1fec1785224cfc486ccf02dc3ef5d │
2. │ 3ca0a8d9744b229f81fae2f59892b546c20a744e │
3. │ 4456058ebd1ae161348b5aae51d86aef423513a6 │
4. │ a3230a93a31f924a2713af72733d522873434025 │
5. │ 4960323c0fbd63ae068ea313c67bb2a3bc133baf │
   └──────────────────────────────────────────┘

尝试用以下语句将其插入ReplacingMergeTree表时，因内存超限失败：

create table addresses engine=ReplacingMergeTree() primary key address as select row_number() over() as id, * from (select * from addresses_tmp)

错误信息：

Code: 241. DB::Exception: Received from localhost:9000. DB::Exception:
Memory limit (total) exceeded: would use 27.89 GiB (attempt to
allocate chunk of 5248943 bytes), maximum: 27.86 GiB.
OvercommitTracker decision: Query was selected to stop by
OvercommitTracker.

可行的转换与去重方法 #

1. 分批插入，降低内存负载 #

先创建空的目标表，再分批次插入数据，避免一次性加载全表：

-- 创建空的ReplacingMergeTree表
CREATE TABLE addresses
ENGINE = ReplacingMergeTree()
PRIMARY KEY address
AS SELECT row_number() over() as id, * FROM addresses_tmp LIMIT 0;

-- 分批插入，示例每次处理100万条（可根据服务器内存调整批次大小）
INSERT INTO addresses
SELECT 0 + row_number() over() as id, * FROM addresses_tmp LIMIT 1000000 OFFSET 0;

INSERT INTO addresses
SELECT 1000000 + row_number() over() as id, * FROM addresses_tmp LIMIT 1000000 OFFSET 1000000;

-- 重复执行直到所有数据插入完成

通过偏移量+行号的方式保证id全局唯一，避免重复。

2. 插入时直接去重，减少数据量 #

如果不需要保留重复数据，可在插入阶段通过GROUP BY完成去重，降低数据规模：

-- 无需id字段的场景
CREATE TABLE addresses
ENGINE = ReplacingMergeTree()
PRIMARY KEY address
AS SELECT address FROM addresses_tmp GROUP BY address;

-- 需要id字段的场景，用聚合函数生成唯一id
CREATE TABLE addresses
ENGINE = ReplacingMergeTree()
PRIMARY KEY address
AS SELECT min(row_number() over()) as id, address FROM addresses_tmp GROUP BY address;

3. 临时调整内存限制（服务器有剩余内存时适用） #

临时调高会话级别的内存限制，允许当前查询使用更多内存：

-- 仅当前会话生效
SET max_memory_usage = 30GiB;
SET max_memory_usage_for_user = 30GiB;

-- 执行原创建表语句
create table addresses engine=ReplacingMergeTree() primary key address as select row_number() over() as id, * from (select * from addresses_tmp);

注意：此方法仅适用于服务器有足够空闲内存的情况，避免影响其他业务查询。

4. 用物化视图逐步同步（适合非紧急场景） #

创建物化视图将数据同步到目标表，若需要立即同步可手动触发：

-- 创建空目标表
CREATE TABLE addresses
ENGINE = ReplacingMergeTree()
PRIMARY KEY address
AS SELECT row_number() over() as id, * FROM addresses_tmp LIMIT 0;

-- 创建物化视图绑定源表与目标表
CREATE MATERIALIZED VIEW addresses_mv TO addresses
AS SELECT row_number() over() as id, * FROM addresses_tmp;

-- 手动触发同步（可选）
REFRESH MATERIALIZED VIEW addresses_mv;

若内存问题依然存在，建议结合分批插入的思路拆分同步任务。

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