Sansan Tech Blog

Sansanのものづくりを支えるメンバーの技術やデザイン、プロダクトマネジメントの情報を発信

BigQueryのストレージ課金モデルの自動最適化

R&D BigQuery 自動化 Google Cloud

研究開発部の田仲です。

本記事は、Sansan Advent Calendar 2024の3日目の記事です。

背景

BigQueryのストレージ料金には、Logical（論理バイト）とPhysical（物理バイト）の2種類の課金モデルが存在します。
それぞれの特徴は次の通りです。

  • Logical: 非圧縮なデータの論理サイズに対して課金
  • Physical: 圧縮されたデータの物理サイズ + タイムトラベルやフェイルセーフ用の圧縮された物理サイズに対して課金

一般的にPhysicalモデルの方が単価は高いものの、データが圧縮されることで全体のコストは抑えられる傾向にあります。
ただし、次のような場合はLogicalモデルの方が有利になることがあります。

  • データの更新や削除が頻繁に発生する場合
  • 圧縮率が低いデータを扱う場合

Datasetごとに最適な課金モデルを選択するのがベストですが、個別にそれらを調査し、判別するのは手間がかかります。
そこで、今回はなるべく手間をかけずにコストを調整する方法をご紹介します。

どちらの課金モデルが適しているかを判断する

Datasetの課金モデルごとの料金は、TABLE_STORAGE ビューを使用し確認できます。1

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
table_storages as (
  select
    table_schema as dataset_name
    , deleted
    -- Logical
    , active_logical_bytes
    , long_term_logical_bytes
    -- Physical
    , active_physical_bytes
    , long_term_physical_bytes
    , time_travel_physical_bytes
    , fail_safe_physical_bytes
  from
    `region-us.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT`
  where
    table_type in(
      'BASE TABLE'
      , 'MATERIALIZED VIEW'
    ) 
)
, storage_sizes as (
  select
    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 - 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
    table_storages
  group by
    dataset_name
)
select
  dataset_name,
  (active_logical_gib * active_logical_gib_price)
    + (long_term_logical_gib * long_term_logical_gib_price) as logical_cost_per_month
  , ((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) as physical_cost_per_month
from
  storage_sizes
order by
  dataset_name;

自動的に安い方にする

次のクエリ2を定期的に実行することで、リージョン内の全データセットから課金モデルの切り替えが必要なテーブルを特定し、適切な課金モデルに自動的に切り替えられます。

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;

for iter in (
  with
  table_storages as (
    select
      table_schema as dataset_name
      , deleted
      -- Logical
      , active_logical_bytes
      , long_term_logical_bytes
      -- Physical
      , active_physical_bytes
      , long_term_physical_bytes
      , time_travel_physical_bytes
      , fail_safe_physical_bytes
    from
      `region-us.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT`
    where
      table_type in(
        'BASE TABLE'
        , 'MATERIALIZED VIEW'
      ) 
  )
  , schemata as (
    select
      schema_name as dataset_name
    from
      `region-us.INFORMATION_SCHEMA.SCHEMATA`
  )
  , schemata_options as (
    select
      schema_name as dataset_name
      , option_value as storage_billing_model
    from
      `region-us.INFORMATION_SCHEMA.SCHEMATA_OPTIONS`
    where
      option_name = 'storage_billing_model'
  )
  , asis_storage_billing_models as (
    select
      schemata.dataset_name
      , coalesce(schemata_options.storage_billing_model, 'LOGICAL') as storage_billing_model
    from
      schemata
    left join
      schemata_options
      using (dataset_name)
  )
  , storage_sizes as (
    select
      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 - 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
      table_storages
    group by
      dataset_name
  )
  , storage_costs as (
    select
      dataset_name,
      (active_logical_gib * active_logical_gib_price)
        + (long_term_logical_gib * long_term_logical_gib_price) as logical_cost_per_month
      , ((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) as physical_cost_per_month
    from
      storage_sizes
  )
  , tobe_storage_billing_models as (
    select
      dataset_name
      , case
          when logical_cost_per_month > physical_cost_per_month then 'PHYSICAL'
          when logical_cost_per_month < physical_cost_per_month then 'LOGICAL'
        else null
      end as storage_billing_model
    from
      storage_costs
  )
  , final as (
    select
      asis.dataset_name
      , tobe.storage_billing_model
    from
      asis_storage_billing_models as asis
    join
      tobe_storage_billing_models as tobe
      using (dataset_name)
    where
      asis.storage_billing_model != tobe.storage_billing_model
  )
  select
    *
  from
    final
) do
  begin
    execute immediate
      format(
        '''
          alter schema `%s`
          set options (
            storage_billing_model = @storage_billing_model
          );
        '''
        , iter.dataset_name
      )
    using
      iter.storage_billing_model as storage_billing_model;
  exception when error then
    case
      when @@error.message not like '%Change of storage billing model is not allowed since the previous switch happened%'
        then select error(@@error.message);
    end case;
  end;
end for;

処理概要

  1. DatasetごとにLogicalとPhysicalの料金を計算する
  2. 現状の課金モデルと、理想的な課金モデルが異なるDatasetを抽出する
  3. Datasetごとの設定を変更する

ポイント

  1. Dataset名は変数としてクエリにbindはできません。
    そのため、execute immediateを使用してクエリを実行しています。
  2. ストレージ課金モデルは、1度変更した後に再度変更するには14日間待つ必要があり3、その間に変更しようとするとエラーになります。
    そのため、その旨のエラーメッセージが返却された場合はその処理をスキップし、処理継続をするようにしています。
  3. あくまでも実績値を元に課金モデルを選択しており、将来的にコストが最安になることを保証するものではない点にご留意ください。

まとめ

BigQueryのストレージ課金モデルを自動的に最適化することで、次のようなメリットが得られます。

  • データの特性に応じて適切な課金モデルの自動選択し、ストレージコストを削減する
  • 運用負荷の軽減
  1. 本記事の料金変数で使用される料金は、USリージョンのものです。
    別のリージョンに対してこのクエリを実行する場合は、ストレージ料金を参考に料金変数を適宜更新し、併せてINFORMATION_SCHEMAのリージョンも変更してください。↩
  2. 本記事の料金変数で使用される料金は、USリージョンのものです。
    別のリージョンに対してこのクエリを実行する場合は、ストレージ料金を参考に料金変数を適宜更新し、併せてINFORMATION_SCHEMAのリージョンも変更してください。↩
  3. ストレージの課金モデル↩

© Sansan, Inc.