BigQueryの物理バイト課金への切り替えでストレージ料金を削減する

こんにちは、CTOの小笠原(@yamitzky)です。

この記事では、BigQueryのストレージ料金を物理バイトストレージ課金に変更して削減した取り組みについて書きます。

コードの修正もデータの移行も不要で、ALTER SCHEMAを一文流すだけなのですが、BigQueryのストレージ料金の削減率は数十パーセント規模になりました。

BigQueryのストレージ課金とは

JX通信社では社内のデータ基盤・分析を BigQuery をベースに作っています。

社内のサーバー費を分析していたところ、BigQueryのストレージ課金が積み上がっていることに気が付きました。

というのも、JX通信社では、膨大なオープンデータの解析情報や、NewsDigestのユーザー行動ログなどを扱っています。さらに、生データの層（Datalake）、整形して統合した層（DWH）、用途別に集計した層（Datamart）の3層構造になっており、データは冗長に保管されています。

ストレージ課金は、分析等でアクティブに利用されていなくても、ただ大量のデータがBigQuery上に保管されているだけで発生する料金です。あまり使われてないからといって、削除してしまってよいか悩ましいようなデータもあります。

そこで、保管しているデータそのものの課金を減らすために、物理バイトストレージ課金(Physical Bytes Storage Billing、PBSB)への切り替えを順次行いました。BigQueryでなくGoogle Cloud Storageに保管するという手もありますが、それよりもお手軽にできる選択肢です

ストレージ課金の2つのモデル

BigQueryのストレージ課金には、データセット単位で選べる2つのモデルがあります。

モデル 課金対象 デフォルト
論理バイトストレージ課金（Logical Bytes Storage Billing、LBSB） 非圧縮換算のバイト数 デフォルトはこちら
物理バイトストレージ課金（Physical Bytes Storage Billing、PBSB） 圧縮後の実バイト数

物理バイトストレージ課金は2023年7月にGA（一般提供）となった機能で、元々は論理バイトストレージ課金しかありませんでした。

BigQuery のようなカラムナ型（列志向）のデータベースでは、一般的に、列ごとに圧縮された状態で物理保存されています。しかしながら、デフォルトの論理バイトストレージ課金では、圧縮前のサイズに対して課金がされます。コンソールで普段見ている「テーブルサイズ」が圧縮前のサイズです。

それに対して、物理バイトストレージ課金は、圧縮後のサイズに対して課金がなされるような課金体系です。

論理バイトストレージ課金から物理バイトストレージ課金に切り替えても、クエリの性能にもクエリ課金にもデータの中身にも影響しません。保存されているものは何も変わらず、変わるのは請求の計算式だけです。設定変更も ALTER 文だけでできます。

物理バイトストレージ課金は、論理バイトストレージ課金よりも高い

では単純に全部物理バイトストレージ課金にすればお得かというと、そうではありません。物理バイトストレージ課金の単価は、論理バイトストレージ課金よりも高く設定されているからです。

東京リージョン（asia-northeast1）の単価をカタログから引くと、記事執筆時点ではおおよそ次の関係になっています。

  • Active（直近90日以内に変更があったデータ）：physical は logical の約2.3倍
  • Long Term（90日間変更がないデータ）：physical は logical の約1.6倍

バイトあたりの単価が約1.6倍から2.3倍に上がる代わりに、課金対象のバイト数が圧縮後のサイズになる、というトレードオフです。つまり、圧縮率が、この課金単価の比率を上回れば安くなります。

ログデータ等であれば、基本的には圧縮率が上回ることの方が多いのではと思います。実際、JX通信社でも2〜30倍の圧縮率がありました。

タイムトラベルとフェイルセーフ

圧縮率の他に気をつけるべき点が、BigQuery に備わっているタイムトラベルとフェイルセーフ機能です。過去７日に遡って BigQuery のデータを復元したり、過去のデータに対してクエリをすることができる機能です。

タイムトラベル分のデータ保管料は、物理バイトストレージ課金に対してのみ発生します。追記するだけで書き換えないログ系だと良いのですが、毎日全件を洗い替えするテーブルだと、物理バイトストレージ課金の方が割高になることがあります。

物理バイトストレージ課金で課金額を抑えるなら、タイムトラベルの期間を短くするなどの対策が考えられます。

損益を分ける圧縮率

ストレージ課金設定は、データセット単位で設定できます。

したがって、損益はデータセット単位で、次のように比較することになります。

論理バイトストレージ課金  = （非圧縮バイト数） × logical単価
物理バイトストレージ課金 = （圧縮後バイト数） × physical単価 ＋ タイムトラベル分

全データセットの棚卸し

論理バイトストレージ課金と物理バイトストレージ課金、どちらの設定の方が得なのかを調べるため、Google Cloudから、クエリが提供されています。

TABLE_STORAGE ビュー  |  BigQuery  |  Google Cloud Documentation

とはいえ、当該クエリは全てのプロジェクトでそれぞれ発行しなければなりません。また、月額の削減幅が小さいものはわざわざ変えたくないかと思います。なので、Claude Code などを使って次のように伝えると良いです。((権限の都合でINFORMATION_SCHEMAが見られない場合は、tables.get相当のAPIから物理バイト数や time travel バイト数を集めても同じ集計ができます。))

以下は、BigQueryの論理バイトストレージ課金と物理バイトストレージ課金、どちらの方が得かを集計するためのクエリです。
Google Cloud の全プロジェクトに対して発行し、物理バイトストレージ課金に変えるべきデータセットや、タイムトラベルの日数を減らすことで大きくコスト削減できるデータセットを教えてください。
ただし、実際の月額の削減効果の予測額とセットで教えてください。

DECLARE active_logical_gib_price FLOAT64 DEFAULT 0.02;
DECLARE long_term_logical_gib_price FLOAT64 DEFAULT 0.01;
DECLARE active_physical_gib_price FLOAT64 DEFAULT 0.04;
DECLARE long_term_physical_gib_price FLOAT64 DEFAULT 0.02;

WITH
 storage_sizes AS (
   SELECT
     table_schema AS dataset_name,
     -- Logical
     SUM(IF(deleted=false, active_logical_bytes, 0)) / power(1024, 3) AS active_logical_gib,
     SUM(IF(deleted=false, long_term_logical_bytes, 0)) / power(1024, 3) AS long_term_logical_gib,
     -- Physical
     SUM(active_physical_bytes) / power(1024, 3) AS active_physical_gib,
     SUM(active_physical_bytes - time_travel_physical_bytes) / power(1024, 3) AS active_no_tt_physical_gib,
     SUM(long_term_physical_bytes) / power(1024, 3) AS long_term_physical_gib,
     -- Restorable previously deleted physical
     SUM(time_travel_physical_bytes) / power(1024, 3) AS time_travel_physical_gib,
     SUM(fail_safe_physical_bytes) / power(1024, 3) AS fail_safe_physical_gib,
   FROM
     `region-asia-northeast1`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
   WHERE total_physical_bytes + fail_safe_physical_bytes > 0
     -- Base the forecast on base tables only for highest precision results
     AND table_type  = 'BASE TABLE'
     GROUP BY 1
 )
SELECT
  dataset_name,
  -- Logical
  ROUND(active_logical_gib, 2) AS active_logical_gib,
  ROUND(long_term_logical_gib, 2) AS long_term_logical_gib,
  -- Physical
  ROUND(active_physical_gib, 2) AS active_physical_gib,
  ROUND(long_term_physical_gib, 2) AS long_term_physical_gib,
  ROUND(time_travel_physical_gib, 2) AS time_travel_physical_gib,
  ROUND(fail_safe_physical_gib, 2) AS fail_safe_physical_gib,
  -- Compression ratio
  ROUND(SAFE_DIVIDE(active_logical_gib, active_no_tt_physical_gib), 2) AS active_compression_ratio,
  ROUND(SAFE_DIVIDE(long_term_logical_gib, long_term_physical_gib), 2) AS long_term_compression_ratio,
  -- Forecast costs logical
  ROUND(active_logical_gib * active_logical_gib_price, 2) AS forecast_active_logical_cost,
  ROUND(long_term_logical_gib * long_term_logical_gib_price, 2) AS forecast_long_term_logical_cost,
  -- Forecast costs physical
  ROUND((active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) * active_physical_gib_price, 2) AS forecast_active_physical_cost,
  ROUND(long_term_physical_gib * long_term_physical_gib_price, 2) AS forecast_long_term_physical_cost,
  -- Forecast costs total
  ROUND(((active_logical_gib * active_logical_gib_price) + (long_term_logical_gib * long_term_logical_gib_price)) -
     (((active_no_tt_physical_gib + time_travel_physical_gib + fail_safe_physical_gib) * active_physical_gib_price) + (long_term_physical_gib * long_term_physical_gib_price)), 2) AS forecast_total_cost_difference
FROM
  storage_sizes
ORDER BY
  (forecast_active_logical_cost + forecast_active_physical_cost) DESC;

JX通信社の場合は、追記型のログデータなどは圧縮率が10倍を超えていました。

逆に、日次でスナップショットを取り込み、毎回全件を入れ替える洗い替えテーブルが多いデータセットは、圧縮率が高くなかったです。試算の結果、10倍程度に悪化するものもありました。そのため個別に、タイムトラベルの日数を減らすか、論理バイトストレージ課金のままとするかを判断しました。

切り替えと効果測定

切り替えるべきものが特定できたら、ALTER 文を発行して変更します。

ALTER SCHEMA `your_project.your_dataset`
SET OPTIONS (storage_billing_model = 'PHYSICAL');

書き換えが多いデータセットを physical にするなら、time travel の保持期間を短くしておくと、課金対象を減らせます（最短2日）。

ALTER SCHEMA `your_project.your_dataset`
SET OPTIONS (max_time_travel_hours = 48);

切り替え前の注意点

切り替えには、知っておくべき制約があります。

  • 反映までに24時間かかります。
  • 一度変更すると14日間は再変更できません。試しに切り替えて翌日戻す、ができないので、事前の損益試算が前提になります。
  • Long Term のステータスは切り替えでリセットされません。90日タイマーが巻き戻る心配はありません。

billing export での効果測定

切り替えたら、本当に効いているかを請求データのエクスポート（Google Cloud Billing export）で日次で追いました。私は怖かったので、Claude Code の /cron 機能で日次でコストを集計しながら、徐々に設定をしていました。

SELECT
  DATE(usage_start_time, "Asia/Tokyo") AS day,
  ROUND(SUM(IF(sku.description LIKE "%Logical%", cost, 0)), 2) AS logical_cost,
  ROUND(SUM(IF(sku.description LIKE "%Physical%", cost, 0)), 2) AS physical_cost
FROM `your_billing_export_table`
WHERE service.description = "BigQuery"
  AND sku.description LIKE "%Storage%"
GROUP BY day
ORDER BY day;

切り替えの翌々日あたりから、論理バイトストレージ課金が落ちて、代わりに小さな物理バイトストレージ課金の課金が乗ってくる様子が観測できました*1。 その水準は、事前にやった圧縮率の試算とほぼ一致しました。

まとめ

BigQueryのストレージ課金には論理バイトストレージ課金と物理バイトストレージ課金の2モデルがあり、デフォルトでは論理バイトストレージ課金で課金されています。

圧縮率が単価の倍率（Long Term で約1.6倍、Active で約2.3倍）を超えれば物理バイトストレージ課金の方が得になりますが、タイムトラベルとフェイルセーフ機能も課金対象になってしまうのがトレードオフです。

全データセットを実測してコストが削減できるものだけ ALTER SCHEMA で切り替えれば、コード変更なし、低リスクでストレージ課金を大きく圧縮できます。また、タイムトラベルの日数を減らすことでもコスト削減ができます。

BigQuery のストレージ料金に困っている方は、以上を踏まえて、ぜひコスト削減に取り組んでみてください。

*1:billing export は1〜2日遅れて反映されるので、最新日が安く見えるのは取り込み途中であることが多いです。完全に埋まった日で判断します

© 2022 JX PRESS Corp.