查询聚簇表

查询聚簇表

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

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

BigQuery 会根据聚簇列中的值对聚簇表中的数据排序,并将这些数据整理为块的形式。

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

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

  • 使用 Cloud Console 或经典版 BigQuery 网页界面
  • 使用命令行工具的 bq query 命令
  • 调用 jobs.insert API 方法并配置查询作业
  • 使用客户端库

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

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());
        }
      }
    }

所需权限

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

以下预定义 Cloud IAM 角色可提供 bigquery.tables.getData 权限:

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

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

如需运行查询作业,您还必须具有 bigquery.jobs.create 权限。 以下预定义 Cloud IAM 角色可提供 bigquery.jobs.create 权限:

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

如需详细了解 BigQuery 中的 Cloud 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`
    

按照指定聚簇列时的顺序过滤聚簇列

指定过滤条件时,请使用按排序顺序过滤聚簇列的表达式。

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

后续步骤