使用Python客户端高效更新BigQuery（BQ）5千行数据的方案咨询

首先修正原有逻辑的两个基础问题：

1. 原函数定义中参数ad_ids未被使用，且WHERE条件的account_id和传入的ad_id列表不匹配，注意对齐实际筛选字段
2. 原字符串拼接逻辑语法错误，没有正确拆分CASE段和IN段的生成逻辑

正确实现代码 #

步骤1：生成CASE和IN语句片段 #

# 提取所有待更新的ad_id
ad_ids = [item["ad_id"] for item in current_ads_removed_json_chunk]
# 生成CASE WHEN部分，注意如果ad_id是字符串类型，需要在两侧加单引号：f"WHEN id = '{ad_id}' THEN 'removed'"
case_when_segment = "CASE " + " ".join([f"WHEN id = {ad_id} THEN 'removed'" for ad_id in ad_ids]) + " END"
# 生成IN查询的ID列表
in_segment = "(" + ", ".join(map(str, ad_ids)) + ")"

步骤2：调整update_bq函数适配通用更新逻辑 #

def update_bq(self, table_id, set_clause, where_condition):
    table_full_name = self.get_table_full_name(table_id)
    query_text = f"""
    UPDATE `{table_full_name}`
    SET {set_clause}
    WHERE {where_condition}
    """
    query_job = self.client.query(query_text)
    # 等待任务执行完成，避免异步执行导致的状态不一致
    query_job.result()

步骤3：调用更新函数 #

update_bq(
    table_id=_ADS_TO_REMOVE_TABLE_NAME,
    set_clause=f"status = {case_when_segment}",
    where_condition=f"id IN {in_segment}"
)

• 关于BQ自动时间戳字段的更新生效问题：
  只有字段定义为DEFAULT CURRENT_TIMESTAMP()，且插入行时未指定该字段的值，才会自动填充时间戳。UPDATE操作不会自动触发该字段更新，如果需要更新行时同步刷新时间戳，需要手动在SET语句中添加更新时间字段名 = CURRENT_TIMESTAMP()。

• 关于批量更新场景下DELETE+INSERT是否更优：
  针对你5000行的小批量更新场景，直接使用单条UPDATE语句是更优的选择，原因如下：

  1. 单条DML是原子操作，要么全成功要么全失败，不会出现删除成功但插入失败导致数据丢失的问题，一致性更高
  2. 小数据量下UPDATE的执行速度和扫描成本和DELETE+INSERT基本一致，写法更简洁
  3. 只有当你需要更新的字段非常多、或者待更新的行分布在数十个以上的分区时，DELETE+INSERT的成本才会低于UPDATE，你当前的场景不需要考虑这种方案。

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