クエリそのままパフォーマンスを改善 BigQuery パフォーマンス向上テクニック

どんなシステムでも導入してしばらく経つと、パフォーマンスを最適化したくなってきますよね。

もちろんBigQueryも例外ではありません。DWHのチューニングというと、SQLを書き換えたり、テーブルを非正規化したりして性能を改善するのが最初に思いつくかもしれませんが、正直、少し面倒です。

幸いなことに、BigQueryにはSQLもテーブルスキーマもそのままに、クエリのパフォーマンスを向上させるテクニックがいくつかあります。この記事では、そんな便利な機能を紹介していきます。あまり知られていない機能も多いので、この機会に皆さんの環境でも適用できないかぜひ検討ください。

Advanced Runtime - 設定ひとつでパフォーマンスを底上げする

公式ドキュメントはこちら

本記事イチオシの機能です。設定一つを切り替えれば、様々な最適化がまとめて適用されパフォーマンスが上がります。

設定はクエリプロジェクト単位、または組織単位で有効にできます。クエリプロジェクト単位で有効化する場合は、次のように ALTER PROJECT 文を実行ください。

ALTER PROJECT ${PROJECT_NAME}
SET OPTIONS (
  `region-${LOCATION}.query_runtime` = 'advanced'
);

これだけで最適化の恩恵を受けられます。

Advanced Runtime の有効化で、パフォーマンスがどのように変化するか試してみましょう。
次の SQL は、DWH のベンチマークとしてよく使用される TPC-H の 1本目の SQL です。

--- @DELTA を 90,100,110 に変化させながら 合計 3回実行 
SELECT
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
FROM
    `bigquerybench.tpch_10T.lineitem`
WHERE
    l_shipdate <= date_sub(date '1998-12-01', interval @DELTA day)
GROUP BY
    l_returnflag,
    l_linestatus
ORDER BY
    l_returnflag,
    l_linestatus;

まずは、Advanced Runtime を有効にしていないプロジェクトで、上記のクエリを実行してみます。
(以下特に断りの無い限りすべてのクエリプロジェクトで、BigQuery Editions の Enterprise Edition を使用し、スロットとしてbaseline 0, autoscale 最大 2000 slot を割り当てているものとします。また、クエリキャッシュは無効にします。)

クエリパラメーター @DELTA は、TPC-H の仕様に準拠する形で 90, 100, 110 と変化させます。
その3回のクエリの消費スロット時間、所要時間を Jobs Explorer で確認すると以下のようになりました。

一方で、Advanced Runtime を有効にしたプロジェクトでクエリを実行したところ、次のようになりました。

消費スロット時間、所要時間はともに1/3前後となり、設定一つで大幅にパフォーマンスが向上したことが確認できます。

パフォーマンス向上の理由を探るため、クエリ実行の詳細 (Execution details) を比較してみてみましょう。適当な実行結果同士 (ここでは、@DELTA = 110としたときの実行結果同士)を比較すると 、Input のステージの所要時間（特に Wait の部分）が短縮されていることが確認できるかと思います。

(Advanced Runtime 適用前)

(Advanced Runtime 適用後)

これは Advanced Runtime で有効になる最適化機能の一つ「拡張ベクトル化 (Enhanced Vectorization)」の効果と思われます。
拡張ベクトル化では、データの読み取り・集計・結合処理をより高い効率で実行可能にします。特にこのクエリでは、読み取りに係る処理の最適化が行われたようです。拡張ベクトル化によるパフォーマンス改善について、技術的な詳細はブログを参照ください。

Advanced Runtime の威力はこれだけではありません、今度は次のクエリを実行してみましょう。
クエリキャッシュを無効にした状態で下記クエリを3回実行し、そのパフォーマンスを比較します。

SELECT
    p.category,
    dc.name AS distribution_center_name,
    u.country AS user_country,
    SUM(oi.sale_price) AS total_sales_amount,
    COUNT(DISTINCT o.order_id) AS total_unique_orders,
    COUNT(DISTINCT o.user_id) AS total_unique_customers_who_ordered,
    AVG(oi.sale_price) AS average_item_sale_price,
    SUM(CASE WHEN oi.status = 'Complete' THEN 1 ELSE 0 END) AS completed_order_items_count,
    COUNT(DISTINCT p.id) AS total_unique_products_sold,
    COUNT(DISTINCT ii.id) AS total_unique_inventory_items_sold
FROM
    `bigquery-public-data.thelook_ecommerce.orders` AS o,
    `bigquery-public-data.thelook_ecommerce.order_items` AS oi,
    `bigquery-public-data.thelook_ecommerce.products` AS p,
    `bigquery-public-data.thelook_ecommerce.inventory_items` AS ii, `bigquery-public-data.thelook_ecommerce.distribution_centers` AS dc, `bigquery-public-data.thelook_ecommerce.users` AS u
WHERE
o.order_id = oi.order_id AND oi.product_id = p.id AND ii.product_distribution_center_id = dc.id AND oi.inventory_item_id = ii.id AND o.user_id = u.id
GROUP BY
    p.category,
    dc.name,
    u.country
ORDER BY
    total_sales_amount DESC
LIMIT 1000;

(Advanced Runtime 適用前)

(Advanced Runtime 適用後)

こちらでも、消費スロット時間、所要時間が半分以下になりました。
パフォーマンス改善の理由を探るためにクエリの実行計画を比較してみましょう。

(Advanced Runtime 適用前)

(Advanced Runtime 適用後)

クエリの実行計画が大幅にシンプルになっていることが確認できます。

実は先ほど実行したクエリは結合・集計処理が複雑ではあるものの、処理するデータ量は 20MiB 未満とそこまで大きくありません。BigQuery は通常、極めて大規模にデータを分散処理します。しかしこの種のクエリならば、あえて 1 ステージで全て処理したほうがシャッフル等のオーバーヘッドを削減できます。

Advanced Runtime の機能の一つ “Short Query Optimization” では、データ量や特性を考慮し、比較的小規模なクエリのパフォーマンスを大幅に改善します。その技術的な詳細は、こちらのブログも合わせて確認ください。なお、前述の拡張ベクトル化などの最適化も同時に適用され、パフォーマンスを複数の側面から底上げしています。(なおこれまで “Short Query Optimized Mode” と呼ばれていた、クエリのレイテンシを削減する機能は “Optional Job Creation Mode” と名称変更されています。こちらについても後で後述します。)

Advanced Runtime は、現在 Preview で提供を開始しています。ぜひテスト用のプロジェクトで有効にして頂き、パフォーマンスの変化をご確認ください。なお将来的にはデフォルトで有効になる予定です。

History based optimization - 過去の類似クエリの情報を使いパフォーマンス最適化

公式ドキュメントはこちら

こちらも設定一つでパフォーマンスが向上します。こちらはその機能名から分かる通り、過去30日間のクエリ履歴を元にパフォーマンスを逐次最適化するものです。まずは有効化方法から紹介しましょう。

Advanced Runtime と同様、機能はプロジェクト、または組織単位で有効にできます。
プロジェクト単位で有効化する場合、次のSQLを実行してください。

ALTER ${PROJECT}
SET OPTIONS (
  `region-${LOCATION}.default_query_optimizer_options` = 'adaptive=on'
);

さて、それでは History based optimization の効果を確認してみましょう。

逐次最適化の効果を確認するため、以下のクエリを5回実行します。

SELECT
    l_orderkey,
    sum(l_extendedprice * (1 - l_discount)) as revenue,
    o_orderdate,
    o_shippriority
FROM
    `bigquerybench.tpch_10T.customer`,
    `bigquerybench.tpch_10T.orders`,
    `bigquerybench.tpch_10T.lineitem`
WHERE
    c_mktsegment = @SEGMENT
    and c_custkey = o_custkey
    and l_orderkey = o_orderkey
    and o_orderdate < DATE(@DATE)
    and l_shipdate > DATE(@DATE)
GROUP BY
    l_orderkey,
    o_orderdate,
    o_shippriority
ORDER BY
    revenue desc,
    o_orderdate
LIMIT 10;

このクエリは TPC-H の 3つめのクエリです。 2つのクエリパラメータは仕様に従って
(@SEGMENT, @DATE) = [(“BUILDING”, “1995-03-15”), (“HOUSEHOLD”, “1995-03-01”), (“AUTOMOBILE”, “1995-03-25”), (“BUILDING”, “1995-03-10”), (“HOUSEHOLD”, “1995-03-20”)]
と実行のたびに変化させるものとします。
（なお、この検証で使用したクエリプロジェクトでは Advanced Runtime も有効になっています。）

さて、実行したところ、5回のクエリの消費スロット時間、所要時間は下図のようになりました。
この図は実行順にソートしており、1行目が1回目のクエリ実行結果となります。

明らかに、2回目以降のクエリパフォーマンスの向上が確認できます。過去の類似クエリを元に以降の実行を高速化する、これが History based optimization の効果です。

この機能では、過去30日間に実行されたクエリの統計情報を用いて、以降のクエリに様々な最適化を適用します。適用される最適化の例としては結合順序の変更・分割や処理の並列度合いの調整などがあります。詳細についてはこちらのブログも参照ください。

先ほど実行したクエリにどのような最適化が適用されたのか確認してみましょう。
INFORMATION_SCHEMA.JOBS_* の query_info.optimization_details カラムから、クエリごとに適用された最適化を確認できます。次のクエリを実行してください。

SELECT
  job_id,
  query_info.optimization_details
FROM `${PROJECT_NAME}.region-${LOCATION}`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
ORDER BY creation_time DESC
LIMIT 5;

私の環境では次の結果になりました。(job_id は一部省略しています。)
なお、クエリ結果が新しい順 (最もはじめに実行したクエリが最下行) に並んでいる点にご注意ください。

この図から2回目以降で join_communication 、3回目以降で parallelism_adjustment が適用されていることが読み取れます。

join_communication では、結合処理時に左右を入れ替えパフォーマンスを向上させます。 BigQuery では JOIN の右側に小さなテーブルを指定する方がパフォーマンスが改善する傾向にあり、この性質を活用しています。結果、所要時間が30%以上短縮されました。
parallelism_adjustment では、並列処理の並列度合いを調整します。ただあいにく、こちらの最適化の効果は今回ほとんどみられなさそうです。
なお、最適化の結果クエリパフォーマンスが劇的に悪化した場合、その最適化は取り消されます。

History based optimization はクエリキャッシュと異なり、クエリ文の部分的な変更やソーステーブルの更新があっても機能します。（変更が大規模である場合、BigQuery のオプティマイザ側判断で最適化が無効化されるケースはあります）
こちらの機能は既に GA となっており、将来的にはデフォルトで有効になる予定ですが、気になる方はぜひ手動で有効化しパフォーマンス変化を確認いただければと思います。

Optional Job Creation Mode - 小規模なクエリのレイテンシを最小化する

公式ドキュメントはこちら

こちらは、クエリ実行にかかるオーバーヘッドを最小化し、クエリの submit から結果取得までの全体のレイテンシを短縮する機能です。
取り扱うデータ量が比較的小さいケースで特に効果を発揮します。

クエリのレイテンシを正確に評価するため、Python で Google Cloud SDK を利用して計測を行います。
SDKを適切に認証したうえで、次のスクリプトを実行してみましょう。

import time
import statistics
from google.cloud import bigquery
from google.cloud.bigquery.enums import JobCreationMode

# --------------------------------------------------
# 1. 評価に使用するSQLクエリの定義
# --------------------------------------------------
sql_query = """
    SELECT
      species,
      count(*) as cnt,
      avg(body_mass_g) as avg_body_mass_g
    FROM `bigquery-public-data.ml_datasets.penguins`
    GROUP BY species
    LIMIT 5;
"""
print("---" + " SQL Query" + "---")
print(sql_query)

# --------------------------------------------------
# 2. ユーティリティ関数定義
# --------------------------------------------------
def execute_query_and_measure_time(client, job_config):
    """指定された設定でクエリを実行し、所要時間を返す"""
    start_time = time.perf_counter()
    # クエリジョブを実行し、結果が返ってくるまで待機
    client.query_and_wait(sql_query, job_config=job_config)
    end_time = time.perf_counter()
    return end_time - start_time

# --------------------------------------------------
# 3. 事前準備：History based optimization の影響を排除するためあらかじめクエリを数回実行
# --------------------------------------------------
print("\n" + "---" + "Phase 1: Warming up (5 times)" + "---")

baseline_client = bigquery.Client()
print(f"BigQuery client created for project: {baseline_client.project}")

nocache_job_config = bigquery.QueryJobConfig(
    use_query_cache=False,
)
for i in range(5):
    print(f"Running warm-up query {i + 1}/5...")
    execute_query_and_measure_time(baseline_client, nocache_job_config)
print("Warm-up complete.")

# --------------------------------------------------
# 4. 通常時のクエリ実行時間を計測
# --------------------------------------------------
print("\n" + "---" + "Phase 2: Baseline performance measurement (5 times)" + "---")
baseline_times = []

for i in range(5):
    duration = execute_query_and_measure_time(baseline_client, nocache_job_config)
    baseline_times.append(duration)
    print(f"Run {i + 1}/5: {duration:.4f} seconds")

# --------------------------------------------------
# 5. Optional Job Creation Mode 有効時のクエリ実行時間を計測
# --------------------------------------------------
print("\n" + "---" + "Phase 3: Optional Job Creation Mode performance measurement (5 times)" + "---")

optional_mode_client = bigquery.Client(
    default_job_creation_mode=JobCreationMode.JOB_CREATION_OPTIONAL
)
print(f"BigQuery client created for project: {optional_mode_client.project}")

optional_mode_times = []

for i in range(5):
    duration = execute_query_and_measure_time(optional_mode_client, nocache_job_config)
    optional_mode_times.append(duration)
    print(f"Run {i + 1}/5: {duration:.4f} seconds")

# --------------------------------------------------
# 6. 結果の集計と表示
# --------------------------------------------------
avg_baseline = statistics.mean(baseline_times)
avg_optional_mode = statistics.mean(optional_mode_times)

print("\n" + "---" + "Evaluation Results" + "---")
print(f"Average time (Baseline): {avg_baseline:.4f} seconds")
print(f"Average time (Optional Job Creation Mode): {avg_optional_mode:.4f} seconds")

上のスクリプトでは、Optional Job Creation Mode を無効、有効にした場合それぞれで、クエリを submit してから結果が返ってくるまでの平均所要時間を計測しています。クエリキャッシュは無効とし、5回の所要時間の平均値を最終的な結果として採用しています。なお、History based optimization による逐次最適化が計測中に発生しないよう、事前に5回クエリを実行して最適化が完了した状態にしています。

コードから読み取れる通り、Optional Job Creation Mode は、bigquery.Client のパラメータ default_job_creation_mode に JobCreationMode.JOB_CREATION_OPTIONAL を渡して初期化することで有効になります。
またクエリの実行は、client.query_and_wait() で明示的に同期実行させる必要があります。(非同期的に結果を取得する client.query() を使用した場合、 Optional Job Creation Mode は有効になりません)
（なお、Job Creation Optional Mode の利用自体は、BigQuery の Web UI からも、bq コマンドからも可能です。)

さて、上記のスクリプトを実行したところ、以下の結果になりました。

通常のクエリの場合所要時間は平均で548ms でしたが、Optional Job Creation Mode では平均で343ms と、200ms 近く短縮されています。

BigQuery では、クエリを実行する際 “ジョブ” と呼ばれる入れ物を作成し、非同期的に処理しますが、この作業には一定のオーバーヘッドが発生しています。Optional Job Creation Mode を有効にすると、小規模なクエリではクエリジョブの作成自体を省略しすべて同期的に処理するためその分内部処理が短縮されます。

ただジョブを作成しないことにはデメリットもあります。
たとえばジョブの詳細情報やクエリ結果を取得する API の job.get や jobs.getQueryResult は使用できません。そもそもこれらの API で必要な jobReference や job_id が作成されません。また、通常のクエリでは一時テーブルにクエリ結果が保存されますが、Optional Job Creation Mode では一時テーブルに結果を保存することなく直接クライアントに結果を出力するため、結果が保存されていません。（ただ少々ややこしいのですが、この状態でもクエリキャッシュは有効です）

なお、Optional Job Creation Mode を指定していても、取り扱うデータ量が多いなどの理由で従来通りジョブを作成・実行したほうがメリットがあると判断された場合は、通常通りジョブを作成して処理が行われます。これ自体はパフォーマンスを担保するために有用な処理なのですが、実行されるまでジョブが作成されるか否かわからない事もありえます。どちらであっても正常に処理が継続されるよう、呼び出し側のコードを実装しておくことも必要です。実際のところ、クエリ結果の取得程度であれば Cloud SDK がうまく隠蔽してほとんど問題になりませんが、job_id を参照している箇所がある場合は注意が必要です。

「いつも通りジョブを作成してクエリを実行したか」「Optional Job Creation Mode を使用してジョブを作成せずクエリを実行したか」「Optional Job Creation Mode を使用したがシステム側判断でジョブが作成されたか」は、BigQuery の Web UI の Jobs Explorer の Job Creation Reason から確認可能です。

上図の場合、Job Creation Reason が “Requested” であるクエリは通常通りジョブを使用して実行されたクエリ、”-” が Job を作成せず実行されたクエリを意味します。それ以外は 「Optional Job Creation Mode を使用したがシステム側判断でジョブが作成された」クエリです。”Long runnning” が長時間実行するためにジョブが作成されたクエリ、”Large results”がクエリの出力行数が多く、結果を一時テーブルに出力するためにジョブが作成されたクエリを意味しています。(他にその他の要因でジョブが作成されたことを示す “OTHER”も存在します）

なおこれらは INFORMATION_SCHEMA からも確認できます。

さて、これまで紹介した機能と比べると留意点は多い Optional Job Creation Mode ですが、データアプリケーションのユーザー体験を改善する可能性を秘めています。例えば BI ダッシュボードではユーザーのアクセス後速やかに必要なデータを表示することが求められますが、この機能が有効であれば表示までの時間を短縮できます。
Optional Job Creation Mode は前述の Advanced Runtime や、インメモリキャッシュの BI Engine とも併用可能です。これらの機能を組み合わせることで、様々なデータアプリケーションのバックエンドとしても BigQuery を安心して組み込めます。

ところで、Looker から投げられるクエリは既に Optional Job Creation Mode が有効になっています。また今後 Looker Studio からのクエリでもこの機能が自動的に有効になる予定です。Looker シリーズを BI ツールとしてお使いの場合、特に意識しなくとも最適化の恩恵に預かれます。

CMETA - ストレージ参照を劇的に高速化する縁の下の力持ち

最後に紹介するのがテーブルのメタデータを管理する枠組み “CMETA” です。
CMETA はカラムないしブロック単位でメタデータを保持しており、大規模なスキャンを伴うクエリであっても BigQuery ストレージのどこに必要なデータが存在しているか効率よく特定できます。これによりデータの読み取りやその後の操作が大幅に高速化されます。

CMETA は BigQuery のネイティブストレージで既に有効になっているため、他の機能とは違い明示的に有効化する必要はありません。
（無効化もできないため、他の機能のように効果を計測することも叶いませんが…）

CMETA が作成されているか・その最終更新日時は、INFORMATION_SCHEMA.TABLE_STORAGE ビューの last_metadata_index_refresh_time カラムから確認できます。なお、1GB 以下のテーブルなど一部のテーブルに対しては CMETA は作成されない点にご注意ください。
こちらのドキュメントも併せてご確認ください。

なお、ここまでは BigQuery のネイティブストレージに関してお伝えしてきましたが、CMETA は外部テーブルに対しても作成できます。
パーティション数の非常に多い Hive テーブルなどのパフォーマンスを劇的に向上させられますので、ぜひ利用をご検討ください。詳細はこちらもご確認ください。

おわりに

ここまでクエリを書き換えずに適用できる、4つのパフォーマンス向上機能を紹介してきました。

BigQuery のパフォーマンスチューニングといえは、パーティショニングやクラスタリング、PK・FK、Search Index の設定や、Materialized View ないし BI Engine の活用がよく挙げられます。SELECT 文以外の最適化では fine-grained DML というのもあります。データの持ち方を工夫することで、MERGE、UPDATE、DELETE 文のパフォーマンスを大幅に引き上げられます。
このように、BigQuery にはパフォーマンス向上につながる多様な機能が用意されています。

ただ、機能が増えたと身構える必要はそこまでありません。今回紹介した機能は次第にデフォルトで有効となり、技術的詳細を意識しなくとも性能が上がってゆきます。追加費用も特にかかりません。ただし CMETAの更新に専用のスロットを明示的に割り当てることもでき、その場合のみスロット費用が発生します。

フルマネージドサービス である BigQuery には、このような新機能が今後も活発に追加されてゆきます。
（Hive や Iceberg といった Open Table Format への対応や Iceberg REST Catalog との互換性サポート、BigQuery Omni によるマルチクラウドでのデータ分析など、オープンなデータ基盤のための機能追加も同時に行われている点は念の為断らせてください）

去年と今年の BigQuery は、名称こそ一緒でも実体は違うといってもよいでしょう。地味なところですが着実に改善を繰り返す、BigQuery に今後もご期待ください。