手动更改表架构

本文档介绍了如何手动更改现有 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_onecolumn_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.mytablecolumn_twocolumn_three 的所有数据,并将 column_one 的类型从 DATE 转换为 STRING。查询结果用于覆盖现有表。覆盖后的表会将 column_one 存储为 STRING 数据类型。

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

控制台

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

  2. 查询编辑器中输入以下查询,选择 mydataset.mytablecolumn_twocolumn_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.mytablecolumn_twocolumn_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.mytablecolumn_twocolumn_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 中表的访问权限,请参阅表访问权限控制简介