使用 INFORMATION_SCHEMA 获取表元数据

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

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

TABLESTABLE_OPTIONS 视图还包含关于视图的概要信息。如需查看详细信息,请改为查询 INFORMATION_SCHEMA.VIEWS 视图。

所需权限

如需获取有关表的信息,您至少必须具有 bigquery.tables.get 权限。以下预定义的 Cloud IAM 角色具有 bigquery.tables.get 权限:

  • bigquery.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

此外,如果用户具有 bigquery.datasets.create 权限,则当该用户创建数据集时,系统会为其授予该数据集的 bigquery.dataOwner 访问权限。 借助 bigquery.dataOwner 访问权限,用户可以检索表元数据。

如需详细了解 BigQuery 中的 Cloud 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 表的创建时间

如需详细了解数据集属性,请参阅 REST API 文档中的数据集资源页。如需详细了解表和视图属性,请参阅 REST API 文档中的表资源页。

示例

示例 1:

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

mydataset 包含以下表:

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

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

如需对非默认项目运行查询,请按以下格式为数据集添加相应的项目 ID:`project_id`.dataset.INFORMATION_SCHEMA.view,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLES

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

控制台

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

    转到 GCP Console

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

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

CLI

使用 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 列。返回的元数据包括默认项目 myproject 的数据集 mydataset 中的所有表。

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

如需对非默认项目运行查询,请按以下格式为数据集添加相应的项目 ID:`project_id`.dataset.INFORMATION_SCHEMA.view,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLES

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

控制台

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

    转到 GCP Console

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

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

CLI

使用 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,表示表的标签

如需详细了解数据集属性,请参阅 REST API 文档中的数据集资源页。如需详细了解表和视图属性,请参阅 REST API 文档中的表资源页。

示例

示例 1:

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

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

如需对非默认项目运行查询,请按以下格式为数据集添加相应的项目 ID:`project_id`.dataset.INFORMATION_SCHEMA.view,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS

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

控制台

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

    转到 GCP Console

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

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

CLI

使用 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.TABLE_OPTIONS

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

控制台

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

    转到 GCP Console

  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. 点击运行

CLI

使用 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

如需详细了解数据集属性,请参阅 REST API 文档中的数据集资源页。如需详细了解表和视图属性,请参阅 REST API 文档中的表资源页。

示例

以下示例演示了如何从 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 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含这些表的数据集。

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

控制台

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

    转到 GCP Console

  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. 点击运行

CLI

使用 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 列的说明

如需详细了解数据集属性,请参阅 REST API 文档中的数据集资源页。如需详细了解表和视图属性,请参阅 REST API 文档中的表资源页。

示例

以下示例演示了如何从 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 视图的查询必须具有数据集限定符。提交查询的用户必须能够访问包含这些表的数据集。

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

控制台

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

    转到 GCP Console

  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. 点击运行

CLI

使用 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.TABLES

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

控制台

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

    转到 GCP Console

  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<STRUCT<column_name STRING, data_type STRING, is_nullable 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_nullable 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 MakeNullable(data_type STRING, is_nullable STRING)
    AS (
      IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '')
    );
    
    CREATE TEMP FUNCTION MakeColumnList(
      columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
    ) AS (
      IFNULL(
        CONCAT(
          '(\n',
          (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type,  MakeNullable(data_type, is_nullable)), ',\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_nullable, 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
    

CLI

使用 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_nullable 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_nullable 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 MakeNullable(data_type STRING, is_nullable STRING)
AS (
  IF(not STARTS_WITH(data_type, 'ARRAY<') and is_nullable = 'NO', ' NOT NULL', '')
);
CREATE TEMP FUNCTION MakeColumnList(
  columns ARRAY<STRUCT<column_name STRING, data_type STRING, is_nullable STRING,  is_partitioning_column STRING, clustering_ordinal_position INT64>>
) AS (
  IFNULL(
    CONCAT(
      '(\n',
      (SELECT STRING_AGG(CONCAT('  ', column_name, ' ', data_type,  MakeNullable(data_type, is_nullable)), ',\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_nullable, 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 NOT NULL,                                                                                                                                                    |
|   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"                                                                                                                   |
| )                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

后续步骤

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

发送以下问题的反馈:

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