建立及使用叢集資料表

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

限制

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

  • 叢集處理僅支援分區資料表
  • 只能透過標準 SQL 語法查詢叢集資料表,以及將查詢結果寫入叢集資料表之中。
  • 只有在資料表建立後,才可以指定叢集資料欄。
  • 叢集資料表建立完成後,您將無法修改叢集欄位。
  • 叢集資料欄必須是頂層的非重複資料欄,且屬於下列任一類型:
    • DATE
    • BOOL
    • GEOGRAPHY
    • INT64
    • NUMERIC
    • STRING
    • TIMESTAMP 如要進一步瞭解資料類型,請參閱標準 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 中建立資料表時,應指定要叢集的欄位。資料表建立完成後,您將無法修改叢集資料欄。您僅能針對分區資料表指定叢集資料欄。

叢集資料欄必須是頂層的非重複資料欄,且屬於下列任一項簡易資料類型: + DATE + BOOLEAN + GEOGRAPHY + INTEGER + NUMERIC + STRING + TIMESTAMP

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

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

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

主控台

  1. 在 Google Cloud Console 中,前往 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

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

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

    建立資料表按鈕。

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

    「Create table from」(使用下列資料建立資料表:) 選項。

  5. 在「Destination」(目的地) 下:

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

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

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

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

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

    如要建立擷取時間分區資料表,請選取 [Partition by ingestion time] (依擷取時間分區)

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

  9. (選用) 針對「Clustering order」(叢集處理順序),輸入一到四個以半形逗號分隔的資料欄名稱。

  10. (選用) 按一下 [Advanced options] (進階選項),並在「Encryption」(加密) 部分點選 [Customer-managed key] (客戶管理的金鑰),即可使用金鑰管理服務的金鑰。如果您保留 [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 Fields」(叢集欄位),輸入一到四個欄位名稱。
    • 在「Destination encryption」(目的地加密) 中保留 Default 選項。這個屬性適用於客戶管理的加密金鑰。根據預設,BigQuery 會加密靜態儲存的客戶內容

      分區資料表詳細資料

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

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

CLI

使用加上以下旗標的 mk 指令:

  • --table (或 -t 捷徑)。
  • --schema。您可利用內嵌方式或以 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。如要進一步瞭解如何使用 --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

輸入下列指令,在 myotherproject (而非預設專案) 中建立名為 myclusteredtable 的叢集資料表,此資料表是擷取時間分區資料表。分區到期時間設為 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 資源階層結構中的較高層級授予 Cloud IAM 角色,例如專案、資料夾或機構層級。在較高層級授予角色可讓實體存取更多資源。舉例來說,在專案層級將特定角色授予實體,可讓該實體擁有適用於專案中所有資料集的權限。關於如何授予資源存取權,請參閱 Cloud IAM 說明文件中的授予、變更及撤銷資源的存取權主題。

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

如要深入瞭解角色和授權,請參閱:

使用叢集資料表

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

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

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

所需權限

您至少要具備 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. 在 Google Cloud Console 中,前往「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 方法,並提供所有相關參數。

SQL

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

-- Set up a table with clustering.
CREATE TABLE myDataset.data (column1 INT64, column2 INT64)
PARTITION BY _PARTITIONDATE
CLUSTER BY column1, column2;

-- This query returns 1 for column1 and 2 for column2.
SELECT column_name, clustering_ordinal_position
FROM myDataset.INFORMATION_SCHEMA.COLUMNS;

您可以透過 INFORMATION_SCHEMA 中的 TABLESTABLE_OPTIONSCOLUMNSCOLUMN_FIELD_PATH 檢視表,查看更多資料表中繼資料。

列出資料集中的叢集資料表

您可以使用下列方法列出資料集中的叢集資料表:

  • 使用 Cloud Console 或 BigQuery 傳統網頁版 UI
  • 使用 bq ls CLI 指令
  • 呼叫 tables.list API 方法
  • 使用用戶端程式庫
  • INFORMATION_SCHEMA.COLUMNS 檢視表中查詢 CLUSTERING_ORDINAL_POSITION 資料欄

列出叢集資料表所需具備的權限及步驟,與分區資料表相同。有關如何列出資料表的詳情,請參閱列出資料集中的分區資料表一節。

開發中的功能

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

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

後續步驟

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

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

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