使用 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 權限的專案層級身分與存取權管理角色。如果您取得專案層級的 bigquery.tables.get 權限,就能取得專案中所有資料表的相關資訊。所有預先定義的專案層級身分與存取權管理角色都具備 bigquery.tables.get 權限,但 bigquery.jobUser 除外bigquery.user

此外,獲指派 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 資料表的建立時間

如要進一步瞭解資料集屬性,請參閱 REST API 說明文件中的資料集資源頁面。如要進一步瞭解資料表和資料檢視屬性,請參閱 REST API 說明文件中的資料表資源頁面。

範例

範例 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

如要執行查詢:

主控台

  1. 在 GCP 主控台中開啟 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

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

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

指令列

使用 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 主控台中開啟 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

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

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

指令列

使用 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 主控台中開啟 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

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

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

指令列

使用 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 選項中的值來尋找在說明中任意位置包含「測試」的資料表。mydataset 在您的預設專案 (myproject) 中。

如要對預設專案以外的專案執行查詢,請使用以下格式將專案 ID 新增至資料集:`[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] ,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS

如要執行查詢:

主控台

  1. 在 GCP 主控台中開啟 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

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

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

指令列

使用 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 主控台中開啟 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

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

    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] (執行)

指令列

使用 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 主控台中開啟 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

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

    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] (執行)

指令列

使用 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 主控台中開啟 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

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

    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
    

指令列

使用 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"                                                                                                       |
| )                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

後續步驟

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

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

這個網頁