查詢叢集資料表

查詢叢集資料表

在 BigQuery 中建立叢集資料表時,資料表資料會依照資料表結構定義中的一或多個資料欄內容進行自動編排。您指定的資料欄會用來將相關資料放在相同位置。使用多個資料欄為資料表組成叢集時,您指定的資料欄的順序非常重要。指定的資料欄順序決定了資料的排序順序。

對叢集資料表執行查詢時,如要達到最佳效能,使用的運算式要能依照指定的叢集資料欄順序,針對一個叢集資料欄或多個叢集資料欄進行篩選。就效能而言,叢集資料欄的篩選查詢通常勝過於非叢集資料欄的篩選查詢。

BigQuery 根據叢集資料欄的值在叢集資料表中進行資料排序,並將資料組織成區塊。

當您提交叢集資料欄上包含有篩選器的查詢時,BigQuery 使用叢集資訊來有效地確定區塊是否包含任何與查詢相關的資料。這會使得 BigQuery 只掃描相關區塊,而這個過程就稱之為區塊修剪。

您可以透過以下方式查詢叢集資料表:

  • 使用 GCP 主控台或傳統 BigQuery 網頁版 UI
  • 使用指令列工具的 bq query 指令
  • 呼叫 jobs.insert API 方法並設定查詢工作

目前對叢集資料表只能使用標準 SQL

所需權限

在資料集層級查詢叢集資料表時,需要對資料表所屬的資料集有 READER 存取權。

如不使用資料集層級權限,您可以使用具備 bigquery.tables.getData 權限的專案層級身分與存取權管理 (IAM) 角色。 要讀取查詢的資料表中的資料,您需要擁有 bigquery.tables.getData 權限。所有預先定義的專案層級 IAM 角色都具備 bigquery.tables.getData 權限,除了 bigquery.userbigquery.jobUserbigquery.metadataViewer 這些角色以外。

您還必須取得 bigquery.jobs.create 權限才可執行查詢工作。下列預先定義的專案層級身分與存取權管理角色擁有 bigquery.jobs.create 權限:

如要進一步瞭解 BigQuery 中的身分與存取權管理角色和權限,請參閱存取權控管。想深入瞭解資料集層級角色,則請參閱資料集的原始角色一節。

最佳做法

如要讓叢集資料表的查詢達到最佳效能,請使用下列最佳做法。

範例中使用的資料表樣本

本頁範例中使用的資料表樣本是使用 DDL 陳述式建立的叢集資料表。DDL 陳述式會建立一個名為 ClusteredSalesData 的資料表。資料表的叢集是依照下列的資料欄順序:customer_idproduct_idorder_id

CREATE TABLE
  `mydataset.ClusteredSalesData`
PARTITION BY
  DATE(timestamp)
CLUSTER BY
  customer_id,
  product_id,
  order_id AS
SELECT
  *
FROM
  `mydataset.SalesData`

依照指定順序,對叢集資料欄進行篩選

指定篩選條件時,使用的運算式要能依照排序順序,對叢集資料欄進行篩選。

下列查詢含有的篩選運算式可依 customer_id 篩選,再依 product_id 篩選。這項查詢依照排序順序,對叢集資料欄進行篩選,因此可達到最佳效能。

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = 10000
  AND product_id LIKE 'gcp_analytics%'

下列查詢並未依照排序順序對叢集資料欄進行篩選。結果,無法達到最佳查詢效能。這項查詢先依 product_id 進行篩選,再依 order_id 進行篩選 (略過 customer_id)。

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  product_id LIKE 'gcp_analytics%'
  AND order_id = 20000

請勿在複合篩選運算式使用叢集資料欄

如果在複合篩選運算式使用叢集資料欄,就無法套用區塊修剪,從而無法達到最佳查詢效能。

舉例來說,下列查詢之所以不會修剪區塊,是因為篩選運算式中的函式使用了 customer_id 叢集資料欄。

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  CAST(customer_id AS STRING) = "10000"

如要修剪區塊,達到最佳查詢效能,請使用簡易篩選運算式,如下所示。在此例中,簡易篩選會套用在 customer_id 叢集資料欄。

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = 10000

不可比對叢集資料欄和其他資料欄

如果篩選運算式把叢集資料欄跟另一個資料欄 (叢集資料欄或非叢集資料欄) 進行比對,就無法達到最佳查詢效能,原因出在於無法套用區塊修剪。

下列查詢之所以沒有修剪區塊,是因為篩選運算式把 customer_id 叢集資料欄跟另一個 order_id 資料欄進行比對。

SELECT
  SUM(totalSale)
FROM
  `mydataset.ClusteredSalesData`
WHERE
  customer_id = order_id

後續步驟

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

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

這個網頁