手动更改表架构

本文档介绍了如何手动更改现有 BigQuery 表的架构定义。许多架构更改在 BigQuery 内均无原生支持，需要手动解决方法。这类不受支持的架构修改包括：

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

如需了解 BigQuery 中支持的架构更改，请参阅修改表架构。

更改列的名称

GCP Console、经典版 BigQuery 网页界面、命令行工具或 API 均不支持对列进行重命名。如果您尝试使用重命名的列更新表架构，系统会返回以下错误：BigQuery error in update operation: Provided Schema does not match Table [PROJECT_ID]:[DATASET].[TABLE]。.

您可以通过两种方法手动重命名一个列：

  • 使用 SQL 查询 - 如果您更在意简单性和易用性，并且不太在意费用，则可以选择此选项。
  • 重新创建表 - 如果您更在意费用，并且不太在意简单性和易用性，则可以选择此选项。

选项 1：使用查询

要使用 SQL 查询更改列名，请选择表中的所有列，并为要重命名的列添加别名。您可以使用查询结果覆盖现有表，也可以创建新的目标表。如果使用新名称为列添加别名，则此别名必须遵循 BigQuery 的列名规则。

优点

  • 使用查询将数据写入新目标表时，系统会保留原始数据。
  • 如果您使用查询作业覆盖原始表，则需要为一个表（而不是两个表）支付存储费用，但您的原始数据会丢失。

缺点

  • 如果要使用查询重命名列，您需要扫描整个表 - 如果这个表非常大，则查询费用可能十分可观。
  • 如果您要将查询结果写入新的目标表，则需要同时为旧表和新表支付存储费用（除非您删除了旧表）。

选项 2：导出数据并将其加载到新表中

您还可以通过以下方式对某列进行重命名：将表数据导出到 Cloud Storage，然后将该数据连同包含正确列名的架构定义一起加载到新表中。您还可以使用加载作业覆盖现有表。

优点

  • 您不需要为导出作业或加载作业支付费用。目前，BigQuery 的加载和导出作业是免费的。
  • 如果您使用加载作业覆盖原始表，则需要为一个表（而不是两个表）支付存储费用，但您的原始数据会丢失。

缺点

  • 如果您要将数据加载到新表，则需要同时为原始表和新表支付存储费用（除非您删除了旧表）。
  • 将导出的数据存储在 Cloud Storage 中会产生费用。

列别名示例

以下示例展示了一个标准 SQL 查询，该查询会选择 mytable 中的所有数据（需要重命名的两列除外）。系统使用一个别名为这两列生成新名称。column_one 重命名为 newcolumn_one，column_two 重命名为 newcolumn_two。查询结果用于覆盖现有表。

Console

  1. 在 Console 的 BigQuery 网页界面中，选择查询编辑器。

  2. 在查询编辑器中输入以下查询，以选择 mydataset.mytable 中的所有数据（需要重命名的两列除外）。mydataset.mytable 属于默认项目。该查询使用别名将 column_one 的名称更改为 newcolumn_one，并将 column_two 的名称更改为 newcolumn_two。

       #standardSQL
   SELECT
     * EXCEPT(column_one, column_two),
     column_one AS newcolumn_one, column_two AS newcolumn_two
   FROM
     mydataset.mytable

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

    查询设置

  4. 在目标位置部分，勾选为查询结果设置目标表。

    设置目标表

  5. 在以下字段中，执行相应的操作：

    1. 对于项目名称，将值保留设置为默认项目。这也是包含 mydataset.mytable 的项目。

    2. 对于数据集名称，选择 mydataset。

    3. 在表名称字段中，输入 mytable。

    4. 点击确定。

  6. 在目标表的写入设置部分中，对于写入偏好设置，选择覆盖表。这会使用查询结果覆盖 mytable。

    覆盖表

  7. 对于处理位置，打开处理位置下拉菜单并选择数据的位置。如果数据位于 US 或 EU 多区域位置，则您可以将处理位置保留设置为“自动选择”。当您的数据位于 US 或 EU 中时，系统会自动检测处理位置。

  8. 点击保存以更新设置，然后在查询编辑器中点击运行。查询作业完成后，mytable 中的列使用的就是新名称了。

经典版界面

  1. 在经典版 BigQuery 网页界面中，点击 Compose Query。

  2. 在 New Query 框中输入以下查询，以选择 mydataset.mytable 中的所有数据（需要重命名的两列除外）。mydataset.mytable 属于默认项目。该查询使用别名将 column_one 的名称更改为 newcolumn_one，并将 column_two 的名称更改为 newcolumn_two。

       #standardSQL
   SELECT
     * EXCEPT(column_one, column_two),
     column_one AS newcolumn_one, column_two AS newcolumn_two
   FROM
     mydataset.mytable

  3. 点击 Show Options。

  4. 在目标表 部分，点击选择表。

  5. 在 Select Destination Table 对话框中，执行以下操作：

    1. 在 Project 部分中，将设置值保留为默认项目。这也是包含 mydataset.mytable 的项目。

    2. 对于 Dataset，选择 mydataset。

    3. 在 Table ID 字段中，输入 mytable。

    4. 点击 OK。

  6. 在 Destination Table 部分的 Write Preference 中，选择 Overwrite table。这会使用查询结果覆盖 mytable。

  7. 对于 Processing Location，点击 Unspecified 并选择数据的位置。如果数据位于 US 或 EU 多区域位置，则可以将处理位置设置为“unspecified”。当您的数据位于 US 或 EU 中时，系统会自动检测处理位置。

  8. 点击 Run Query。查询作业完成后，mytable 中的列使用的就是新名称了。

CLI

输入以下 bq query 命令，选择 mydataset.mytable 中的所有数据（需要重命名的两列除外）。 mydataset.mytable 位于默认项目中。该查询使用别名将 column_one 的名称更改为 newcolumn_one，并将 column_two 的名称更改为 newcolumn_two。

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

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

bq --location=[LOCATION] query --destination_table mydataset.mytable --replace --use_legacy_sql=false 'SELECT * EXCEPT(column_one, column_two), column_one AS newcolumn_one, column_two AS newcolumn_two FROM mydataset.mytable'

API

要将 column_one 的名称更改为 newcolumn_one，并将 column_two 的名称更改为 newcolumn_two，请调用 jobs.insert 方法并配置一个查询作业。在 jobReference 部分的 location 属性中指定您的区域。

查询作业中使用的 SQL 查询是：SELECT * EXCEPT(column_one, column_two), column_one AS newcolumn_one, column_two AS newcolumn_two FROM mydataset.mytable。此查询会选择 mytable 中的所有数据（需要重命名的两列除外）。系统会使用一个别名为这两列生成新名称。

要使用查询结果覆盖 mytable，请将 mydataset.mytable 添加到 configuration.query.destinationTable 属性中，并在 configuration.query.writeDisposition 属性中指定 WRITE_TRUNCATE。要指定新的目标表，请在 configuration.query.destinationTable 属性中输入表名称。

更改列的数据类型

GCP Console、经典版 BigQuery 网页界面、命令行工具或 API 均不支持更改列的数据类型。如果您尝试应用一种架构来为列指定新的数据类型，以此更新表，则系统会返回以下错误：BigQuery error in update operation: Provided Schema does not match Table [PROJECT_ID]:[DATASET].[TABLE]。.

可以通过以下两种方法手动更改列的数据类型：

  • 使用 SQL 查询 - 如果您更在意简单性和易用性，并且不太在意费用，则可以选择此选项。
  • 重新创建表 - 如果您更在意费用，并且不太在意简单性和易用性，则可以选择此选项。

选项 1：使用查询

使用 SQL 查询选择所有表数据，并对相关列执行类型转换，将其转为不同的数据类型。您可以使用查询结果覆盖表，也可以新建一个目标表。

优点

  • 使用查询将数据写入新目标表时，系统会保留原始数据。
  • 如果您使用查询作业覆盖原始表，则需要为一个表（而不是两个表）支付存储费用，但您的原始数据会丢失。

缺点

  • 如果要使用查询更改列的数据类型，您需要扫描整个表 - 如果这个表非常大，则查询费用可能十分可观。
  • 如果您要将查询结果写入新的目标表，则需要同时为旧表和新表支付存储费用（除非您删除了旧表）。

选项 2：导出数据并将其加载到新表中

您还可以通过以下方式更改某列的数据类型：将表数据导出到 Cloud Storage，然后将该数据连同用于为该列指定正确数据类型的架构定义一起加载到新表中。您还可以使用加载作业覆盖现有表。

优点

  • 您不需要为导出作业或加载作业支付费用。目前，BigQuery 的加载和导出作业是免费的。
  • 如果您使用加载作业覆盖原始表，则需要为一个表（而不是两个表）支付存储费用，但您的原始数据会丢失。

缺点

  • 如果您要将数据加载到新表，则需要同时为原始表和新表支付存储费用（除非您删除了旧表）。
  • 将导出的数据存储在 Cloud Storage 中会产生费用。

CAST 示例

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

在使用 CAST 时，如果 BigQuery 无法执行类型转换，查询就会失败。如需详细了解标准 SQL 中的类型转换规则，请参阅函数和运算符参考文档中的类型转换。

Console

  1. 在 Console 的 BigQuery 网页界面中，选择查询编辑器。

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

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

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

  4. 在目标位置部分，勾选为查询结果设置目标表。

  5. 在以下字段中，执行相应的操作：

    1. 对于项目名称，将值保留设置为默认项目。这也是包含 mydataset.mytable 的项目。

    2. 对于数据集名称，选择 mydataset。

    3. 在表名称字段中，输入 mytable。

    4. 点击确定。

  6. 在目标表的写入设置部分中，对于写入偏好设置，选择覆盖表。这会使用查询结果覆盖 mytable。

  7. 对于处理位置，点击自动选择并选择数据的位置。如果数据位于 US 或 EU 多区域位置，则您可以将处理位置设置为“自动选择”。当您的数据位于 US 或 EU 中时，系统会自动检测处理位置。

  8. 点击保存以更新设置，然后在查询编辑器中点击运行。查询作业完成后，column_one 的数据类型为 STRING。

经典版界面

  1. 在经典版 BigQuery 网页界面中，点击 Compose Query。

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

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

  3. 点击 Show Options。

  4. 在目标表 部分，点击选择表。

  5. 在 Select Destination Table 对话框中，执行以下操作：

    1. 在 Project 部分中，将设置值保留为默认项目。这也是包含 mydataset.mytable 的项目。

    2. 对于 Dataset，选择 mydataset。

    3. 在 Table ID 字段中，输入 mytable。

    4. 点击 OK。

  6. 在 Destination Table 部分的 Write Preference 中，选择 Overwrite table。这会使用查询结果覆盖 mytable。

  7. 对于 Processing Location，点击 Unspecified 并选择数据的位置。如果数据位于 US 或 EU 多区域位置，则可以将处理位置设置为“unspecified”。当您的数据位于 US 或 EU 中时，系统会自动检测处理位置。

  8. 点击 Run Query。查询作业完成后，column_one 的数据类型为 STRING。

CLI

输入以下 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 标志以使用标准 SQL 语法。

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

bq --location=[LOCATION] 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 方法并配置一个查询作业。在 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 也称为列放宽。如需了解如何将 REQUIRED 列放宽为 NULLABLE，请参阅放宽列的模式。

如果您尝试将系统不支持的更改应用于列模式，则会返回如下错误。此示例尝试将列模式从 NULLABLE 更改为 REPEATED：BigQuery error in update operation: Provided Schema does not match Table [PROJECT_ID]:[DATASET].[TABLE]. Field [FIELD] has changed mode from NULLABLE to REPEATED。

导出数据并将其加载到新表中

您可以通过以下方式手动更改列的模式：将表数据导出到 Cloud Storage，然后将该数据连同用于为列指定正确模式的架构定义一起加载到新表中。您还可以使用加载作业覆盖现有表。

优点

  • 您不需要为导出作业或加载作业支付费用。目前，BigQuery 的加载和导出作业是免费的。
  • 如果您使用加载作业覆盖原始表，则需要为一个表（而不是两个表）支付存储费用，但您的原始数据会丢失。

缺点

  • 如果您要将数据加载到新表，则需要同时为原始表和新表支付存储费用（除非您删除了旧表）。
  • 将导出的数据存储在 Cloud Storage 中会产生费用。

从表架构中删除列

GCP Console、经典版 BigQuery 网页界面、命令行工具或 API 均不支持从现有表的架构中删除列。如果您尝试应用一种架构来移除列，以此更新表，系统会返回以下错误：BigQuery error in update operation: Provided Schema does not match Table [PROJECT_ID]:[DATASET].[TABLE]。.

可以通过以下两种方式手动删除列：

  • 使用 SQL 查询 - 如果您更在意简单性和易用性，并且不太在意费用，则可以选择此选项。
  • 重新创建表 - 如果您更在意费用，并且不太在意简单性和易用性，则可以选择此选项。

选项 1：使用查询

使用 SELECT * EXCEPT 查询排除您要移除的一列或多列，并使用查询结果覆盖表或创建一个新的目标表。

优点

  • 使用查询将数据写入新目标表时，系统会保留原始数据。
  • 如果您使用查询作业覆盖原始表，则需要为一个表（而不是两个表）支付存储费用，但您的原始数据会丢失。

缺点

  • 如果要使用查询删除一列，您需要扫描所有列中的数据（要移除的列除外）。如果这个表非常大，则查询费用可能十分可观。
  • 如果您要将查询结果写入新的目标表，则需要同时为旧表和新表支付存储费用（除非您删除了旧表）。

选项 2：导出数据并将其加载到新表中

您还可以通过以下方式移除某列：将表数据导出到 Cloud Storage，然后删除与要移除的一列或多列相对应的数据，最后将该数据连同不包含已移除列的架构定义一起加载到新表中。您还可以使用加载作业覆盖现有表。

优点

  • 您不需要为导出作业或加载作业支付费用。目前，BigQuery 的加载和导出作业是免费的。
  • 如果您使用加载作业覆盖原始表，则需要为一个表（而不是两个表）支付存储费用，但您的原始数据会丢失。

缺点

  • 如果您要将数据加载到新表，则需要同时为原始表和新表支付存储费用（除非您删除了旧表）。
  • 将导出的数据存储在 Cloud Storage 中会产生费用。

SELECT * EXCEPT 示例

以下示例展示了一个标准 SQL 查询，该查询从 mydataset.mytable 中选择除 column_two 之外的所有数据。查询结果用于覆盖现有表。

Console

  1. 在 Console 的 BigQuery 网页界面中，选择查询编辑器。

  2. 在查询编辑器中输入以下查询，以选择 mydataset.mytable 中的所有数据（column_two 除外）。mydataset.mytable 属于默认项目。

       #standardSQL
   SELECT
     * EXCEPT(column_two)
   FROM
     mydataset.mytable

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

  4. 在目标位置部分，勾选为查询结果设置目标表。

  5. 在以下字段中，执行相应的操作：

    1. 对于项目名称，将值保留设置为默认项目。这也是包含 mydataset.mytable 的项目。

    2. 对于数据集名称，选择 mydataset。

    3. 在表名称字段中，输入 mytable。

    4. 点击确定。

  6. 在目标表的写入设置部分中，对于写入偏好设置，选择覆盖表。这会使用查询结果覆盖 mytable。

  7. 对于处理位置，点击自动选择并选择数据的位置。如果数据位于 US 或 EU 多区域位置，则您可以将处理位置设置为“自动选择”。当您的数据位于 US 或 EU 中时，系统会自动检测处理位置。

  8. 点击保存以更新设置，然后在查询编辑器中点击运行。查询作业完成后，表就会包括除 column_two 之外的所有列。

经典版界面

  1. 在经典版 BigQuery 网页界面中，点击 Compose Query。

  2. 在 New Query 框中输入以下查询，以选择 mydataset.mytable 中的所有数据（column_two 除外）。mydataset.mytable 属于默认项目。

       #standardSQL
   SELECT
     * EXCEPT(column_two)
   FROM
     mydataset.mytable

  3. 点击 Show Options。

  4. 在目标表 部分，点击选择表。

  5. 在 Select Destination Table 对话框中，执行以下操作：

    1. 在 Project 部分中，将设置值保留为默认项目。这也是包含 mydataset.mytable 的项目。

    2. 对于 Dataset，选择 mydataset。

    3. 在 Table ID 字段中，输入 mytable。

    4. 点击 OK。

  6. 在 Destination Table 部分的 Write Preference 中，选择 Overwrite table。这会使用查询结果覆盖 mytable。

  7. 对于 Processing Location，点击 Unspecified 并选择数据的位置。如果数据位于 US 或 EU 多区域位置，则可以将处理位置设置为“unspecified”。当您的数据位于 US 或 EU 中时，系统会自动检测处理位置。

  8. 点击 Run Query。查询作业完成后，表就会包括除 column_two 之外的所有列。

CLI

输入以下 bq query 命令，选择 mydataset.mytable 中的所有数据（column_two 除外）。mydataset.mytable 在默认项目中。使用 --destination_table 标志将查询结果写入 mydataset.mytable，并指定 --replace 标志以覆盖 mytable。指定 use_legacy_sql=false 标志以使用标准 SQL 语法。

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

bq --location=[LOCATION] query --destination_table mydataset.mytable --replace --use_legacy_sql=false 'SELECT * EXCEPT(column_two) FROM mydataset.mytable'

API

要选择 mydataset.mytable 中除 column_two 之外的所有数据，请调用 jobs.insert 方法并配置一个查询作业。在 jobReference 部分的 location 属性中指定您的区域。

查询作业中使用的 SQL 查询为：SELECT * EXCEPT(column_two) FROM mydataset.mytable。

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

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

发送以下问题的反馈：

此网页
文档反馈
BigQuery
产品反馈
需要帮助？请访问我们的支持页面。