如何在BigQuery中为同一名称插入多条RECORD类型列数据

问题描述 #

现有BigQuery表结构如下：

CREATE TABLE MY_TABLE (
  name STRING,
  values STRUCT<model STRING, group BOOL>
)

执行以下插入语句后，得到两行name='export'的数据，但期望同一name对应多条values（一行数据关联多个values条目）：

INSERT INTO MY_TABLE  (name ,values)
VALUES (
  'export',
  STRUCT('model1', false)
),
(
  'export',
  STRUCT('model2', true)
)

想知道当前表结构是否正确，以及如何实现无需重复name就能插入多条RECORD类型列数据。

解决方案 #

1. 当前表结构的问题 #

现有表中的values是单个STRUCT类型，只能存储一条结构化数据，完全满足不了同一name关联多条values的需求。必须把values改成STRUCT数组类型（ARRAY<STRUCT<model STRING, group BOOL>>），这样单个name的行里才能存储多个values条目。

2. 调整结构并实现插入 #

先修改表结构（或重新建表）

CREATE TABLE MY_TABLE (
  name STRING,
  values ARRAY<STRUCT<model STRING, group BOOL>>
)

首次插入单name对应多条values

直接用数组形式把多个STRUCT打包插入：

INSERT INTO MY_TABLE (name, values)
VALUES (
  'export',
  [STRUCT('model1', false), STRUCT('model2', true)]
)

给已有name追加新values

如果要给已经存在的name='export'添加新的values，用UPDATE结合ARRAY_CONCAT函数拼接数组：

UPDATE MY_TABLE
SET values = ARRAY_CONCAT(values, [STRUCT('model3', false)])
WHERE name = 'export'

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