はじめに

こんにちは、 IT 本部 IT 基盤部 第三グループの渡邊です。IT 基盤部では、組織横断的にさまざまなサービスのインフラ運用とコスト削減に取り組んでいます。

BigQuery のクエリ費用には、スキャンしたバイト数に課金するオンデマンドと、確保したスロット（BigQuery の計算リソースの単位）の時間に課金する BigQuery Editions（以下 Editions）の 2 つの課金モデルがあり、どちらが安いかはワークロードの性質によって決まります。

BigQuery を使っている Google Cloud プロジェクトで、オンデマンドから Editions への移行を進めていますが、実際にやってみると、事前の試算どおりに下がるとは限りませんでした。

本記事では、プロジェクトのワークロードがオンデマンドと Editions のどちらに向いているかの選定方法と Editions への移行方法を詳細に記したうえで、移行による効果と試行錯誤について紹介します。

BigQuery のコスト管理・削減に関心がある方の参考になれば幸いです。

前回記事以降に変わった前提

Editions の料金体系、スロットの自動スケーリング、移行フローについては、2025 年に公開された記事が詳しいので、前提としてあわせてご覧ください。

その公開後、移行の判断そのものを左右する機能が 2 つ使えるようになったため、本記事ではこの 2 つを織り込んだ手順を紹介します。

Editions のスロットは需要に応じて 50 スロット単位で増減しますが、従来はスケールアップすると最低 60 秒分が課金されたため、10 秒で終わるクエリが散発的に飛ぶワークロードでは、実際の消費を大きく上回る請求になっていました。fluid scaling はこの最低保持を撤廃するものです。

もうひとつは課金モデルの混在です。前回記事では Editions 向きではないジョブのためにオンデマンド実行用のプロジェクトを別に用意し、ジョブによって使い分ける運用が紹介されていましたが、現在はクエリごとにリザベーションを指定できるため、プロジェクトを分けずに同じことができます。

移行の手順

上から順に実施すれば移行が完了する形で並べています。先に手順に出てくる 3 つの用語について説明します。

ここからは選定方法、移行手順に進んでいきます。

手順に記されているコマンドと SQL に出てくる < と > で囲まれた語は、実行時に対象プロジェクトの値へ置き換えてください。たとえば <REGION> は BigQuery のロケーション、<TIMEZONE> は集計に使うタイムゾーン（Asia/Tokyo など）を指します。

1. 移行するかを実測で判定する

オンデマンドはスキャンしたバイト数に、Editions は確保したスロット時間に課金するため、課金の対象そのものが違い、両方を実測しない限り比較できません。

スロット消費を実測して試算する

Editions の課金は「その瞬間に何スロット確保していたか」の積み上げで決まり、ジョブ単位の合計値ではこの形を再現できないため、INFORMATION_SCHEMA.JOBS_TIMELINE を秒単位に集計し直します。

-- 目的: 秒ごとの同時消費スロット数を再構築し、容量課金の入力と Max 設計用の分布を出す
-- 出典: INFORMATION_SCHEMA.JOBS_TIMELINE（ジョブごとの 1 秒粒度のスロット消費履歴）
WITH per_second AS (
  -- period_slot_ms = 各ジョブがその 1 秒間に消費したスロット ms。
  -- 全ジョブを合算すると「その 1 秒間に何スロット使っていたか」になる
  SELECT
    TIMESTAMP_TRUNC(period_start, SECOND) AS sec,
    SUM(period_slot_ms) / 1000.0         AS slots,
    SUM(period_estimated_runnable_units) AS runnable_units  -- その 1 秒間にスロット待ちだった作業単位
  FROM `region-<REGION>`.INFORMATION_SCHEMA.JOBS_TIMELINE
  WHERE period_start >= TIMESTAMP('<START_DATE>', '<TIMEZONE>')
    AND period_start <  TIMESTAMP('<END_DATE>', '<TIMEZONE>')
    AND job_type = 'QUERY'  -- LOAD / EXTRACT / COPY は別プールのため除外
    -- マルチステートメントの親ジョブ（SCRIPT）は子ジョブの消費を重複計上するため除外
    AND (statement_type IS NULL OR statement_type != 'SCRIPT')
  GROUP BY sec
)
SELECT
  SUM(slots) / 3600.0                    AS consumed_slot_hours,      -- 実消費スロット時間
  SUM(CEIL(slots / 50) * 50) / 3600.0    AS fluid_billed_slot_hours,  -- 50 スロット単位に切り上げた課金近似
  ROUND(AVG(slots), 1)                   AS avg_slots,
  APPROX_QUANTILES(slots, 100)[OFFSET(50)] AS p50_slots,
  APPROX_QUANTILES(slots, 100)[OFFSET(95)] AS p95_slots,
  MAX(slots)                             AS peak_slots,               -- 観測されたピーク
  ROUND(COUNTIF(runnable_units > 0) / COUNT(*) * 100, 2) AS pct_sec_pending  -- スロット待ちが出ていた時間の割合（秒単位）
FROM per_second;

SQL の注意点は 2 つあります。SCRIPT を除外しているのは二重計上を防ぐためで、コンソールや Airflow からまとめて実行した複数文のジョブは、親と子の両方に同じ消費量が記録されます ³。逆にエラーで終わったジョブは除外してはいけません。Editions は確保したスロットに課金するため、成功ジョブだけに絞ると試算が過小になってしまいます ⁴。

実消費と 50 スロット切り上げの 2 つが容量課金の月額の下限と上限になるので、これをオンデマンド側のスキャン量に単価を掛けた額と比べます。

pct_sec_pending は、観測されたピークを信じてよいかの判定に使います。オンデマンドのスロットはプロジェクトあたり 2,000 が上限の目安で、これを超えるバーストは保証されません。移行したプロジェクトでは、スロットが多く供給されていた時間帯でも待ちが残っていました。観測されたピークは供給の上限に当たっていただけで、真の需要はその上にあったことになります。分布も二極化していて、普段はごく小さく、まれに大きく跳ねます。観測されたピークをそのまま Max の根拠にできるかは、待ちの残り方から判断します。

試算だけではなく、オンデマンド側の試算した金額と Cloud Billing の請求額が合うことも確認してください。

Standard で使えない機能を確認する

エディションを決める前に、Standard では使用することのできない機能を使っていないかを確認します。

大半はジョブの種別から検出できます。

-- 目的: ジョブ種別 × ステートメント種別ごとの件数を集計し、Standard 非対応機能の利用を検出する
-- 出典: INFORMATION_SCHEMA.JOBS（このプロジェクトのジョブ履歴）
SELECT
  job_type,
  statement_type,
  COUNT(*) AS job_count,
  ROUND(SUM(total_slot_ms) / 1000 / 3600, 1) AS slot_hours
FROM `region-<REGION>`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP('<START_DATE>', '<TIMEZONE>')
  AND creation_time <  TIMESTAMP('<END_DATE>', '<TIMEZONE>')
  AND state = 'DONE'
GROUP BY job_type, statement_type
ORDER BY slot_hours DESC;

こちらは機能の利用有無を漏れなく数えるのが目的なので、SCRIPT を除外しません。判定に使うのは次の値です。

暗号鍵と列のポリシータグは、ジョブ履歴ではなくメタデータから確認します。データセット既定の顧客管理の暗号鍵（CMEK）、テーブル個別の CMEK、そして列レベルアクセス制御と動的マスキングを実装する列ポリシータグをまとめて確認します。

-- 目的: Standard 非対応の設定のうち、CMEK と列ポリシータグの使用有無を見る
-- 出典: INFORMATION_SCHEMA.SCHEMATA_OPTIONS / TABLE_OPTIONS / COLUMN_FIELD_PATHS
SELECT
  (SELECT COUNT(*)  -- データセット既定の CMEK 設定数
   FROM `region-<REGION>`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
   WHERE option_name = 'default_kms_key_name') AS dataset_cmek_count,
  (SELECT COUNT(*)  -- テーブル個別の CMEK 設定数
   FROM `region-<REGION>`.INFORMATION_SCHEMA.TABLE_OPTIONS
   WHERE option_name = 'kms_key_name') AS table_cmek_count,
  (SELECT COUNT(*)  -- ポリシータグが付いた列の数
   FROM `region-<REGION>`.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
   WHERE ARRAY_LENGTH(policy_tags) > 0) AS tagged_column_count;

どちらの集計にも該当がなければ、Standard 採用の妨げになるものはありません。1 つでも該当があれば、Enterprise を選ぶか、Standard を選んだうえで後述する実行時のリザベーション指定で該当のクエリだけをオンデマンドへ逃がすかの判断になります。

ただし CMEK とポリシータグは Enterprise を選ぶのが現実的です。これらはクエリではなくデータ側に付いた設定なので、逃がす対象が「その機能を使うクエリ」ではなく「そのテーブルに触るクエリすべて」に広がってしまいます。

Editions 向きではないクエリを洗い出す

スキャン量に対してスロット消費が大きいクエリも洗い出しておきます。プロジェクト全体では Editions が有利でも、そういうクエリは単体ではオンデマンドより高くつくため、移行前に見つけておけば、最初からオンデマンドで動かす設計にできます。

-- 目的: 同一クエリごとにオンデマンド換算額と Editions 換算額を比べ、Editions 向きではないクエリを検出する
-- 出典: INFORMATION_SCHEMA.JOBS（このプロジェクトのジョブ履歴）
SELECT
  -- 空白と日付リテラルを除いて正規化した MD5。毎日動く同じクエリを 1 つに束ねる
  TO_HEX(MD5(REGEXP_REPLACE(query, r'\s+|\d{4}-\d{2}-\d{2}[^\s]*', ' '))) AS query_fingerprint,
  ANY_VALUE(job_id) AS sample_job_id,  -- コンソールのジョブ履歴から本文を開ける
  COUNT(*) AS exec_count,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * <ONDEMAND_USD_PER_TIB>, 2) AS ondemand_usd,
  -- Editions 換算額。50 スロット切り上げを加味した上限寄りの近似
  ROUND(SUM(GREATEST(total_slot_ms, 50 * TIMESTAMP_DIFF(end_time, start_time, MILLISECOND))) / 1000 / 3600 * <EDITIONS_USD_PER_SLOT_HOUR>, 2) AS editions_usd_upper
FROM `region-<REGION>`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP('<START_DATE>', '<TIMEZONE>')
  AND creation_time <  TIMESTAMP('<END_DATE>', '<TIMEZONE>')
  AND job_type = 'QUERY'
  AND (statement_type IS NULL OR statement_type != 'SCRIPT')  -- SCRIPT 親ジョブは子の合計を重複計上するため除外
  AND end_time IS NOT NULL
  AND total_slot_ms > 0                                       -- キャッシュヒット等の消費ゼロを除外
GROUP BY query_fingerprint
ORDER BY (editions_usd_upper - ondemand_usd) DESC             -- 差額の大きい順
LIMIT 30;

対象にするのは、Editions 換算額からオンデマンド換算額を引いた差額が大きいクエリだけです。

エディションを決める

ここまでに計測した値や使用中の機能を考慮してエディションを決めていきます。

エディション選定の軸になるのは、必要なスロット数でサービス要件を満たしたうえでコストを最適化できるかです。

単価だけなら Standard が最安ですが、スロット数の上限が 1,600 に定められており、これは fluid scaling を有効にしても変わりません。スロットが足りなければレイテンシの悪化につながります。

しかし、レイテンシの悪化がサービス要件の範囲内に収まるのであれば Standard を選べます。単価の差がそのままコストに効くので、Standard で足りるかを先に検討する価値があります。前述のとおり Enterprise でしか使えない機能を利用していても、後述する実行時のリザベーション指定を使えば、その機能を使うクエリだけをオンデマンドで実行する構成を取ることもできます。

サービス要件、レイテンシの許容度、コストなど多面的に見てからエディションを決めてください。

2. 管理プロジェクトを用意する

管理プロジェクトに専用のプロジェクト種別があるわけではなく、既存のプロジェクトを管理プロジェクトにすることもできます。Google 公式は専用プロジェクトをベストプラクティスとしていますが、必須要件ではありません ⁵。

管理プロジェクトを 1 つ立てて development・staging・production といった複数の環境をまとめて紐づけてもよく、対象プロジェクト自身を管理プロジェクトにしても構いません。

まとめる場合は、リザベーションを共有するかどうかで影響範囲が変わります。同じリザベーションを共有すればスロットの競合が環境をまたぐので、development で使いすぎた分だけ production が遅くなります。環境ごとにリザベーションを分ければ影響は環境内に閉じますが、Max をそれぞれに持つことになります。

対象プロジェクト自身を管理プロジェクトにする場合は、リザベーションに自分自身を紐づける必要があることに注意してください。リザベーションを作っただけでは管理プロジェクト自身のクエリがオンデマンドのままとなってしまいます。

3. リザベーションを作成する

作成の前にスロットクォータを確認してください。リージョンあたりのスロット総数にデフォルトの上限が設けられており、必要な Max スロット数を下回っている可能性があります。

gcloud alpha services quota list --service=bigqueryreservation.googleapis.com \
  --consumer=projects/<ADMIN_PROJECT_ID> --format=json

bigqueryreservation.googleapis.com/total_slots の quotaBuckets[] から対象リージョンの行を見ます。US・EU のマルチリージョンは total_slots_us / total_slots_eu という別のメトリクスになります。

実際に効く上限は effectiveLimit で、これが想定する Max を下回っていれば緩和を申請します。クォータはプロジェクトとリージョンの組で効くため、見るのは管理プロジェクトの方になります。

adminOverride に値が入っている場合、effectiveLimit はそのプロジェクト固有の値です。他のプロジェクトへ同じ設計を広げるときは、リージョンの既定値である defaultLimit も確認しておきます。

次に Baseline スロット数と Max スロット数を決めてリザベーションを作成します。Baseline は常時確保しておく固定のスロット数、Max は需要に応じて自動で確保する上限です。<EDITION> には判定で決めたエディションを入れます（例: Standard、Enterprise）。

bq mk --reservation \
  --project_id=<ADMIN_PROJECT_ID> \
  --location=<REGION> \
  --slots=0 \
  --edition=<EDITION> \
  --autoscale_max_slots=<MAX_SLOTS> \
  <RESERVATION_NAME>

Baseline は 0 にしています。常時確保する分は使っていない時間も課金されるため、0 にしておけばリザベーションを作った時点では課金が発生せず、プロジェクトを紐づけるまで費用はゼロです。

Enterprise を選んだうえで安全側に倒すなら、Max スロットは 2,000 から始めるのがよいと考えています。Standard の場合はリザベーションあたり 1,600 が上限なので、そこが実質の初期値です。なぜこの考えに至ったかについては後述します。

2,000 で動かしたうえで、設定値をさらに下げられるかを試す価値もあります。Max を段階的に下げ、サービス要件を満たせているかを確認します。もし 1,600 まで下げても許容できるなら、より単価の安い Standard へ切り替えも検討できます。

4. fluid scaling を有効化する

fluid scaling は、管理プロジェクトのオプションとして、リージョンごとに対象リザベーション名のリストを設定することで有効化できます。リザベーション個別のプロパティではない点に注意してください。なお反映には数分かかります。

-- 有効化。対象リザベーションをリストで指定する
ALTER PROJECT `<ADMIN_PROJECT_ID>`
SET OPTIONS (
  `region-<REGION>.preflight_fluid_autoscaling_reservations` = ["<RESERVATION_NAME>"]);

もし fluid scaling を有効化するリザベーションを増減させたい場合、SET OPTIONS のリストには有効化したいリザベーションをすべて列挙する必要があります。仮に新規分だけを指定してしまった場合、それまで有効だったリザベーションがリストから外れ、標準の自動スケーリングに戻って 60 秒最低課金が復活してしまうため注意してください。

fluid scaling の効果が及ぶのは Baseline を超えて自動で確保された分だけなので、Baseline を 0 にしていれば課金の全体に効きます。

5. アサインメントを作成する

対象プロジェクトをリザベーションに紐づけると、オンデマンドから Editions に切り替わります。切り替わるのはコマンドを実行した時点ですが、反映されるまでに間があるため、クエリの実行は作成から 5 分以上空けてください ⁶。

bq mk --reservation_assignment \
  --project_id=<ADMIN_PROJECT_ID> \
  --location=<REGION> \
  --reservation_id=<RESERVATION_NAME> \
  --assignee_type=PROJECT \
  --assignee_id=<WORKLOAD_PROJECT_ID> \
  --job_type=QUERY

必要な操作はこのアサインメント作成だけで、アサインされる側のプロジェクトでの設定変更・API 有効化・再デプロイは発生しません。

対象プロジェクト自身を管理プロジェクトにした構成では、<ADMIN_PROJECT_ID> と <WORKLOAD_PROJECT_ID> に同じプロジェクト ID が入ります。前述のとおり、自分自身を紐づけないとオンデマンドのままです。

job_type=QUERY が対象とするのは、SQL・DDL・DML と BigQuery ML（組み込みモデル）のクエリです。ロードやエクスポートのジョブも容量課金に乗せたい場合は PIPELINE のアサインメントを別途作成しますが、ロードジョブは無料なので通常はオンデマンドのままで問題ありません。

6. Editions 向きではないクエリをオンデマンドへ逃がす

前述の手順で洗い出したクエリを、オンデマンドで実行するように切り替えます。クエリの先頭でシステム変数を設定すると、そのジョブだけ課金先を上書きできます。SQL の場合、none を指定するとオンデマンドで、リザベーション名を指定するとそのリザベーションで実行されます。

-- アサインメント済みのプロジェクトでも、このジョブだけオンデマンドで実行される
SET @@reservation = 'none';
SELECT COUNT(*) FROM <DATASET>.<TABLE>;

指定できるのは SQL だけではなく、bq CLI の --reservation_id、API の configuration.reservation、コンソールのクエリ設定でも同じことができ、いずれも双方向に切り替えられます。なお指定するリザベーションは、クエリと同じロケーションにある必要があります。

SET @@reservation を使ったクエリは SCRIPT ジョブになり、課金先を示す reservation_id は子ジョブに付きます。後述のコスト比較でどちらの課金モデルで実行されたかを判定するときは、ここでも SCRIPT の親ジョブを除外してください。

7. レイテンシとコストをモニタリングする

移行後は、レイテンシとコストの変化を続けて見ます。移行直後の 1〜2 週間は日次で、安定してからは週次にしています。

レイテンシは待ち時間と実行時間の分位数を日次で追います。

-- 目的: 日次の待ち時間と実行時間を追い、Max 不足やレイテンシの変化を検知する
-- 出典: INFORMATION_SCHEMA.JOBS（このプロジェクトのジョブ履歴）
SELECT
  DATE(creation_time, '<TIMEZONE>') AS date_local,
  COUNT(*) AS job_count,
  -- 投入から実行開始までの待ち。ここが伸びていたらスロットが足りていない
  APPROX_QUANTILES(TIMESTAMP_DIFF(start_time, creation_time, MILLISECOND), 100)[OFFSET(95)] AS wait_p95_ms,
  APPROX_QUANTILES(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND), 100)[OFFSET(50)] AS run_p50_ms,
  APPROX_QUANTILES(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND), 100)[OFFSET(95)] AS run_p95_ms
FROM `region-<REGION>`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL <DAYS> DAY)
  AND job_type = 'QUERY' AND state = 'DONE'
  AND (statement_type IS NULL OR statement_type != 'SCRIPT')  -- SCRIPT 親ジョブは子の合計を重複計上するため除外
GROUP BY date_local
ORDER BY date_local DESC;

コストは Editions の課金額とオンデマンド換算額を日次で突き合わせます。参照する 2 つのビューは別々のプロジェクトにあるため、クエリでは所属を明示して書きます。JOBS はワークロードプロジェクト、RESERVATIONS_TIMELINE は管理プロジェクトです。

タイムゾーンを America/Los_Angeles に指定しているのは、Cloud Billing の日付基準に合わせるためです。

-- 目的: Editions の課金額とオンデマンド換算額を日次で比較する
WITH scanned AS (
  -- オンデマンド換算側: Editions 経由ジョブのスキャン量を単価で金額換算する
  SELECT
    DATE(creation_time, 'America/Los_Angeles') AS date_local,
    SUM(total_bytes_billed) / POW(1024, 4) * <ONDEMAND_USD_PER_TIB> AS on_demand_usd
  FROM `<WORKLOAD_PROJECT_ID>`.`region-<REGION>`.INFORMATION_SCHEMA.JOBS
  WHERE job_type = 'QUERY' AND state = 'DONE'
    AND (statement_type IS NULL OR statement_type != 'SCRIPT')  -- SCRIPT 親ジョブは子の合計を重複計上するため除外
    AND reservation_id IS NOT NULL                              -- Editions 経由で実行されたジョブに限定
    AND creation_time >= TIMESTAMP('<START_DATE>', 'America/Los_Angeles')
  GROUP BY 1
),
billed AS (
  -- Editions 課金側: 自動で確保されたスロット秒を単価で金額換算する（Baseline = 0 前提）
  SELECT
    DATE(period_start, 'America/Los_Angeles') AS date_local,
    SUM(period_autoscale_slot_seconds) / 3600 * <EDITIONS_USD_PER_SLOT_HOUR> AS editions_usd
  FROM `<ADMIN_PROJECT_ID>`.`region-<REGION>`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE
  WHERE period_start >= TIMESTAMP('<START_DATE>', 'America/Los_Angeles')
    AND reservation_name = '<RESERVATION_NAME>'
  GROUP BY 1
)
SELECT
  date_local,                                                          -- 米国太平洋時間基準の日付
  ROUND(IFNULL(on_demand_usd, 0), 2) AS on_demand_usd,                 -- オンデマンドだった場合の日額（USD）
  ROUND(IFNULL(editions_usd, 0), 2) AS editions_usd,                   -- Editions の課金相当の日額（USD）
  ROUND(IFNULL(editions_usd, 0) - IFNULL(on_demand_usd, 0), 2) AS cost_diff_usd  -- プラス = オンデマンドの方が安かった、マイナス = Editions の方が安かった
FROM scanned
FULL OUTER JOIN billed USING (date_local)
ORDER BY date_local;

cost_diff_usd は Editions の課金額からオンデマンド換算額を引いた差なので、プラスの日はオンデマンドのままの方が安かったことになります。この列を日ごとに追えば、全体として下がっているかと、下がらなかった日がなかったかを一度に確認できます。

モニタリングの結果が想定に届かなければオンデマンドに戻せます。リザベーションからアサインメントを削除するだけで、データとアプリケーションへの影響はありません。

削除しても、そのリザベーションのスロットで実行中のジョブは失敗せず完走します ⁶。

一方でリザベーションそのものの削除は実行中のジョブを失敗させますので、アサインメントの削除とは挙動が違う点に注意してください。

移行後の試行錯誤

移行したあとに、想定と違う挙動が 2 つ出ました。別々の環境で起きたものなので、環境ごとに問題と対策の順で整理します。

Max スロットを高く設定しすぎた

問題

Enterprise で移行した環境では、Max を観測ピークを上回る 10,000 に設定しました。ピーク以上に取れば性能が劣化しないと考えたこと、そして fluid scaling は実消費に課金するので使わない分の Max は請求に影響しないと考えたことが設定の理由です。

しかし、移行後の請求は下がるどころかオンデマンド時より高く出ました。レイテンシは悪化しておらず、意図しないオンデマンド実行も起きておらず、設定は意図どおりのままコストだけが逆に振れていました。

誤っていたのは後者の考えです。公式ドキュメントには、クエリのスロット消費量が処理データ量とクエリの複雑さに加えて利用可能なスロット数にも依存すると明記されています ⁷。Max は、使うか使わないかを決めるだけの値ではありません。同じクエリの処理のされ方そのものを変えます。

なぜ増えるのかは公式には説明されていませんが、並列度が上がるとテーブルの分割やワーカー間でのデータの受け渡しといったオーバーヘッドが増えるためだろうと考えています。

対策

Max を段階的に引き下げました。変更は即時に反映されていつでも戻せるため様子を見ながら試せるので、10,000 から 4,000、次に 2,000 と 2 段階で下げています。

効果は同一クエリの前後比較で確認しており、Max を変えるたびに実行して、業務影響のある遅延が出ていないかとあわせて見ています。

-- 目的: 同一クエリの 1 回あたりスロット消費と実行時間を変更前後で比較する
WITH runs AS (
  SELECT
    -- 空白と日付リテラルを除いて正規化した MD5。毎日動く同じクエリを 1 つに束ねる
    TO_HEX(MD5(REGEXP_REPLACE(query, r'\s+|\d{4}-\d{2}-\d{2}[^\s]*', ' '))) AS query_fingerprint,
    IF(creation_time < TIMESTAMP('<CHANGE_DATETIME>', '<TIMEZONE>'), 'before', 'after') AS phase,
    total_slot_ms,
    TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_sec
  FROM `region-<REGION>`.INFORMATION_SCHEMA.JOBS
  WHERE creation_time >= TIMESTAMP('<START_DATE>', '<TIMEZONE>')
    AND creation_time <  TIMESTAMP('<END_DATE>', '<TIMEZONE>')
    AND job_type = 'QUERY' AND state = 'DONE'
    AND (statement_type IS NULL OR statement_type != 'SCRIPT')
    AND end_time IS NOT NULL
    AND total_slot_ms > 0  -- キャッシュヒット等の消費ゼロを除外
)
SELECT
  query_fingerprint,
  ROUND(AVG(IF(phase = 'before', total_slot_ms, NULL)) / 1000 / 3600, 2) AS slot_hours_before,
  ROUND(AVG(IF(phase = 'after',  total_slot_ms, NULL)) / 1000 / 3600, 2) AS slot_hours_after,
  ROUND(AVG(IF(phase = 'before', duration_sec, NULL)), 0) AS duration_sec_before,
  ROUND(AVG(IF(phase = 'after',  duration_sec, NULL)), 0) AS duration_sec_after
FROM runs
GROUP BY query_fingerprint
HAVING COUNTIF(phase = 'before') >= 5 AND COUNTIF(phase = 'after') >= 5  -- 平均を安定させる
ORDER BY AVG(IF(phase = 'before', total_slot_ms, NULL)) DESC
LIMIT 20;

対策後

10,000 から 4,000 に下げた時点で、毎日動く重いバッチ上位 2 本の 1 回あたりのスロット消費が減りました。

消費は 12〜26% 減り、2 番目のジョブに至っては実行時間が変わらないまま消費だけが減っています。さらに 4,000 から 2,000 に下げたところ、日次の課金がオンデマンド換算を下回るようになりました。

落ち着いた 2,000 は、オンデマンド時代の実質的な上限と同じ値で、最初からここに置いておけばよかったことになります。

移行前に書いた提案書には、Max を観測ピーク以上に取れば実績と比べて劣化しない安全圏だと記していました。レイテンシの観点では正しく、実際に悪化しませんでした。取り違えていたのはコスト側で、先に触れたとおり公式ドキュメントに記載があったので、確かめていれば避けられた設定でした。

エラーで終わったクエリにも課金される

問題

Standard で移行した環境では、移行後に日次でコストを比べると Editions の方が高くつく日がありました。原因はいずれも失敗または中断したクエリで、最大の 1 本は 6 時間の上限まで走って中断され、それだけで 460.42 USD を消費していました。

クエリの実行時間の上限が初期値の 6 時間のままだったことが、1 本あたりのコストを大きくしました ⁸。オンデマンドでは失敗したクエリに課金されないため、この設定を見直す動機がそれまでありませんでした。Editions では失敗したクエリでも稼働していた分のスロット時間が課金されるので、該当のクエリは実行時間の上限に達するまで課金され続けます。

対策

実行時間の上限を、業務上必要な範囲まで下げます。ALTER PROJECT でプロジェクト単位の既定値を変更でき ⁹、指定はミリ秒で 5 分から 48 時間の範囲です。

-- クエリの実行時間の上限を 1 時間にする（キュー待ちと実行時間の合計）
ALTER PROJECT `<WORKLOAD_PROJECT_ID>`
SET OPTIONS (
  `region-<REGION>.default_query_job_timeout_ms` = 3600000);

あわせて、試行錯誤するクエリは手順で触れた実行時のリザベーション指定でオンデマンドに寄せます。失敗しても課金されないため、開発中や調査中のクエリに向いているからです。

得られた効果

移行したプロジェクトはいくつかありますが、ここでは前章でご紹介した Standard の環境を一例として紹介します。asia-northeast1 で、ひとつの管理プロジェクトに development・staging・production の 3 環境を紐づける構成にしています。Max スロットは staging と production が 1,600、development が 800 です。

金額はすべて移行後 10 日間の集計で、公開されている単価で計算しています。オンデマンドが 1 TiB あたり 7.5 USD、Standard が 1 スロット時間あたり 0.051 USD です。

移行後の 3 環境の合計は、オンデマンド換算で 1,228.92 USD に対して Editions の課金が 921.65 USD で、25.0% 下がったことになります。production だけを見れば、ほとんどの日で Editions の方が安く済んでいます。

この 25.0% には前章で触れた失敗・停止したクエリの課金が乗ったままです。これらを除くとどこまで下がるかを見積もってみます。

失敗・停止したクエリを除くと 70% 下がっていた

100 スロット時間以上を使ったクエリを、終了理由で分類しました。

手動で停止したクエリは、オンデマンドでの課金額を確定できないため「—」にしています。

これらを除いた場合の削減率です。

移行そのものの判断は正しく、残っていた改善余地はクエリの実行のしかたにありました。

実行時間の上限を 1 時間にしていれば、460.42 USD を失った 1 本は 80 USD 程度に収まっていた計算で、上限をどこに置くかはバッチの実行時間の実績から決めます。

この差し引きは概算です。差し引く側に 50 スロット単位の余剰を含めていないため、実際の削減率は 70.0% より大きくなります。

スロットを多く使っていた上位のクエリはすべて手動で実行されたもので、しかも複数の日で起きており、一度きりの事象ではありません。

development 環境だけは 11.4% のプラスでしたが、これは金額の規模が小さく、1 日分の失敗クエリだけで期間全体がプラスに転じたためです。加えて、使用量が少ない日ほど 50 スロット単位でしか確保できないことによるムダが相対的に重くなります。小さなクエリが散発的に飛ぶだけの環境は、そもそもオンデマンドのままにしておく方が合理的な場合があります。

まとめ

本記事では、BigQuery の課金モデルの選定手順と Editions への移行手順、実際に出た効果と試行錯誤について紹介しました。

要点を 5 点に整理します。

1. 判定は秒単位の実測で行う: Editions の課金は、その 1 秒間に何スロット確保していたかの積み上げで決まります。ジョブ単位の合計値では再現できません。エラーで終わったジョブも含めて集計してください
2. 試算も設定も fluid scaling を前提にする: 60 秒の最低課金が残る前提で見積もると、課金が実消費を大きく上回って見え、移行そのものを見送る判断になりかねません
3. Max はオンデマンド時の上限から始める: Enterprise なら 2,000 から始めるのがよいと考えています。Max を上げるとスロット消費そのものが増えるので、低い側から始めます。移行後に 1,600 で足りると分かったら、単価の安い Standard への切り替えも検討できます
4. 実行時間の上限は移行と同時に見直す: オンデマンドでは失敗したクエリが無料なので、初期値の 6 時間が問題になりません。Editions では 1 本の暴走がそのまま請求に乗ります
5. 課金モデルは混ぜられる: プロジェクト単位で全面移行するか否かの二択ではなくなりました。スキャン量に対してスロット消費が大きいクエリや、試行錯誤するクエリは、実行時のリザベーション指定でオンデマンドへ逃がせます

Editions への移行は、リザベーションからアサインメントを削除するだけでオンデマンドに戻せます。試算に不安が残るなら、2 週間ほど有効化して実ワークロードで計測してしまうのが確実です。

最後までお読みいただき、ありがとうございました。

脚注