使用 INFORMATION_SCHEMA 获取表元数据

INFORMATION_SCHEMA 是一系列视图,可以访问有关数据集、表和视图的元数据。

您可以查询 INFORMATION_SCHEMA.TABLESINFORMATION_SCHEMA.TABLE_OPTIONS 视图,以检索有关项目中表和视图的元数据。您还可以查询 INFORMATION_SCHEMA.COLUMNSINFORMATION_SCHEMA.COLUMN_FIELD_PATHS 视图以检索有关表中列(字段)的元数据。

TABLESTABLE_OPTIONS 视图还包含有关视图的概要信息。如需了解详情,请改为查询 INFORMATION_SCHEMA.VIEWS 视图。

所需权限

要获取有关表的信息,您必须被指定为数据集的 READER 角色,或者必须被指定为具有 bigquery.tables.get 权限的项目级层 IAM 角色。如果您拥有项目级层的 bigquery.tables.get 权限,则可获取项目中所有表的相关信息。除了 bigquery.jobUserbigquery.user,其他所有预定义的项目级层 IAM 角色都具有 bigquery.tables.get 权限。

此外,被指定为 bigquery.user 角色的用户具有 bigquery.datasets.create 权限。这样一来,分配有 bigquery.user 角色的用户就可以获取该用户创建的任何数据集内的表的相关信息。当被指定为 bigquery.user 角色的用户创建数据集时,系统将为该用户授予对该数据集的 OWNER 访问权限。凭借对数据集的 OWNER 访问权限,用户可以完全掌控该数据集以及其中的所有表。

要详细了解 BigQuery 中的 IAM 角色和权限,请参阅访问权限控制。要详细了解数据集级层角色,请参阅数据集的初始角色

TABLES 视图

查询 INFORMATION_SCHEMA.TABLES 视图时,查询结果包含数据集中每个表或视图对应的一行。

针对 INFORMATION_SCHEMA.TABLES 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含表或视图的数据集。

INFORMATION_SCHEMA.TABLES 视图具有以下架构:

列名 数据类型
TABLE_CATALOG STRING 包含数据集的项目的名称
TABLE_SCHEMA STRING 包含表或视图的数据集的名称(也称为 datasetId
TABLE_NAME STRING 表或视图的名称(也称为 tableId
TABLE_TYPE STRING 表类型:
IS_INSERTABLE_INTO STRING YESNO,具体取决于表是否支持 DML INSERT 语句
IS_TYPED STRING 值始终为 NO
CREATION_TIME TIMESTAMP 表的创建时间

示例

示例 1:

以下示例从 INFORMATION_SCHEMA.TABLES 视图中检索所有列,is_typed 除外,该列留待将来使用。返回的元数据用于默认项目 myprojectmydataset 中的所有表。

mydataset 包含以下表:

  • mytable1:标准 BigQuery 表
  • myview1:BigQuery 视图

针对 INFORMATION_SCHEMA.TABLES 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含表的数据集。

要对默认项目以外的项目运行查询,请按照以下格式将项目 ID 添加到数据集:`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`,例如 `myproject:mydataset.INFORMATION_SCHEMA.TABLES`

要运行查询,请执行以下操作:

Console

  1. 在 GCP Console 中打开 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 需要标准 SQL 语法。标准 SQL 是 GCP Console 中的默认语法。

    SELECT
     * EXCEPT(is_typed)
    FROM
     `mydataset.INFORMATION_SCHEMA.TABLES`
    
  3. 点击运行

命令行

使用 query 命令并使用 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询需要标准 SQL 语法。

要运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_typed) FROM `mydataset.INFORMATION_SCHEMA.TABLES`'

结果应如下所示:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 |
  | myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

示例 2:

以下示例从 INFORMATION_SCHEMA.TABLES 视图中检索类型为 BASE TABLE 的所有表。不包括 is_typed 列。返回的元数据用于默认项目 myprojectmydataset 中的表。

针对 INFORMATION_SCHEMA.TABLES 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含表的数据集。

要对默认项目以外的项目运行查询,请按照以下格式将项目 ID 添加到数据集:`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`,例如 `myproject:mydataset.INFORMATION_SCHEMA.TABLES`

要运行查询,请执行以下操作:

Console

  1. 在 GCP Console 中打开 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 需要标准 SQL 语法。标准 SQL 是 GCP Console 中的默认语法。

    SELECT
     * EXCEPT(is_typed)
    FROM
     `mydataset.INFORMATION_SCHEMA.TABLES`
    WHERE
     table_type="BASE TABLE"
    
  3. 点击运行

命令行

使用 query 命令并使用 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询需要标准 SQL 语法。

要运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_typed) FROM `mydataset.INFORMATION_SCHEMA.TABLES`
WHERE table_type="BASE TABLE"'

结果应如下所示:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | NO                 | 2018-10-31 22:40:05 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

TABLE_OPTIONS 视图

查询 INFORMATION_SCHEMA.TABLE_OPTIONS 视图时,查询结果包含数据集中每个表或视图对应的一行。

针对 INFORMATION_SCHEMA.TABLE_OPTIONS 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含表或视图的数据集。

INFORMATION_SCHEMA.TABLE_OPTIONS 视图具有以下架构:

列名 数据类型
TABLE_CATALOG STRING 包含数据集的项目的名称
TABLE_SCHEMA STRING 包含表或视图的数据集的名称(也称为 datasetId
TABLE_NAME STRING 表或视图的名称(也称为 tableId
OPTION_NAME STRING 选项表中的一个名称值
OPTION_TYPE STRING 选项表中的一个数据类型值
OPTION_VALUE STRING 选项表中的一个值选项
选项表
OPTION_NAME OPTION_TYPE OPTION_VALUE
partition_expiration_days FLOAT64 分区表中所有分区的默认生命周期(以天为单位)
expiration_timestamp FLOAT64 表的默认生命周期(以天为单位)
kms_key_name STRING 用于加密表的 Cloud KMS 密钥的名称
friendly_name STRING 表的描述性名称
description STRING 表说明
labels ARRAY<STRUCT<STRING, STRING>> 一组 STRUCT,表示表上的标签

示例

示例 1:

以下示例通过查询 INFORMATION_SCHEMATA.TABLE_OPTIONS 视图来检索默认项目 (myproject) 中 mydataset 中的所有表的默认表到期时间。

针对 INFORMATION_SCHEMA.TABLE_OPTIONS 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含表的数据集。

要对默认项目以外的项目运行查询,请按照以下格式将项目 ID 添加到数据集:`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`,例如 `myproject:mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS`

要运行查询,请执行以下操作:

Console

  1. 在 GCP Console 中打开 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 需要标准 SQL 语法。标准 SQL 是 GCP Console 中的默认语法。

    SELECT
     *
    FROM
     `mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS`
    WHERE
     option_name="expiration_timestamp"
    
  3. 点击运行

命令行

使用 query 命令并使用 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询需要标准 SQL 语法。

要运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT * FROM `mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS`
WHERE option_name="expiration_timestamp"'

结果应如下所示:

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | table_catalog  | table_schema  | table_name |     option_name      | option_type |             option_value             |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | myproject      | mydataset     | mytable1   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2020-01-16T21:12:28.000Z" |
  | myproject      | mydataset     | mytable2   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2021-01-01T21:12:28.000Z" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  

示例 2:

以下示例检索 mydataset 中包含测试数据的所有表的元数据。该查询使用 description 选项中的值查找在描述的任何位置包含"test"的表。mydataset 位于默认项目 myproject 中。

要对默认项目以外的项目运行查询,请按照以下格式将项目 ID 添加到数据集:`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`,例如 `myproject:mydataset.INFORMATION_SCHEMA.SCHEMATA_OPTIONS`

要运行查询,请执行以下操作:

Console

  1. 在 GCP Console 中打开 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 需要标准 SQL 语法。标准 SQL 是 GCP Console 中的默认语法。

    SELECT
     *
    FROM
     `mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS`
    WHERE
     option_name="description" AND option_value LIKE "%test%"
    
  3. 点击运行

命令行

使用 query 命令并使用 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询需要标准 SQL 语法。

要运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT * FROM `mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS`
WHERE option_name="description" AND option_value LIKE "%test%"'

结果应如下所示:

  +----------------+---------------+------------+-------------+-------------+--------------+
  | table_catalog  | table_schema  | table_name | option_name | option_type | option_value |
  +----------------+---------------+------------+-------------+-------------+--------------+
  | myproject      | mydataset     | mytable1   | description | STRING      | "test data"  |
  | myproject      | mydataset     | mytable2   | description | STRING      | "test data"  |
  +----------------+---------------+------------+-------------+-------------+--------------+
  

COLUMNS 视图

查询 INFORMATION_SCHEMA.COLUMNS 视图时,查询结果包含表中每列(字段)对应的一行。

针对 INFORMATION_SCHEMA.COLUMNS 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含表的数据集。

INFORMATION_SCHEMA.COLUMNS 视图具有以下架构:

列名 数据类型
TABLE_CATALOG STRING 包含数据集的项目的名称
TABLE_SCHEMA STRING 包含表的数据集的名称(也称为 datasetId
TABLE_NAME STRING 表或视图的名称(也称为 tableId
COLUMN_NAME STRING 列的名称
ORDINAL_POSITION INT64 表中列的 1 编入索引的偏移量;如果列为伪列(例如 _PARTITIONTIME 或 _PARTITIONDATE),则值为 NULL
IS_NULLABLE STRING YESNO,具体取决于列的模式是否允许 NULL
DATA_TYPE STRING 列的标准 SQL 数据类型
IS_GENERATED STRING 值始终为 NEVER
GENERATION_EXPRESSION STRING 值始终为 NULL
IS_STORED STRING 值始终为 NULL
IS_HIDDEN STRING YESNO,具体取决于列是否为伪列,例如 _PARTITIONTIME 或 _PARTITIONDATE
IS_UPDATABLE STRING 值始终为 NULL
IS_SYSTEM_DEFINED STRING YESNO,具体取决于列是否为伪列,例如 _PARTITIONTIME 或 _PARTITIONDATE
IS_PARTITIONING_COLUMN STRING YESNO,具体取决于列是否为分区列
CLUSTERING_ORDINAL_POSITION STRING 表的聚簇列中列的 1 编入索引的偏移量;如果表不是聚簇表,则值为 NULL

示例

以下示例从 census_bureau_usa 数据集中 population_by_zip_2010 表的 INFORMATION_SCHEMA.COLUMNS 视图中检索元数据。此数据集是 BigQuery 公共数据集程序的一部分。

由于此表属于 bigquery-public-data 项目,因此您可以按照以下格式将项目 ID 添加到数据集:`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`,例如 `bigquery-public-data:census_bureau_usa.INFORMATION_SCHEMA.TABLES`

以下列会从查询结果中排除,因为它们当前保留供将来使用:

  • IS_GENERATED
  • GENERATION_EXPRESSION
  • IS_STORED
  • IS_UPDATABLE

针对 INFORMATION_SCHEMA.COLUMNS 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含表的数据集。

要运行查询,请执行以下操作:

Console

  1. 在 GCP Console 中打开 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 需要标准 SQL 语法。标准 SQL 是 GCP Console 中的默认语法。

    SELECT
     * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
    FROM
     `bigquery-public-data:census_bureau_usa.INFORMATION_SCHEMA.COLUMNS`
    WHERE
     table_name="population_by_zip_2010"
    
  3. 点击运行

命令行

使用 query 命令并使用 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询需要标准 SQL 语法。

要运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
FROM `bigquery-public-data:census_bureau_usa.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name="population_by_zip_2010"'

结果应如下所示。为确保可读性,table_catalogtable_schema 会从结果中排除:

+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
|       table_name       | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
| population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
  

COLUMN_FIELD_PATHS 视图

查询 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 视图时,查询结果包含嵌套RECORD(或 STRUCT)列中的每列对应的一行。

针对 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含表的数据集。

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 视图具有以下架构:

列名 数据类型
TABLE_CATALOG STRING 包含数据集的项目的名称
TABLE_SCHEMA STRING 包含表的数据集的名称(也称为 datasetId
TABLE_NAME STRING 表或视图的名称(也称为 tableId
COLUMN_NAME STRING 列的名称
FIELD_PATH STRING 嵌套“RECORD”(或“STRUCT”)列中的列的路径
DATA_TYPE STRING 列的标准 SQL 数据类型
DESCRIPTION STRING 说明

示例

以下示例从 github_repos 数据集commits 表的 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 视图中检索元数据。此数据集是 BigQuery 公共数据集程序的一部分。

由于此表属于 bigquery-public-data 项目,因此您可以按照以下格式将项目 ID 添加到数据集:`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`;例如 `bigquery-public-data:github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`

commits 表包含以下嵌套列以及嵌套和重复列:

  • author:嵌套 RECORD
  • committer:嵌套 RECORD
  • trailer:嵌套和重复 RECORD
  • difference:嵌套和重复 RECORD

查询将检索有关 authordifference 列的元数据。

针对 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含表的数据集。

要运行查询,请执行以下操作:

Console

  1. 在 GCP Console 中打开 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 需要标准 SQL 语法。标准 SQL 是 GCP Console 中的默认语法。

    SELECT
     *
    FROM
     `bigquery-public-data:github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
    WHERE
     table_name="commits"
     AND column_name="author"
     OR column_name="difference"
    
  3. 点击运行

命令行

使用 query 命令并使用 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询需要标准 SQL 语法。

要运行查询,请输入以下命令:

bq query --nouse_legacy_sql \
'SELECT * FROM `bigquery-public-data:github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE table_name="commits" AND column_name="author" OR column_name="difference"'

结果应如下所示。为确保可读性,table_catalogtable_schema 会从结果中排除。

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | table_name | column_name |     field_path      |                                                                      data_type                                                                      | description |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | commits    | author      | author              | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>                                                                  | NULL        |
  | commits    | author      | author.name         | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.email        | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.time_sec     | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.tz_offset    | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.date         | TIMESTAMP                                                                                                                                           | NULL        |
  | commits    | difference  | difference          | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL        |
  | commits    | difference  | difference.old_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.new_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.old_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_repo | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_repo | STRING                                                                                                                                              | NULL        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  

高级示例

以下高级示例查询 INFORMATION_SCHEMA.TABLESTABLE_OPTIONSCOLUMNS 视图以检索有关默认项目 myprojectmydataset 中的表的元数据。mydataset 包含 2 个表:

  • mytable1:使用与 github_repos 公共数据集中的 commits 表相同的架构
  • mytable2:使用与 census_bureau_usa 公共数据集中的 population_by_zip_2010 表相同的架构

用户定义的函数使用结果来汇编重新创建表所需的 DDL 语句。然后,您可以在查询结果中使用 DDL 语句以在 mydataset 中重新创建表。

要对默认项目以外的项目运行查询,请按照以下格式将项目 ID 添加到数据集:`[PROJECT_ID]:[DATASET].INFORMATION_SCHEMA.[VIEW]`,例如 `myproject:mydataset.INFORMATION_SCHEMA.SCHEMATA_OPTIONS`

要运行查询,请执行以下操作:

Console

  1. 在 GCP Console 中打开 BigQuery 网页界面。

    转到 BigQuery 网页界面

  2. 查询编辑器框中输入以下标准 SQL 查询。 INFORMATION_SCHEMA 需要标准 SQL 语法。标准 SQL 是 GCP Console 中的默认语法。

    CREATE TEMP FUNCTION MakePartitionByExpression(
      column_name STRING, data_type STRING
    ) AS (
      IF(
        column_name = '_PARTITIONTIME',
        'DATE(_PARTITIONTIME)',
        IF(
          data_type = 'TIMESTAMP',
          CONCAT('DATE(', column_name, ')'),
          column_name
        )
      )
    );
    
    CREATE TEMP FUNCTION MakePartitionByClause(
      columns ARRAY>
    ) AS (
      IFNULL(
        CONCAT(
          'PARTITION BY ',
          (SELECT MakePartitionByExpression(column_name, data_type)
           FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'),
          '\n'),
        ''
      )
    );
    
    CREATE TEMP FUNCTION MakeClusterByClause(
      columns ARRAY>
    ) AS (
      IFNULL(
        CONCAT(
          'CLUSTER BY ',
          (SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position)
            FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL),
          '\n'
        ),
        ''
      )
    );
    
    CREATE TEMP FUNCTION MakeColumnList(
      columns ARRAY>
    ) AS (
      IFNULL(
        CONCAT(
          '(\n',
          (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type), ',\n')
           FROM UNNEST(columns)),
          '\n)\n'
        ),
        ''
      )
    );
    
    CREATE TEMP FUNCTION MakeOptionList(
      options ARRAY>
    ) AS (
      IFNULL(
        CONCAT(
          'OPTIONS (\n',
          (SELECT STRING_AGG(CONCAT('  ', option_name, '=', option_value), ',\n') FROM UNNEST(options)),
          '\n)\n'),
        ''
      )
    );
    
    WITH Components AS (
      SELECT
        CONCAT('`', table_catalog, '.', table_schema, '.', table_name, '`') AS table_name,
        ARRAY_AGG(
          STRUCT(column_name, data_type, is_partitioning_column, clustering_ordinal_position)
          ORDER BY ordinal_position
        ) AS columns,
        (SELECT ARRAY_AGG(STRUCT(option_name, option_value))
         FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2
         WHERE t.table_name = t2.table_name) AS options
      FROM mydataset.INFORMATION_SCHEMA.TABLES AS t
      LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS
      USING (table_catalog, table_schema, table_name)
      WHERE table_type = 'BASE TABLE'
      GROUP BY table_catalog, table_schema, t.table_name
    )
    SELECT
      CONCAT(
        'CREATE OR REPLACE TABLE ',
        table_name,
        '\n',
        MakeColumnList(columns),
        MakePartitionByClause(columns),
        MakeClusterByClause(columns),
        MakeOptionList(options))
    FROM Components
    

命令行

使用 query 命令并使用 --nouse_legacy_sql--use_legacy_sql=false 标志指定标准 SQL 语法。INFORMATION_SCHEMA 查询需要标准 SQL 语法。

要运行查询,请输入以下命令:

QUERY_TEXT=$(cat <<ENDQUERY
CREATE TEMP FUNCTION MakePartitionByExpression(
  column_name STRING, data_type STRING
) AS (
  IF(
    column_name = '_PARTITIONTIME',
    'DATE(_PARTITIONTIME)',
    IF(
      data_type = 'TIMESTAMP',
      CONCAT('DATE(', column_name, ')'),
      column_name
    )
  )
);
CREATE TEMP FUNCTION MakePartitionByClause(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      'PARTITION BY ',
      (SELECT MakePartitionByExpression(column_name, data_type)
       FROM UNNEST(columns) WHERE is_partitioning_column = 'YES'),
      '\n'),
    ''
  )
);
CREATE TEMP FUNCTION MakeClusterByClause(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      'CLUSTER BY ',
      (SELECT STRING_AGG(column_name, ', ' ORDER BY clustering_ordinal_position)
        FROM UNNEST(columns) WHERE clustering_ordinal_position IS NOT NULL),
      '\n'
    ),
    ''
  )
);
CREATE TEMP FUNCTION MakeColumnList(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      '(\n',
      (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type), ',\n')
       FROM UNNEST(columns)),
      '\n)\n'
    ),
    ''
  )
);
CREATE TEMP FUNCTION MakeOptionList(
  options ARRAY<STRUCT<option_name STRING, option_value STRING>>
) AS (
  IFNULL(
    CONCAT(
      'OPTIONS (\n',
      (SELECT STRING_AGG(CONCAT('  ', option_name, '=', option_value), ',\n') FROM UNNEST(options)),
      '\n)\n'),
    ''
  )
);
WITH Components AS (
  SELECT
    CONCAT('\`', table_catalog, '.', table_schema, '.', table_name, '\`') AS table_name,
    ARRAY_AGG(
      STRUCT(column_name, data_type, is_partitioning_column, clustering_ordinal_position)
      ORDER BY ordinal_position
    ) AS columns,
    (SELECT ARRAY_AGG(STRUCT(option_name, option_value))
     FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS AS t2
     WHERE t.table_name = t2.table_name) AS options
  FROM mydataset.INFORMATION_SCHEMA.TABLES AS t
  LEFT JOIN mydataset.INFORMATION_SCHEMA.COLUMNS
  USING (table_catalog, table_schema, table_name)
  WHERE table_type = 'BASE TABLE'
  GROUP BY table_catalog, table_schema, t.table_name
)
SELECT
  CONCAT(
    'CREATE OR REPLACE TABLE ',
    table_name,
    '\n',
    MakeColumnList(columns),
    MakePartitionByClause(columns),
    MakeClusterByClause(columns),
    MakeOptionList(options))
FROM Components
ENDQUERY
)
bq query --nouse_legacy_sql "$QUERY_TEXT"

输出应如下所示:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                f0_                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE OR REPLACE TABLE `myproject.mydataset.population_by_zip_2010`                                                                                              |
| (                                                                                                                                                                 |
|   zipcode STRING,                                                                                                                                                 |
|   geo_id STRING,                                                                                                                                                  |
|   minimum_age INT64,                                                                                                                                              |
|   maximum_age INT64,                                                                                                                                              |
|   gender STRING,                                                                                                                                                  |
|   population INT64                                                                                                                                                |
| )                                                                                                                                                                 |
| OPTIONS (                                                                                                                                                         |
|   expiration_timestamp=TIMESTAMP "2019-04-17T02:10:32.055Z"                                                                                                       |
| )                                                                                                                                                                 |
| CREATE OR REPLACE TABLE `myproject.mydataset.commits`                                                                                                             |
| (                                                                                                                                                                 |
|   commit STRING,                                                                                                                                                  |
|   tree STRING,                                                                                                                                                    |
|   parent ARRAY<STRING>,                                                                                                                                           |
|   author STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>,                                                                      |
|   committer STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>,                                                                   |
|   subject STRING,                                                                                                                                                 |
|   message STRING,                                                                                                                                                 |
|   trailer ARRAY<STRUCT<key STRING, value STRING, email STRING>>,                                                                                                  |
|   difference ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>>, |
|   difference_truncated BOOL,                                                                                                                                      |
|   repo_name ARRAY<STRING>,                                                                                                                                        |
|   encoding STRING                                                                                                                                                 |
| )                                                                                                                                                                 |
| OPTIONS (                                                                                                                                                         |
|   expiration_timestamp=TIMESTAMP "2019-04-17T03:12:03.248Z"                                                                                                       |
| )                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

后续步骤

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

发送以下问题的反馈:

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