Clickhouse：6TB跨3个月历史数据迁移至分团队新表的最优方案咨询

针对你6TB未压缩、3个月跨度的历史数据迁移需求，以下是几种高效且稳妥的解决方案：

一、按时间分片分批插入（最推荐的方案） #

由于你的event_source表按toYYYYMMDD(ingest_time)分区，拆分批次处理可避免单次数据量过大导致的内存溢出或超时问题。

操作步骤： #

1. 先确认目标表的最新数据时间，避免重复插入：

SELECT max(ingest_time) FROM event_source_business1;

2. 按日期分批执行插入（示例处理2024-01-01当天数据）：

INSERT INTO event_source_business1
SELECT
    ingest_time,
    JSONExtractString(message,'source') AS source,
    JSONExtractString(message,'eventName') AS event_name,
    JSONExtractString(JSONExtractString(message,'entityContext'),'type') AS ec_type,
    JSONExtractString(JSONExtractString(message,'additionalInfo'),'ip_address') AS ai_ip_address
FROM event_source
WHERE ingest_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-02 00:00:00';

3. 用Shell脚本自动化循环处理所有历史日期：

#!/bin/bash
START_DATE="2024-01-01"
END_DATE="2024-03-31"
TARGET_LATEST=$(clickhouse-client --query "SELECT max(ingest_time) FROM event_source_business1")

current_date=$START_DATE
while [ "$current_date" != "$END_DATE" ]; do
    next_date=$(date -d "$current_date +1 day" +%Y-%m-%d)
    if [[ "$current_date" < "$(date -d "$TARGET_LATEST" +%Y-%m-%d)" ]]; then
        echo "Processing date: $current_date"
        clickhouse-client --query "
            INSERT INTO event_source_business1
            SELECT
                ingest_time,
                JSONExtractString(message,'source') AS source,
                JSONExtractString(message,'eventName') AS event_name,
                JSONExtractString(JSONExtractString(message,'entityContext'),'type') AS ec_type,
                JSONExtractString(JSONExtractString(message,'additionalInfo'),'ip_address') AS ai_ip_address
            FROM event_source
            WHERE ingest_time BETWEEN '$current_date 00:00:00' AND '$next_date 00:00:00';
        "
        [ $? -eq 0 ] && echo "Success: $current_date" || echo "Fail: $current_date" >> migration_errors.log
    fi
    current_date=$next_date
done

二、优化参数后批量插入 #

若服务器内存充足（如16G以上），可调整Clickhouse会话参数提升单次处理能力：

-- 调整内存限制（根据服务器配置修改）
SET max_memory_usage = 16G;
-- 延长执行超时至1小时
SET max_execution_time = 3600;
-- 启用并行插入
SET allow_experimental_parallel_insert = 1;
-- 禁用插入时自动合并，降低IO压力
SET optimize_on_insert = 0;

INSERT INTO event_source_business1
SELECT
    ingest_time,
    JSONExtractString(message,'source') AS source,
    JSONExtractString(message,'eventName') AS event_name,
    JSONExtractString(JSONExtractString(message,'entityContext'),'type') AS ec_type,
    JSONExtractString(JSONExtractString(message,'additionalInfo'),'ip_address') AS ai_ip_address
FROM event_source
WHERE ingest_time < (SELECT max(ingest_time) FROM event_source_business1);

注：此方法建议配合分批逻辑使用，避免一次性处理全量6TB数据。

三、分区导出+导入（超大规模数据适配） #

通过导出为Parquet格式（高压缩率、快读写），再导入目标表，规避直接INSERT的内存瓶颈：

导出单个分区： #

SELECT
    ingest_time,
    JSONExtractString(message,'source') AS source,
    JSONExtractString(message,'eventName') AS event_name,
    JSONExtractString(JSONExtractString(message,'entityContext'),'type') AS ec_type,
    JSONExtractString(JSONExtractString(message,'additionalInfo'),'ip_address') AS ai_ip_address
FROM event_source
WHERE toYYYYMMDD(ingest_time) = '20240101'
INTO OUTFILE '/data/clickhouse_export/20240101.parquet'
FORMAT Parquet;

导入单个分区： #

INSERT INTO event_source_business1
FORMAT Parquet
INFILE '/data/clickhouse_export/20240101.parquet';

可并行处理多个分区的导出导入，最大化利用服务器资源。

关键注意事项 #

• 避免重复数据：始终以目标表的max(ingest_time)作为历史数据的截止时间，确保只迁移物化视图同步前的历史数据。
• 低峰期操作：大体积数据迁移会占用大量IO和CPU，建议在业务流量低谷时段执行。
• 错误重试：对失败批次单独记录并重试，避免数据遗漏。

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