G-genの杉村です。Google Cloud（旧称 GCP）のフルマネージドのデータウェアハウスである BigQuery には、パフォーマンスの向上に当たり パーティション と クラスタリング という重要な概念があります。それぞれの仕組みや使い分けを解説していきます。

パーティション
クラスタリング
パーティション VS クラスタリング
- パーティションとクラスタリングの違い
- パーティションとクラスタリングの使い分け
パーティション・クラスターのレコメンデーション
その他
- 注意点
- 参考情報

パーティション

パーティションとは

パーティション とは、 BigQuery の一つのテーブルを、特定の列の値を基準にして内部的に複数パートに分割する機能です。
これによりクエリ時にスキャンする範囲を狭め、パフォーマンス向上とスキャン料金の節約ができます。

分割基準として使う列をテーブル作成時に指定することで、パーティション分割されたテーブルを作成することができます。
パーティション列は一つしか指定できません。

テーブル作成方法は以下のドキュメントの通りです。

パーティション分割テーブルの作成

例として、以下のような DDL で作成することができます。

CREATE TABLE
  mydataset.purchase_tran (purchase_dt DATE, prod_id STRING, prod_name STRING, store_id INT64, store_name STRING)
PARTITION BY
  purchase_dt

このように作成されたテーブルで以下のようにクエリを実行すると、 BigQuery は当該の値を含んだパーティションだけをスキャンします。

SELECT
  *
FROM
  mydataset.purchase_tran
WHERE
  purchase_dt = "2021-10-01"

パーティションフィルタ要件 (Partition filter requirements)

パーティション分割テーブルの作成時にパーティションフィルタ要件 (Partition filter requirements) を有効化することで、WHERE 句でパーティション列を使ったフィルタがないクエリをエラーとして拒否することができます。

これを設定することで、テーブルの利用者は、必ずパーティションが効くクエリしか投げられなくなりますので、テーブルに対する不用意なフルスキャンを予防することができます。

メリット

パーティションが無い場合、BigQuery はテーブル全体をフルスキャンしますので、それに比べて大幅にスキャン範囲を節約でき、料金と時間の節約となります。

また前述のパーティションフィルタ要件を使えば、ユーザーが大規模なテーブル全体に対して誤ってクエリを実行した場合の費用の急増を防ぐ効果もあります。

パーティションの分割基準

時間の列

TIMESTAMP 型 / DATE 型 / DATETIME 型 のいずれかの列をパーティション列として指定可能です。

TIMESTAMP 列と DATETIME 列では、パーティションを時間単位、日単位、月単位、年単位のいずれかで作成できます。
DATE 列の場合、パーティションは日単位、月単位、年単位で作成できます。
※いずれもデフォルトは日単位

(DDL の例)

CREATE TABLE
  mydataset.newtable (transaction_id INT64, transaction_date DATE)
PARTITION BY
  transaction_date

取り込み時間

BigQuery がデータを取り込んだタイムスタンプに基づいてテーブルが分割されます。
分割粒度は、時間単位、日単位、月単位、年単位から選択できます。
※デフォルトは日単位

テーブル作成時には _PARTITIONTIME という疑似列 (仮想列) をパーティション列として指定します。

(DDL の例)

CREATE TABLE
  mydataset.newtable (transaction_id INT64)
PARTITION BY
  _PARTITIONDATE

整数範囲の列

分割基準列として INTEGER 型の列を指定可能です。
また分割の開始値・終了値と分割の間隔を指定できます。

(DDL の例)

CREATE TABLE
  mydataset.newtable (customer_id INT64, date1 DATE)
PARTITION BY
  RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 100, 10))

この例では customer_id 列でパーティショニングし、開始値 0、終了値 100、間隔 10 としています。
このようにしたとき customer_id が 0 から 9 の行が最初のパーティションに入り、 10 から 19 が次のパーティションに入ります。この処理が 99 まで続きます。
この範囲外の値は、__UNPARTITIONED__ という名前のパーティションに入ります。
customer_id が NULL の行は __NULL__ という名前のパーティションに入ります。

※ テーブルにどんなパーティションが存在しているかはテーブルのメタデータから取得できる他、 bq コマンドを使ってパーティションの内容を取得できます。

パーティションの管理

時間単位または取り込み時間で分割したテーブルの場合、パーティションの 有効期限 を設定できます。

指定した有効期限が過ぎたらデータは自動的に削除され、このとき BigQuery のユーザーに割り当てれたリソースは消費されません。
うまく使えば、ハウスキーピングのジョブをユーザーが作成する必要がなくなります。

パーティションの有効期限を設定する

なおテーブル単位で有効期限が設定されている場合は、テーブルの有効期限が優先されます。

パーティションの有効期限はテーブル作成時に指定するほか、作成後にも変更できます。

クラスタリング

クラスタリングとは

クラスタリング とは、 BigQuery のテーブルの特定の列の値に基づいてテーブルのデータをソート・内部的に近い位置に配置しすることでフィルタや集計のクエリを高速化する機能です。
テーブル作成時に特定の列をクラスタ化列として指定します。

クラスタリングを利用すると指定した列の値に基づいて行がソートされるため、 WHERE 句でこの列に基づいてフィルタするクエリを投げた際、不要なデータのスキャンをスキップすることができます。
またクラスタ化に指定した列で GROUP BY して集計するクエリの場合、行がソートされ近い位置に配置されているのでパフォーマンスが向上します。

クラスタリングはパーティションと併用することも可能です。
クラスタリングとパーティショニングを併用すると、データはパーティション分割された後にクラスタ化されます。

またクラスタ化列は複数 (最大 4 列) まで指定可能です。
複数指定した場合、指定の順番が重要になります。まず最初に指定した列で行がソートされ、次にその中で2番めに指定した列でソート、次に3番目... というようになります。

クラスタリングされたテーブルを作成する方法は、以下のドキュメントのとおりです。

クラスタ化テーブルの作成と使用

例として以下のような DDL でクラスタリングされたテーブルを作成できます。

CREATE TABLE
  mydataset.purchase_tran_cls (purchase_dt DATE, prod_id STRING, prod_name STRING, store_id INT64, store_name STRING)
PARTITION BY
  purchase_dt
CLUSTER BY
  prod_id

また既存テーブルをクラスタリングすることも可能ですが、仕様に注意する必要があります。

クラスタ化に指定する列

クラスタ化に指定する列は、一意の値を大量に含む (カーディナリティの高い) 列が推奨されます。
そのほうが、ソートによるスキャン範囲のスキップの効果が高く期待されるためです。

また組み合わせて使われることの多い複数の列をクラスタ化すると効果が期待できます。
先の記述の通り、順番に注意して WHERE で指定あるいは GROUP BY される複数列をクラスタ化列として指定すると、効果が大きいでしょう。

自動再クラスタリング

クラスタリングのメンテナンスは自動で行われます。
データが新規で追加されたり、変更されたりした場合でも、自動でクラスタリングが行われます。
一般的なデータウェアハウスで必要とされる VACUUM といった処理は不要です。

スロットなどのリソースが消費されることもなく、自動的・透過的に行われるため、ユーザーが意識する必要はありません。

パーティション VS クラスタリング

パーティションとクラスタリングの違い

パーティションとクラスタリングは併用できますが、どういうケースでどちらを使えばいいのか、どういう列をどちらに指定すればいいのか、使い分けに迷うときもあるかもしれません。

まず、パーティションとクラスタリングの違いは、以下のような点にあります。

パーティショニングでは実際のクエリ実行前に dry-run でスキャン量の試算ができる (料金試算が可能) 。クラスタリングでは、試算はテーブル単位/パーティション単位で行われ、実際のスキャン量は見積もりより小さくなる可能性がある。
パーティショニングでは有効期限の設定ができる。
パーティショニングでは分割粒度 (時間・日・月・年・整数範囲) の選択ができる。
パーティショニングでは1つの列しか指定できない。クラスタリングでは4列まで指定できる。
パーティショニングでは特定の型の列しか指定できないが、クラスタリングには型の制限はない。

パーティションとクラスタリングの使い分け

まずはパーティションを適用できる列があるかどうかを検討します。
以下のような場合、パーティションの利用を検討します。

日付/時間列があり、日付/時間でフィルタするクエリがある
パーティションの有効期限設定を使いたい
dry-run でスキャン量 (費用) 見積もりを厳密に行いたい

パーティショニングの検討をした後、以下のような場合にクラスタリングを採用します。

複数列に対してフィルタ/集計するクエリがある
カーディナリティが大きく一意の列がある
パーティションを使うと分割粒度が小さくなりすぎ、1テーブルあたりの上限である 4,000 パーティションを超えてしまう
テーブル内の大部分のパーティションが頻繁に (たとえば、数分ごとに) 変更されるミューテーションオペレーションが生成される場合、パーティションは避けてクラスタリングを利用する (上限があるため)

多くの場合で、パーティションとクラスタリングを併用することでスキャン料を節減し、パフォーマンスとコスト効率を向上させることができるはずです。

パーティション・クラスターのレコメンデーション

BigQuery では、過去のワークロードに基づいてテーブルの適切なパーティショニング・クラスタリングを推奨してくれます。なお当機能は2023年5月現在、Preview 中ですのでご注意ください。

Recommender API が過去30日間の実績を機械学習で分析し、テーブルの適切なパーティショニング・クラスタリング設定を提示します。対象テーブル、対象列、またどのくらいのスロット時間が節約できるかの見込みが表示されます。

推奨の対象となるテーブルは「パーティショニング無し・クラスタリング無し」「パーティショニング有り・クラスタリング無し」のテーブルです。

一方で 10 GB 以下のテーブルや既にパーティションとクラスターを両方設定済みのテーブル、また過去30日以内に読み取りされていないテーブルなどは対象外となります。

推奨はコンソール、gcloud、REST API で確認可能です。コンソールでは、画面右上の電球マークから確認できます。

参考: View partition and cluster recommendations

その他

注意点

1テーブルが持てるパーティション数には上限があり、1テーブルにつき10,000パーティションまでです。従来は4,000が最大値でしたが、2024年5月29日のアップデートで10,000に変更されました。

これは、時間単位であれば 10,000 時間 = 約416日 = 約13ヶ月間であり、日単位での分割であれば 10,000日 = 約322ヶ月 = 約27年です。

パーティション数の上限に達すると、ジョブがエラーとなります。パーティションに有効期限を設けてデータが自動削除されるようにするなどの考慮を行いましょう。

また「1 つのジョブで変更されるパーティションの数」や「1 日の取り込み時間パーティション分割テーブルあたりのパーティションの変更回数」「1 日の列パーティション分割テーブルあたりのパーティション変更数」などにも上限があります。バッチ処理がこれに抵触していないかは、十分注意する必要があります。

参考: Quotas and limits - Partitioned tables

以下は、10,001 個目のパーティションを追加しようとした場合のエラーメッセージです。

Resources exceeded during query execution: Table my-project:my_dataset.my_table will have 10001 partitions when the job finishes, exceeding limit 10000. If partitions were recently expired, it may take some time to be reflected unless explicitly deleted.