在表架构中指定嵌套和重复的列

本页面介绍如何在 BigQuery 中定义具有嵌套和重复列的表架构。如需大致了解表架构,请参阅指定架构

定义嵌套和重复的列

要创建包含嵌套数据的列,请在架构中将列的数据类型设置为 RECORDRECORD 可以在标准 SQL 中作为 STRUCT 类型进行访问。STRUCT 存储有序字段。

要创建包含重复数据的列,请在架构中将列的模式设置为 REPEATED。重复字段可以在标准 SQL 中作为 ARRAY 类型进行访问。

RECORD 列可以使用 REPEATED 模式,该模式表示为 STRUCT 类型的数组。此外,记录中的字段可以重复,表示为包含 ARRAYSTRUCT。数组不能直接包含另一个数组。如需了解详情,请参阅声明 ARRAY 类型

限制

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

一个架构最多支持 15 层嵌套的 RECORD 类型。
RECORD 类型的列可以包含嵌套的 RECORD 类型(也称为子记录)。嵌套深度上限为 15 层。此限制与 RECORD 是基于标量还是基于数组(重复)无关。

示例架构

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

  • 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 页面。

    转到 BigQuery

  2. 浏览器面板中,展开您的项目并选择数据集。

  3. 在详细信息视图中,点击创建表

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

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

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

        地址架构

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

        嵌套按钮

        • 名称字段中,输入 status。请注意,该字段会预先填充 addresses.,表示这是嵌套字段。
        • Type 部分中,选择 STRING
        • 模式部分,保留设置的 NULLABLE 值。

          状态架构

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

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

bq

要在 JSON 架构文件中指定嵌套和重复的 addresses 列,请使用文本编辑器创建新文件。粘贴上述示例架构定义。

创建 JSON 架构文件后,您可以通过 bq 命令行工具提供此文件。如需了解详情,请参阅使用 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)
	}
	defer client.Close()

	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
}

Java

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.Field.Mode;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;

public class NestedRepeatedSchema {

  public static void runNestedRepeatedSchema() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createTableWithNestedRepeatedSchema(datasetName, tableName);
  }

  public static void createTableWithNestedRepeatedSchema(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, tableName);

      Schema schema =
          Schema.of(
              Field.of("id", StandardSQLTypeName.STRING),
              Field.of("first_name", StandardSQLTypeName.STRING),
              Field.of("last_name", StandardSQLTypeName.STRING),
              Field.of("dob", StandardSQLTypeName.DATE),
              // create the nested and repeated field
              Field.newBuilder(
                      "addresses",
                      StandardSQLTypeName.STRUCT,
                      Field.of("status", StandardSQLTypeName.STRING),
                      Field.of("address", StandardSQLTypeName.STRING),
                      Field.of("city", StandardSQLTypeName.STRING),
                      Field.of("state", StandardSQLTypeName.STRING),
                      Field.of("zip", StandardSQLTypeName.STRING),
                      Field.of("numberOfYears", StandardSQLTypeName.STRING))
                  .setMode(Mode.REPEATED)
                  .build());

      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);
      System.out.println("Table with nested and repeated schema created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

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

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

何时使用嵌套和重复的列

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

例如,用于跟踪图书馆书籍的关系数据库可能会将所有作者信息保存在单独的表中。您可以使用 author_id 之类的键将图书与作者关联起来。

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

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

表安全性

如需控制对 BigQuery 中表的访问权限,请参阅表访问权限控制简介

后续步骤