使用 INFORMATION_SCHEMA 取得資料檢視中繼資料

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

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

必要權限

如要取得資料檢視的相關資訊,您必須獲指派資料集的 READER 角色,或者取得擁有 bigquery.tables.get 權限的專案層級身分與存取權管理 (IAM) 角色。如果您取得了專案層級的 bigquery.tables.get 權限,就可以取得專案內所有資料檢視的相關資訊。所有預先定義的專案層級身分與存取權管理角色都具備 bigquery.tables.get 權限,但 bigquery.jobUserbigquery.user 除外

此外,獲指派 bigquery.user 角色的使用者還擁有 bigquery.datasets.create 權限。換句話說,具備bigquery.user 角色身分的使用者可以取得自己建立的任何資料集中的資料檢視相關資訊。具備 bigquery.user 角色身分的使用者建立資料集後,即可取得該資料集的 OWNER 存取權。資料集的 OWNER 存取權可讓使用者完全控管該資料集以及其中的所有資料檢視。

如要深入瞭解 BigQuery 中的 IAM 角色和權限,請參閱存取權控制。如要深入瞭解資料集層級角色,請參閱資料集的原始角色一文。

VIEWS 資料檢視

當您查詢 INFORMATION_SCHEMA.VIEWS 資料檢視時,系統會針對資料集中的每個資料檢視,都列出一個相對應的資料列在查詢結果中。

INFORMATION_SCHEMA.VIEWS 資料檢視進行查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料檢視的資料集。

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

資料欄名稱 資料類型
TABLE_CATALOG STRING 包含資料集的專案名稱
TABLE_SCHEMA STRING 包含資料檢視的資料集名稱 (又稱為資料集 id)
TABLE_NAME STRING 資料檢視的名稱 (又稱為資料表 id)
VIEW_DEFINITION STRING 定義資料檢視的 SQL 查詢
CHECK_OPTION STRING 傳回的值一律為 NULL
USE_STANDARD_SQL STRING 如果資料檢視是使用標準 SQL 查詢所建立,即為 YES;如果 useLegacySql 設為 true,則為 NO

範例

範例 1:

下列範例從 INFORMATION_SCHEMA.VIEWS 資料檢視擷取了所有資料欄,但保留 check_option,供未來使用。系統傳回的是預設專案 (myproject) 中 mydataset 所有資料檢視的中繼資料。

INFORMATION_SCHEMA.VIEWS 資料檢視進行查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料檢視的資料集。

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

如何執行查詢:

主控台

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

    前往 BigQuery 網頁版 UI

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

    SELECT
     * EXCEPT(check_option)
    FROM
     mydataset.INFORMATION_SCHEMA.VIEWS
    
  3. 按一下 [Run] (執行)。

指令列

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

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

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(check_option) FROM mydataset.INFORMATION_SCHEMA.VIEWS'

結果如下所示:

  +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+
  | table_catalog  | table_schema  |  table_name   |                        view_definition                              | use_standard_sql |
  +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+
  | myproject      | mydataset     | myview        | SELECT column1, column2 FROM [myproject:mydataset.mytable] LIMIT 10 | NO               |
  +----------------+---------------+---------------+---------------------------------------------------------------------+------------------+
  

請注意,結果顯示這個資料檢視是使用舊版 SQL 查詢建立。

範例 2:

以下範例擷取了用來在預設專案 myprojectmydataset 中定義 myview 的 SQL 查詢與查詢語法。

INFORMATION_SCHEMA.VIEWS 資料檢視進行查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料檢視的資料集。

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

如何執行查詢:

主控台

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

    前往 BigQuery 網頁版 UI

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

    SELECT
     table_name, view_definition, use_standard_sql
    FROM
     mydataset.INFORMATION_SCHEMA.VIEWS
    WHERE
     table_name="myview"
    
  3. 按一下 [Run] (執行)。

指令列

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

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

bq query --nouse_legacy_sql \
'SELECT table_name, view_definition, use_standard_sql FROM mydataset.INFORMATION_SCHEMA.VIEWS WHERE table_name="myview"'

結果如下所示:

  +---------------+---------------------------------------------------------------+------------------+
  |  table_name   |                        view_definition                        | use_standard_sql |
  +---------------+---------------------------------------------------------------+------------------+
  | myview        | SELECT column1, column2, column3 FROM mydataset.mytable       | YES              |
  +---------------+---------------------------------------------------------------+------------------+
  

請注意,結果顯示這個資料檢視是使用標準 SQL 查詢建立。

後續步驟

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

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

這個網頁