BigQuery：如何将字段添加到重复记录中？

Question

我在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;

这给了我一个错误：＜code＞无法访问数组类型的值的字段ceId

有没有人对如何进行有任何建议？谢谢！

Answer 1

就在我发布之前，我设法找到了这个问题的答案——但我认为分享这个方法对其他人会很有用。以下是有效的查询：

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;

希望这是有用的！请随时添加到我的结果或评论中以继续对话。

Answer 2

每个人都正确。也不正确。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;

在第一个参数上显示错误。参数1，"无法访问数组类型的值上的字段参数1

但是，移除< code >插入到...并修改如下行，查询有效。

-- insert into dummydata_withuuid (id, name, created, valid, parameters)
select id, name, created, valid,
[(
GENERATE_UUID(), parameters
)]
from dummydata_nouuid;

我可以将结果保存为另一个表，这离得到我需要的答案还有很长的路要走。在我的＜code＞插入到…＜/code＞行中是否需要修改某些内容以使查询有效？

Answer 3

我遵循了官方文档中数据操作语言语法中的过程。

然后，基本上您想要的是更新重复的记录。我遵循了所有示例，从插入到更新，直到在重复记录中添加第二条评论的那一刻。

然后我应用了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中使用Copy table获取没有“id”的表的副本。我的起始表在DML之后，副本在DMLmod之后。

通过在 Cloud Shell 中运行以下命令，将表中的架构复制到 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"
}

< li >现在您需要通过运行以下命令来更新架构

bq update testing.followingDMLmod myschema.json

最后，回到BigQuery UI，我使用了查询

UPDATE `testing.followingDMLmod` 
SET comments = ARRAY(
    SELECT AS STRUCT * REPLACE(GENERATE_UUID() AS id)
    FROM UNNEST(comments)
  )
WHERE true

以在创建后填充 id 字段。按照此堆栈溢出帖子中的建议进行操作。现在，最终结果确实是预期的！