GCP BigQuery含重复RECORD列的表间无展平数据复制方法

结构差异 #

两张表的字段定义差异如下：

• 源表input_table
  • column1：可空STRING类型
  • column2：REPEATED（数组）模式RECORD，嵌套单个可空STRING字段sub1
• 目标表copy_table
  • column1：可空STRING类型
  • column2：REPEATED（数组）模式RECORD，嵌套单个REPEATED（数组）模式STRING字段sub

原有语句问题 #

你之前写的语句在主查询FROM后对column2做了UNNEST交叉连接，会直接把源表的数组元素拆成独立行，最终插入结果的行数远多于源表原始行数，不符合不展平的要求。

正确实现代码 #

不需要对主表做任何UNNEST展平操作，直接逐行做数组结构转换即可，代码如下：

INSERT INTO copy_table (column1, column2)
SELECT
  column1,
  ARRAY(
    SELECT AS STRUCT
      [sub_item.sub1] AS sub
    FROM input_table.column2 AS sub_item
  ) AS column2
FROM input_table;

逻辑说明 #

• 整个语句没有对源表做交叉连接，源表的每一行最终只会对应生成目标表的一行，完全不会出现行数膨胀的问题
• 外层ARRAY()函数负责生成目标表要求的REPEATED RECORD结构，生成的数组长度和源表column2的数组长度完全一致
• 内层将源RECORD中的sub1字段值包裹一层数组，刚好匹配目标表sub字段的REPEATED STRING类型要求
• 边界值完全对齐：源表column2为空数组时转换结果仍为空数组，sub1为NULL时转换后sub字段值为[NULL]，不会丢失数据精度

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