指定嵌套重复列

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

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

指定嵌套重复列

BigQuery 支持从支持基于对象的架构的源格式加载嵌套重复数据,例如 JSON 文件、Avro 文件、Firestore 导出文件和 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 数据文件如下所示。请注意,addresses 列包含一个值数组(以 [ ] 表示)。该数组中的多个地址是重复数据。各地址中的多个字段是嵌套数据。

{"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"
            }
        ]
    }
]

在示例中指定嵌套重复列

控制台

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

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

  2. 在导航面板的资源部分中,展开您的项目并选择数据集。点击创建表

    创建表

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

    • 对于来源,选择空表。
    • 对于目标位置,选择您的数据集并在表名称字段中输入表名称。
    • 架构部分,添加一个字段:

      • 名称字段中,输入 addresses
      • Type 部分中,选择 RECORD
      • 模式部分中,选择 REPEATED

        地址架构

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

        嵌套按钮

        • 名称字段中,输入 status。请注意,该字段会预先填充 addresses.,表示这是嵌套字段。
        • Type 部分中,选择 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 页面中执行以下操作:

    • 对于源数据,点击从源创建 (Create from source)。
    • 对于目标表,选择您的数据集并在目标表名称 (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 字段如下所示:

“Schema”标签页中的嵌套字段

CLI

要在 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 参考文档

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// createTableComplexSchema demonstrates creating a BigQuery table and specifying a complex schema that includes
// an array of Struct types.
func createTableComplexSchema(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}

	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.Fprintf(w, "created table %s\n", tableRef.FullyQualifiedName())
	return nil
}

Node.js

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

// Import the Google Cloud client library and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function nestedRepeatedSchema() {
  // Creates a new table named "my_table" in "my_dataset"
  // with nested and repeated columns in schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = "my_dataset";
  // const tableId = "my_table";
  // const schema = [
  //   {name: 'Name', type: 'STRING', mode: 'REQUIRED'},
  //   {
  //     name: 'Addresses',
  //     type: 'RECORD',
  //     mode: 'REPEATED',
  //     fields: [
  //       {name: 'Address', type: 'STRING'},
  //       {name: 'City', type: 'STRING'},
  //       {name: 'State', type: 'STRING'},
  //       {name: 'Zip', type: 'STRING'},
  //     ],
  //   },
  // ];

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}

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 本身支持多种架构更改,例如向记录添加新的嵌套字段或放宽嵌套字段的模式。如需了解详情,请参阅修改表架构

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