BigQueryのパーティショニングとクラスタリングによるクエリ容量最適化の効果を検証してみた

BigQueryを利用する際、クエリの実行速度や、クエリ実行により発生する料金が気になる方が多いのではないでしょうか。利用料金を理由に、BigQueryの積極的な活用ができないという意見もよく耳にします。

BigQueryには、パーティショニングとクラスタリングという機能があり、テーブル作成時にそれらを設定することで、クエリ実行時のパフォーマンスを向上させることができます。今回は、そのパーティショニングとクラスタリングの仕組みや効果を理解するために、GA4データを用いて効果を検証しました。

BigQueryのパーティショニングとクラスタリング

パーティショニングとは

パーティショニングとは、BigQueryのテーブルをパーティションと呼ばれるセグメントで、内部的に分割することを意味します。パーティショニングでテーブルを分割するためには、テーブル作成時に、分割の基準とする列をひとつ指定します。

テーブルあたりの最大パーティショニング数は10,000です。10,000を超えるパーティショニングは設定できないのでご注意ください。

WHERE句でパーティショニングに設定した列を指定すると、該当のパーティションのみを参照することができるため、クエリによるデータ読取容量を削減できます。

参考：パーティション分割テーブルの概要 | BigQuery | Google Cloud

クラスタリングとは

クラスタリングとは、BigQueryのテーブルを内部的に並べ替え（ソート）することを意味します。クラスタリングをテーブルに適用するには、テーブル作成時に、並べ替えの基準とする列を指定します（最大4つ）。

クラスタリングで指定する列は、一意の値を多く含むフィールドが推奨されています。（GA4データの場合、参照元、メディア、国など）

WHERE句やGROUP BY句でクラスタリングに設定した列を指定すると、並び替えされたデータを参照するため、余計なデータの読み込みがスキップされ、クエリの高速化やデータ読取容量を削減できます。

参考：クラスタ化テーブルの概要 | BigQuery | Google Cloud

検証方法

パーティショニングとクラスタリングの効果を検証するために、以下4つのテーブルを作成しました。

各テーブルに対して実行したSQLクエリは下記です。

SELECT
    page_location
    ,sum(pv) AS pv
  FROM
    `project_id.dataset_id.table_name`  --テーブル①〜④の宛先を設定
  WHERE
    date BETWEEN '2024-01-01' AND '2024-05-31'
  AND
    page_location = 'https://www.principle-c.com/service/'
  GROUP BY
    page_location

検証結果

4テーブルへのクエリ結果を、「処理されたバイト数」「課金されるバイト数」「スロット（ミリ秒）」の3項目で評価しました。

※「スロット」とは、BigQueryでクエリを実行するための基本的な計算リソースの単位です。CPU、メモリ、ストレージなどのリソースを含む仮想的な処理単位です。

※「スロット（ミリ秒）」は、クエリの実行において、スロットが使用された時間をミリ秒単位で示します。クエリのコストや効率性を評価するための重要な指標です。

パーティショニングの効果

②パーティショニング適用テーブルでは、①通常テーブルと比べ、処理されるバイト数と課金されるバイト数が少ないことがわかります。つまり、コストの削減を実現しています。

一方で、スロットが大きく増加していることもわかります。

スロットが大きくなる理由は、パーティションを管理するために計算リソースが追加されているからだと考えられます。

クラスタリングの効果

③クラスタリング適用テーブルでは、①通常テーブルと比べ、スロットが小さくなっていることがわかります。

一方で、処理されたバイト数と課金されるバイト数は変化なしでした。

パーティショニングとクラスタリングの両方を適用する効果

④パーティショニングとクラスタリングの両方を適用したテーブルでは、②パーティショニングのみ適用したテーブルと同様に、処理されたバイト数と課金されるバイト数が少ないことがわかります。

また、①通常テーブルと比べてスロットが増加していますが、クラスタリングの効果によって、②パーティショニングのみ適用したテーブルよりその増加幅が抑えられています。

これらの検証結果から、BigQueryでテーブルを作成する際は、パーティショニングとクラスタリングの両方を設定したほうが、クエリ課金やクエリ実行速度を最適化できることがわかりました。

まとめ

今回は、BigQueryのパーティショニングとクラスタリングによる、クエリパフォーマンス最適化の効果を検証してみました。

BigQuery初心者の方にとっては難しい内容かもしれませんが、パーティショニングとクラスタリングを活用することで、BigQueryの利用料金やクエリ実行速度を最適化できます。

ぜひ皆さんも活用してみてください。