使用 INFORMATION_SCHEMA 取得資料集中繼資料

INFORMATION_SCHEMA 是一系列檢視表,可提供資料集、資料表與檢視表相關中繼資料的存取權。

您可以查詢 INFORMATION_SCHEMA.SCHEMATAINFORMATION_SCHEMA.SCHEMATA_OPTIONS 資料檢視,藉以擷取專案中資料集的相關中繼資料。

所需權限

您至少要具備 bigquery.datasets.get 權限,才能取得資料集的相關資訊或中繼資料。以下是具有 bigquery.datasets.get 權限的預先定義 Cloud IAM 角色:

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

如要進一步瞭解 BigQuery 中的 Cloud IAM 角色和權限,請參閱存取權控管

SCHEMATA 檢視表

當您查詢 INFORMATION_SCHEMA.SCHEMATA 資料檢視時,系統會針對當前使用者可存取的專案下的每個資料集,都列出一個相對應的資料列在查詢結果中。

INFORMATION_SCHEMA.SCHEMATA 資料檢視具有下列結構定義:

資料欄名稱 資料類型
CATALOG_NAME STRING 包含資料集的專案名稱
SCHEMA_NAME STRING 資料集的名稱又稱為 datasetId
SCHEMA_OWNER STRING 此值一律為 NULL
CREATION_TIME TIMESTAMP 資料集的建立時間
LAST_MODIFIED_TIME TIMESTAMP 資料集的上次修改時間
LOCATION STRING 資料集的地理位置

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

示例

以下例子從 INFORMATION_SCHEMA.SCHEMATA 檢視表擷取了所有資料欄,但保留 schema_owner,以供未來使用。系統傳回的是預設專案 (myproject) 中所有資料集的中繼資料。

如要對預設專案以外的專案執行查詢,請使用以下格式將專案 ID 新增至資料集:`project_id`.INFORMATION_SCHEMA.view (例如 `myproject`.INFORMATION_SCHEMA.SCHEMATA)。

執行查詢的方式如下:

主控台

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

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

    SELECT
     * EXCEPT(schema_owner)
    FROM
     INFORMATION_SCHEMA.SCHEMATA
    
  3. 按一下 [Run] (執行)

CLI

使用 query 指令並透過 --nouse_legacy_sql--use_legacy_sql=false 標記指定標準 SQL 語法。INFORMATION_SCHEMA 查詢需要標準 SQL 語法。

如要執行查詢,請輸入以下指令:

bq query --nouse_legacy_sql \
'SELECT
   * EXCEPT(schema_owner)
 FROM
   INFORMATION_SCHEMA.SCHEMATA'

結果如下所示:

  +----------------+---------------+---------------------+---------------------+-----------------+
  |  catalog_name  |  schema_name  |    creation_time    | last_modified_time  |    location     |
  +----------------+---------------+---------------------+---------------------+-----------------+
  | myproject      | mydataset1    | 2018-11-07 19:50:24 | 2018-11-07 19:50:24 | US              |
  | myproject      | mydataset2    | 2018-07-16 04:24:22 | 2018-07-16 04:24:22 | US              |
  | myproject      | mydataset3    | 2018-02-07 21:08:45 | 2018-05-01 23:32:53 | asia-northeast1 |
  +----------------+---------------+---------------------+---------------------+-----------------+
  

SCHEMATA_OPTIONS 資料檢視

當您查詢 INFORMATION_SCHEMA.SCHEMATA_OPTIONS 資料檢視時,系統會針對當前使用者可存取的專案下的每個資料集,都列出一個相對應的資料列在查詢結果中。

INFORMATION_SCHEMA.SCHEMATA_OPTIONS 資料檢視具有下列結構定義:

資料欄名稱 資料類型
CATALOG_NAME STRING 包含資料集的專案名稱
SCHEMA_NAME STRING 資料集的名稱又稱為 datasetId
OPTION_NAME STRING 選項表格中的其中一個名稱值
OPTION_TYPE STRING 選項表格中的其中一個資料類型值
OPTION_VALUE STRING 選項表格中的其中一個值選項
選項表格
OPTION_NAME OPTION_TYPE OPTION_VALUE
default_table_expiration_days FLOAT64 資料集中所有資料表的預設生命週期 (以天為單位)
friendly_name STRING 資料集的描述性名稱
description STRING 資料集的說明
labels ARRAY<STRUCT<STRING, STRING>> 代表資料集標籤的 STRUCT 陣列

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

範例

範例 1:

以下範例查詢了 INFORMATION_SCHEMATA.SCHEMATA_OPTIONS 檢視表,藉以擷取預設專案 (myproject) 中所有資料集的預設資料表到期時間。

如要對預設專案以外的專案執行查詢,請使用以下格式將專案 ID 新增至資料集:`project_id`.INFORMATION_SCHEMA.view (例如 `myproject`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS)。

執行查詢的方式如下:

主控台

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

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

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

CLI

使用 query 指令並透過 --nouse_legacy_sql--use_legacy_sql=false 標記指定標準 SQL 語法。INFORMATION_SCHEMA 查詢需要標準 SQL 語法。

如要執行查詢,請輸入以下指令:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   INFORMATION_SCHEMA.SCHEMATA_OPTIONS
 WHERE
   option_name="default_table_expiration_days"'

結果如下所示:

  +----------------+---------------+-------------------------------+-------------+---------------------+
  |  catalog_name  |  schema_name  |          option_name          | option_type |    option_value     |
  +----------------+---------------+-------------------------------+-------------+---------------------+
  | myproject      | mydataset3    | default_table_expiration_days | FLOAT64     | 0.08333333333333333 |
  | myproject      | mydataset2    | default_table_expiration_days | FLOAT64     | 90.0                |
  | myproject      | mydataset1    | default_table_expiration_days | FLOAT64     | 30.0                |
  +----------------+---------------+-------------------------------+-------------+---------------------+
  

示例 2:

以下範例查詢了 INFORMATION_SCHEMATA.SCHEMATA_OPTIONS 檢視表,藉以擷取預設專案 (myproject) 中所有資料集的標籤。

如要對預設專案以外的專案執行查詢,請使用以下格式將專案 ID 新增至資料集:`project_id`.INFORMATION_SCHEMA.view (例如 `myproject`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS)。

執行查詢的方式如下:

主控台

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

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

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

CLI

使用 query 指令並透過 --nouse_legacy_sql--use_legacy_sql=false 標記指定標準 SQL 語法。INFORMATION_SCHEMA 查詢需要標準 SQL 語法。

如要執行查詢,請輸入以下指令:

bq query --nouse_legacy_sql \
'SELECT
   *
 FROM
   INFORMATION_SCHEMA.SCHEMATA_OPTIONS
 WHERE
   option_name="labels"'

結果如下所示:

  +----------------+---------------+-------------+---------------------------------+------------------------+
  |  catalog_name  |  schema_name  | option_name |          option_type            |      option_value      |
  +----------------+---------------+-------------+---------------------------------+------------------------+
  | myproject      | mydataset1    | labels      | ARRAY<STRUCT<STRING, STRING>>   | [STRUCT("org", "dev")] |
  | myproject      | mydataset2    | labels      | ARRAY<STRUCT<STRING, STRING>>   | [STRUCT("org", "dev")] |
  +----------------+---------------+-------------+---------------------------------+------------------------+
  

後續步驟

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

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

這個網頁