查询聚簇表

查询聚簇表

在 BigQuery 中创建聚簇表时，系统会根据表架构中一个或多个列的内容自动整理表数据。您指定的列用于共置相关数据。使用多个列对表进行聚簇时，指定列时依照的顺序很重要。指定列时的先后顺序决定了数据的排序顺序。

要在针对聚簇表运行查询时优化性能，请使用表达式对一个聚簇列或多个聚簇列（按指定聚簇列时的顺序）进行过滤。与仅对非聚簇列进行过滤的查询相比，对聚簇列进行过滤的查询的效果通常更好。

BigQuery 会根据聚簇列中的值对聚簇表中的数据排序，并将其整理为多个块。

当您提交包含对聚簇列进行过滤的查询时，BigQuery 会使用聚簇信息高效地确定某个块是否包含与查询相关的任何数据。这样一来，BigQuery 就可以仅扫描相关块（此过程称为块剪除）。

您可以通过以下方式查询聚簇表：

  • 使用 Cloud Console
  • 使用 bq 命令行工具的 bq query 命令
  • 调用 jobs.insert API 方法并配置查询作业
  • 使用客户端库

目前，您只能对聚簇表使用标准 SQL。

Go

在尝试此示例之前，请按照《BigQuery 快速入门：使用客户端库》中的 Go 设置说明进行操作。如需了解详情，请参阅 BigQuery Go API 参考文档。

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 参考文档。

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 参考文档。

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.
query_job = client.query(sql, job_config=job_config)  # Make an API request.
query_job.result()  # Wait for the 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."
    )

所需权限

如需查询表，您至少必须具有 bigquery.tables.getData 权限。

以下预定义的 IAM 角色包含 bigquery.tables.getData 权限：

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

此外，如果用户具有 bigquery.datasets.create 权限，则当该用户创建数据集时，系统会为其授予该数据集的 bigquery.dataOwner 访问权限。借助 bigquery.dataOwner 访问权限，用户可以查询数据集内的表和视图。

如需运行查询作业，您还必须获得 bigquery.jobs.create 权限。以下预定义的 IAM 角色包含 bigquery.jobs.create 权限：

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

如需详细了解 BigQuery 中的 IAM 角色和权限，请参阅访问权限控制。

最佳做法

要在查询聚簇表时获得最高性能，请使用以下最佳做法。

示例中使用的示例表

本页的示例中使用的示例表是一个使用 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`

按排序顺序过滤聚簇列

指定过滤条件时，请使用按排序顺序过滤聚簇列的表达式。如需获得聚簇的好处，请将所有聚簇列或部分列按从左到右的顺序进行排序，从第一列开始。例如，如果列排序顺序为 A、B、C，则过滤 A 和 B 的查询可能会从聚簇中受益，但过滤 B 和 C 的查询却不会受益。过滤条件表达式中列名称的排序不会影响性能。

以下查询会添加一个过滤表达式，该表达式先按 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

后续步骤