如何从结构体/数组结构有差异的源表插入数据到BigQuery表

BigQuery对嵌套STRUCT类型做强Schema校验，不会自动为STRUCT内缺失的REPEATED（数组）类型字段补默认值，这是你插入失败的核心原因。你需要显式构造与目标表Schema完全对齐的STRUCT结构，对源表缺失的col2.col_d字段显式赋值为空的对应类型数组即可。

可直接使用的插入SQL #

-- 替换成你自己的项目、数据集、表名
INSERT INTO `target_project.target_dataset.target_billing_table`
(
  Col1,
  col2,
  col3
)
SELECT
  Col1,
  STRUCT(
    col2.col_a AS col_a,
    col2.col_b AS col_b,
    col2.col_c AS col_c,
    -- 显式声明缺失的col_d字段，赋值为对应结构的空数组
    CAST([] AS ARRAY<STRUCT<fld1 STRING>>) AS col_d
  ) AS col2,
  col3
FROM `source_project.source_dataset.old_format_billing_table`

后续合并新旧数据的写法 #

如果要把新格式账单表的数据一起写入目标表，直接用UNION ALL拼接两个查询即可，新格式表自带col2.col_d字段，不需要额外构造空值：

INSERT INTO `target_project.target_dataset.target_billing_table`
(
  Col1,
  col2,
  col3
)
-- 旧格式数据查询
SELECT
  Col1,
  STRUCT(
    col2.col_a AS col_a,
    col2.col_b AS col_b,
    col2.col_c AS col_c,
    CAST([] AS ARRAY<STRUCT<fld1 STRING>>) AS col_d
  ) AS col2,
  col3
FROM `source_project.source_dataset.old_format_billing_table`

UNION ALL

-- 新格式数据查询，直接读取即可
SELECT
  Col1,
  col2,
  col3
FROM `source_project.source_dataset.new_format_billing_table`

注意事项 #

• 构造STRUCT时字段顺序必须和目标表Schema的字段顺序完全一致，字段名必须完全匹配，不能省略别名，否则会出现字段错位、类型不匹配报错
• 空数组必须用CAST显式声明元素类型，直接写[]时BigQuery无法推断数组结构，同样会报类型错误
• 如果后续谷歌再调整账单格式，在col_d或其他嵌套STRUCT下新增子字段，只需要修改对应CAST内的STRUCT定义，为新增字段补对应类型的空值即可

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