如何使用嵌套名称语法(即"item.description")插入列? [英] How do I INSERT columns with nested name syntax (ie. "item.description")?

问题描述

我正在尝试在Google BigQuery上合并具有相同架构的两个数据库.

I'm trying to merge two databases with the same schema on Google BigQuery.

我在这里关注合并示例:https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement

I'm following the merge samples here: https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax#merge_statement

但是，我的表具有嵌套列，即"service.id"或"service.description"

However, my tables have nested columns, ie "service.id" or "service.description"

到目前为止，我的代码是:

My code so far is:

MERGE combined_table
USING table1
ON table1.id = combined_table.id
WHEN NOT MATCHED THEN
  INSERT(id, service.id, service.description)
  VALUES(id, service.id, service.description)

但是，我得到了错误消息:语法错误:预期为)"或，"，但是得到了." ，并在 .id 下带有红色的弯曲下划线在 INSERT(...)行上.

However, I get the error message: Syntax error: Expected ")" or "," but got ".", and a red squiggly underline under .id on the INSERT(...) line.

这是表的一部分架构的视图:

Here is a view of part of my table's schema:

[
    {
        "name": "id",
        "type": "STRING"
    },
    {
        "name": "service",
        "type": "RECORD",
        "fields": [
            {
                "name": "id",
                "type": "STRING"
            },
            {
                "name": "description",
                "type": "STRING"
            }
        ]
    },
    {
        "name": "cost",
        "type": "FLOAT"
    }
...
]

如何正确构造此INSERT(...)语句，以便可以包括嵌套列?

How do I properly structure this INSERT(...) statement so that I can include the nested columns?

推荐答案

我找到了答案.

当引用STRUCT的顶层时，BigQuery也会引用所有嵌套列.因此，如果我想插入 service 及其所有子列(service.id和service.description)，则只需在 INSERT中包括 service (...)语句.

It turns out when referencing the top level of a STRUCT, BigQuery references all of the nested columns as well. So if I wanted to INSERT service and all of it's sub-columns (service.id and service.description), I only have to include service in the INSERT(...) statement.

以下代码有效:

...
WHEN NOT MATCHED THEN
  INSERT(id, service)
  VALUES(id, service)

这将合并所有子列，包括 service.id 和 service.description .

This would merge all sub columns, including service.id and service.description.

这篇关于如何使用嵌套名称语法(即"item.description")插入列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！