如何在ClickHouse中模拟INSERT ON CONFLICT DO UPDATE？附Python实现

场景1：仅新增数据（手动生成ID） #

这类场景核心是先获取目标表的最新ID，基于此生成新数据ID后批量插入：

• 初始化ClickHouse客户端：使用clickhouse_connect.get_client()建立连接
• 查询当前最大ID：执行SELECT MAX(id) FROM <table>获取最新ID值
• 构造新数据：基于最新ID递增生成新记录的ID，组装成待插入的DataFrame
• 批量插入：调用client.insert_df()完成数据写入

示例代码：

import clickhouse_connect
import pandas as pd

# 初始化客户端连接
client = clickhouse_connect.get_client(host='your_host', port=8123, user='your_user', password='your_pwd')

# 查询表中最新ID
max_id_result = client.query("SELECT MAX(id) FROM your_database.your_table")
current_max_id = max_id_result.result_rows[0][0] if max_id_result.result_rows[0][0] is not None else 0

# 构造待插入的新数据
new_records = [
    {'id': current_max_id + 1, 'name': 'demo1', 'value': 100},
    {'id': current_max_id + 2, 'name': 'demo2', 'value': 200}
]
insert_df = pd.DataFrame(new_records)

# 执行插入
client.insert_df('your_database.your_table', insert_df)

场景2：支持新增与更新（自动ID） #

你的思路完全可行：先匹配新旧数据的重复ID，筛选出有差异的记录执行更新，无匹配ID的记录直接插入。以下是具体实现方案：

1. 拆分待操作数据 #

先将新数据拆分为两类：

• 待插入数据：ID不存在于目标表中的记录
• 待更新数据：ID已存在且字段内容有差异的记录

2. 通过Mutation执行更新 #

ClickHouse中通过ALTER TABLE UPDATE（属于Mutation操作）实现数据更新，结合clickhouse_connect的执行方式如下：

# 假设已拆分出待更新的DataFrame：update_df、待插入的DataFrame：insert_df

# 批量执行更新操作
for _, row in update_df.iterrows():
    update_sql = f"""
    ALTER TABLE your_database.your_table 
    UPDATE name = '{row['name']}', value = {row['value']} 
    WHERE id = {row['id']}
    """
    client.command(update_sql)

# 可选：等待所有更新操作后台执行完成（确保后续查询能获取最新数据）
client.command("SYSTEM SYNC MUTATIONS your_database.your_table")

# 执行新增数据插入
client.insert_df('your_database.your_table', insert_df)

Mutation是否为最优方案？ #

Mutation适合中小批量更新场景，但存在局限性：

• 异步执行：提交后不会立即生效，需等待后台合并操作完成
• 性能损耗：频繁执行会生成大量临时分区，增加存储与合并开销
• 无事务支持：无法保证更新与插入操作的原子性

如果是大规模高频更新场景，更推荐使用ReplacingMergeTree引擎：

• 建表时指定ReplacingMergeTree(version)，用版本字段控制数据替换逻辑
• 直接批量写入所有新数据（含重复ID的记录），后台会自动保留最新版本的记录
• 这种方式性能更高，适合批量更新场景

核心语法参考 #

ClickHouse更新操作的基础语法：

ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>

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