使用 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 權限,才能取得資料表相關資訊。以下是具有 bigquery.tables.get 權限的預先定義 Cloud IAM 角色:

  • 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 說明文件中的「Dataset resource」(資料集資源) 頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 REST API 說明文件中的「Table resource」(資料表資源) 頁面。

示例

範例 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 網頁版 UI。

    前往 GCP Console

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 SQL 查詢。 INFORMATION_SCHEMA 需要標準 SQL 語法。標準 SQL 是 GCP Console 的預設語法。

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    
  3. 按一下 [Run] (執行)

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 網頁版 UI。

    前往 GCP Console

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 SQL 查詢。 INFORMATION_SCHEMA 需要標準 SQL 語法。標準 SQL 是 GCP Console 的預設語法。

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    WHERE
     table_type="BASE TABLE"
    
  3. 按一下 [Run] (執行)

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 說明文件中的「Dataset resource」(資料集資源) 頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 REST API 說明文件中的「Table resource」(資料表資源) 頁面。

示例

範例 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 網頁版 UI。

    前往 GCP Console

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 SQL 查詢。 INFORMATION_SCHEMA 需要標準 SQL 語法。標準 SQL 是 GCP Console 的預設語法。

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="expiration_timestamp"
    
  3. 按一下 [Run] (執行)

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 網頁版 UI。

    前往 GCP Console

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 SQL 查詢。 INFORMATION_SCHEMA 需要標準 SQL 語法。標準 SQL 是 GCP Console 的預設語法。

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="description" AND option_value LIKE "%test%"
    
  3. 按一下 [Run] (執行)

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 說明文件中的「Dataset resource」(資料集資源) 頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 REST API 說明文件中的「Table resource」(資料表資源) 頁面。

示例

以下範例會針對 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 網頁版 UI。

    前往 GCP Console

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 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. 按一下 [Run] (執行)

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 說明文件中的「Dataset resource」(資料集資源) 頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 REST API 說明文件中的「Table resource」(資料表資源) 頁面。

示例

以下範例會針對 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 網頁版 UI。

    前往 GCP Console

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 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. 按一下 [Run] (執行)

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 檢視表,藉以擷取預設專案 (myproject) 的 mydataset 中資料表的相關中繼資料。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 網頁版 UI。

    前往 GCP Console

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 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"                                                                                                                   |
| )                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

後續步驟

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁