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

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

定义嵌套和重复的列

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

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

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

限制

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

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

RECORD 类型与 UNION、INTERSECT、EXCEPT DISTINCT 和 SELECT DISTINCT 不兼容。

示例架构

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

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

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

如需使用先前嵌套和重复的列创建新表，请选择以下选项之一：

控制台

指定嵌套和重复的 addresses 列：

  1. 在 Google Cloud 控制台中，打开 BigQuery 页面。

    转到 BigQuery

  2. 在左侧窗格中，点击 探索器：

    突出显示的“探索器”窗格按钮。

    如果您没有看到左侧窗格，请点击 展开左侧窗格以打开该窗格。

  3. 在探索器窗格中，展开您的项目，点击数据集，然后选择一个数据集。

  4. 在详细信息窗格中，点击 创建表。

  5. 在创建表页面上，指定以下详细信息：

    • 对于来源，在基于以下数据创建表字段中，选择空表。

    • 在目标位置部分中，指定以下字段：

      • 在数据集部分中，选择要在其中创建表的数据集。
      • 在表部分中，输入您要创建的表的名称。

    • 在架构部分中，点击 添加字段并输入以下表架构：

      • 对于字段名称，输入 addresses。
      • 对于类型，选择记录。
      • 在模式部分中，选择重复。 地址架构

      • 对于嵌套的字段，指定以下字段：

        • 在字段名称字段中，输入 status。
        • 在类型部分中，选择字符串。
        • 在模式部分中，让保留设置的值可以为 Null。

        • 点击 添加字段以添加以下字段：

          字段名称 类型 模式
          address STRING NULLABLE
          city STRING NULLABLE
          state STRING NULLABLE
          zip STRING NULLABLE
          numberOfYears STRING NULLABLE

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

SQL

使用 CREATE TABLE 语句。使用列选项指定架构：

  1. 在 Google Cloud 控制台中，前往 BigQuery 页面。

    转到 BigQuery

  2. 在查询编辑器中，输入以下语句：

    CREATE TABLE IF NOT EXISTS mydataset.mytable (
  id STRING,
  first_name STRING,
  last_name STRING,
  dob DATE,
  addresses
    ARRAY<
      STRUCT<
        status STRING,
        address STRING,
        city STRING,
        state STRING,
        zip STRING,
        numberOfYears STRING>>
) OPTIONS (
    description = 'Example name and addresses table');

  3. 点击 运行。

如需详细了解如何运行查询，请参阅运行交互式查询。

bq

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

创建 JSON 架构文件后，您可以通过 bq 命令行工具提供此文件。如需了解详情，请参阅使用 JSON 架构文件。

Go

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

如需向 BigQuery 进行身份验证，请设置应用默认凭证。如需了解详情，请参阅为客户端库设置身份验证。 

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

如需向 BigQuery 进行身份验证，请设置应用默认凭证。如需了解详情，请参阅为客户端库设置身份验证。 

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",