指定嵌套和重复的列

如果您的数据是非规范化的,BigQuery 会取得最佳效果。与其保留星型或雪花型架构等关系型架构,不如对数据进行反规范化并利用嵌套和重复的字段。嵌套和重复的列可以维持关系,而不会由于保留关系型(标准化)架构产生性能影响。

您可以在网页界面或 JSON 架构文件中指定嵌套数据或者嵌套并重复的数据。要指定嵌套列或嵌套并重复的列,您可以使用 RECORD (STRUCT) 数据类型。

指定嵌套和重复的列

BigQuery 支持从支持基于对象的架构的源格式(例如 JSON文件、Avro 文件、Cloud Firestore 导出文件和 Cloud Datastore 导出文件)加载嵌套和重复的数据。

例如,用于跟踪图书馆书籍的关系型数据库可能会将所有作者信息保存在单独的表中。诸如 author_id 等键可用于将书籍链接到作者。

在 BigQuery 中,您可以在不创建单独作者表的情况下,保留书籍与作者之间的关系。您可以创建一个作者列,并将各种字段嵌套在其中,例如作者的名字、姓氏、出生日期等。如果一本书由多名作者合著,您可以使用重复的嵌套作者列。

要创建一个包含嵌套数据和重复数据的列,请将列的数据类型设置为 RECORDRECORD 将存储为 STRUCT,并可作为标准 SQL 中的 STRUCT 访问。STRUCT 是有序字段的容器,每个字段都有一个类型(必需)和一个名称(可选)。要将列设为嵌套列,您要向 RECORD(父级)添加子字段。要将列设为重复列,请将模式更改为 REPEATED

限制

嵌套和重复的架构存在以下限制:

  • 在加载嵌套数据和重复数据时,您的架构最多支持 15 层嵌套 STRUCTRECORD 类型)。
    • BigQuery 支持 STRUCT(或 RECORD)类型的列。STRUCT 是一种复杂类型,可用于表示具有多个子列的对象。在 STRUCT 列中,您还可以将一个或多个子列定义为 STRUCT 类型(称为嵌套或嵌入的 STRUCT)。在嵌套 STRUCTS 时,BigQuery 会强制执行 15 个级层的嵌套深度上限。嵌套深度上限与 STRUCT 是基于标量还是基于数组无关。

示例

示例架构

以下显示了嵌套和重复数据的示例。此表包含有关人员的信息。其中包含以下字段:

  • id
  • first_name
  • last_name
  • dob(出生日期)
  • addresses(嵌套和重复的字段)
    • addresses.status(目前或以前)
    • addresses.address
    • addresses.city
    • addresses.state
    • addresses.zip
    • addresses.numberOfYears(居住在此地址的年份)

JSON 数据文件如下所示。请注意,地址列包含一个值数组(以 [ ] 表示)。数组中的多个地址属于重复数据。各地址中的多个字段属于嵌套数据。

{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}
{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}

此表的架构如下所示:

[
    {
        "name": "id",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "first_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "last_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "dob",
        "type": "DATE",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "status",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "address",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "city",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "state",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "zip",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "numberOfYears",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

在示例中指定嵌套和重复的列

Console

要在 GCP Console 中指定嵌套和重复的 addresses 列,请执行以下操作:

  1. 在 GCP Console 中打开 BigQuery 网页界面。
    转到 BigQuery 网页界面

  2. 在导航面板的资源部分中,展开您的项目并选择数据集。点击创建表。加载数据的过程与创建空表的过程相同。

    创建表

  3. 创建表页面上执行以下操作:

    • 对于来源,选择空表

      创建表来源

    • 对于目标位置,选择您的数据集并在表名称字段中输入表名称。

    • 对于架构,添加一个字段:

      • 名称字段中输入 addresses
      • 对于类型,选择 RECORD
      • 对于模式,选择 REPEATED

        地址架构

      • 点击 addresses 右侧的加号图标,添加一个嵌套字段。

        Nest 按钮

        • 名称字段中输入 status。请注意,该字段已预先填充了 addresses.,表示这是一个嵌套字段。
        • 对于类型,选择 STRING
        • 对于模式,将值保留设置为 NULLABLE

          状态架构

        • 重复以上步骤,添加 address (NULLABLE STRING)、city (NULLABLE STRING)、state (NULLABLE STRING)、zip (NULLABLE STRING) 和 numberOfYears (NULLABLE STRING)。

    • 您还可以点击以文本形式修改,并以 JSON 数组形式指定架构。

经典版界面

要在经典版 BigQuery 网页界面中指定嵌套和重复的 addresses 列,请执行以下操作:

  1. 转到 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 在导航窗格中,点击您的数据集名称旁边的向下箭头图标 向下箭头图标,然后点击 Create new table。加载数据的过程与创建空表的过程相同。

  3. Create table 页面上,执行以下操作:

    • 对于 Source Data,点击 Create from source
    • 对于 Destination Table,选择您的数据集并在 Destination table name 字段中输入表名称。
    • Schema 部分中:

      • Name 字段中输入 addresses
      • 对于 Type,选择 RECORD
      • 对于 Mode,选择 REPEATED
      • 点击 RECORD 右侧的加号图标 加号图标,添加一个嵌套字段。

        • Name 字段中输入 status。请注意,该字段已预先填充了 addresses.,表示这是一个嵌套字段。
        • 对于 Type,选择 STRING
        • 对于 Mode,将值保留设置为 NULLABLE
        • 重复以上步骤,添加 address (NULLABLE STRING)、city (NULLABLE STRING)、state (NULLABLE STRING)、zip (NULLABLE STRING) 和 numberOfYears (NULLABLE STRING)。

        界面中的嵌套字段

    • 您还可以点击 Edit as Text,并以 JSON 数组形式指定架构。

在 BigQuery 网页界面中检查架构时,addresses 字段的形式如下所示:

“架构”标签中的嵌套字段

命令行

要在 JSON 架构文件中指定嵌套和重复的 addresses 列,请使用文本编辑器输入以下代码:

[
    {
        "name": "id",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "first_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "last_name",
        "type": "STRING",
        "mode": "NULLABLE"
    },
    {
        "name": "dob",
        "type": "DATE",
        "mode": "NULLABLE"
    },
    {
        "name": "addresses",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "status",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "address",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "city",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "state",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "zip",
                "type": "STRING",
                "mode": "NULLABLE"
            },
            {
                "name": "numberOfYears",
                "type": "STRING",
                "mode": "NULLABLE"
            }
        ]
    }
]

创建 JSON 架构文件之后,您可以通过命令行提供此文件

Go

试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Go 设置说明进行操作。如需了解详情,请参阅 BigQuery Go API 参考文档

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "id", Type: bigquery.StringFieldType},
	{Name: "first_name", Type: bigquery.StringFieldType},
	{Name: "last_name", Type: bigquery.StringFieldType},
	{Name: "dob", Type: bigquery.DateFieldType},
	{Name: "addresses",
		Type:     bigquery.RecordFieldType,
		Repeated: true,
		Schema: bigquery.Schema{
			{Name: "status", Type: bigquery.StringFieldType},
			{Name: "address", Type: bigquery.StringFieldType},
			{Name: "city", Type: bigquery.StringFieldType},
			{Name: "state", Type: bigquery.StringFieldType},
			{Name: "zip", Type: bigquery.StringFieldType},
			{Name: "numberOfYears", Type: bigquery.StringFieldType},
		}},
}

metaData := &bigquery.TableMetadata{
	Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metaData); err != nil {
	return err
}
fmt.Printf("created table %s\n", tableRef.FullyQualifiedName())

Python

试用此示例之前,请按照《BigQuery 快速入门:使用客户端库》中的 Python 设置说明进行操作。如需了解详情,请参阅 BigQuery Python API 参考文档

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

schema = [
    bigquery.SchemaField("id", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("first_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("last_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("dob", "DATE", mode="NULLABLE"),
    bigquery.SchemaField(
        "addresses",
        "RECORD",
        mode="REPEATED",
        fields=[
            bigquery.SchemaField("status", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("address", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("city", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("state", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("zip", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("numberOfYears", "STRING", mode="NULLABLE"),
        ],
    ),
]
table_ref = dataset_ref.table("my_table")
table = bigquery.Table(table_ref, schema=schema)
table = client.create_table(table)  # API request

print("Created table {}".format(table.full_table_id))

修改嵌套和重复的列

将一个嵌套列或一个嵌套并重复的列添加到表的架构定义后,您可以像修改任何其他类型的列一样修改此列。BigQuery 本身支持多种架构更改,例如向记录添加新的嵌套字段或放宽嵌套字段的模式。如需了解详情,请参阅修改表架构

此外,您还可以手动修改包含嵌套列和重复列的架构定义。如需了解详情,请参阅手动更改表架构

此页内容是否有用?请给出您的反馈和评价:

发送以下问题的反馈:

此网页
需要帮助?请访问我们的支持页面