如何在ClickHouse中低内存从大表创建带转换的新表

针对大表迁移转换时的内存耗尽问题，ClickHouse提供了多种低内存友好的方案，以下是最常用的几种实现方式：

1. 按分区/业务键分批插入（推荐） #

如果原表是按时间（如天）或其他维度分区的，直接按分区分批处理是最高效的方式，因为ClickHouse对分区的扫描和读取做了深度优化。

步骤： #

• 先创建目标表：根据转换需求定义NEW_TABLE的结构，确保引擎、分区键、排序键符合业务查询需求：

CREATE TABLE NEW_TABLE
(
    id UInt64,
    event_date Date,
    value String,
    transformed_value Float64 -- 自定义转换字段
)
ENGINE = MergeTree()
PARTITION BY event_date
ORDER BY id;

• 分批插入数据：通过WHERE条件过滤单个分区的数据，执行转换后插入目标表。例如按天处理：

-- 处理2023-01-01分区的数据
INSERT INTO NEW_TABLE
SELECT
    id,
    event_date,
    value,
    toFloat64(value) * 1.5 AS transformed_value -- 替换为你的转换逻辑
FROM OLD_BIG_TABLE
WHERE event_date = '2023-01-01';

• 批量生成分区任务：如果分区数量较多，可以通过系统表获取所有有效分区，再批量生成插入语句：

-- 获取原表所有活跃分区
SELECT DISTINCT partition
FROM system.parts
WHERE table = 'OLD_BIG_TABLE' AND active = 1;

遍历查询结果中的每个分区值，执行对应的INSERT语句即可。

2. 调整查询内存限制，让ClickHouse自动溢写到磁盘 #

如果不想拆分太多批次，可以通过设置查询级别的内存参数，让ClickHouse在内存不足时自动将中间数据写入磁盘，避免服务崩溃。适合数据量适中、转换逻辑包含排序/聚合的场景：

INSERT INTO NEW_TABLE
SELECT
    id,
    event_date,
    value,
    toFloat64(value) * 1.5 AS transformed_value
FROM OLD_BIG_TABLE
SETTINGS 
    max_memory_usage = 10G, -- 单查询最大内存限制
    max_bytes_before_external_sort = 5G, -- 排序时超过该阈值则溢写到磁盘
    max_bytes_before_external_group_by = 5G; -- 聚合时超过该阈值则溢写到磁盘

根据你的服务器内存配置调整参数值即可。

3. 按主键范围分块插入 #

如果原表没有分区，但有自增主键（如id），可以按主键范围拆分批次。注意：这种方式效率低于分区扫描，因为每次查询需要扫描范围内的数据：

-- 先获取主键的最小和最大值
SELECT min(id), max(id) FROM OLD_BIG_TABLE;

-- 假设min_id=1，max_id=1000000，每次处理10000条
INSERT INTO NEW_TABLE
SELECT
    id,
    event_date,
    value,
    toFloat64(value) * 1.5 AS transformed_value
FROM OLD_BIG_TABLE
WHERE id BETWEEN 1 AND 10000;

INSERT INTO NEW_TABLE
SELECT ... WHERE id BETWEEN 10001 AND 20000;
-- 以此类推完成所有数据块的迁移

额外优化建议 #

• 分批插入时，临时关闭optimize_on_insert参数，避免每次插入触发MergeTree的合并操作，提升插入效率：

INSERT INTO NEW_TABLE ... SETTINGS optimize_on_insert = 0;

所有数据插入完成后，再手动执行合并：

OPTIMIZE TABLE NEW_TABLE FINAL;

• 如果转换逻辑复杂，尽量避免在单条查询中包含过多计算，可以将转换逻辑拆分为多个步骤，或使用Materialized View辅助处理。

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