查询聚簇表

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

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

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

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

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

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

目前，您只能对聚簇表使用 GoogleSQL。

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.
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.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 角色，请参阅预定义的角色和权限。

最佳做法

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

对于上下文，最佳实践示例中使用的示例表是使用 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 子句中指定的列顺序。要获得聚簇的优势，请将所有聚簇列或部分列从第一列开始按从左到右的顺序进行排序。例如，如果列排序顺序为 A、B、C，则按 A 和 B 过滤的查询可能会受益于聚簇，但按 B 和 C 过滤的查询则不会。过滤条件表达式中列名称的排序不会影响性能。

以下示例查询在上一个示例中创建的 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 中表的访问权限，请参阅表访问权限控制简介。

后续步骤

• 如需详细了解如何运行查询，请参阅运行交互式查询和批量查询。
• 如需了解如何创建和使用聚簇表，请参阅创建和使用聚簇表。
• 如需大致了解 BigQuery 中的分区表支持，请参阅分区表简介。
• 如需了解如何创建分区表，请参阅创建分区表。