BigQuery新增列后如何从JSON文件批量回填原有表行字段值

由于你的表已开启streaming buffer，BigQuery默认禁止对存在流式缓存的表执行UPDATE/DELETE等DML操作，优先推荐无DML限制的覆盖写入方案：

方案1：INSERT OVERWRITE 全表覆盖（推荐） #

该方案无需等待streaming buffer落盘，200万行数据处理成本低、速度快，不会修改原有列数据：

• 第一步：将原始JSON文件加载为临时staging表，加载时选择自动检测schema或手动指定完整的10个字段schema，确保3个新增字段被正确解析，同时临时表需保留与原表一致的记录唯一标识（如主键，无主键可使用原有7个字段组合作为关联键）
• 第二步：执行覆盖写入语句回填数据：

INSERT OVERWRITE `你的项目ID.你的数据集.原表名`
SELECT
  -- 原有7个列全部取原表的值，确保不会被修改
  t1.原有列1,
  t1.原有列2,
  t1.原有列3,
  t1.原有列4,
  t1.原有列5,
  t1.原有列6,
  t1.原有列7,
  -- 3个新增列从临时表取数，关联不上默认保留NULL，可按需调整逻辑
  t2.新增列1,
  t2.新增列2,
  t2.新增列3
FROM `你的项目ID.你的数据集.原表名` t1
LEFT JOIN `你的项目ID.你的数据集.staging临时表名` t2
-- 替换为实际的记录唯一关联条件，无主键可写多个列的等值匹配
ON t1.唯一标识列 = t2.唯一标识列

方案2：UPDATE 增量更新 #

如果不想执行全表覆盖，可等待streaming buffer中的数据自动落盘（通常需要30分钟到1小时），再执行UPDATE语句：

UPDATE `你的项目ID.你的数据集.原表名` t1
SET 
  t1.新增列1 = t2.新增列1,
  t1.新增列2 = t2.新增列2,
  t1.新增列3 = t2.新增列3
FROM `你的项目ID.你的数据集.staging临时表名` t2
-- 替换为实际的关联条件
WHERE t1.唯一标识列 = t2.唯一标识列

注意：执行UPDATE前可先运行SELECT JOIN语句验证关联结果是否符合预期，避免关联错误导致数据异常。

结果验证 #

回填完成后执行以下语句校验新增列的非空情况，确认回填效果：

SELECT COUNT(*) AS 新增列仍为NULL的行数
FROM `你的项目ID.你的数据集.原表名`
WHERE 新增列1 IS NULL OR 新增列2 IS NULL OR 新增列3 IS NULL

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