ClickHouse如何合并列结构不同的表完成数据整合？

首先纠正两个认知偏差：

• 该场景是向空的新表写入合并后的数据，完全不需要UPDATE ... SELECT语法，直接用INSERT ... SELECT搭配关联查询即可完成，性能远高于逐行更新
• clickhouse-driver支持执行所有ClickHouse原生SQL（包括异步更新的Mutation语句），但本场景不需要用到UPDATE操作，直接执行写入SQL即可

方案1：直接执行SQL合并（推荐，性能最优） #

前置操作 #

先执行提前准备好的建表语句创建table_3，确保表结构和定义一致。

合并写入SQL #

根据两张原表的主键匹配情况选择对应语句：

• 如果table_1和table_2的(key1, dt)组合完全一一对应，没有缺失的主键对，用内连接即可：

INSERT INTO table_3 (key1, dt, data1, data2, data3, data4)
SELECT
    t1.key1,
    t1.dt,
    t1.data1,
    t1.data2,
    t2.data3,
    t2.data4
FROM table_1 t1
INNER JOIN table_2 t2 USING (key1, dt);

• 如果存在主键对不匹配的情况（比如某个key1+dt仅存在于单张表中），用全外连接自动补全空值，未匹配到的字段会自动使用建表时设置的默认值nan填充：

INSERT INTO table_3 (key1, dt, data1, data2, data3, data4)
SELECT
    coalesce(t1.key1, t2.key1) AS key1,
    coalesce(t1.dt, t2.dt) AS dt,
    t1.data1,
    t1.data2,
    t2.data3,
    t2.data4
FROM table_1 t1
FULL OUTER JOIN table_2 t2 USING (key1, dt);

大数据量优化 #

如果单表数据量超过千万级，可以在执行写入前设置并行写入参数提升速度：

-- 根据自身CPU核数调整线程数，建议不超过CPU核数的一半
SET max_insert_threads = 8, insert_block_size = 1048576;
-- 执行完参数设置后再运行上面的INSERT SELECT语句即可

方案2：Python侧通过clickhouse-driver实现 #

不需要额外封装更新逻辑，直接通过驱动执行上面的合并SQL即可，示例代码：

from clickhouse_driver import Client

# 替换为你的ClickHouse连接信息
client = Client(
    host="127.0.0.1",
    port=9000,
    user="default",
    password="",
    database="your_database"
)

# 已提前建表可跳过下面的建表语句
create_sql = """
CREATE TABLE IF NOT EXISTS table_3
(
key1 FixedString(10),
dt Datetime,
data1 Float32 default nan,
data2 Float32 default nan,
data3 Float32 default nan,
data4 Float32 default nan
)
Engine = MergeTree()
order by (key1,dt);
"""
client.execute(create_sql)

# 执行合并写入
merge_sql = """
INSERT INTO table_3 (key1, dt, data1, data2, data3, data4)
SELECT
    coalesce(t1.key1, t2.key1) AS key1,
    coalesce(t1.dt, t2.dt) AS dt,
    t1.data1,
    t1.data2,
    t2.data3,
    t2.data4
FROM table_1 t1
FULL OUTER JOIN table_2 t2 USING (key1, dt);
"""
client.execute(merge_sql)

注意事项 #

不要使用ALTER TABLE ... UPDATE做全量数据合并：MergeTree引擎的UPDATE属于后台异步执行的Mutation操作，会重写全表数据分片，不仅执行速度慢，还会产生大量数据碎片，写完后需要额外执行OPTIMIZE TABLE清理碎片，对于新表合并场景完全没有必要。

内容的提问来源于stack exchange，提问作者定坤宋