手动更改表架构

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

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

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

更改列的名称

控制台、bq 命令行工具或 API 不支持重命名列。如果您尝试使用重命名的列更新表架构，系统会返回以下错误：BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table。

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

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

方法 1：使用查询

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

优势

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

缺点

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

列别名示例

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

控制台

  1. 在控制台中，选择查询编辑器。

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

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

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

  4. 在目标部分中执行如下设置：

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

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

    3. 对于表 ID，输入 mytable。

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

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

bq

输入以下 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 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 方法并配置一项 query 作业。（可选）在 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 属性中输入表名称。

方法 2：导出数据并将其加载到新表中

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

优势

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

缺点

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

更改列的数据类型

控制台、bq 命令行工具和 API 不支持更改列的数据类型。如果您尝试应用一种架构来为列指定新的数据类型，从而更新表，则系统会返回以下错误：BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

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

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

方法 1：使用查询

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

优势

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

缺点

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

CAST 示例

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

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

控制台

  1. 在控制台中，点击编写新查询。

  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. 在目标位置部分，勾选为查询结果设置目标表。

  5. 在以下字段中：

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

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

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

    4. 点击确定。

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

  7. （可选）对于处理位置，点击自动选择并选择数据的位置。

  8. 点击保存以更新设置，然后在查询编辑器中点击运行。查询作业完成后，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 标志可使用标准 SQL 语法。

（可选）提供 --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。

方法 2：导出数据并将其加载到新表中

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

优势

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

缺点

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

更改列模式

当前对列的模式的修改仅支持将其从 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 中会产生费用。

从表架构中删除列

控制台、bq 命令行工具和 API 不支持从现有表的架构中删除列。如果您尝试通过应用移除列的架构来更新表，则系统会返回以下错误：BigQuery error in update operation: Provided Schema does not match Table project_id:dataset.table.

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

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

方法 1：使用查询

您可以使用两种 SQL 语句删除列：

  • SELECT * EXCEPT
  • ALTER TABLE DROP COLUMN

以下部分介绍了如何使用 SELECT * EXCEPT 查询来排除您想要移除的某列（或多列），以及如何使用查询结果来覆盖表或创建新的目标表。

如需使用 ALTER TABLE DROP COLUMN 语句，请参阅数据定义语言页面上的示例。

优势

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

缺点

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

SELECT * EXCEPT 示例

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

控制台

  1. 在控制台中，点击编写新查询。

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

    SELECT
 * EXCEPT(column_two)
FROM
 mydataset.mytable

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

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

  5. 在以下字段中：

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

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

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

    4. 点击确定。

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

  7. （可选）对于处理位置，点击自动选择并选择数据的位置。

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

bq

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

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

bq query \
--destination_table mydataset.mytable \
--replace \
--use_legacy_sql=false \
'SELECT
  * EXCEPT(column_two)
FROM
  mydataset.mytable'

API

如需选择 mydataset.mytable 中的所有数据（column_two 除外），请调用 jobs.insert 方法并配置一项 query 作业。（可选）在 jobReference 部分的 location 属性中指定您的位置。

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

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

方法 2：导出数据并将其加载到新表中

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

优势

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

缺点

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

表安全性

如需控制对 BigQuery 中表的访问权限，请参阅表访问权限控制简介。