BigQuery:如何将字段添加到重复记录中？
我在Google BigQuery中有一个由几个字段组成的表，然后是一个可能包含一个或多个对象的重复记录。我希望在重复数据中创建一个带有额外字段的新表，并将原始数据复制到新表中，用GENERATE_UUID()的输出填充新字段，这样每重复的数据行就有一个唯一的标识符。
我在当目标包含重复字段时，如何从一个BigQuery表复制到另一个表？有一个类似的问题，但我不知道如何调整这个以适应我当前的用例。
这是我的“新”模式1(即上面链接中的模式2)。
[
{"name": "id", "type": "NUMERIC", "mode": "REQUIRED"},
{"name": "name", "type": "STRING", "mode": "REQUIRED"},
{"name": "created", "type": "TIMESTAMP", "mode": "REQUIRED"},
{"name": "valid", "type": "BOOLEAN", "mode": "REQUIRED"},
{"name": "parameters", "type": "RECORD", "mode": "REPEATED", "fields":
[
{"name": "parameter1", "type": "STRING", "mode": "REQUIRED"},
{"name": "parameter2", "type": "FLOAT", "mode": "REQUIRED"},
{"name": "parameter3", "type": "BOOLEAN", "mode": "REQUIRED"}
]
}
]我希望这样结束，模式2：
[
{"name": "id", "type": "NUMERIC", "mode": "REQUIRED"},
{"name": "name", "type": "STRING", "mode": "REQUIRED"},
{"name": "created", "type": "TIMESTAMP", "mode": "REQUIRED"},
{"name": "valid", "type": "BOOLEAN", "mode": "REQUIRED"},
{"name": "parameters", "type": "RECORD", "mode": "REPEATED", "fields":
[
{"name": "uuid", "type": "STRING", "mode": "REQUIRED"},
{"name": "parameter1", "type": "STRING", "mode": "REQUIRED"},
{"name": "parameter2", "type": "FLOAT", "mode": "REQUIRED"},
{"name": "parameter3", "type": "BOOLEAN", "mode": "REQUIRED"}
]
}
]因此，我已经用这个模式创建了我的新表(表2)。我想从表1中复制，我正在尝试这样的方法：
insert into table2_with_uuid(id, name, created, valid, parameters)
select id, name, created, valid,
[(
GENERATE_UUID(), parameters.parameter1, parameters.parameter2, parameters.parameter3
)]
from table1_no_guid;这给了我一个错误：Cannot access field ceId on a value with type ARRAY<STRUCT<parameter1 (等)
有没有人对如何进行有任何建议？谢谢!
回答 3
Stack Overflow用户
发布于 2020-04-07 12:35:51
我在正式文档上遵循了数据操作语言语法的程序。
然后，基本上您想要的是更新重复记录。我遵循了所有的例子，从插入到更新，再到在重复记录中添加第二个注释的时刻。
然后我应用了UNNEST查询：
insert into `testing.followingDMLmod` (product, quantity, supply_constrained, comments)
select product, quantity, supply_constrained,
[(
GENERATE_UUID(), com.created, com.comment
)]
from `testing.followingDML` , UNNEST(comments) com;当然，这是可行的，但并没有提供想要的结果。
根据正式文件，"BigQuery本机支持几种模式更改，例如向记录中添加新的嵌套字段或放松嵌套字段的模式。“然后，路径可能是复制表，然后添加额外的字段。
这可以在管理表架构文档之后完成。也就是说，要么使用API并调用tables.patch (在这其他堆栈溢出帖子中有更详细的讨论)，要么使用带有命令行模式的JSON文件。
我亲自遵循了第二种方法(JSON模式文件)，并且非常适合我。关于更多细节，我遵循的步骤如下(如这里中所示)：
- 使用BigQuery UI中的复制表可以获得没有"id“的表的副本。我的起始表是followingDML和复制followingDMLmod。
- 通过在Cloud中运行以下命令，将模式从表复制到JSON文件(此处称为myschema.json)
bq show \
--schema \
--format=prettyjson \
testing.followingDMLmod > myschema.json- 在文本编辑器中打开架构。例如，运行
vim myschema.json- 现在修改架构以将新的嵌套列添加到字段数组的末尾。(如果您从未使用过vim，一个非常简单的解释将是"esc“将您返回到正常模式，而在正常模式下，单击"i”允许您写入打开的文件，":w“保存文件并":q”退出该文件)我包括了字段"id"：
{
"mode": "NULLABLE",
"name": "id",
"type": "STRING"
}- 现在，您需要通过运行
bq update testing.followingDMLmod myschema.json最后，回到BigQuery用户界面上，我使用了查询
UPDATE `testing.followingDMLmod`
SET comments = ARRAY(
SELECT AS STRUCT * REPLACE(GENERATE_UUID() AS id)
FROM UNNEST(comments)
)
WHERE true若要在创建后填充id字段，请执行以下操作。遵循这堆栈溢出帖子中的建议。现在，最终的结果是真正的预期！
Stack Overflow用户
发布于 2020-04-03 15:20:05
就在我发帖之前，我已经找到了这个问题的答案--但我认为分享这个方法对其他人是有用的。下面是有效的查询：
insert into table2_with_uuid(id, name, created, valid, parameters)
select id, name, created, valid,
[(
GENERATE_UUID(), params.parameter1, params.parameter2, params.parameter3
)]
from table1_no_guid, UNNEST(parameters) params;希望这是有用的！请随时加入我的结果或评论，继续对话。
Stack Overflow用户
发布于 2020-04-06 10:01:41
每个人都是对的。而且不正确。unnest将原始数据替换为每重复记录一行。尝试此查询：
insert into dummydata_withuuid (id, name, created, valid, parameters)
select id, name, created, valid,
[(
GENERATE_UUID(), parameters.parameter1, parameters.parameter2, parameters.parameter3
)]
from dummydata_nouuid;在第一个parameters.parameter1上显示一个错误，“无法访问5:29类型ARRAY>的值上的字段parameter1”。
但是，删除insert into...并按下面的行进行修改，查询是有效的。
-- insert into dummydata_withuuid (id, name, created, valid, parameters)
select id, name, created, valid,
[(
GENERATE_UUID(), parameters
)]
from dummydata_nouuid;我可以把结果保存成另一张表，这是一个很长的路要得到我需要的答案。在我的insert into...行中是否需要修改一些内容以使查询有效？
https://stackoverflow.com/questions/61015002
复制相似问题
腾讯云开发者
