如何将数据插入包含 BQ 中结构数据类型的表中

Question

尝试将数据插入下表时，我收到以下错误消息。

--create table mydataset.struct_1(x struct<course string,id int64>)
insert into `mydataset.struct_1` (course,id)  values("B.A",12)
Error: Column course is not present in table mydataset.struct_1 at [2:35]

Answer 1

-- CREATE TABLE mydataset.struct_1(x STRUCT<course STRING,id INT64>)
INSERT INTO `mydataset.struct_1` (x)  VALUES(STRUCT("B.A",12))

Answer 2

如果你想用一个名为x的嵌套结构创建STRUCT，有两个字段y和z，你应该这样做：

STRUCT<x STRUCT<y INT64, z INT64>>

所以在你的例子中：

create table mydataset.struct_1(STRUCT<x STRUCT<course string,id int64>>)

Answer 3

CREATE TABLE STRUCT_1 (x STRUCT<course: STRING,id: int>)
comment 'demonstrating how to work-around to insert complex 
datatype unnested structs into a complex table '
Stored as parquet
location '/user/me/mestruct'
tblproperties ('created date: '=' 2019/01','done by: '='me');

现在让我们进行插入。

insert into table STRUCT_1 select named_struct("course","B.A","id",12) from (select 't') s;