クラスタ化テーブルのクエリ

クラスタ化テーブルのクエリ

BigQuery でクラスタ化テーブルを作成すると、テーブルのスキーマ内の 1 つ以上の列の内容に基づいてテーブルのデータが自動的に編成されます。指定した列は、関連するデータを同じ場所に配置するために使用されます。複数の列を使用してテーブルをクラスタ化する場合は、指定する列の順序が重要です。指定した列の順序によって、データの並べ替え順序が決まります。

クラスタ化テーブルに対してクエリを実行するときのパフォーマンスを最適化するには、クラスタ化列を指定した際の順序で、1 つまたは複数のクラスタ化列をフィルタリングする式を使用します。通常、クラスタ化列でフィルタリングするクエリは、非クラスタ化列のみでフィルタリングするクエリよりも優れたパフォーマンスを発揮します。

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`

並べ替え順序でクラスタ化列をフィルタリングする

フィルタを指定するときは、並べ替え順序でクラスタ化列をフィルタリングする式を使用します。クラスタ化の利点を活用するには、クラスタ化したすべての列または列のサブセットを左から右に並べて、最初の列から順に指定します。たとえば、列の並べ替え順が ABC なら、AB でフィルタリングを行うクエリはクラスタ化の恩恵を受けられますが、BC でフィルタリングを行うクエリは恩恵を受けられません。フィルタ式内の列名の順序はパフォーマンスに影響しません。

次のクエリには、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

次のステップ