修改表架构

本文档介绍了如何修改现有 BigQuery 表的架构定义。BigQuery 原生支持以下架构修改:

  • 将列添加到架构定义
  • 将列的模式从 REQUIRED 放宽到 NULLABLE

在未定义初始架构的情况下创建表,稍后再向该表中添加架构定义,这种做法是有效的。

系统不支持任何其他架构修改方式,如果需要的话,都必须采用手动解决方法,包括:

  • 更改列的名称
  • 更改列的数据类型
  • 更改列的模式(除了将 REQUIRED 列放宽为 NULLABLE
  • 删除列

如需了解系统不支持而需要采取其它方法的架构更改,请参阅手动更改表架构

将列添加到表的架构定义

您可以在以下情况将列添加到现有表的架构定义中:

  • 手动(创建一个空列)
  • 在使用加载或查询作业覆盖表时
  • 在使用加载或查询作业将数据附加到表时

添加的任何列都必须遵守 BigQuery 的列名称规则。如需详细了解如何创建架构组件,请参阅指定架构

手动添加空列

如需将空列添加到现有表中,您可以执行以下操作:

  • 使用 Cloud Console 或经典版 BigQuery 网页界面
  • 使用命令行工具的 bq update 命令
  • 调用 tables.patch API 方法
  • 使用客户端库

如果向现有表架构添加新列,则该列必须是 NULLABLEREPEATED。不能向现有表架构添加 REQUIRED 列。如果您尝试通过 CLI 或 API 向现有表架构添加 REQUIRED 列,则会收到以下错误:只有在加载数据时创建表或者创建具有架构定义的空表时,才能添加 BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Cannot add required columns to an existing schema. REQUIRED 列。

向表的架构定义添加新列后,可以使用以下方式将数据加载到新列中:

如需向表的架构定义中添加空列,请按如下所述操作:

控制台

  1. 资源窗格中,选择表。

  2. 查询编辑器下方,滚动到架构部分的底部,然后点击修改架构。

修改表架构

  1. 滚动到打开的面板底部,然后点击添加字段

    • 对于 Name,输入列名称。
    • 对于 Type,选择 data type
    • 对于 Mode,选择 NULLABLEREPEATED
  2. 添加完列后,点击保存

经典版界面

  1. 在导航窗格中,选择表。

  2. Table Details 页面上,点击 Add New Fields。

  3. New Fields 部分:

    • 对于 Name,输入列名称。
    • 对于 Type,选择 data type
    • 对于 Mode,选择 NULLABLEREPEATED

      更新表架构

  4. 添加完列后,点击 Add to Table

CLI

发出 bq update 命令并提供 JSON 架构文件。如果要更新的表在非默认项目中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

bq update project_id:dataset.table schema

其中:

  • project_id 是您的项目 ID
  • dataset 是您要更新的表所属数据集的名称。
  • table 是要更新的表的名称
  • schema 是本地机器上的 JSON 架构文件的路径。

在命令行中指定架构时,您不能添加 RECORD (STRUCT) 类型和列说明,也不能指定列的模式。所有模式均默认为 NULLABLE

如果尝试使用内嵌架构定义添加列,则必须提供包含新列的整个架构定义。由于您无法使用内嵌架构定义来指定列模式,因此更新操作会尝试将任何现有 REQUIRED 列更改为 NULLABLE。这会导致以下错误:BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table. Field field has changed mode from REPEATED to NULLABLE.

使用 CLI 向现有表添加列的首选方法是提供 JSON 架构文件

要使用 JSON 架构文件将空列添加到表的架构,请执行以下操作:

  1. 首先,发出带有 --schema 标志的 bq show 命令,并将现有表架构写入文件。如果要更新的表在非默认项目中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    其中:

    • project_id 是您的项目 ID
    • dataset 是您要更新的表所属数据集的名称。
    • table 是要更新的表的名称
    • schema_file 是写入本地机器的架构定义文件。

    例如,如需将 mydataset.mytable 的架构定义写入文件,请输入以下命令。mydataset.mytable 属于默认项目。

       bq show \
       --schema \
       --format=prettyjson \
       mydataset.mytable > /tmp/myschema.json
    
  2. 在文本编辑器中打开架构文件。架构应如下所示:

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. 将新列添加到架构定义的末尾。如果尝试在数组的其他位置添加新列,则系统会返回以下错误:BigQuery error in update operation: Precondition Failed

    您可以使用 JSON 文件为新列指定说明、NULLABLEREPEATED 模式以及 RECORD 类型。例如,使用上一步中的架构定义,新的 JSON 数组可能如下所示。在此示例中,添加了名为 column4 的新 NULLABLE 列。column4 包含说明。

      [
        {
          "mode": "REQUIRED",
          "name": "column1",
          "type": "STRING"
        },
        {
          "mode": "REQUIRED",
          "name": "column2",
          "type": "FLOAT"
        },
        {
          "mode": "REPEATED",
          "name": "column3",
          "type": "STRING"
        },
        {
          "description": "my new column",
          "mode": "NULLABLE",
          "name": "column4",
          "type": "STRING"
        }
      ]
      

    如需详细了解如何使用 JSON 架构文件,请参阅指定 JSON 架构文件

  4. 更新架构文件后,发出以下命令来更新表的架构。如果要更新的表在非默认项目中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

    bq update project_id:dataset.table schema
    

    其中:

    • project_id 是您的项目 ID
    • dataset 是您要更新的表所属数据集的名称。
    • table 是要更新的表的名称
    • schema 是本地机器上的 JSON 架构文件的路径。

    例如,输入以下命令可更新默认项目中 mydataset.mytable 的架构定义。本地机器上架构文件的路径为 /tmp/myschema.json

    bq update mydataset.mytable /tmp/myschema.json
    

API

调用 tables.patch 方法并使用 schema 属性向架构定义中添加空列。由于 tables.update 方法会替换整个表资源,因此建议使用 tables.patch 方法。

Go

import (
	"context"
	"fmt"

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

// updateTableAddColumn demonstrates modifying the schema of a table to append an additional column.
func updateTableAddColumn(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)
	}

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	newSchema := append(meta.Schema,
		&bigquery.FieldSchema{Name: "phone", Type: bigquery.StringFieldType},
	)
	update := bigquery.TableMetadataToUpdate{
		Schema: newSchema,
	}
	if _, err := tableRef.Update(ctx, update, meta.ETag); err != nil {
		return err
	}
	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 addEmptyColumn() {
  // Adds an empty column to the schema.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';
  const column = {name: 'size', type: 'STRING'};

  // Retrieve current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Update table schema
  const schema = metadata.schema;
  const new_schema = schema;
  new_schema.fields.push(column);
  metadata.schema = new_schema;

  const [result] = await table.setMetadata(metadata);
  console.log(result.schema.fields);
}

Python

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

Table.schema 的副本附加一个新的 SchemaField 对象,然后将 Table.schema 属性的值替换为更新后的架构。
from google.cloud import bigquery

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

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

table = client.get_table(table_id)  # Make an API request.

original_schema = table.schema
new_schema = original_schema[:]  # Creates a copy of the schema.
new_schema.append(bigquery.SchemaField("phone", "STRING"))

table.schema = new_schema
table = client.update_table(table, ["schema"])  # Make an API request.

if len(table.schema) == len(original_schema) + 1 == len(new_schema):
    print("A new column has been added.")
else:
    print("The column has not been added.")

RECORD 添加嵌套列

除了向表架构添加新列之外,您还可以向 RECORD 添加新的嵌套列。添加新嵌套列的过程与添加新列的过程非常相似。

控制台

Cloud Console 当前不支持向现有 RECORD 列添加新的嵌套字段。

经典版界面

经典版 BigQuery 网页界面目前不支持向现有 RECORD 列添加新的嵌套字段。

CLI

发出 bq update 命令,并提供用来向现有 RECORD 列的架构定义添加嵌套字段的 JSON 架构文件。如果要更新的表在非默认项目中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

bq update project_id:dataset.table schema

其中:

  • project_id 是您的项目 ID
  • dataset 是您要更新的表所属数据集的名称。
  • table 是要更新的表的名称
  • schema 是本地机器上的 JSON 架构文件的路径。

在命令行中指定架构时,您不能添加 RECORD (STRUCT) 类型和列说明,也不能指定列的模式。所有模式均默认为 NULLABLE。因此,如果您要向某个 RECORD 中添加新的嵌套列,则必须提供 JSON 架构文件

要使用 JSON 架构文件将嵌套列添加到 RECORD,请执行以下操作:

  1. 首先,发出带有 --schema 标志的 bq show 命令,并将现有表架构写入文件。如果要更新的表在非默认项目中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset.table

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    其中:

    • project_id 是您的项目 ID
    • dataset 是您要更新的表所属数据集的名称。
    • table 是要更新的表的名称
    • schema_file 是写入本地机器的架构定义文件。

    例如,如需将 mydataset.mytable 的架构定义写入文件,请输入以下命令。mydataset.mytable 属于默认项目。

    bq show \
    --schema \
    --format=prettyjson \
    mydataset.mytable > /tmp/myschema.json
    
  2. 在文本编辑器中打开架构文件。架构应如下所示。在本例中,column3 是嵌套的重复列。嵌套列为 nested1nested2fields 数组列出了 column3 中嵌套的字段。

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "fields": [
          {
            "mode": "NULLABLE",
            "name": "nested1",
            "type": "STRING"
          },
          {
            "mode": "NULLABLE",
            "name": "nested2",
            "type": "STRING"
          }
        ],
        "mode": "REPEATED",
        "name": "column3",
        "type": "RECORD"
      }
    ]
    
  3. 将新的嵌套列添加到 fields 数组的末尾。在本例中,nested3 是新的嵌套列。

      [
        {
          "mode": "REQUIRED",
          "name": "column1",
          "type": "STRING"
        },
        {
          "mode": "REQUIRED",
          "name": "column2",
          "type": "FLOAT"
        },
        {
          "fields": [
            {
              "mode": "NULLABLE",
              "name": "nested1",
              "type": "STRING"
            },
            {
              "mode": "NULLABLE",
              "name": "nested2",
              "type": "STRING"
            },
            {
              "mode": "NULLABLE",
              "name": "nested3",
              "type": "STRING"
            }
          ],
          "mode": "REPEATED",
          "name": "column3",
          "type": "RECORD"
        }
      ]
      

    如需详细了解如何使用 JSON 架构文件,请参阅指定 JSON 架构文件

  4. 更新架构文件后,发出以下命令来更新表的架构。如果要更新的表在非默认项目中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

    bq update project_id:dataset.table schema
    

    其中:

    • project_id 是您的项目 ID
    • dataset 是您要更新的表所属数据集的名称。
    • table 是要更新的表的名称
    • schema 是本地机器上的 JSON 架构文件的路径。

    例如,输入以下命令可更新默认项目中 mydataset.mytable 的架构定义。本地机器上架构文件的路径为 /tmp/myschema.json

    bq update mydataset.mytable /tmp/myschema.json
    

API

调用 tables.patch 方法并使用 schema 属性将嵌套列添加到架构定义中。由于 tables.update 方法会替换整个表资源,因此建议使用 tables.patch 方法。

覆盖或附加数据时添加列

当您向现有表加载数据和选择覆盖现有表时,可以向现有表添加新列。当覆盖现有表时,您正在加载的数据的架构将用于覆盖现有表的架构。如需了解如何使用加载作业来覆盖表,请参阅:

您还可以在使用加载或查询作业将数据附加到现有表时,向现有表添加新列。

在加载附加作业中添加列

如需在加载作业中向某个表附加数据的同时向该表添加列,您可以执行以下操作:

  • 使用命令行工具的 bq load 命令
  • 调用 API 的 jobs.insert 方法并配置 load 作业
  • 使用客户端库

目前,Cloud Console 或经典版 BigQuery 网页界面不支持在附加操作期间向现有表中添加列。

在加载作业中使用附加操作添加列时,可以:

  • 自动检测到更新的架构(针对 CSV 和 JSON 文件)
  • 在 JSON 架构文件中指定更新的架构(针对 CSV 和 JSON 文件)
  • 从 Avro、ORC、Parquet 和 Datastore 导出文件的自描述源数据中检索更新的架构

如果在 JSON 文件中指定架构,则必须在其中定义新列。如果缺少新列定义,则系统会在您尝试附加数据时,返回以下错误:Error while reading data, error message: parsing error in row starting at position int: No such field: field.

在附加操作期间添加新列时,新列中现有行的值会设为 NULL

如需在加载作业期间向表附加数据时添加新列,请按如下所述操作:

控制台

使用 Cloud Console 加载数据时,无法向现有表添加新列。

经典版界面

使用经典版 BigQuery 网页界面加载数据时,无法向现有表添加新列。

CLI

使用 bq load 命令加载数据,并指定 --noreplace 标志以表明您要将数据附加到现有表。

如果要附加的数据采用的是 CSV 格式或以换行符分隔的 JSON 格式,请指定 --autodetect 标志以使用架构自动检测功能,或在 JSON 架构文件中提供架构。添加的列可以从 Avro 或 Datastore 导出文件自动推断出来。

--schema_update_option 标志设置为 ALLOW_FIELD_ADDITION,以表明您要附加的数据包含新列。

如果要附加的表在非默认项目的数据集中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

(可选)提供 --location 标志并将其值设置为您的位置

输入 load 命令,如下所示:

bq --location=location load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

其中:

  • location 是您的位置的名称。--location 是可选标志。例如,如果您在东京区域使用 BigQuery,请将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • formatNEWLINE_DELIMITED_JSONCSVAVROPARQUETORCDATASTORE_BACKUP
  • project_id 是您的项目 ID。
  • dataset 是包含该表的数据集的名称
  • table 是要附加的表的名称。
  • path_to_source 是完全限定的 Cloud Storage URI、以英文逗号分隔的 URI 列表,或指向本地机器上数据文件的路径。
  • schema 是指向本地 JSON 架构文件的路径。如果未指定 --autodetect,只有 CSV 和 JSON 文件才需要架构文件。Avro 和 Datastore 架构是根据源数据推断出来的。

示例:

输入以下命令可使用加载作业将本地 Avro 数据文件 /tmp/mydata.avro 附加到 mydataset.mytable。由于架构可以根据 Avro 数据自动推断出来,因此您无需使用 --autodetect 标志。mydataset 属于默认项目。

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=AVRO \
mydataset.mytable \
/tmp/mydata.avro

输入以下命令可使用加载作业将 Cloud Storage 中以换行符分隔的 JSON 数据文件附加到 mydataset.mytable--autodetect 标志用于检测新列。mydataset 属于默认项目。

bq load \
--noreplace \
--autodetect \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json

输入以下命令可使用加载作业将 Cloud Storage 中以换行符分隔的 JSON 数据文件附加到 mydataset.mytable。包含新列的架构在本地 JSON 架构文件 /tmp/myschema.json 中进行指定。mydataset 属于 myotherproject,而非默认项目。

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_ADDITION \
--source_format=NEWLINE_DELIMITED_JSON \
myotherproject:mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

API

调用 jobs.insert 方法。配置 load 作业并设置以下属性:

  • 使用 sourceUris 属性引用 Cloud Storage 云端存储中的数据。
  • 通过设置 sourceFormat 属性来指定数据格式。
  • schema 属性中指定架构。
  • 使用 schemaUpdateOptions 属性指定架构更新选项。
  • 使用 writeDisposition 属性将目标表的写入处置方式设置为 WRITE_APPEND

Go

import (
	"context"
	"fmt"
	"os"

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

// createTableAndWidenLoad demonstrates augmenting a table's schema to add a new column via a load job.
func createTableAndWidenLoad(projectID, datasetID, tableID, filename 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)
	}

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, import data from a local file, but specify field additions are allowed.
	// Because the data has a second column (age), the schema is amended as part of
	// the load.
	f, err := os.Open(filename)
	if err != nil {
		return err
	}
	source := bigquery.NewReaderSource(f)
	source.AutoDetect = true   // Allow BigQuery to determine schema.
	source.SkipLeadingRows = 1 // CSV has a single header line.

	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)
	loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	return nil
}

Node.js

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

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate client
const bigquery = new BigQuery();

async function addColumnLoadAppend() {
  // Adds a new column to a BigQuery table while appending rows via a load job.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const fileName = '/path/to/file.csv';
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // In this example, the existing table contains only the 'Name', 'Age',
  // & 'Weight' columns. 'REQUIRED' fields cannot  be added to an existing
  // schema, so the additional column must be 'NULLABLE'.
  const schema = 'Name:STRING, Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // Set load job options
  const options = {
    schema: schema,
    schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
  };

  // Load data from a local file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(fileName, options);

  console.log(`Job ${job.id} completed.`);
  console.log(`New Schema:`);
  console.log(job.configuration.load.schema.fields);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

Python

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

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the length of the schema
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
print("Table {} contains {} columns.".format(table_id, len(table.schema)))

# Configures the load job to append the data to the destination table,
# allowing field addition
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
# In this example, the existing table contains only the 'full_name' column.
# 'REQUIRED' fields cannot be added to an existing schema, so the
# additional column must be 'NULLABLE'.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated length of the schema
table = client.get_table(table)
print("Table {} now contains {} columns.".format(table_id, len(table.schema)))

在查询附加作业中添加列

如需在向某个表附加查询结果的同时向该表添加列,您可以执行以下操作:

  • 使用命令行工具的 bq query 命令
  • 调用 API 的 jobs.insert 方法并配置 query 作业
  • 使用客户端库

目前,Cloud Console 或经典版 BigQuery 网页界面不支持在附加操作期间添加列。

在查询作业中使用附加操作添加列时,查询结果的架构将用于更新目标表的架构。请注意,您无法查询一个位置中的表而将结果写入另一个位置的表。

如需在查询作业期间向表附加数据时添加新列,请按如下所述操作:

控制台

使用 Cloud Console 附加查询结果时,无法向现有表添加新列。

经典版界面

使用经典版 BigQuery 网页界面附加查询结果时,无法向现有表添加新列。

CLI

使用 bq query 命令查询数据,并指定 --destination_table 标志以表明您要附加的表。

如需指定您要将查询结果附加到现有的目标表,请指定 --append_table 标志。

--schema_update_option 标志设置为 ALLOW_FIELD_ADDITION,以表明您要附加的查询结果包含新列。

指定 use_legacy_sql=false 标志以使用标准 SQL 语法进行查询。

如果要附加的表在非默认项目的数据集中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset。请注意,您要查询的表和目标表必须位于同一位置。

(可选)提供 --location 标志并将其值设置为您的位置

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'query'

其中:

  • location 是您的位置的名称。--location 是可选标志。例如,如果您在东京区域使用 BigQuery,请将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。请注意,您无法将查询结果附加到另一个位置的表。
  • project_id 是您的项目 ID。
  • dataset 是要附加的表所属的数据集的名称
  • table 是要附加的表的名称。
  • query 是使用标准 SQL 语法的查询

示例:

在默认项目中输入以下命令查询 mydataset.mytable,并将查询结果附加到 mydataset.mytable2(也在默认项目中)。

bq query \
--destination_table mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

在默认项目中输入以下命令查询 mydataset.mytable,并将查询结果附加到 myotherproject 中的 mydataset.mytable2

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_ADDITION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

调用 jobs.insert 方法。配置 query 作业并设置以下属性:

  • 使用 destinationTable 属性指定目标表。
  • 使用 writeDisposition 属性将目标表的写入处置方式设置为 WRITE_APPEND
  • 使用 schemaUpdateOptions 属性指定架构更新选项。
  • 使用 query 属性指定标准 SQL 查询。

Go

import (
	"context"
	"fmt"

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

// createTableAndWidenQuery demonstrates how the schema of a table can be modified to add columns by appending
// query results that include the new columns.
func createTableAndWidenQuery(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)
	}

	// First, we create a sample table.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, original); err != nil {
		return err
	}
	// Our table has two columns.  We'll introduce a new favorite_color column via
	// a subsequent query that appends to the table.
	q := client.Query("SELECT \"Timmy\" as full_name, 85 as age, \"Blue\" as favorite_color")
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_ADDITION"}
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	return nil
}

Node.js

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

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate client
const bigquery = new BigQuery();

async function addColumnQueryAppend() {
  // Adds a new column to a BigQuery table while appending rows via a query job.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // In this example, the existing table contains only the 'name' column.
  // 'REQUIRED' fields cannot  be added to an existing schema,
  // so the additional column must be 'NULLABLE'.
  const query = `SELECT name, year
    FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
    WHERE state = 'TX'
    LIMIT 10`;

  // Set load job options
  const options = {
    query: query,
    schemaUpdateOptions: ['ALLOW_FIELD_ADDITION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
  };

  const [job] = await bigquery.createQueryJob(options);
  console.log(`Job ${job.id} started.`);

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();
  console.log(`Job ${job.id} completed.`);

  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
}

Python

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

from google.cloud import bigquery

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

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

# Retrieves the destination table and checks the length of the schema.
table = client.get_table(table_id)  # Make an API request.
print("Table {} contains {} columns".format(table_id, len(table.schema)))

# Configures the query to append the results to a destination table,
# allowing field addition.
job_config = bigquery.QueryJobConfig(destination=table_id)
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION
]
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND

# Start the query, passing in the extra configuration.
query_job = client.query(
    # In this example, the existing table contains only the 'full_name' and
    # 'age' columns, while the results of this query will contain an
    # additional 'favorite_color' column.
    'SELECT "Timmy" as full_name, 85 as age, "Blue" as favorite_color;',
    job_config=job_config,
)  # Make an API request.
query_job.result()  # Wait for the job to complete.

# Checks the updated length of the schema.
table = client.get_table(table_id)  # Make an API request.
print("Table {} now contains {} columns".format(table_id, len(table.schema)))

放宽列的模式

当前对列的模式的修改仅支持将其从 REQUIRED 更改为 NULLABLE。将列的模式从 REQUIRED 更改为 NULLABLE 也称为列放宽。您可以在以下情况放宽 REQUIRED 列:

  • 手动
  • 在使用加载或查询作业覆盖表时
  • 在使用查询作业将数据附加到表时

手动将 REQUIRED 列更改为 NULLABLE

如需手动将列的模式从 REQUIRED 更改为 NULLABLE,您可以执行以下操作:

  • 使用 Cloud Console 或经典版 BigQuery 网页界面
  • 使用命令行工具的 bq update 命令
  • 调用 tables.patch API 方法。
  • 使用客户端库

要将列的模式从 REQUIRED 手动更改为 NULLABLE,请执行以下操作:

控制台

目前,Cloud Console 不支持放宽列的模式。

经典版界面

  1. 展开数据集并选择表。

  2. Table Details 页面上,点击 Schema 标签。

  3. 点击所需列右侧的向下箭头,然后选择以下任一选项:

    • Make NULLABLE - 放宽单列的模式
    • All REQUIRED to NULLABLE - 将架构定义中的所有 REQUIRED 列更改为 NULLABLE

      放宽列模式

  4. Confirm Mode Change 对话框中,点击 OK 以将模式更改为 NULLABLE。请注意,此项更改无法撤消。

CLI

  1. 首先,发出带有 --schema 标志的 bq show 命令,并将现有表架构写入文件。如果要更新的表在非默认项目中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

    bq show \
    --schema \
    --format=prettyjson \
    project_id:dataset.table > schema_file
    

    其中:

    • project_id 是您的项目 ID
    • dataset 是您要更新的表所属数据集的名称。
    • table 是要更新的表的名称
    • schema_file 是写入本地机器的架构定义文件。

    例如,如需将 mydataset.mytable 的架构定义写入文件,请输入以下命令。mydataset.mytable 属于默认项目。

      bq show \
      --schema \
      --format=prettyjson \
      mydataset.mytable > /tmp/myschema.json
    
  2. 在文本编辑器中打开架构文件。架构应如下所示:

    [
      {
        "mode": "REQUIRED",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    
  3. 将现有列的模式从 REQUIRED 更改为 NULLABLE。在此示例中,放宽了 column1 的模式。

    [
      {
        "mode": "NULLABLE",
        "name": "column1",
        "type": "STRING"
      },
      {
        "mode": "REQUIRED",
        "name": "column2",
        "type": "FLOAT"
      },
      {
        "mode": "REPEATED",
        "name": "column3",
        "type": "STRING"
      }
    ]
    

    如需详细了解如何使用 JSON 架构文件,请参阅指定 JSON 架构文件

  4. 更新架构文件后,发出以下命令来更新表的架构。如果要更新的表在非默认项目中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

    bq update project_id:dataset.table schema
    

    其中:

    • project_id 是您的项目 ID
    • dataset 是您要更新的表所属数据集的名称。
    • table 是要更新的表的名称
    • schema 是本地机器上的 JSON 架构文件的路径。

    例如,输入以下命令可更新默认项目中 mydataset.mytable 的架构定义。本地机器上架构文件的路径为 /tmp/myschema.json

      bq update mydataset.mytable /tmp/myschema.json
    

API

调用 tables.patch 并使用 schema 属性将架构定义中的 REQUIRED 列更改为 NULLABLE。由于 tables.update 方法会替换整个表资源,因此建议使用 tables.patch 方法。

Go

import (
	"context"
	"fmt"

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

// relaxTableAPI demonstrates modifying the schema of a table to remove the requirement that columns allow
// no NULL values.
func relaxTableAPI(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)
	}

	// Setup: We first create a table with a schema that's restricts NULL values.
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	original := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	if err := client.Dataset(datasetID).Table(tableID).Create(ctx, original); err != nil {
		return err
	}

	tableRef := client.Dataset(datasetID).Table(tableID)
	meta, err := tableRef.Metadata(ctx)
	if err != nil {
		return err
	}
	// Iterate through the schema to set all Required fields to false (nullable).
	var relaxed bigquery.Schema
	for _, v := range meta.Schema {
		v.Required = false
		relaxed = append(relaxed, v)
	}
	newMeta := bigquery.TableMetadataToUpdate{
		Schema: relaxed,
	}
	if _, err := tableRef.Update(ctx, newMeta, meta.ETag); err != nil {
		return err
	}
	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 relaxColumn() {
  /**
   * Changes columns from required to nullable.
   * Assumes existing table with the following schema:
   * [{name: 'Name', type: 'STRING', mode: 'REQUIRED'},
   * {name: 'Age', type: 'INTEGER'},
   * {name: 'Weight', type: 'FLOAT'},
   * {name: 'IsMagic', type: 'BOOLEAN'}];
   */

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  const newSchema = [
    {name: 'Name', type: 'STRING', mode: 'NULLABLE'},
    {name: 'Age', type: 'INTEGER'},
    {name: 'Weight', type: 'FLOAT'},
    {name: 'IsMagic', type: 'BOOLEAN'},
  ];

  // Retrieve current table metadata
  const table = bigquery.dataset(datasetId).table(tableId);
  const [metadata] = await table.getMetadata();

  // Update schema
  metadata.schema = newSchema;
  const [apiResponse] = await table.setMetadata(metadata);

  console.log(apiResponse.schema.fields);
}

Python

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

使用 mode 属性设置为 'NULLABLE'SchemaField 对象列表覆盖 Table.schema 属性

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
# table_id = 'my_table'

original_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
table_ref = client.dataset(dataset_id).table(table_id)
table = bigquery.Table(table_ref, schema=original_schema)
table = client.create_table(table)
assert all(field.mode == "REQUIRED" for field in table.schema)

# SchemaField properties cannot be edited after initialization.
# To make changes, construct new SchemaField objects.
relaxed_schema = [
    bigquery.SchemaField("full_name", "STRING", mode="NULLABLE"),
    bigquery.SchemaField("age", "INTEGER", mode="NULLABLE"),
]
table.schema = relaxed_schema
table = client.update_table(table, ["schema"])

assert all(field.mode == "NULLABLE" for field in table.schema)

在加载或查询作业中将 REQUIRED 更改为 NULLABLE

当您向现有表加载数据和选择覆盖现有表时,可以将现有表的架构中的 REQUIRED 列放宽为 NULLABLE。当覆盖现有表时,您正在加载的数据的架构将用于覆盖现有表的架构。如需了解如何使用加载作业来覆盖表,请参阅:

当使用查询作业向现有表附加数据时,您还可以在现有表的架构中将 REQUIRED 列放宽为 NULLABLE

在加载附加作业中将 REQUIRED 更改为 NULLABLE

如需在加载作业中向表附加数据的同时放宽列的模式,您可以执行以下操作:

  • 使用命令行工具的 bq load 命令
  • 调用 API 的 jobs.insert 方法并配置加载作业
  • 使用客户端库

目前,Cloud Console 或经典版 BigQuery 网页界面不支持在附加操作期间更改列的模式。

在加载作业中使用附加操作放宽列的模式时,可以执行以下操作:

  • 通过指定 JSON 架构文件放宽各个列的模式(在附加 CSV 和 JSON 文件中的数据时)
  • 在 Avro、ORC 或 Parquet 架构中将列模式放宽为 null,并允许架构推断功能检测已放宽模式的列

如需在加载作业期间向表附加数据时将列从 REQUIRED 放宽为 NULLABLE,请按如下所述操作:

控制台

目前,Cloud Console 不支持放宽列的模式。

经典版界面

使用经典版 BigQuery 网页界面在加载作业中向表附加数据时,您无法放宽现有列的模式。

CLI

使用 bq load 命令加载数据,并指定 --noreplace 标志以表明您要将数据附加到现有表。

如果要附加的数据采用的是 CSV 格式或以换行符分隔的 JSON 格式,请在本地 JSON 架构文件中指定已放宽模式的列,或使用 --autodetect 标志通过架构检测功能来发现源数据中已放宽模式的列。如需了解如何使用 JSON 架构文件放宽列模式,请参阅手动将 REQUIRED 列更改为 NULLABLE

系统可以根据 Avro、ORC 和 Parquet 文件自动推断已放宽模式的列。列放宽不适用于 Datastore 导出数据附加操作。通过加载 Datastore 导出文件创建的表中的列始终为 NULLABLE

--schema_update_option 标志设置为 ALLOW_FIELD_RELAXATION,以表明您要附加的数据包含已放宽的列。

如果要附加的表在非默认项目的数据集中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

(可选)提供 --location 标志并将其值设置为您的位置

输入 load 命令,如下所示:

bq --location=location load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=format \
project_id:dataset.table \
path_to_source \
schema

其中:

  • location 是您的位置的名称。--location 是可选标志。例如,如果您在东京区域使用 BigQuery,请将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • formatNEWLINE_DELIMITED_JSONCSVPARQUETORCAVRODATASTORE_BACKUP 文件不需要放宽列模式。根据 Datastore 导出文件创建的表中的列始终为 NULLABLE
  • project_id 是您的项目 ID。
  • dataset 是包含该表的数据集的名称
  • table 是要附加的表的名称。
  • path_to_source 是完全限定的 Cloud Storage URI、以英文逗号分隔的 URI 列表,或指向本地机器上数据文件的路径。
  • schema 是指向本地 JSON 架构文件的路径。此选项仅用于 CSV 和 JSON 文件。系统会自动根据 Avro 文件推断出已放宽模式的列。

示例:

输入以下命令可使用加载作业将本地 Avro 数据文件 /tmp/mydata.avro 附加到 mydataset.mytable。由于系统可以根据 Avro 数据自动推断已放宽的列,因此您无需指定架构文件。mydataset 属于默认项目。

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=AVRO \
mydataset.mytable \
/tmp/mydata.avro

输入以下命令,使用加载作业将 Cloud Storage 中以换行符分隔的 JSON 文件中的数据附加到 mydataset.mytable。已放宽的列所属的架构位于本地 JSON 架构文件 - /tmp/myschema.jsonmydataset 属于默认项目。

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable \
gs://mybucket/mydata.json \
/tmp/myschema.json

输入以下命令,使用加载作业将本地机器上 CSV 文件中的数据附加到 mydataset.mytable。该命令使用架构自动检测功能来发现源数据中已放宽的列。mydataset 属于 myotherproject,而非默认项目。

bq load \
--noreplace \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--source_format=CSV \
--autodetect \
myotherproject:mydataset.mytable \
mydata.csv

API

调用 jobs.insert 方法。配置 load 作业并设置以下属性:

  • 使用 sourceUris 属性引用 Cloud Storage 云端存储中的数据。
  • 通过设置 sourceFormat 属性来指定数据格式。
  • schema 属性中指定架构。
  • 使用 schemaUpdateOptions 属性指定架构更新选项。
  • 使用 writeDisposition 属性将目标表的写入处置方式设置为 WRITE_APPEND

Go

import (
	"context"
	"fmt"
	"os"

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

// relaxTableImport demonstrates amending the schema of a table to relax columns from
// not allowing NULL values to allowing them.
func relaxTableImport(projectID, datasetID, tableID, filename 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)
	}

	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, import data from a local file, but specify relaxation of required
	// fields as a side effect while the data is appended.
	f, err := os.Open(filename)
	if err != nil {
		return err
	}
	source := bigquery.NewReaderSource(f)
	source.AutoDetect = true   // Allow BigQuery to determine schema.
	source.SkipLeadingRows = 1 // CSV has a single header line.

	loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(source)
	loader.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
	job, err := loader.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	return nil
}

Node.js

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

// Import the Google Cloud client libraries
const {BigQuery} = require('@google-cloud/bigquery');

// Instantiate client
const bigquery = new BigQuery();

async function relaxColumnLoadAppend() {
  // Changes required column to nullable in load append job.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const fileName = '/path/to/file.csv';
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // In this example, the existing table contains the 'Name'
  // column as a 'REQUIRED' field.
  const schema = 'Age:INTEGER, Weight:FLOAT, IsMagic:BOOLEAN';

  // Retrieve destination table reference
  const [table] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .get();
  const destinationTableRef = table.metadata.tableReference;

  // Set load job options
  const options = {
    schema: schema,
    schemaUpdateOptions: ['ALLOW_FIELD_RELAXATION'],
    writeDisposition: 'WRITE_APPEND',
    destinationTable: destinationTableRef,
  };

  // Load data from a local file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(fileName, options);

  console.log(`Job ${job.id} completed.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}

Python

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

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')
# filepath = 'path/to/your_file.csv'

# Retrieves the destination table and checks the number of required fields
table_id = "my_table"
table_ref = dataset_ref.table(table_id)
table = client.get_table(table_ref)
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
# In this example, the existing table has 3 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the load job to append the data to a destination table,
# allowing field relaxation
job_config = bigquery.LoadJobConfig()
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
# In this example, the existing table contains three required fields
# ('full_name', 'age', and 'favorite_color'), while the data to load
# contains only the first two fields.
job_config.schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]
job_config.source_format = bigquery.SourceFormat.CSV
job_config.skip_leading_rows = 1

with open(filepath, "rb") as source_file:
    job = client.load_table_from_file(
        source_file,
        table_ref,
        location="US",  # Must match the destination dataset location.
        job_config=job_config,
    )  # API request

job.result()  # Waits for table load to complete.
print(
    "Loaded {} rows into {}:{}.".format(
        job.output_rows, dataset_id, table_ref.table_id
    )
)

# Checks the updated number of required fields
table = client.get_table(table)
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))

在查询附加作业中将 REQUIRED 更改为 NULLABLE

如需在附加查询结果时放宽表中的所有列,您可以执行以下操作:

  • 使用命令行工具的 bq query 命令
  • 调用 API 的 jobs.insert 方法并配置查询作业
  • 使用客户端库

目前,Cloud Console 或经典版 BigQuery 网页界面不支持在附加操作期间放宽列的模式。

在查询作业中使用附加操作放宽列时,可以通过将 --schema_update_option 标志设置为 ALLOW_FIELD_RELAXATION 来放宽目标表中的所有必需字段。您无法使用查询附加来放宽目标表中的单个列。

如需在查询作业期间向目标表附加数据时放宽该表中所有列的模式,请按如下所述操作:

控制台

目前,Cloud Console 不支持放宽列的模式。

经典版界面

使用 BigQuery 网页界面附加查询结果时,无法放宽目标表中的列。

CLI

使用 bq query 命令查询数据,并指定 --destination_table 标志以表明您要附加的表。

如需指定您要将查询结果附加到现有的目标表,请指定 --append_table 标志。

--schema_update_option 标志设置为 ALLOW_FIELD_RELAXATION,以表明您要附加的表中所有的 REQUIRED 列都应更改为 NULLABLE

指定 use_legacy_sql=false 标志以使用标准 SQL 语法进行查询。

如果要附加的表在非默认项目的数据集中,请按以下格式将相应项目 ID 添加到数据集名称中:project_id:dataset

(可选)提供 --location 标志并将其值设置为您的位置

bq --location=location query \
--destination_table project_id:dataset.table \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'query'

其中:

  • location 是您的位置的名称。--location 是可选标志。例如,如果您在东京区域使用 BigQuery,请将该标志的值设置为 asia-northeast1。您可以使用 .bigqueryrc 文件设置该位置的默认值。
  • project_id 是您的项目 ID。
  • dataset 是要附加的表所属的数据集的名称
  • table 是要附加的表的名称。
  • query 是使用标准 SQL 语法的查询

示例:

在默认项目中输入以下命令查询 mydataset.mytable,并将查询结果附加到 mydataset.mytable2(也在默认项目中)。该命令将目标表中的所有 REQUIRED 列更改为 NULLABLE

bq query \
--destination_table mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

在默认项目中输入以下命令查询 mydataset.mytable,并将查询结果附加到 myotherproject 中的 mydataset.mytable2。该命令将目标表中的所有 REQUIRED 列更改为 NULLABLE

bq query \
--destination_table myotherproject:mydataset.mytable2 \
--append_table \
--schema_update_option=ALLOW_FIELD_RELAXATION \
--use_legacy_sql=false \
'SELECT
   column1,column2
 FROM
   mydataset.mytable'

API

调用 jobs.insert 方法。配置 query 作业并设置以下属性:

  • 使用 destinationTable 属性指定目标表。
  • 使用 writeDisposition 属性将目标表的写入处置方式设置为 WRITE_APPEND
  • 使用 schemaUpdateOptions 属性指定架构更新选项。
  • 使用 query 属性指定标准 SQL 查询。

Go

import (
	"context"
	"fmt"

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

// relaxTableQuery demonstrates relaxing the schema of a table by appending query results to
// enable the table to allow NULL values.
func relaxTableQuery(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)
	}
	sampleSchema := bigquery.Schema{
		{Name: "full_name", Type: bigquery.StringFieldType, Required: true},
		{Name: "age", Type: bigquery.IntegerFieldType, Required: true},
	}
	meta := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, meta); err != nil {
		return err
	}
	// Now, append a query result that includes nulls, but allow the job to relax
	// all required columns.
	q := client.Query("SELECT \"Beyonce\" as full_name")
	q.QueryConfig.Dst = client.Dataset(datasetID).Table(tableID)
	q.SchemaUpdateOptions = []string{"ALLOW_FIELD_RELAXATION"}
	q.WriteDisposition = bigquery.WriteAppend
	q.Location = "US"
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	_, err = job.Wait(ctx)
	if err != nil {
		return err
	}
	return nil
}

Python

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

from google.cloud import bigquery

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

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

# Retrieves the destination table and checks the number of required fields.
table = client.get_table(table_id)  # Make an API request.
original_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)

# In this example, the existing table has 2 required fields.
print("{} fields in the schema are required.".format(original_required_fields))

# Configures the query to append the results to a destination table,
# allowing field relaxation.
job_config = bigquery.QueryJobConfig(destination=table_id)
job_config.schema_update_options = [
    bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION
]
job_config.write_disposition = bigquery.WriteDisposition.WRITE_APPEND

# Start the query, passing in the extra configuration.
query_job = client.query(
    # In this example, the existing table contains 'full_name' and 'age' as
    # required columns, but the query results will omit the second column.
    'SELECT "Beyonce" as full_name;',
    job_config=job_config,
)  # Make an API request.
query_job.result()  # Wait for the job to complete.

# Checks the updated number of required fields.
table = client.get_table(table_id)  # Make an API request.
current_required_fields = sum(field.mode == "REQUIRED" for field in table.schema)
print("{} fields in the schema are now required.".format(current_required_fields))

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

发送以下问题的反馈:

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