修改表架构

本文档介绍了如何修改现有 BigQuery 表的架构定义。

您可以使用 SQL 数据定义语言 (DDL) 语句执行本文档中所述的大多数架构修改。这些语句不会产生费用。

您可以通过将表数据导出到 Cloud Storage 并在随后将数据加载到已修改了架构定义的新表中，使用此页面上介绍的所有方法来修改表架构。 BigQuery 加载和导出作业是免费的，但将导出的数据存储在 Cloud Storage 中会产生费用。以下部分介绍了执行各种类型架构修改的其他方法。

添加列

您可以使用以下选项之一向现有表的架构定义中添加列：

  • 添加新的空列。
  • 使用加载或查询作业覆盖表。
  • 通过加载或查询作业将数据附加到表中。

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

添加空列

如果向现有表架构添加新列，则该列必须是 NULLABLE 或 REPEATED。不能向现有表架构添加 REQUIRED 列。使用 API 或 bq 命令行工具向现有表架构添加 REQUIRED 列会导致错误。但是，您可以创建嵌套的 REQUIRED 列以作为新的 RECORD 字段的一部分。只有在加载数据时创建表或者创建具有架构定义的空表时，才能添加 REQUIRED 列。

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

控制台

  1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

    转到 BigQuery

  2. 在探索器面板中，展开您的项目和数据集，然后选择表。

  3. 在详细信息面板中，点击架构标签页。

  4. 点击修改架构。您可能需要滚动才能看到此按钮。

  5. 在当前架构页面中的新字段下，点击添加字段。

    • 对于 Name ，输入列名称。
    • 对于 Type，选择 data type。
    • 对于模式，选择 NULLABLE 或 REPEATED。

  6. 添加完列后，点击保存。

SQL

使用 ALTER TABLE ADD COLUMN DDL 语句：

  1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

    转到 BigQuery

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

    ALTER TABLE mydataset.mytable
ADD COLUMN new_column STRING;

  3. 点击 运行。

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

bq

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

bq update PROJECT_ID:DATASET.TABLE SCHEMA

请替换以下内容：

  • PROJECT_ID：您的项目 ID。
  • DATASET：要更新的表所属数据集的名称。
  • TABLE：要更新的表的名称。
  • SCHEMA：本地机器上 JSON 架构文件的路径。

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

如果尝试使用内嵌架构定义添加列，则必须提供包含新列的整个架构定义。由于您无法使用内嵌架构定义来指定列模式，因此更新会将任何现有 REPEATED 列更改为 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.

使用 bq 命令行工具向现有表添加列的首选方法是提供 JSON 架构文件。

如需使用 JSON 架构文件将空列添加到表的架构，请执行以下操作：

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

    bq show \
--schema \
--format=prettyjson \
PROJECT_ID:DATASET.TABLE > SCHEMA

    请替换以下内容：

    • PROJECT_ID：您的项目 ID。
    • DATASET：要更新的表所属数据集的名称。
    • TABLE：要更新的表的名称。
    • SCHEMA：写入本地机器的架构定义文件。

    例如，如需将 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 文件为新列指定说明、NULLABLE 或 REPEATED 模式以及 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：写入本地机器的架构定义文件。

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

    bq update mydataset.mytable /tmp/myschema.json

API

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

Go

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

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

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)
	}
	defer client.Close()

	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
}

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.FieldList;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.Table;
import java.util.ArrayList;
import java.util.List;

public class AddEmptyColumn {

  public static void runAddEmptyColumn() {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableId = "MY_TABLE_NAME";
    String newColumnName = "NEW_COLUMN_NAME";
    addEmptyColumn(newColumnName, datasetName, tableId);
  }

  public static void addEmptyColumn(String newColumnName, String datasetName, String tableId) {
    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();

      Table table = bigquery.getTable(datasetName, tableId);
      Schema schema = table.getDefinition().getSchema();
      FieldList fields = schema.getFields();

      // Create the new field/column
      Field newField = Field.of(newColumnName, LegacySQLTypeName.STRING);

      // Create a new schema adding the current fields, plus the new one
      List<Field> fieldList = new ArrayList<Field>();
      fields.forEach(fieldList::add);
      fieldList.add(newField);
      Schema newSchema = Schema.of(fieldList);

      // Update the table with the new schema
      Table updatedTable =
          table.toBuilder().setDefinition(StandardTableDefinition.of(newSchema)).build();
      updatedTable.update();
      System.out.println("Empty column successfully added to table");
    } catch (BigQueryException e) {
      System.out.println("Empty column was not added. \n" + e.toString());
    }
  }
}

Node.js

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

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


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

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

向 Table.schema 的副本附加一个新的 SchemaField 对象，然后将 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 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 列添加新的嵌套列。添加新嵌套列的过程与添加新列的过程相似。

控制台

Google Cloud 控制台不支持向现有 RECORD 列添加新的嵌套字段。

SQL

不支持使用 SQL DDL 语句向现有 RECORD 列添加新的嵌套字段。

bq

发出 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

    请替换以下内容：

    • PROJECT_ID：您的项目 ID。
    • DATASET：要更新的表所属数据集的名称。
    • TABLE：要更新的表的名称。
    • SCHEMA：写入本地机器的架构定义文件。

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

    bq show \
--schema \
--format=prettyjson \
mydataset.mytable > /tmp/myschema.json

  2. 在文本编辑器中打开架构文件。架构应如下所示。在本例中，column3 是嵌套的重复列。嵌套列为 nested1 和 nested2。fields 数组列出了 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 方法。

覆盖或附加数据时添加列

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

在加载附加作业中添加列

在加载作业中向表附加数据时，您可以向表添加列。新架构由以下其中一种方式确定：

  • 自动检测（针对 CSV 和 JSON 文件）
  • JSON 架构文件中指定的架构（针对 CSV 和 JSON 文件）
  • Avro、ORC、Parquet 和 Datastore 导出文件的自描述源数据

如果在 JSON 文件中指定架构，则必须在其中定义新列。如果缺少新列定义，则系统会在您尝试附加数据时返回错误。

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

如需在加载作业期间向表附加数据时添加新列，请使用以下选项之一：

bq

使用 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 文件设置该位置的默认值。
  • FORMAT：架构的格式。NEWLINE_DELIMITED_JSON、CSV、AVRO、PARQUET、ORC 或 DATASTORE_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

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

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

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)
	}
	defer client.Close()

	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
}

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.FormatOptions;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.LegacySQLTypeName;
import com.google.cloud.bigquery.LoadJobConfiguration;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.TableId;
import com.google.common.collect.ImmutableList;
import java.util.UUID;

public class AddColumnLoadAppend {

  public static void runAddColumnLoadAppend() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    String sourceUri = "/path/to/file.csv";
    addColumnLoadAppend(datasetName, tableName, sourceUri);
  }

  public static void addColumnLoadAppend(String datasetName, String tableName, String sourceUri)
      throws Exception {
    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);

      // Add a new column to a BigQuery table while appending rows via a load job.
      // 'REQUIRED' fields cannot  be added to an existing schema, so the additional column must be
      // 'NULLABLE'.
      Schema newSchema =
          Schema.of(
              Field.newBuilder("name", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.REQUIRED)
                  .build(),
              // Adding below additional column during the load job
              Field.newBuilder("post_abbr", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build());

      LoadJobConfiguration loadJobConfig =
          LoadJobConfiguration.builder(tableId, sourceUri)
              .setFormatOptions(FormatOptions.csv())
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setSchema(newSchema)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_ADDITION))
              .build();

      // Create a job ID so that we can safely retry.
      JobId jobId = JobId.of(UUID.randomUUID().toString());
      Job loadJob = bigquery.create(JobInfo.newBuilder(loadJobConfig).setJobId(jobId).build());

      // Load data from a GCS parquet file into the table
      // Blocks until this load table job completes its execution, either failing or succeeding.
      Job completedJob = loadJob.waitFor();

      // Check for errors
      if (completedJob == null) {
        throw new Exception("Job not executed since it no longer exists.");
      } else if (completedJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(
            "BigQuery was unable to load into the table due to an error: \n"
                + loadJob.getStatus().getError());
      }
      System.out.println("Column successfully added during load append job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Column not added during load append \n" + e.toString());
    }
  }
}

Node.js

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

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

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

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

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, '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

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

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

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

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

如果要附加的表在非默认项目的数据集中，请按以下格式将相应项目 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：采用 GoogleSQL 语法的查询。

示例：

在默认项目中输入以下命令查询 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 属性指定 GoogleSQL 查询。

Go

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

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

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)
	}
	defer client.Close()

	// 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
}

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.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;

public class RelaxTableQuery {

  public static void runRelaxTableQuery() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    relaxTableQuery(projectId, datasetName, tableName);
  }

  // To relax all columns in a destination table when you append data to it during a query job
  public static void relaxTableQuery(String projectId, String datasetName, String tableName)
      throws Exception {
    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);

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query = "SELECT word FROM " + sourceTable + " WHERE word like '%is%'";

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Use standard SQL syntax for queries.
              // See: https://cloud.google.com/bigquery/sql-reference/
              .setUseLegacySql(false)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setDestinationTable(tableId)
              .build();

      Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).build());

      queryJob = queryJob.waitFor();

      // Check for errors
      if (queryJob == null) {
        throw new Exception("Job no longer exists");
      } else if (queryJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(queryJob.getStatus().getError().toString());
      }

      // Get the results.
      TableResult results = queryJob.getQueryResults();

      // Print all pages of the results.
      results
          .iterateAll()
          .forEach(
              rows -> {
                rows.forEach(row -> System.out.println("row: " + row.toString()));
              });

      System.out.println("Successfully relaxed all columns in destination table during query job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Columns not relaxed during query job \n" + e.toString());
    }
  }
}

Node.js

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

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

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

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

from google.cloud import bigquery

# 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,
    schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_ADDITION],
    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)))

更改列的名称

如需重命名表中的列，请使用 ALTER TABLE RENAME COLUMN DDL 语句。 以下示例会将 mytable 中的 old_name 列重命名为 new_name：

ALTER TABLE mydataset.mytable
  RENAME COLUMN old_name TO new_name;

如需详细了解 ALTER TABLE RENAME COLUMN 语句，请参阅 DDL 详细信息。

更改列的数据类型

Google Cloud 控制台、bq 命令行工具和 BigQuery API 不支持更改列的数据类型。如果您尝试通过应用一种架构来为列指定新的数据类型，从而更新表，则系统会返回错误。

使用 DDL 语句更改列的数据类型

您可以使用 GoogleSQL 对列的数据类型进行某些更改。如需了解详情并查看支持的数据类型转换的完整列表，请参阅 ALTER COLUMN SET DATA TYPE DDL 语句。

以下示例创建一个表，表中包含 INT64 类型的列，然后将类型更新为 NUMERIC：

CREATE TABLE mydataset.mytable(c1 INT64);

ALTER TABLE mydataset.mytable
ALTER COLUMN c1 SET DATA TYPE NUMERIC;

转换列的数据类型

要将列的数据类型更改为可转换类型，请使用 SQL 查询来选择表数据，然后转换相关数据并覆盖表。对于非常大的表，不建议进行类型转换和覆盖，因为需要进行全表扫描。

以下示例展示了一个 SQL 查询，该查询会选择 mydataset.mytable 中 column_two 和 column_three 的所有数据，并将 column_one 的类型从 DATE 转换为 STRING。查询结果用于覆盖现有表。覆盖后的表会将 column_one 存储为 STRING 数据类型。

在使用 CAST 时，如果 BigQuery 无法执行类型转换，查询就会失败。如需详细了解 GoogleSQL 中的类型转换规则，请参阅类型转换。

控制台

  1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

    转到 BigQuery

  2. 在查询编辑器中输入以下查询，选择 mydataset.mytable 中 column_two 和 column_three 的所有数据，并将 column_one 的类型从 DATE 转换为 STRING。该查询使用别名对 column_one 进行类型转换，并且名称保持相同。mydataset.mytable 属于默认项目。

    SELECT
 column_two,
 column_three,
 CAST(column_one AS STRING) AS column_one
FROM
 mydataset.mytable;

  3. 点击更多，然后选择查询设置。

  4. 在目标部分，执行以下操作：

    1. 选择为查询结果设置目标表。

    2. 对于项目名称，将值保留设置为默认项目， 即包含 mydataset.mytable 的项目。

    3. 对于数据集，选择 mydataset。

    4. 在表 ID 字段中，输入 mytable。

    5. 对于目标表的写入设置，选择覆盖表。此选项会使用查询结果覆盖 mytable。

  5. （可选）选择数据的位置。

  6. 要更新设置，请点击保存。

  7. 点击 运行。

    查询作业完成后，column_one 的数据类型为 STRING。

bq

输入以下 bq query 命令，选择 mydataset.mytable 中 column_two 和 column_three 的所有数据，并将 column_one 的类型从 DATE 转换为 STRING。该查询使用别名对 column_one 进行类型转换，并且名称保持相同。mydataset.mytable 属于默认项目。

使用 --destination_table 标志将查询结果写入 mydataset.mytable，并使用 --replace 标志以覆盖 mytable。指定 use_legacy_sql=false 标志以使用 GoogleSQL 语法。

（可选）添加 --location 标志并将其值设置为您的位置。

bq query \
    --destination_table mydataset.mytable \
    --replace \
    --use_legacy_sql=false \
'SELECT
  column_two,
  column_three,
  CAST(column_one AS STRING) AS column_one
FROM
  mydataset.mytable'

API

如需选择 mydataset.mytable 中 column_two 和 column_three 的所有数据，并将 column_one 的类型从 DATE 转换为 STRING，请调用 jobs.insert 方法并配置一项 query 作业。（可选）在 jobReference 部分的 location 属性中指定您的位置。

查询作业中使用的 SQL 查询为：SELECT column_two, column_three, CAST(column_one AS STRING) AS column_one FROM mydataset.mytable。该查询使用别名来对 column_one 执行类型转换，并且名称保持相同。

如需使用查询结果覆盖 mytable，请将 mydataset.mytable 添加到 configuration.query.destinationTable 属性中，并在 configuration.query.writeDisposition 属性中指定 WRITE_TRUNCATE。

更改列的模式

对列的模式的修改仅支持将其从 REQUIRED 更改为 NULLABLE。将列的模式从 REQUIRED 更改为 NULLABLE 也称为列放宽。此外，您还可以在加载数据以覆盖现有表时，或将数据附加到现有表时，放宽列。 您无法将列的模式从 NULLABLE 更改为 REQUIRED。

在现有表中使列可以为 NULLABLE

要将列的模式从 REQUIRED 更改为 NULLABLE，请选择以下选项之一：

控制台

  1. 转到 BigQuery 页面。

    转到 BigQuery

  2. 在探索器面板中，展开您的项目和数据集，然后选择表。

  3. 在详细信息面板中，点击架构标签页。

  4. 点击修改架构。您可能需要滚动才能看到此按钮。

  5. 在当前架构页面中，找到您要更改的字段。

  6. 在该字段的模式下拉列表中，选择 NULLABLE。

  7. 要更新设置，请点击保存。

SQL

使用 ALTER COLUMN DROP NOT NULL DDL 语句. 以下示例将列 mycolumn 的模式从 REQUIRED 更改为 NULLABLE：

  1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

    转到 BigQuery

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

    ALTER TABLE mydataset.mytable
ALTER COLUMN mycolumn
DROP NOT NULL;

  3. 点击 运行。

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

bq

  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

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

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

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)
	}
	defer client.Close()

	// 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
}

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.LegacySQLTypeName;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.Table;

public class RelaxColumnMode {

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

  public static void relaxColumnMode(String datasetName, String tableId) {
    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();

      Table table = bigquery.getTable(datasetName, tableId);

      // Create new relaxed schema based on the existing table schema
      Schema relaxedSchema =
          Schema.of(
              // The only supported modification you can make to a column's mode is changing it from
              // REQUIRED to NULLABLE
              // Changing a column's mode from REQUIRED to NULLABLE is also called column relaxation
              // INFO: LegacySQLTypeName will be updated to StandardSQLTypeName in release 1.103.0
              Field.newBuilder("word", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("word_count", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("corpus", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build(),
              Field.newBuilder("corpus_date", LegacySQLTypeName.STRING)
                  .setMode(Field.Mode.NULLABLE)
                  .build());

      // Update the table with the new schema
      Table updatedTable =
          table.toBuilder().setDefinition(StandardTableDefinition.of(relaxedSchema)).build();
      updatedTable.update();
      System.out.println("Table schema successfully relaxed.");
    } catch (BigQueryException e) {
      System.out.println("Table schema not relaxed \n" + e.toString());
    }
  }
}

Node.js

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

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

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

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

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

from google.cloud import bigquery

client = bigquery.Client()

# TODO(dev): Change table_id to full name of the table you want to create.
table_id = "your-project.your_dataset.your_table"

table = client.get_table(table_id)
new_schema = []
for field in table.schema:
    if field.mode != "REQUIRED":
        new_schema.append(field)
    else:
        # SchemaField properties cannot be edited after initialization.
        # To make changes, construct new SchemaField objects.
        new_field = field.to_api_repr()
        new_field["mode"] = "NULLABLE"
        relaxed_field = bigquery.SchemaField.from_api_repr(new_field)
        new_schema.append(relaxed_field)

table.schema = new_schema
table = client.update_table(table, ["schema"])

print(f"Updated {table_id} schema: {table.schema}.")

通过附加的加载作业使列可以为 NULLABLE

您可以在向加载作业中的表附加数据时放宽列的模式。 根据文件类型选择以下其中一项：

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

如需在加载作业期间向表附加数据时将列从 REQUIRED 放宽为 NULLABLE，请选择以下选项之一：

控制台

Google Cloud 控制台不支持放宽列的模式。

bq

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

如果要附加的数据采用的是 CSV 格式或以换行符分隔的 JSON 格式，请在本地 JSON 架构文件中指定已放宽模式的列，或使用 --autodetect 标志通过架构检测功能来发现源数据中已放宽模式的列。

系统可以根据 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 文件设置该位置的默认值。
  • FORMAT：NEWLINE_DELIMITED_JSON、CSV、PARQUET、ORC 或 AVRO。DATASTORE_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.json。mydataset 属于默认项目。

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

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

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

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)
	}
	defer client.Close()

	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
}

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.CsvOptions;
import com.google.cloud.bigquery.Field;
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.Table;
import com.google.cloud.bigquery.TableId;
import com.google.common.collect.ImmutableList;

// Sample to append relax column in a table.
public class RelaxColumnLoadAppend {

  public static void main(String[] args) {
    // 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.csv";
    relaxColumnLoadAppend(datasetName, tableName, sourceUri);
  }

  public static void relaxColumnLoadAppend(String datasetName, String tableName, String sourceUri) {
    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();

      // Retrieve destination table reference
      Table table = bigquery.getTable(TableId.of(datasetName, tableName));

      // column as a 'REQUIRED' field.
      Field name =
          Field.newBuilder("name", StandardSQLTypeName.STRING).setMode(Field.Mode.REQUIRED).build();
      Field postAbbr =
          Field.newBuilder("post_abbr", StandardSQLTypeName.STRING)
              .setMode(Field.Mode.REQUIRED)
              .build();
      Schema schema = Schema.of(name, postAbbr);

      // Skip header row in the file.
      CsvOptions csvOptions = CsvOptions.newBuilder().setSkipLeadingRows(1).build();

      // Set job options
      LoadJobConfiguration loadConfig =
          LoadJobConfiguration.newBuilder(table.getTableId(), sourceUri)
              .setSchema(schema)
              .setFormatOptions(csvOptions)
              .setSchemaUpdateOptions(
                  ImmutableList.of(JobInfo.SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(JobInfo.WriteDisposition.WRITE_APPEND)
              .build();

      // Create a load job and wait for it to complete.
      Job job = bigquery.create(JobInfo.of(loadConfig));
      job = job.waitFor();
      // Check the job's status for errors
      if (job.isDone() && job.getStatus().getError() == null) {
        System.out.println("Relax column append 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());
    }
  }
}

Node.js

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

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

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

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

# from google.cloud import bigquery
# client = bigquery.Client()
# project = client.project
# dataset_ref = bigquery.DatasetReference(project, '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))

通过附加作业使所有列可以为 NULLABLE

您可以在将查询结果附加到表时放宽表中的所有列。您可以通过将 --schema_update_option 标志设置为 ALLOW_FIELD_RELAXATION 来放宽目标表中的所有必需字段。您无法使用查询附加来放宽目标表中的单个列。如需使用加载附加作业放宽单个列，请参阅通过附加作业使列可以为 NULLABLE。

如需在将查询结果附加到目标表时放宽所有列，请选择以下选项之一：

控制台

Google Cloud 控制台不支持放宽列的模式。

bq

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

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

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

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

如果要附加的表在非默认项目的数据集中，请按以下格式将相应项目 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：采用 GoogleSQL 语法的查询。

示例：

在默认项目中输入以下命令查询 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 属性指定 GoogleSQL 查询。

Go

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

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

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)
	}
	defer client.Close()

	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
}

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.Job;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.JobInfo.SchemaUpdateOption;
import com.google.cloud.bigquery.JobInfo.WriteDisposition;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableResult;
import com.google.common.collect.ImmutableList;

public class RelaxTableQuery {

  public static void runRelaxTableQuery() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    relaxTableQuery(projectId, datasetName, tableName);
  }

  // To relax all columns in a destination table when you append data to it during a query job
  public static void relaxTableQuery(String projectId, String datasetName, String tableName)
      throws Exception {
    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);

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query = "SELECT word FROM " + sourceTable + " WHERE word like '%is%'";

      QueryJobConfiguration queryConfig =
          QueryJobConfiguration.newBuilder(query)
              // Use standard SQL syntax for queries.
              // See: https://cloud.google.com/bigquery/sql-reference/
              .setUseLegacySql(false)
              .setSchemaUpdateOptions(ImmutableList.of(SchemaUpdateOption.ALLOW_FIELD_RELAXATION))
              .setWriteDisposition(WriteDisposition.WRITE_APPEND)
              .setDestinationTable(tableId)
              .build();

      Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).build());

      queryJob = queryJob.waitFor();

      // Check for errors
      if (queryJob == null) {
        throw new Exception("Job no longer exists");
      } else if (queryJob.getStatus().getError() != null) {
        // You can also look at queryJob.getStatus().getExecutionErrors() for all
        // errors, not just the latest one.
        throw new Exception(queryJob.getStatus().getError().toString());
      }

      // Get the results.
      TableResult results = queryJob.getQueryResults();

      // Print all pages of the results.
      results
          .iterateAll()
          .forEach(
              rows -> {
                rows.forEach(row -> System.out.println("row: " + row.toString()));
              });

      System.out.println("Successfully relaxed all columns in destination table during query job");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Columns not relaxed during query job \n" + e.toString());
    }
  }
}

Python

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

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

from google.cloud import bigquery

# 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,
    schema_update_options=[bigquery.SchemaUpdateOption.ALLOW_FIELD_RELAXATION],
    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))

更改列的默认值

如需更改列的默认值，请选择以下选项之一：

控制台

  1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

    转到 BigQuery

  2. 在探索器面板中，展开您的项目和数据集，然后选择表。

  3. 在详细信息面板中，点击架构标签页。

  4. 点击修改架构。您可能需要滚动才能看到此按钮。

  5. 在当前架构页面中，找到您要更改的顶级字段。

  6. 输入该字段的默认值。

  7. 点击保存。

SQL

使用 ALTER COLUMN SET DEFAULT DDL 语句.

  1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

    转到 BigQuery

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

    ALTER TABLE mydataset.mytable
ALTER COLUMN column_name SET DEFAULT default_expression;

  3. 点击 运行。

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

删除列

您可以使用 ALTER TABLE DROP COLUMN DDL 语句从现有表中删除列。

该语句不会立即释放与丢弃的列关联的存储空间。如需详细了解丢弃列对存储空间的影响，请参阅 ALTER TABLE DROP COLUMN 语句详细信息。您可以通过以下两种方式立即回收存储空间：

  • 使用 SELECT * EXCEPT 查询覆盖表。
  • 将数据导出到 Cloud Storage，删除不需要的列，然后将数据加载到具有正确架构的新表中。