ClickHouse最优批量插入方案咨询：千万级JSON记录高效导入

我有一个包含1000万余条JSON记录的大型数据集，需要插入到仅含字符串列的ClickHouse表中。目前使用clickhouse_driver工具，每插入10万条记录约耗时1分钟，寻求最优插入方法建议。

当前操作步骤：

• 从数据库读取数据（每行以JSON格式存储）
• 加载至DataFrame
• 使用insert_dataframe或execute方法插入ClickHouse表（目标表仅含一列存储JSON行）

当前代码：

from clickhouse_driver import Client
client = Client(host, user, password, database)

for chunk in pd.read_sql(src_sql, conn1, chunksize=300000):              
    df = pd.DataFrame(chunk, columns=bq_column_names)
    df_list = df['JsonData'].tolist()

    bulk_insert_data = [(item, 1)
                        for item in df_list
                        ]
        
    client.execute("INSERT INTO schema.table (JsonData,batchseq) SETTINGS async_insert=1, wait_for_async_insert=1 VALUES", bulk_insert_data)

1. 优化批量块大小与数据转换逻辑 #

• 调整chunksize至50万-100万（根据本地内存情况适配），更大的批量能减少ClickHouse的写入次数，提升整体效率。
• 跳过手动构造元组列表的步骤，直接用insert_dataframe插入DataFrame，减少中间转换的开销：

from clickhouse_driver import Client
client = Client(host, user, password, database)

for chunk in pd.read_sql(src_sql, conn1, chunksize=500000):              
    df = pd.DataFrame(chunk, columns=bq_column_names)
    # 直接给DataFrame添加batchseq列后插入
    client.insert_dataframe(
        "INSERT INTO schema.table (JsonData, batchseq)",
        df.assign(batchseq=1),
        settings={
            "async_insert": 1,
            "wait_for_async_insert": 1,
            "max_insert_block_size": 2097152
        }
    )

2. 调整ClickHouse插入参数 #

• 开启async_insert时，关闭wait_for_async_insert（设为0），不需要等待单批次插入确认，能大幅降低等待时间。
• 调大max_insert_block_size（比如2097152），让ClickHouse合并更大的数据块后再写入存储，减少磁盘IO次数。
• 临时禁用表的TTL、索引等维护逻辑（插入完成后再恢复），减少插入时的额外计算开销。

3. 跳过DataFrame，直接用文件导入（最快方式） #

如果源数据库支持直接导出数据为文本文件，完全跳过Python中间层，用ClickHouse原生的文件导入功能：

1. 从源数据库导出所有JSON记录到本地文件，每行一条JSON，同时在每行末尾追加,1（对应batchseq字段）。
2. 使用ClickHouse客户端命令执行导入：

clickhouse-client --host <你的host> --user <用户名> --password <密码> --database <数据库名> \
--query "INSERT INTO schema.table (JsonData, batchseq) FORMAT CSV" \
--input_format_csv_allow_single_quotes 1 < 你的数据文件.csv

这种方式利用ClickHouse的原生导入能力，性能远高于通过Python客户端插入。

4. 改用HTTP接口批量插入 #

clickhouse_driver的性能略逊于直接调用ClickHouse的HTTP接口，用requests库直接发送批量数据：

import requests
import pandas as pd

url = f"http://{host}:8123/?database={database}&user={user}&password={password}"
headers = {"Content-Type": "text/csv"}

for chunk in pd.read_sql(src_sql, conn1, chunksize=1000000):
    # 构造CSV格式数据，给JSON字符串加引号避免格式问题
    csv_lines = [f'"{row["JsonData"]}",1' for _, row in chunk.iterrows()]
    csv_data = '\n'.join(csv_lines)
    # 发送POST请求插入
    resp = requests.post(
        url,
        data=csv_data.encode('utf-8'),
        params={"query": "INSERT INTO schema.table (JsonData,batchseq) FORMAT CSV"}
    )
    resp.raise_for_status()

5. 调整ClickHouse服务器端配置（临时） #

• 调大max_insert_threads至8-16，让插入操作使用更多线程并行处理。
• 增加memory_limit和buffer_pool_size，给批量插入分配更多内存资源，减少磁盘临时写入。

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