指定架构

BigQuery 允许您在将数据加载到表中时以及创建空表时指定表架构。对于支持的数据格式,您还可以使用架构自动检测功能。加载 Avro、Parquet、ORC、Cloud Firestore 导出文件或 Cloud Datastore 导出文件时,系统会自动从自描述源数据中检索表架构。

您可以通过以下方式之一指定表架构:

  • 手动指定架构:
    • 使用 GCP Console
    • 使用经典版 BigQuery 网页界面
    • 以内嵌的方式使用 CLI
  • 创建 JSON 格式的架构文件。
  • 调用 jobs.insert 方法并配置加载作业配置中的 schema 属性。
  • 调用 tables.insert 方法并使用 schema 属性配置表资源中的架构。

加载数据或创建空表之后,您可以修改表的架构定义

架构组件

指定表架构时,必须提供每个列的名称和数据类型。您可以选择性地提供列的说明和模式。

列名称

列名称只能包含字母(a-z、A-Z),数字 (0-9) 或下划线 (_),并且必须以字母或下划线开头。列名长度不得超过 128 个字符。列名不能使用以下任何前缀:

  • _TABLE_
  • _FILE_
  • _PARTITION

列名不可重复,即使其大小写不同也不行。例如,系统会认为名为 Column1 的列与名为 column1 的列相同。

列说明

每个列均可以选择性地包含说明信息。说明就是一个字符串,长度最大为 1024 个字符。

标准 SQL 数据类型

BigQuery 标准 SQL 允许您在架构中指定以下数据类型。数据类型是必需的。

数据类型 说明
整数 不带小数部分的数值
浮点 含有小数部分的近似数值
数字 含有小数部分的精确数值
布尔值 TRUE 或 FALSE(不区分大小写)
字符串 长度可变的字符 (Unicode) 数据
字节 长度可变的二进制数据
日期 逻辑日历日期
日期时间 年、月、日、小时、分钟、秒钟和亚秒
时间 独立于具体日期的时间
时间戳 绝对时间点,精度为微秒
结构体(记录) 有序字段的容器,其中每个字段都有一个类型(必需)和字段名称(可选)
地理位置 地球表面上的点集(WGS84 参考球体上的一组点、线和多边形,具有测地线边)。

如需详细了解标准 SQL 中的数据类型,请参阅标准 SQL 数据类型

您还可以在查询数据时声明数组类型。如需了解详情,请参阅使用数组

模式

BigQuery 支持列的以下模式。模式是可选的。如果未指定模式,则列默认为 NULLABLE

模式 说明
可以为 Null 列允许 NULL 值(默认)
必需 不允许使用 NULL
重复 列包含指定类型的值数组

如需详细了解模式,请参阅 TableFieldSchema 中的 mode

手动指定架构

在加载数据或创建空表时,可以使用 GCP Console、经典版 BigQuery 网页界面或命令行工具手动指定表架构。加载 CSV 和 JSON(以换行符分隔)文件时,系统支持手动指定架构。加载 Avro、Parquet、ORC、Cloud Firestore 导出数据或 Cloud Datastore 导出文件时,系统会自动从自描述源数据中检索表架构。

要手动指定表架构,请执行以下操作:

Console

在 Console 的 BigQuery 网页界面中,您可以使用添加字段选项或以文本形式修改选项指定架构。

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

  2. 从导航面板的资源部分选择数据集。点击窗口右侧的创建表

    创建表

  3. 创建表页面的来源部分,选择创建空表

    创建表来源

  4. 创建表页面的目标位置部分,执行以下操作:

    • 数据集名称部分,选择相应的数据集。

      选择数据集

    • 表名称字段中,输入您要在 BigQuery 中创建的表的名称。

    • 确认表类型是否设置为原生表

  5. 架构部分中,输入架构定义。

    • 选项 1:使用添加字段并指定各字段的名称类型模式。在 BigQuery 网页界面中,使用添加字段选项时,您不能添加字段说明,但在加载数据后,您可以在界面中手动添加字段说明。
    • 选项 2:点击以文本形式修改,并以 JSON 数组的形式粘贴架构。使用 JSON 数组时,您要使用与创建 JSON 架构文件相同的流程生成架构。
  6. 点击创建表

经典版界面

在经典版 BigQuery 网页界面中,您可以使用 Add Field 选项或 Edit as Text 选项指定架构。

  1. 转到 BigQuery 网页界面。

    转到 BigQuery 网页界面

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

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

    • Source Data 部分中,如果您要加载数据,可以点击 Create from source,或者点击 Create empty table
    • 对于 Destination Table,选择您的数据集并在 Destination table name 字段中输入表名称。
    • Schema 部分,选择以下选项之一以手动指定架构:

      • 选项 1:使用添加字段并指定各字段的名称类型模式。在 BigQuery 网页界面中,使用添加字段选项时,您不能添加字段说明,但在加载数据后,您可以在界面中手动添加字段说明。

        使用“添加”字段添加架构

      • 选项 2:点击 Edit as Text,并以 JSON 数组的形式粘贴架构。使用 JSON 数组时,您要使用与创建 JSON 架构文件相同的流程生成架构。

        以 JSON 数组格式添加架构

    • 点击 Create Table

命令行

使用如下命令,按照 [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] 的格式手动提供内嵌架构:

  • load 命令(如果您要加载数据)
  • mk 命令(如果您要创建空表)

使用命令行指定架构时,您不能添加 RECORD (STRUCT) 类型和列说明,也不能指定列的模式。所有模式均默认为 NULLABLE。要添加说明、模式和 RECORD 类型,请改为提供 JSON 架构文件

要使用内嵌架构定义将数据加载到表中,请输入 load 命令并使用 --source_format 标志指定数据格式。如果您要将数据加载到非默认项目内的表中,请按此格式包含项目 ID:[PROJECT_ID]:[DATASET].[TABLE_NAME]

提供 --location 标志并将值设置为您的位置

bq --location=[LOCATION] load --source_format=[FORMAT] [PROJECT_ID]:[DATASET].[TABLE_NAME] [PATH_TO_SOURCE] [SCHEMA]

其中:

  • [LOCATION] 是位置的名称。--location 是可选标志。例如,如果您在东京区域使用 BigQuery,可将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • [FORMAT]NEWLINE_DELIMITED_JSONCSV
  • [PROJECT_ID] 是项目 ID。
  • [DATASET] 是一个数据集,它包含要加载数据的表。
  • [TABLE] 为要加载数据的表的名称。
  • [PATH_TO_SOURCE] 是本地机器或 Cloud Storage 中 CSV 或 JSON 数据文件的位置。
  • [SCHEMA] 是内嵌架构定义。

示例:

输入以下命令,将名为 myfile.csv 的本地 CSV 文件中的数据加载到默认项目的 mydataset.mytable 中。架构是以内嵌方式手动指定的。mydataset 是在 US 多区域位置创建的。

bq --location=US load --source_format=CSV mydataset.mytable ./myfile.csv qtr:STRING,sales:FLOAT,year:STRING

输入以下命令,将名为 myfile.csv 的本地 CSV 文件中的数据加载到默认项目的 mydataset.mytable 中。mydataset 是在 asia-northeast1 区域中创建的。架构以内嵌方式手动指定,具体如下:

bq --location=asia-northeast1 load --source_format=CSV mydataset.mytable ./myfile.csv qtr:STRING,sales:FLOAT,year:STRING

要详细了解如何将数据加载到 BigQuery 中,请参阅加载数据简介

要在创建空表时指定内嵌架构定义,请输入带 --table-t 标志的 mk 命令。如果您要在默认项目以外的某个项目中创建表,请按以下格式将相应项目 ID 添加到数据集内:[PROJECT_ID]:[DATASET].[TABLE]

bq mk --table [PROJECT_ID]:[DATASET].[TABLE] [SCHEMA]

其中:

  • [PROJECT_ID] 是项目 ID。
  • [DATASET] 是您项目中的数据集。
  • [TABLE] 是您要创建的表的名称。
  • [SCHEMA] 是内嵌架构定义。

例如,以下命令在默认项目中创建名为 mytable 的空表。该架构以内嵌方式手动指定,具体如下:

bq mk --table mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING

如需详细了解如何创建空表,请参阅使用架构定义创建空表

C#

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

要在将数据加载到表中时指定表架构,请执行以下操作:
using Google.Apis.Bigquery.v2.Data;
using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryLoadTableGcsJson
{
    public void LoadTableGcsJson(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
        var dataset = client.GetDataset(datasetId);
        var schema = new TableSchemaBuilder {
            { "name", BigQueryDbType.String },
            { "post_abbr", BigQueryDbType.String }
        }.Build();
        TableReference destinationTableRef = dataset.GetTableReference(
            tableId: "us_states");
        // Create job configuration
        var jobOptions = new CreateLoadJobOptions()
        {
            SourceFormat = FileFormat.NewlineDelimitedJson
        };
        // Create and run job
        BigQueryJob loadJob = client.CreateLoadJob(
            sourceUri: gcsURI, destination: destinationTableRef,
            schema: schema, options: jobOptions);
        loadJob.PollUntilCompleted();  // Waits for the job to complete.
        // Display the number of rows uploaded
        BigQueryTable table = client.GetTable(destinationTableRef);
        Console.WriteLine(
            $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
    }
}

要在创建空表时指定架构,请执行以下操作:

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryCreateTable
{
    public BigQueryTable CreateTable(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var dataset = client.GetDataset(datasetId);
        // Create schema for new table.
        var schema = new TableSchemaBuilder
        {
            { "full_name", BigQueryDbType.String },
            { "age", BigQueryDbType.Int64 }
        }.Build();
        // Create the table
        return dataset.CreateTable(tableId: "your_table_id", schema: schema);
    }
}

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")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")
gcsRef.SourceFormat = bigquery.JSON
gcsRef.Schema = bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.StringFieldType},
}
loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)
loader.WriteDisposition = bigquery.WriteEmpty

job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}

if status.Err() != nil {
	return fmt.Errorf("Job completed with error: %v", status.Err())
}

要在创建空表时指定架构,请执行以下操作:

// 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: "full_name", Type: bigquery.StringFieldType},
	{Name: "age", Type: bigquery.IntegerFieldType},
}

metaData := &bigquery.TableMetadata{
	Schema:         sampleSchema,
	ExpirationTime: time.Now().AddDate(1, 0, 0), // Table will be automatically deleted in 1 year.
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metaData); err != nil {
	return err
}

Python

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

要在将数据加载到表中时指定表架构,请配置 LoadJobConfig.schema 属性。
# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
uri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"

load_job = client.load_table_from_uri(
    uri,
    dataset_ref.table("us_states"),
    location="US",  # Location must match that of the destination dataset.
    job_config=job_config,
)  # API request
print("Starting job {}".format(load_job.job_id))

load_job.result()  # Waits for table load to complete.
print("Job finished.")

destination_table = client.get_table(dataset_ref.table("us_states"))
print("Loaded {} rows.".format(destination_table.num_rows))

要在创建空表时指定架构,请配置 Table.schema 属性。

from google.cloud import bigquery

schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]

# TODO(developer): Construct a BigQuery client object.
# client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create
# table_id = "your-project.your_dataset.your_table_name"

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # API request
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

指定 JSON 架构文件

如果您不想手动指定架构,则可以创建一个配合 CLI 使用的 JSON 架构文件。JSON 架构文件由一个 JSON 数组构成,该数组内包含如下内容:

创建 JSON 架构文件

要创建 JSON 架构文件,请使用适当的文本编辑器输入以下内容:

[
 {
   "description": "[DESCRIPTION]",
   "name": "[NAME]",
   "type": "[TYPE]",
   "mode": "[MODE]"
 },
 {
   "description": "[DESCRIPTION]",
   "name": "[NAME]",
   "type": "[TYPE]",
   "mode": "[MODE]"
 }
]

JSON 数组由开始和结束括号(即 [])表示。每个列条目必须用英文逗号 (},) 分隔。

您可以通过输入以下命令将现有表架构写入本地文件:

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE] > [PATH_TO_FILE]

您可以利用输出文件来创建自己的 JSON 架构文件。如果使用此方法,请确保该文件仅包含表示表架构的 JSON 数组。

例如,以下 JSON 数组表示基本表架构。此架构包含 3 列:qtr (REQUIRED STRING)、rep (NULLABLE STRING) 和 sales (NULLABLE FLOAT)。

[
  {
    "description": "quarter",
    "mode": "REQUIRED",
    "name": "qtr",
    "type": "STRING"
  },
  {
    "description": "sales representative",
    "mode": "NULLABLE",
    "name": "rep",
    "type": "STRING"
  },
  {
    "description": "total sales",
    "mode": "NULLABLE",
    "name": "sales",
    "type": "FLOAT"
  }
]

使用 JSON 架构文件

创建 JSON 架构文件后,可以在命令行中指定此文件。您不能将架构文件与 GCP Console、经典版 BigQuery 网页界面或 API 一起使用。

您可以使用以下命令手动提供架构文件:

  • load 命令(如果您要加载数据)
  • mk 命令(如果您要创建空表)

提供 JSON 架构文件时,它必须存储在本地可读的位置。您不能指定存储在 Cloud Storage 或 Google 云端硬盘中的 JSON 架构文件。

加载数据时指定架构文件

以下命令使用 JSON 文件中的架构定义将数据加载到表中:

bq --location=[LOCATION] load --source_format=[FORMAT] [PROJECT_ID]:[DATASET].[TABLE] [PATH_TO_DATA_FILE] [PATH_TO_SCHEMA_FILE]

其中:

  • [LOCATION] 是位置的名称。--location 是可选标志。例如,如果您在东京区域使用 BigQuery,可将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • [FORMAT]NEWLINE_DELIMITED_JSONCSV
  • [PROJECT_ID] 是项目 ID。
  • [DATASET] 是一个数据集,它包含要加载数据的表。
  • [TABLE] 为要加载数据的表的名称。
  • [PATH_TO_DATA_FILE] 是 CSV 或 JSON 数据文件在本地机器或 Google Cloud Storage 中的位置。
  • [SCHEMA_FILE] 是本地机器上架构文件的路径。

示例:

输入以下命令,将名为 myfile.csv 的本地 CSV 文件中的数据加载到默认项目内的 mydataset.mytable 中。mydataset 创建于 US 多区域位置。架构在 myschema.json 中指定。

bq --location=US load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json

输入以下命令,将名为 myfile.csv 的本地 CSV 文件中的数据加载到默认项目的 mydataset.mytable 中。mydataset 是在 asia-northeast1 区域中创建的。架构在 myschema.json 中指定。

bq --location=asia-northeast1 load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json

创建表时指定架构文件

以下命令使用 JSON 文件中的架构定义在现有数据集中创建空表:

bq mk --table [PROJECT_ID]:[DATASET].[TABLE] [PATH_TO_SCHEMA_FILE]

其中:

  • [PROJECT_ID] 是项目 ID。
  • [DATASET] 是您项目中的数据集。
  • [TABLE] 是您要创建的表的名称。
  • [PATH_TO_SCHEMA_FILE] 是本地计算机上架构文件的路径。

例如,以下命令在默认项目的 mydataset 中创建名为 mytable 的表。架构在 myschema.json 中指定:

bq mk --table mydataset.mytable ./myschema.json

在 API 中指定架构

要使用 API 指定表架构,请执行以下操作:

使用 API 指定架构的过程类似于创建 JSON 架构文件的过程。

后续步骤

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

发送以下问题的反馈:

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