英文:
Insert several data in BIGQUERY in a column of type RECORD
问题
我在BigQuery中有一张具有以下结构的表：
CREATE TABLE MY_TABLE (
name STRING,
values STRUCT<model STRING, group BOOL>
)
我想要在与相同名称相关的values列中插入多条数据。
这是我正在执行的操作：
INSERT INTO MY_TABLE (name ,values)
VALUES (
'export',
STRUCT('model1', false)
),
(
'export',
STRUCT('model2', true)
)
这是我得到的结果：
| name | values.model | values.group |
|---|---|---|
| export | model1 | false |
| export | model2 | true |
这是我想要的：
| name | values.model | values.group |
|---|---|---|
| export | model1 | false |
| model2 | true |
如何在name列的记录类型列中插入多个数据，而不必重复所有数据？我需要每个名称都有一个记录，但包含多个值。我不知道是否这是创建表以实现此目的的正确方式。
英文:
I have a table in bigquery with the following structure:
CREATE TABLE MY_TABLE (
name STRING,
values STRUCT<model STRING, group BOOL>
)
What I want is to insert several data in the values column related to the same name.
This is what I'm doing:
INSERT INTO MY_TABLE (name ,values)
VALUES (
'export',
STRUCT('model1', false)
),
(
'export',
STRUCT('model2', true)
)
This is what I get:
| name | values.model | values.group |
|---|---|---|
| export | model1 | false |
| export | model2 | true |
This is what I want:
| name | values.model | values.group |
|---|---|---|
| export | model1 | false |
| model2 | true |
How can I insert several data for in the RECORS type column for the name column without having to repeat all the data? I need a record for each name, but that contains several values.
I don't know if this is the correct way to create the table to achieve this.
答案1
得分: 0
以下是您要求的代码部分的翻译：
CREATE TEMP TABLE MY_TABLE (
name STRING,
values ARRAY<STRUCT<model STRING, `group` BOOL>>;
);
INSERT INTO MY_TABLE (name, values)
VALUES ('export', [STRUCT('model1', false), STRUCT('model2', true)]),
('import', [STRUCT('model3', true), STRUCT('model4', false)])
;
SELECT * FROM MY_TABLE;
查询结果
或者，
CREATE TEMP TABLE MY_TABLE AS
SELECT 'export' AS name,
[STRUCT('model1' AS model, false AS `group`), ('model2', true)] AS values
UNION ALL
SELECT 'import', [('model3', true), ('model4', false)] AS values;
英文:
You might consider below.
CREATE TEMP TABLE MY_TABLE (
name STRING,
values ARRAY<STRUCT<model STRING, `group` BOOL>>
);
INSERT INTO MY_TABLE (name ,values)
VALUES ( 'export', [STRUCT('model1', false), STRUCT('model2', true)] ),
( 'import', [STRUCT('model3', true), STRUCT('model4', false)] )
;
SELECT * FROM MY_TABLE;
Query results
or,
CREATE TEMP TABLE MY_TABLE AS
SELECT 'export' AS name,
[STRUCT('model1' AS model, false AS `group`), ('model2', true)] AS values
UNION ALL
SELECT 'import', [('model3', true), ('model4', false)] AS values;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论