Bigquery SQL如何同时实现行数据更新与新数据插入操作

不需要逐行编码判断插入或更新逻辑，BigQuery 原生支持 MERGE 语句（也常被称为 upsert 操作），可以在同一条 SQL 中同时完成更新已有数据、插入新增数据的需求。

基础语法示例 #

假设你有以下两张表：

• 目标表：your_project.your_dataset.operation_data，存储正式运营数据，唯一主键为id，其他字段包括user_count、revenue、create_time、update_time
• 源数据表：your_project.your_dataset.tmp_sync_data，存储待同步的新数据，结构和目标表一致

对应的MERGE语句写法如下：

MERGE `your_project.your_dataset.operation_data` target
USING `your_project.your_dataset.tmp_sync_data` source
-- 按唯一主键关联，判断行属于待更新还是待插入
ON target.id = source.id

-- 主键匹配到已有数据，执行更新操作
WHEN MATCHED THEN
  UPDATE SET
    user_count = source.user_count,
    revenue = source.revenue,
    update_time = CURRENT_TIMESTAMP()

-- 主键未匹配到，属于新数据，执行插入操作
WHEN NOT MATCHED THEN
  INSERT (id, user_count, revenue, create_time, update_time)
  VALUES (source.id, source.user_count, source.revenue, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP())

常见扩展用法 #

• 待同步数据量较小时，可以直接用 VALUES 构造源数据，不需要提前建临时表：

  MERGE `your_project.your_dataset.operation_data` target
  USING (
    SELECT 1 AS id, 120 AS user_count, 6200 AS revenue UNION ALL
    SELECT 3 AS id, 78 AS user_count, 2100 AS revenue
  ) source
  ON target.id = source.id
  -- 后续更新、插入逻辑和上文一致
  

• 可以给匹配逻辑加额外限制条件，避免旧数据覆盖新数据：

  -- 只有源数据的更新时间晚于目标表已有数据的更新时间时才执行更新
  WHEN MATCHED AND source.update_time > target.update_time THEN
    UPDATE SET ...
  

注意事项 #

• 关联条件ON中用到的字段必须能唯一标识目标表的行，否则会触发一行匹配到多行的运行报错
• MERGE是原子操作，所有变更要么全部执行成功，要么全部回滚，不会出现部分更新成功的中间状态，数据一致性有保障

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