建立及使用叢集資料表

本文件說明如何在 BigQuery 中建立及使用叢集資料表。如需 BigQuery 中的叢集資料表支援總覽,請參閱叢集資料表簡介

限制

在 BigQuery 中,叢集資料表具有以下限制:

  • 目前叢集僅支援分區資料表
  • 只能透過標準 SQL 語法查詢叢集資料表,以及將查詢結果寫入叢集資料表之中。
  • 只有在資料表建立後,才可以指定所欲叢集的資料欄。
  • 當叢集資料表建立完成後,所指定的叢集資料欄即無法更改。
  • 叢集資料欄必須是頂層的非重複資料欄,且屬於下列任一項資料欄類型:INT64STRINGDATETIMESTAMPBOOLNUMERICGEOGRAPHY。如要進一步瞭解資料類型,請參閱標準 SQL 資料類型
  • 您最多可指定四個叢集欄位。

建立叢集資料表

目前您只能夠為已分區完成的資料表建立叢集,包含擷取時間分區資料表以及分區資料表 (即按 TIMESTAMPDATE 資料欄分區的資料表)

您可用以下方式,在 BigQuery 中建立叢集資料表:

資料表命名

在 BigQuery 中建立資料表時,每個資料集裡的資料表名稱不得重複。資料表名稱可以:

  • 最多包含 1,024 個字元
  • 包含字母 (大寫或小寫)、數字與底線

所需權限

如要建立資料表,您至少要具備下列權限:

  • 建立資料表的 bigquery.tables.create 權限
  • 使用載入工作、查詢工作或複製工作將資料寫入資料表的 bigquery.tables.updateData 權限
  • 執行查詢工作、載入工作或複製工作以將資料寫入資料表的 bigquery.jobs.create 權限

您可能還需要其他權限 (例如 bigquery.tables.getData),才能存取您要寫入資料表的資料。

以下是具有 bigquery.tables.createbigquery.tables.updateData 權限的預先定義 Cloud IAM 角色:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

以下是具有 bigquery.jobs.create 權限的預先定義 Cloud IAM 角色:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

此外,具備 bigquery.datasets.create 權限的使用者在建立資料集時,會獲得 bigquery.dataOwner 存取權。 bigquery.dataOwner 存取權可讓使用者在資料集裡建立及更新資料表。

如要進一步瞭解 BigQuery 中的 Cloud IAM 角色和權限,請參閱預先定義的角色和權限

透過結構定義建立空白叢集資料表

您在 BigQuery 中建立資料表時,應指定要叢集的欄位。資料表建立完成後,您將無法修改叢集欄位。目前您僅能針對已分區的資料表指定要叢集的欄位。

叢集資料欄必須是頂層的非重複資料欄,且屬於下列任一項簡易資料類型:INTEGERSTRINGDATETIMESTAMPBOOLEANNUMERICGEOGRAPHY

您最多可指定四個叢集資料欄,當您指定多個叢集欄位時,欄位的順序將決定資料的排序方式。舉例來說,若資料表是依欄位 a、b、c 來進行叢集,則資料也會按照相同順序排序,即第一順位是欄位 a、第二順位是欄位 b、第三順位是欄位 c。按照最佳做法,最常用以進行資料篩選或匯總的欄位應排在第一順位。

叢集資料欄順序亦會影響資料查詢的效能與定價。如要進一步瞭解叢集資料表查詢的最佳做法,請參閱查詢叢集資料表

如要使用結構定義建立空白叢集資料表:

主控台

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

  2. 在導覽面板的「Resources」(資源) 區段,展開您的專案並選取資料集。

  3. 在視窗右側的詳細資料面板中,按一下 [Create table] (建立資料表)

    建立資料表按鈕

  4. 在「Create table」(建立資料表) 頁面的「Source」(來源) 區段中,針對「Create table from」(使用下列資料建立資料表) 選取 [Empty table] (空白資料表)

    使用下列資料建立資料表選項

  5. 在「Destination」(目的地) 區段中:

    • 在「Dataset name」(資料集名稱) 部分,選擇適當的資料集,然後在「Table name」(資料表名稱) 欄位中,輸入您在建立資料表時使用的名稱。
    • 確認「Table type」(資料表類型) 設為 [Native table] (原生資料表)
  6. 在「Schema」(結構定義) 區段中輸入結構定義

    • 透過下列方式,手動輸入結構定義資訊:

      • 啟用 [Edit as text] (以文字形式編輯),然後以 JSON 陣列的形式輸入資料表結構定義。

      • 使用 [Add Field] (新增欄位) 手動輸入結構定義

  7. 在「Partition and cluster settings」(分區與叢集設定) 部分,按一下 [No Partitioning] (無分區),然後依序選取 [Partition by field] (依欄位分區)DATETIMESTAMP 資料欄。如果結構定義不包含 DATETIMESTAMP 資料欄,則此選項不可用。

    如要建立擷取時間分區資料表,請按一下 [No partitioning] (無分區),然後選取 [Partition by ingestion time] (依擷取時間分區)

  8. (選用) 在「Partitioning filter」(分區篩選器) 部分,勾選 [Require partition filter] (需要分區篩選器) 方塊,藉此要求使用者加入指定了待查詢分區的 WHERE 子句。使用分區篩選器可以降低成本並提升成效。詳情請參閱查詢分區資料表一文。

  9. 針對「Clustering order」(分群順序),輸入一到四個以半形逗號分隔的欄位名稱。

  10. (選用) 按一下 [Advanced options] (進階選項),並在「Encryption」(加密) 部分按一下 [Customer-managed key] (客戶管理的金鑰),即可使用 Cloud Key Management Service 金鑰。如果您保留「Google-managed key」(Google 代管的金鑰) 這項設定,則 BigQuery 會加密靜態資料

  11. 按一下 [Create table] (建立資料表)

傳統版 UI

  1. 使用傳統 BigQuery 網頁版 UI

    前往傳統 BigQuery 網頁版 UI

  2. 在導覽窗格中,按一下資料集名稱旁的向下箭號圖示 向下箭號圖示,然後按一下 [Create new table] (建立新資料表)

  3. 在「Create Table」(建立資料表) 頁面的「Source Data」(來源資料) 區段中,按一下 [Create empty table] (建立空白資料表)

  4. 在「Create Table」(建立資料表) 頁面的「Destination Table」(目標資料表) 區段中:

    • 在「Table name」(資料表名稱) 部分選擇適當的資料集,然後在資料表名稱欄位中,輸入您要建立的資料表名稱。
    • 確認「Table type」(資料表類型) 設為 [Native table] (原生資料表)
  5. 在「Schema」(結構定義) 區段中,手動輸入結構定義

    • 您可以透過下列方式,手動輸入結構定義資訊:

      • 按一下 [Edit as text] (以文字形式編輯),然後以 JSON 陣列的形式輸入資料表結構定義:

      • 使用 [Add Field] (新增欄位) 輸入結構定義。

  6. 在「Options」(選項) 區段中:

    • 針對「Partitioning Type」(分區類型),按一下 [None] (無),接著選擇 [Day] (日)
    • 針對「Partitioning Field」(分區欄位),選擇下列其中一個選項:
      • 選擇 timestamp 建立依「DATE」(日期) 或「TIMESTAMP」(時間戳記) 資料欄分區的資料表。
      • 選擇 _PARTITIONTIME 建立擷取時間分區資料表。
    • 針對「Clustering columns」(叢集資料欄),輸入一到四個欄位名稱。
    • 在「Destination encryption」(目的地加密) 中保留 Default 選項。這個屬性適用於客戶管理的加密金鑰。根據預設,BigQuery 會加密靜態儲存的客戶內容

      分區資料表詳細資料

  7. 按一下 [Create Table] (建立資料表)

建立資料表後,您可以更新叢集資料表的資料表到期時間說明標籤,但您無法在使用 BigQuery 網頁版 UI 建立資料表後,新增分區到期時間。

CLI

使用 mk 指令搭配下列標記:

  • --table (或是 -t 縮寫)。
  • --schema &mdash:您可利用內嵌方式或以 JSON 結構定義檔提供資料表結構定義。
  • --time_partitioning_type (用於擷取時間分區資料表) 或 --time_partitioning_field (用於分區資料表)。目前 --time_partitioning_type 唯一支援的值是 DAY
  • --clustering_fields 可以指定最多四個叢集欄位。

選用參數包括 --expiration--description--time_partitioning_expiration--destination_kms_key--label

如果您要在非預設的專案中建立資料表,請使用下列格式將專案 ID 新增至資料集:project_id:dataset

本文不示範 --destination_kms_key。如要深入瞭解此標記的使用資訊,請參閱客戶管理的加密金鑰

輸入下列指令,建立含有結構定義的空白叢集資料表:

bq mk \
--table \
--expiration integer1 \
--schema schema \
--time_partitioning_type=DAY \
--time_partitioning_field partition_column \
--clustering_fields cluster_columns \
--time_partitioning_expiration integer2 \
--description "description" \
--label key:value,key:value \
project_id:dataset.table

其中:

  • 「integer1」是資料表的預設生命週期 (以秒為單位),最小值是 3,600 秒 (1 小時)。到期時間為目前的世界標準時間加上整數值。如果您在建立時間分區資料表時設定了資料表到期時間,系統會忽略資料集的預設資料表到期時間設定。設定這個值會在指定時間到期後刪除資料表和所有分區。
  • 「schema」是格式為 column:data_type,column:data_type 的內嵌結構定義,或您本機電腦上的 JSON 結構定義檔案路徑。
  • 「partition_column」是用來建立分區資料表的 TIMESTAMPDATE 資料欄名稱。若您建立分區資料表,則無須指定 --time_partitioning_type=DAY 標記。
  • 「cluster_columns」是以逗號分隔的清單,最多包含四個叢集欄位。
  • 「integer2」是資料表分區的預設生命週期 (以秒為單位)。這個值沒有下限。到期時間為分區日期加上整數值。分區到期時間與資料表的到期時間無關,並不會覆寫資料表到期時間。如果您設定的分區到期時間比資料表到期時間長,則以資料表到期時間為優先。
  • 「description」是置於括號中的資料表說明。
  • 「key:value」是代表標籤的「金鑰:值」組合。您可以用逗號分隔的清單輸入多個標籤。
  • 「project_id」是您的專案 ID。
  • 「dataset」是專案中的資料集。
  • 「table」是您所建立的分區資料表名稱。

您在指令列中指定結構定義時,無法加入 RECORD (STRUCT) 類型和資料欄說明,也不能指定資料欄模式。所有模式都預設為 NULLABLE。如要包含說明、模式和 RECORD 類型,請改為提供 JSON 結構定義檔案

範例:

輸入下列指令,在預設專案中的 mydataset 內建立名為 myclusteredtable 的叢集資料表,這個資料表是分區式資料表 (依 TIMESTAMP 資料欄分區而成)。分區到期時間設為 86,400 秒 (1 天)、資料表到期時間設為 2,592,000 秒 (1 個月 - 30 天)、說明設為 This is my clustered table,而標籤則設為 organization:development。這個指令使用 -t 捷徑而非 --table

結構定義以內嵌方式指定,例如:timestamp:timestamp,customer_id:string,transaction_amount:float,所指定的叢集欄位 customer_id 用來叢集分區之用。

bq mk -t \
--expiration 2592000 \
--schema 'timestamp:timestamp,customer_id:string,transaction_amount:float' \
--time_partitioning_field timestamp \
--clustering_fields customer_id \
--time_partitioning_expiration 86400  \
--description "This is my clustered table" \
--label org:dev \
mydataset.myclusteredtable

輸入下列指令,在 myclusteredtable 而非您的預設專案中建立名為 myotherproject 的叢集資料表。此資料表是擷取時間分區資料表。分區到期時間設為 259,200 秒 (3 天)、說明設為 This is my partitioned table,而標籤則設為 organization:development。指令使用 -t 縮寫而非 --table。這個指令不會指定資料表到期時間。如果資料集有預設資料表到期時間,系統會直接套用這個時間。如果資料集沒有預設資料表到期時間,資料表將永不過期,但分區會在 3 天內過期。

結構定義在本機 JSON 檔案 /tmp/myschema.json 中指定。customer_id 欄位用於叢集分區。

bq mk -t \
--expiration 2592000 \
--schema /tmp/myschema.json \
--time_partitioning_type=DAY \
--clustering_fields=customer_id \
--time_partitioning_expiration 86400  \
--description "This is my partitioned table" \
--label org:dev \
myotherproject:mydataset.myclusteredtable

建立資料表後,您可以更新分區資料表的資料表到期時間分區到期時間說明標籤

API

使用指定 timePartitioningclustering.fieldsschema 屬性的已定義資料表資源呼叫 tables.insert 方法。

Go

在試行這個範例之前,請按照 BigQuery 快速入門導覽課程:使用用戶端程式庫中的 Go 相關設定指示操作。詳情請參閱 BigQuery Go API 參考說明文件

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "timestamp", Type: bigquery.TimestampFieldType},
	{Name: "origin", Type: bigquery.StringFieldType},
	{Name: "destination", Type: bigquery.StringFieldType},
	{Name: "amount", Type: bigquery.NumericFieldType},
}
metaData := &bigquery.TableMetadata{
	Schema: sampleSchema,
	TimePartitioning: &bigquery.TimePartitioning{
		Field:      "timestamp",
		Expiration: 90 * 24 * time.Hour,
	},
	Clustering: &bigquery.Clustering{
		Fields: []string{"origin", "destination"},
	},
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metaData); err != nil {
	return err
}

從查詢結果建立叢集資料表

您可以透過下列兩種方式,從查詢結果建立叢集資料表:

您可以藉由查詢分區資料表或非分區資料表的方式建立叢集資料表,但不能利用查詢結果將現有的資料表變更為叢集資料表。

當您從查詢結果建立叢集資料表時,必須使用標準 SQL 語法。系統目前不支援使用舊版 SQL 查詢叢集資料表或將查詢結果寫入叢集資料表。

主控台

使用主控台 BigQuery 網頁版 UI 查詢資料時,不能指定目的地資料表的叢集選項,除非您使用 DDL 陳述式。詳情請參閱使用資料定義語言陳述式

傳統版 UI

使用 BigQuery 傳統網頁版 UI 查詢資料時,不能指定目的地資料表的叢集選項,除非您使用 DDL 陳述式。詳情請參閱使用資料定義語言陳述式

CLI

輸入下列指令,從查詢結果中建立新的叢集目的地資料表:

bq --location=location query \
--use_legacy_sql=false 'query'

其中:

  • 「location」是您位置的名稱,--location 是選用標記。舉例來說,如果您在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
  • 「query」是採用標準 SQL 語法的查詢。目前您無法使用舊版 SQL 查詢叢集資料表或是將查詢結果寫入叢集資料表。該查詢可包含 CREATE TABLE DDL 陳述式,以指定建立叢集資料表的選項。您可以使用 DDL 取代指定個別指令列標記的做法。

範例:

輸入下列指令,將查詢結果寫入 mydataset 中名稱為 myclusteredtable 的叢集目的地資料表,mydataset 位於您的預設專案內。查詢會從非分區資料表 mytable 中擷取資料,資料表的 customer_id 資料欄用於叢集資料表;資料表的 timestamp 資料欄用於建立分區資料表。

bq query --use_legacy_sql=false \
'CREATE TABLE
   mydataset.myclusteredtable
 PARTITION BY
   DATE(timestamp)
 CLUSTER BY
   customer_id AS
 SELECT
   *
 FROM
   `mydataset.mytable`'

API

如要將查詢結果儲存到叢集資料表,請呼叫 jobs.insert 方法、設定 query 工作,然後加入可建立叢集資料表的 CREATE TABLE DDL 陳述式。

工作資源jobReference 區段,於 location 屬性中指定您的位置。

於載入資料時建立叢集資料表

您載入資料到一個新的資料表時,可透過指定叢集欄的方式來建立叢集資料表。您不必在資料載入資料表之前,事先建立空白的資料表,因為您可以同時建立叢集資料表並載入資料。

要深入瞭解如何載入資料,請參閱將資料載入 BigQuery 的簡介

如要在定義載入工作時定義叢集,請進行以下操作:

API

如要在透過載入工作建立資料表時定義叢集設定,您可以填入資料表的 Clustering 屬性。

Go

在試行這個範例之前,請按照 BigQuery 快速入門導覽課程:使用用戶端程式庫中的 Go 相關設定指示操作。詳情請參閱 BigQuery Go API 參考說明文件

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/sample-transactions/transactions.csv")
gcsRef.SkipLeadingRows = 1
gcsRef.Schema = bigquery.Schema{
	{Name: "timestamp", Type: bigquery.TimestampFieldType},
	{Name: "origin", Type: bigquery.StringFieldType},
	{Name: "destination", Type: bigquery.StringFieldType},
	{Name: "amount", Type: bigquery.NumericFieldType},
}
loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
loader.TimePartitioning = &bigquery.TimePartitioning{
	Field: "timestamp",
}
loader.Clustering = &bigquery.Clustering{
	Fields: []string{"origin", "destination"},
}
loader.WriteDisposition = bigquery.WriteEmpty

job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}

if status.Err() != nil {
	return fmt.Errorf("Job completed with error: %v", status.Err())
}

控管叢集資料表的存取權

您不能直接指派資料表或分區的存取權控管權限。您可以授予的最低 BigQuery 資源存取層級為資料集層級。如要設定資料表和檢視表的存取權,請在資料集層級或更高層級授予 Cloud IAM 角色。

在資料集層級授予角色,即可讓實體對特定資料集裡的資料表和檢視表執行指定作業。如要瞭解如何調整資料集存取權控管設定,請參閱控管資料集存取權一文。

您也可以在 Google Cloud Platform 資源階層中的更高層級 (例如專案、資料夾或機構層級) 授予 Cloud IAM 角色。在較高層級授予角色可讓實體存取更多資源。舉例來說,在專案層級將特定角色授予實體,可讓該實體擁有適用於專案中所有資料集的權限。如要進一步瞭解如何授予資源存取權,請參閱 Cloud IAM 說明文件中的授予、變更及撤銷資源的存取權一文。

您也可以建立 Cloud IAM 自訂角色。建立自訂角色之後,您就能依據要讓實體執行的特定作業授予權限。

如要進一步瞭解角色和權限,請參閱下列文章:

使用叢集資料表

取得叢集資料表的相關資訊

您可以透過下列方式取得資料表的相關資訊:

  • 使用 GCP Console 或 BigQuery 傳統網頁版 UI
  • 使用 bq show CLI 指令
  • 呼叫 tables.get API 方法
  • 查詢 INFORMATION_SCHEMA 檢視表 (Beta 版)

所需權限

您至少要具備 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 角色和權限,請參閱預先定義的角色和權限

獲得叢集資料表資訊

如要查看叢集資料表的相關資訊:

主控台

  1. 在「Resources」(資源) 窗格中,按一下資料集名稱,即可展開該資料集,然後點選您想要查看的資料表名稱。

  2. 按一下 [Details] (詳細資料)。這個頁面會顯示包含叢集資料欄的資料表詳細資料。

    資料表詳細資料

傳統版 UI

  1. 在導覽窗格中,按一下資料集左側的向下箭號圖示 向下箭號圖示 以展開下拉式選單,或按兩下資料集的名稱,畫面上會顯示資料集中的資料表和檢視表。

  2. 按一下資料表名稱。

  3. 按一下 [Details] (詳細資料)。「Table Details」(資料表詳細資料) 頁面會顯示包含叢集資料欄的資料表詳細資料。

    叢集資料表細節

CLI

發出 bq show 指令以顯示所有資料表資訊。使用 --schema 標記,可以只顯示資料表結構定義資訊。--format 標記可用來控管輸出內容。

如果您要取得非預設專案中資料表的相關資訊,請使用下列格式將專案 ID 新增至資料集:project_id:dataset

bq show \
--schema \
--format=prettyjson \
project_id:dataset.table

其中:

  • 「project_id」是您的專案 ID。
  • 「dataset」是資料集名稱。
  • 「table」是資料表名稱。

範例:

輸入下列指令,以顯示關於 mydatasetmyclusteredtable 的所有資訊。mydataset 位於您的預設專案中。

bq show --format=prettyjson mydataset.myclusteredtable

輸出應如下所示:

{
  "clustering": {
    "fields": [
      "customer_id"
    ]
  },
...
}

API

呼叫 bigquery.tables.get 方法,並提供所有相關參數。

使用 INFORMATION_SCHEMA 取得叢集資料表資訊 (Beta 版)

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

您可以查詢 INFORMATION_SCHEMA.TABLESINFORMATION_SCHEMA.TABLE_OPTIONS 檢視表,藉以擷取專案中資料表與檢視表的相關中繼資料,也可以查詢 INFORMATION_SCHEMA.COLUMNSINFORMATION_SCHEMA.COLUMN_FIELD_PATHS 檢視畫面,以擷取資料表中資料欄 (欄位) 的相關中繼資料。

對於叢集資料表,您可以在 INFORMATION_SCHEMA.COLUMNS 檢視畫面中查詢 CLUSTERING_ORDINAL_POSITION 資料欄,以擷取叢集資料欄的相關資訊。

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 Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP 主控台

  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 主控台

  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 說明文件中的資料表資源頁面。

示例

示例 1:

以下示例查詢 mydataset 檢視表,以擷取預設專案 (myproject) 的 INFORMATION_SCHEMATA.TABLE_OPTIONS 中所有資料表的預設資料表到期時間。

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

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

執行查詢:

主控台

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

    前往 GCP 主控台

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

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

執行查詢:

主控台

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

    前往 GCP 主控台

  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 說明文件中的資料集資源頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 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 網頁版 UI。

    前往 GCP 主控台

  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 說明文件中的資料集資源頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 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 網頁版 UI。

    前往 GCP 主控台

  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        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  

在資料集裡列出叢集資料表

您可以透過以下方式列出資料集中的叢集資料表:

  • 使用 GCP Console 或 BigQuery 傳統網頁版 UI
  • 使用 bq ls CLI 指令
  • 呼叫 tables.list API 方法
  • 使用用戶端程式庫

列出叢集資料表須具備的權限以及列出資料表的步驟與處理分區資料表時相同,如要進一步瞭解如何列出資料表,請參閱在資料集中列出分區資料表一節。

開發中的功能

下列功能正在開發中,目前在 Alpha 版中無法使用:

  • 支援原生資料表 (非分區) 的叢集。
  • 減少在叢集資料欄使用篩選器的特定類型查詢成本。

後續步驟

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

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

這個網頁
需要協助嗎?請前往我們的支援網頁