この記事は TimeTree Advent Calendar 2025 の 4 日目の記事です。
はじめに
TimeTree でデータアナリストをしている Nicky です。
入社してからは、扱うデータの規模が大きいこともあって、クエリのパフォーマンスを意識するようになりました。
この記事では、BigQuery でパフォーマンスの良いクエリを書くために意識していることを紹介します。
列指向
BigQuery は列指向のデータベース（ DB ）です[1]。そのため、クエリのパフォーマンスを意識するとき、列指向 DB のイメージを持っているとわかりやすいと感じました。
列指向 DB はデータを列単位で保存しています。対比して、データを行単位で保存しているデータベースを行指向 DB と呼びます。データをどのように保存するかは、DB からデータを取出すのにかかる時間を大きく左右します。
行指向 DB は一般的なサービス運営に、列指向 DB はデータ分析の用途で使われます。行単位でデータを取出せると高頻度でレコードの問合せがしやすく、列単位でデータを取出せると一度に大量のレコードを処理しやすいためです。
行指向 DB と列指向 DB は、どちらも同じ文法でクエリを書くことができますが、データの保存の形式から処理の仕方も異なるため、パフォーマンスの良いクエリの書き方も異なります。
列指向 DB では、扱うデータ量でパフォーマンスが変わります。そしてデータ量は、スキャンする列数、範囲、回数、それとテーブル結合によって決まります。
これらは SELECT、WHERE、WITH、JOIN と対応します。以降では、それぞれでパフォーマンスを良くするために意識していることを紹介します。
SELECT
必要な列を明示して書く
スキャンする列数を最小限にできるよう、出力に使う列だけを書きます。
SELECT に書いてあると、出力に使われていない列もスキャンされます。そのため、必要な列だけを明示することは、不要なスキャンを発生させず処理時間を節約できます[2]。
LIMIT を量削減のために使わない
いくつかのレコードを試しに見たいとき、次のようなクエリが考えられますが、避けるべきです。
SELECT
*
FROM
`bigquery-public-data.stackoverflow.stackoverflow_posts`
LIMIT
1
LIMIT はスキャンするデータ量を変えません。そのため、このクエリでは全ての列と行がスキャンされて非効率です。BigQuery コンソールだと、エディタ下部に表示されるドライラン結果のデータ量が LIMIT の あり / なし で変わらないことから、スキャンの量が変わらないことを確かめられます。
このクエリのようにお試しでレコードを見たいときは、BigQuery コンソールからテーブルのプレビューを見ると簡単です。
WHERE
パーティションやクラスタを指定する
パーティション分割やクラスタ化されたテーブルで WHERE を使うときは、その基準となった列を指定します。
基準となった列は、BigQuery コンソールでテーブル詳細から「フィールドで分割」と「クラスタ化の基準」の項目で確認できます。
パーティション分割とクラスタ化は、どちらもストレージレイアウトの設定です。
- パーティション分割は、データをパーティションごとに別々の物理ブロックに保存します。WHERE でパーティションを指定すると、一致するパーティションのブロックだけスキャンされ、それ以外のブロックのスキャンはスキップされます。
- クラスタ化は、クラスタ化列の値によってデータを並び替えます。WHERE でクラスタを指定すると、関連するブロックだけスキャンし、残りをスキップします。
いずれも、列をスキャンする範囲を制限できるため、パフォーマンスを良くできます。
早い段階でフィルターする
できるだけ列をスキャンするときにデータがフィルターされるように書きます。つまり、WITH 句を書き連ねていくとき、はじめのうちに実行される WITH 句に WHERE を書きます。
早い段階で処理すべきデータ量を少なくできると、後続の処理にかかる時間が短くなります[3]。
副作用として、同じような条件を複数書くことがあります。JOIN 後にまとめてフィルターするのではなく、個々のテーブルに WHERE を書くためです。
このときは、BigQuery の DECLARE で変数を宣言すると見通しが良くなります。WHERE 自体は複数書くものの、WHERE で使うパラメータを一箇所で管理できるため、条件の変更が簡単です。
WITH
一回だけ使う
同じ WITH 句はクエリ内で一回だけ使います。
WITH は共通テーブル式（ CTE ）を作成します。そして、CTE の内容は必ずしも一時テーブルとして実体化されません。クエリ内で同じ CTE を使い回せば、その内容が複数回実行される可能性があります[4]。スキャンの回数もそれだけ増えるので、クエリ結果を再利用するよりパフォーマンスは下がります。
同じ CTE を繰り返し使いたい場合、永続テーブルとして書き出して再利用します。
永続テーブルとしての書き出しは、権限と領域があれば CREATE で簡単につくることができます。
CREATE OR REPLACE TABLE `project-name.dataset_name.my_tmp_table`
OPTIONS (expiration_timestamp = TIMESTAMP(CURRENT_DATE() + 1))
AS (
WITH
...
)
OPTIONS で期限を設定すれば消す忘れることがないため、永続テーブルを一時テーブルのように気軽に使えます。
永続テーブルを作れないときでも、クエリ結果の一時テーブルが利用できます。クエリを実行した後に「クエリ結果」→「ジョブ情報」→「宛先テーブル」の「一時テーブル」から辿れるテーブル ID を FROM に指定すると、クエリ結果を再利用できます。
JOIN
大きいテーブルに小さいテーブルを結合する
テーブルを結合するとき、レコード数が多いテーブルを左にして、レコード数が少ないテーブルを右から結合します。
SELECT
...
FROM
big_table
LEFT JOIN small_table
USING(join_key)
JOIN の処理では、結合キーが同じデータを同じスロットに集めます。
結合パターンがブロードキャスト結合のとき、大きいテーブルを処理する各スロットに小さいテーブルのデータが移動します[5]。大きいテーブルのシャッフルが回避できるため、パフォーマンスが高くなります。
大規模なテーブルだと別のパターンで結合されることもあります。どの結合パターンが適しているかはクエリオプティマイザが判断するため、クエリでは「可能ならブロードキャスト結合をする」という書き方でとくに問題ありません。
一対多や多対多の JOIN をしない
JOIN は多対一または一対一とします。
結合によってレコード数が増えると、分散処理のためのデータ転送を必要とするデータ量も多くなります。そして、データ転送は処理のボトルネックとなりやすいため、不必要なら避けます。
一対多や多対多の JOIN が必要なときは、あらかじめ組み合わせを作成しておく、JOIN の前に GROUP BY をしておく、ウィンドウ関数を使うなどして避けます。
結合キーを整数にする
結合キーに文字列型でなく、整数型を使います。
結合に限らず、文字列の比較は整数の比較より高コストです。文字列は各文字を比較しなければならない一方、整数は一度で比較できるためです[6]。
キーが整数でないときでも、ハッシュ関数の FARM_FINGERPRINT を使って整数に変換できます。とくに複数の列を使って結合をするとき、ハッシュ関数でまとめると簡単です。
SELECT
FARM_FINGERPRINT(CONCAT(creation_date, post_id)) AS date_post_sk,
FARM_FINGERPRINT 関数は、データ量がおよそ 10 億件以下なら、衝突の可能性は十分低く実用的です。
おわりに
長々と書きましたが、パフォーマンスで意識していることは「スキャンを少なくする」「 JOIN でのシャッフルを回避する」の 2 点に集約できます。
BigQuery コンソールのクエリ結果から「実行グラフ」でハイライトされる処理を見ると、パフォーマンスのボトルネックが特定しやすいです。特定できた箇所でパフォーマンス改善を目指すと効果的に思います。
いずれにせよ、「計算をしない」ことが最もパフォーマンスが良いです。何回も使う計算であれば、冗長であっても事前に計算してマートテーブルとして実体化し、再利用できると簡単です。
ここまでの内容が、パフォーマンスの良いクエリを書く参考になれば幸いです。
TimeTreeのエンジニアによる記事です。メンバーのインタビューはこちらで発信中！ note.com/timetree_inc/m/m4735531db852
Discussion