查詢叢集資料表

查詢叢集資料表

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

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

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

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

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

  • 使用 Cloud Console 或 BigQuery 傳統網頁版 UI
  • 使用指令列工具的 bq query 指令
  • 呼叫 jobs.insert API 方法並設定查詢工作
  • 使用用戶端程式庫

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

所需權限

您至少必須具備 bigquery.tables.getData 權限,才能查詢資料表。

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

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

此外,當具備 bigquery.datasets.create 權限的使用者建立資料集時,會獲得該資料集的 bigquery.dataOwner 存取權。bigquery.dataOwner 存取權可讓使用者查詢資料集中的資料表和檢視表。

此外,您還必須取得 bigquery.jobs.create 權限才能執行查詢工作。以下是具有 bigquery.jobs.create 權限的預先定義 Cloud IAM 角色:

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

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

最佳做法

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

範例中使用的資料表樣本

本頁範例中使用的資料表樣本是使用 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

後續步驟