查詢分群資料表

在 BigQuery 查詢分群資料表時，篩選分群資料欄可提升效能並降低成本。當查詢篩選一或多個分群資料欄時，BigQuery 會使用分群資訊，只掃描相關區塊，這個程序稱為「區塊剪除」。

本文說明查詢分群資料表的權限和最佳做法，包括篩選器中分群資料欄順序的重要性。您只能使用 GoogleSQL 查詢分群資料表。

所需權限

如要執行查詢工作，您必須具備執行查詢工作的專案 bigquery.jobs.create Identity and Access Management (IAM) 權限。

下列各項預先定義的 IAM 角色，都包含執行查詢工作所需的權限：

• roles/bigquery.admin
• roles/bigquery.jobUser
• roles/bigquery.user

此外，您也必須具備查詢參照的所有資料表和檢視表的 bigquery.tables.getData 權限。此外，查詢檢視表時，您必須具備所有基礎資料表和檢視表的這項權限。不過，如果您使用授權檢視表或授權資料集，則不需要基礎來源資料的存取權。

下列各項預先定義的 IAM 角色，都包含查詢參照的所有資料表和檢視表所需權限：

• roles/bigquery.admin
• roles/bigquery.dataOwner
• roles/bigquery.dataEditor
• roles/bigquery.dataViewer

如要進一步瞭解 BigQuery 的 IAM 角色，請參閱「預先定義的角色和權限」。

查詢分群資料表

您可以透過下列方式查詢分群資料表：

• 使用 Google Cloud 控制台
• 在 bq 指令列工具中使用 bq query 指令
• 呼叫 jobs.insert 方法並設定查詢工作
• 使用用戶端程式庫

Go

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

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"
)

// queryClusteredTable demonstrates querying a table that has a clustering specification.
func queryClusteredTable(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	q := client.Query(fmt.Sprintf(`
	SELECT
	  COUNT(1) as transactions,
	  SUM(amount) as total_paid,
	  COUNT(DISTINCT destination) as distinct_recipients
    FROM
	  `+"`%s.%s`"+`
	 WHERE
	    timestamp > TIMESTAMP('2015-01-01')
		AND origin = @wallet`, datasetID, tableID))
	q.Parameters = []bigquery.QueryParameter{
		{
			Name:  "wallet",
			Value: "wallet00001866cb7e0f09a890",
		},
	}
	// Run the query and print results when the query job is completed.
	job, err := q.Run(ctx)
	if err != nil {
		return err
	}
	status, err := job.Wait(ctx)
	if err != nil {
		return err
	}
	if err := status.Err(); err != nil {
		return err
	}
	it, err := job.Read(ctx)
	for {
		var row []bigquery.Value
		err := it.Next(&row)
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintln(w, row)
	}
	return nil
}

Java

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Java 設定操作說明進行操作。詳情請參閱 BigQuery Java API 參考文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;

public class QueryClusteredTable {

  public static void runQueryClusteredTable() throws Exception {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    queryClusteredTable(projectId, datasetName, tableName);
  }

  public static void queryClusteredTable(String projectId, String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      String sourceTable = "`" + projectId + "." + datasetName + "." + tableName + "`";
      String query =
          "SELECT word, word_count\n"
              + "FROM "
              + sourceTable
              + "\n"
              // Optimize query performance by filtering the clustered columns in sort order
              + "WHERE corpus = 'romeoandjuliet'\n"
              + "AND word_count >= 1";

      QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

      TableResult results = bigquery.query(queryConfig);

      results
          .iterateAll()
          .forEach(row -> row.forEach(val -> System.out.printf("%s,", val.toString())));

      System.out.println("Query clustered table performed successfully.");
    } catch (BigQueryException | InterruptedException e) {
      System.out.println("Query not performed \n" + e.toString());
    }
  }
}

Python

在試用這個範例之前，請先按照「使用用戶端程式庫的 BigQuery 快速入門導覽課程」中的 Python 設定操作說明進行操作。詳情請參閱 BigQuery Python API 參考文件。

如要向 BigQuery 進行驗證，請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the destination table.
# table_id = "your-project.your_dataset.your_table_name"

sql = "SELECT * FROM `bigquery-public-data.samples.shakespeare`"
cluster_fields = ["corpus"]

job_config = bigquery.QueryJobConfig(
    clustering_fields=cluster_fields, destination=table_id
)

# Start the query, passing in the extra configuration.
client.query_and_wait(
    sql, job_config=job_config
)  # Make an API request and wait for job to complete.

table = client.get_table(table_id)  # Make an API request.
if table.clustering_fields == cluster_fields:
    print(
        "The destination table is written using the cluster_fields configuration."
    )

最佳做法

如要讓分群資料表的查詢發揮最佳效能，請採用下列最佳做法。

為提供背景資訊，最佳做法範例中使用的範例資料表，是使用 DDL 陳述式建立的分群資料表。DDL 陳述式會建立名為 ClusteredSalesData 的資料表。資料表會依下列資料欄分群：customer_id、product_id、order_id，並依此順序排序。

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

依排列順序篩選分群欄

指定篩選條件時，請使用依排列順序篩選分群資料欄的運算式。排序順序是 CLUSTER BY 子句中指定的資料欄順序。如要享有叢集的好處，請按照由左至右的排列順序，加入一或多個叢集資料欄，從第一個資料欄開始。在多數情況下，第一個叢集資料欄在區塊剪除中最有效，其次是第二個資料欄，第三個資料欄則最不有效。您仍可在查詢中單獨使用第二或第三欄，但區塊剪除的效果可能不佳。篩選運算式中的資料欄名稱順序不會影響效能。

下列範例會查詢在上述範例中建立的 ClusteredSalesData 分群資料表。查詢包含篩選運算式，會先篩選 customer_id，再篩選 product_id。這項查詢會按照排序順序 (CLUSTER BY 子句中指定的資料欄順序) 篩選分群資料欄，藉此提升效能。

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

資料表安全性

如要控管 BigQuery 資料表的存取權，請參閱「使用 IAM 控管資源存取權」。

後續步驟

• 如需執行查詢的詳細資訊，請參閱執行互動式與批次查詢一文。
• 如要瞭解如何建立及使用分群資料表，請參閱「建立及使用分群資料表」。
• 如需 BigQuery 中的分區資料表支援總覽，請參閱分區資料表簡介一文。
• 如要瞭解如何建立分區資料表，請參閱「建立分區資料表」。