Python结合BigQuery使用for循环插入数据仅保留最后一行问题咨询

问题根因 #

1. 中间表覆盖（最高概率） #

如果你的<QUERY>逻辑包含CREATE OR REPLACE TABLE <tmp_proc1>/<tmp_proc2>语句，且这两个是指定了所属数据集的永久表，同时你提交BigQuery作业时没有等待前一次作业完成就提交下一次，会导致80个并发作业先后覆盖中间表内容，最终所有INSERT操作读取的都是最后一次覆盖的、对应第80个id的中间表数据，因此目标表仅保留最后一次插入结果。

2. 写入模式配置错误 #

如果提交查询作业时显式指定了写入模式为WRITE_TRUNCATE，每次插入都会清空目标表再写入当前id的数据，最终仅保留最后一次写入内容。

3. 临时表生命周期问题 #

如果<tmp_proc1>/<tmp_proc2>是会话级临时表，每次循环提交的独立作业会生成独立会话，作业结束后临时表自动清空，仅有最后一次作业的临时表内容成功插入目标表。

修复方案 #

• 方案一（最简修复）：每次作业执行后添加等待逻辑，避免并发覆盖，同时确认写入模式为默认追加模式
  代码示例：

  from google.cloud import bigquery
  client = bigquery.Client()
  
  for x in proc_arr:
      query = """
      BEGIN
      <QUERY>
      SELECT * FROM <table1> WHERE  procedureid = {}
      INSERT INTO <table> 
      SELECT procedureid FROM <tmp_proc1>
      UNION ALL
      SELECT procedureid FROM <tmp_proc2>;
      END;
      """.format(x)
      # 等待当前作业执行完成再进入下一次循环
      job = client.query(query)
      job.result()
  

• 方案二（性能优化）：取消循环提交作业，直接把所有id放入IN条件，单次查询完成全部插入，避免并发问题同时大幅提升执行效率
  代码示例：

  proc_ids = ','.join(map(str, proc_arr))
  query = f"""
  INSERT INTO <table>
  SELECT procedureid FROM (
    -- 替换为原<QUERY>中生成tmp_proc1的逻辑，添加IN过滤
    SELECT procedureid FROM <table1> WHERE procedureid IN ({proc_ids})
  )
  UNION ALL
  SELECT procedureid FROM (
    -- 替换为原<QUERY>中生成tmp_proc2的逻辑，添加IN过滤
    SELECT procedureid FROM <table2> WHERE procedureid IN ({proc_ids})
  )
  """
  client.query(query).result()
  

• 方案三：如果必须保留中间表逻辑，每次循环给中间表设置唯一名称（例如拼接当前id值），避免不同循环的作业共用同一张中间表导致覆盖。

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