如何在BigQuery中为数组的数组创建DDL

Question

我正在尝试在BigQuery中创建一个表定义，该表定义可用于插入包含数组数组的记录

阵列阵列的样本数据：[["1","2","3","4"],["1","2","3","4"],["1","2","3","4"]]

我试着跟随

CREATE TABLE IF NOT EXISTS dataset.test1 (
a String,
b STRUCT <STRUCT <c ARRAY <ARRAY <STRING>>>>
)

但得到以下错误：不支持数组数组

如何为数组记录的数组创建表结构？

Answer 1

不支持数组的数组，你能做的最好的事情就是拥有STRUCT的外部数组，然后STRUCT有一个内部数组字段，试试这个SQL:

create table yourDataset.t (arrayOfArray ARRAY< STRUCT< arr ARRAY<STRING> > >)
AS SELECT  [Struct<ARRAY<STRING>>(["1","2","3","4"]),
Struct<ARRAY<STRING>>(["1","2","3","4"]),
Struct<ARRAY<STRING>>(["1","2","3","4"])];