指定架构

通过 BigQuery,您可以在将数据加载到表中以及创建空表时指定表架构。对于支持的数据格式,您还可以使用架构自动检测功能。

加载 Avro、Parquet、ORC、Firestore 导出文件或 Datastore 导出文件时,系统会自动从自描述源数据中检索架构。

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

  • 手动指定架构:
    • 使用 Cloud Console。
    • 使用 bq 命令行工具以内嵌方式提供。
  • 创建 JSON 格式的架构文件。
  • 调用 jobs.insert 方法并在 load 作业配置中配置 schema 属性。
  • 调用 tables.insert 方法并使用 schema 属性在表资源中配置架构。

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

架构组件

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

列名称

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

  • _TABLE_
  • _FILE_
  • _PARTITION

列名不可重复,即使其大小写不同也不行。例如,名为 Column1 的列和名为 column1 的列被视作相同。

列说明

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

标准 SQL 数据类型

通过 BigQuery 标准 SQL,您可以在架构中指定以下数据类型。数据类型是必需的。

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

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

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

模式

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

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

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

手动指定架构

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

手动指定表架构的方法如下:

控制台

在 Cloud Console 中,您可以使用添加字段选项或以文本形式修改选项指定架构。

  1. 在 Cloud Console 中打开 BigQuery 页面。

    转到 BigQuery 页面

  2. 从导航面板的资源部分选择数据集。

  3. 点击窗口右侧的创建表

    创建表。

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

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

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

      选择数据集。

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

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

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

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

bq

使用以下任一命令,手动提供采用 field:data_type,field:data_type 格式的内嵌架构:

  • 如果要加载数据,请使用 bq load 命令。
  • 如果要创建空表,请使用 bq 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 文件设置默认位置值。
  • formatNEWLINE_DELIMITED_JSONCSV
  • project_id:您的项目 ID。
  • dataset:您要向其中加载数据的表所属的数据集。
  • table_name:要向其中加载数据的表的名称。
  • path_to_source:本地机器或 Cloud Storage 中 CSV 或 JSON 数据文件的位置。
  • schema:内嵌架构定义。

例如:

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

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

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

如需在创建空表时指定内嵌架构定义,请输入带 --table-t 标志的 bq 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#

如需在将数据加载到表中时指定表架构,请执行以下操作:


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

如需在将数据加载到表中时指定表架构,请执行以下操作:

import (
	"context"
	"fmt"

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

// importJSONExplicitSchema demonstrates loading newline-delimited JSON data from Cloud Storage
// into a BigQuery table and providing an explicit schema for the data.
func importJSONExplicitSchema(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	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())
	}
	return nil
}

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

import (
	"context"
	"fmt"
	"time"

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

// createTableExplicitSchema demonstrates creating a new BigQuery table and specifying a schema.
func createTableExplicitSchema(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: "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
	}
	return nil
}

Java

如需在将数据加载到表中时指定表架构,请执行以下操作:

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.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.TableId;

// Sample to load JSON data from Cloud Storage into a new BigQuery table
public class LoadJsonFromGCS {

  public static void runLoadJsonFromGCS() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
    Schema schema =
        Schema.of(
            Field.of("name", StandardSQLTypeName.STRING),
            Field.of("post_abbr", StandardSQLTypeName.STRING));
    loadJsonFromGCS(datasetName, tableName, sourceUri, schema);
  }

  public static void loadJsonFromGCS(
      String datasetName, String tableName, String sourceUri, Schema schema) {
    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);
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.json())
              .setSchema(schema)
              .build();

      // Load data from a GCS JSON file into the table
      Job job = bigquery.create(JobInfo.of(loadConfig));
      // Blocks until this load table job completes its execution, either failing or succeeding.
      job = job.waitFor();
      if (job.isDone()) {
        System.out.println("Json from GCS successfully loaded in a table");
      } else {
        System.out.println(
            "BigQuery was unable to load into the table due to an error:"
                + job.getStatus().getError());
      }
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

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

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.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 CreateTable {

  public static void runCreateTable() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    Schema schema =
        Schema.of(
            Field.of("stringField", StandardSQLTypeName.STRING),
            Field.of("booleanField", StandardSQLTypeName.BOOL));
    createTable(datasetName, tableName, schema);
  }

  public static void createTable(String datasetName, String tableName, Schema schema) {
    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);
      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

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

Python

要在将数据加载到表中时指定表架构,请配置 LoadJobConfig.schema 属性。

from google.cloud import bigquery

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

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("name", "STRING"),
        bigquery.SchemaField("post_abbr", "STRING"),
    ],
    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,
    table_id,
    location="US",  # Must match the destination dataset location.
    job_config=job_config,
)  # Make an API request.

load_job.result()  # Waits for the job to complete.

destination_table = client.get_table(table_id)
print("Loaded {} rows.".format(destination_table.num_rows))

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

from google.cloud import bigquery

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

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

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

指定 JSON 架构文件

如果您不想手动指定架构,则可以创建一个配合 bq 命令行工具使用的 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 架构文件后,您可以使用 bq 命令行工具指定该文件。您不能将架构文件与 Cloud Console 或 API 一起使用。

手动提供架构文件:

  • 如果要加载数据,请使用 bq load 命令。
  • 如果要创建空表,请使用 bq 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 文件设置默认位置值。
  • formatNEWLINE_DELIMITED_JSONCSV
  • project_id:您的项目 ID。
  • dataset:您要向其中加载数据的表所属的数据集。
  • table:要向其中加载数据的表的名称。
  • path_to_data_file:本地机器或 Cloud Storage 中 CSV 或 JSON 数据文件的位置。
  • path_to_schema_file:本地机器上架构文件的路径。

例如:

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

bq 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 架构文件的过程。

后续步骤