全部見せます！ BigQueryのコスト削減の手法とその効果 株式会社ZOZO
 技術本部 データシステム部 データ基盤ブロック
 ブロック長
 塩崎健弘 Copyright © ZOZO, Inc. 1

© ZOZO, Inc. 株式会社ZOZO 技術本部 データシステム部 データ基盤ブロック ブロック長 テックリード 塩崎 健弘 ● ZOZOのデータエンジニア ● 公式ドキュメントを読んで裏技を探すのが好き ● 巨大なごはんですよのオブジェは自作です 2

© ZOZO, Inc. https://zozo.jp/ 3 ● ファッションEC ● 1,600以上のショップ、9,000以上のブランドの取り扱い ● 常時102万点以上の商品アイテム数と毎日平均2,600点以上の新着 商品を掲載（2024年6月末時点） ● ブランド古着のファッションゾーン「ZOZOUSED」や コスメ専門モール「ZOZOCOSME」、シューズ専門ゾーン 「ZOZOSHOES」、ラグジュアリー＆デザイナーズゾーン 「ZOZOVILLA」を展開 ● 即日配送サービス ● ギフトラッピングサービス ● ツケ払い など

© ZOZO, Inc. 4 目次 ● クエリ実行の課金モデル変更 ● ストレージの課金モデル変更 ● テーブルのパーティショニング・クラスタリング ● 余剰Slotのみを使用するプロジェクトの活用 ● コミットメントの購入 ● オンデマンド課金のプロジェクトのQuota設定 ● 参照頻度の低いテーブルの削除 ● スプレットシート経由のクエリの棚卸し ● Pub/SubのBigQuery Subscription機能への移行 ● まとめ

© ZOZO, Inc. 5 目次 ● クエリ実行の課金モデル変更 ● ストレージの課金モデル変更 ● テーブルのパーティショニング・クラスタリング ● 余剰Slotのみを使用するプロジェクトの活用 ● コミットメントの購入 ● オンデマンド課金のプロジェクトのQuota設定 ● 参照頻度の低いテーブルの削除 ● スプレットシート経由のクエリの棚卸し ● Pub/SubのBigQuery Subscription機能への移行 ● まとめ

© ZOZO, Inc. 6 クエリ実行の課金モデルは大きく2つに分類 On-demand Capacity (Editions) 登場時期 BigQueryの登場初期から 2023年7月 ※Slotを使った課金モデルは昔からあったが 　このタイミングで大幅改定 課金対象 スキャンしたデータ量 Slot割当量 従量課金 or 定額 従量課金 設定次第でどちらでも コスパ良く集計できるクエリ ・CPUを沢山使う ・スキャン量が少ない ・CPUをあまり使わない ・スキャン量が多い クエリの特性によって、どちらの課金モデルで集計したほうがコスパ良いかが変わる → 最適な課金モデルを自動的に決定する仕組みを構築

© ZOZO, Inc. 7 それぞれのモデルでコスパの良いクエリの極端な例 -- On-demandの方がコスパの良いクエリ (CPUを多く消費・スキャン量少ない ) SELECT 　　複雑な計算 FROM 小さなテーブル CROSS JOIN 小さなテーブル CROSS JOIN 小さなテーブル CROSS JOIN 小さなテーブル -- Capacityの方がコスパの良いクエリ (CPUをあまり消費しない・スキャン量多い ) SELECT * FROM 大きなテーブル LIMIT 1

© ZOZO, Inc. 8 Capacity(Editions)は更に3つに細分化 Standard Edition Enterprise Edition Enterprise plus Edition ※ Enterpriseとほとんど同機能なので　 本発表では除外 単価(USD / slot / hour) 0.04 0.06 0.1 SLA >= 99.9% >= 99.99% >= 99.99% Slot数上限 1600 無制限 無制限 行・列レベルセキュリティ 動的データマスク VPC SCなど X O O CMCKなど X X O ● Enterprise Editionの方ができることが多いが高価 ● On-demandはEnterprise plusと同等の機能を持つ

© ZOZO, Inc. 9 本発表で切り替え対象の3つのモデル Standard Edition Enterprise Edition On-demand 課金対象 Slot割当量 Slot割当量 スキャンしたデータ量 単価 0.04 USD/slot/hour 0.06 USD/slot/hour 6.25 USD/TB 行・列レベル セキュリティなど X O O ● これら3つの中から機能が十分かつ最安価なものを決定する ● 行・列レベルセキュリティを使用している場合 ○ Enterprise Edition vs On-demand ● 行・列レベルセキュリティを使用していない場合 ○ Standard Edition vs On-demand

© ZOZO, Inc. 10 それぞれのモデルで実行した時のコスト見積もり SELECT -- 料金単価はUSリージョンのもの total_bytes_billed / POW(2, 40) * 6.25 AS on_demand_cost, total_slot_ms / 1000 / 3600 * 0.04 AS approximate_standard_edition_cost, total_slot_ms / 1000 / 3600 * 0.06 AS approximate_enterprise_edition_cost, FROM `region-us`.INFORMATION_SCHEMA.JOBS WHERE job_id = "調査対象のJob ID" ● INFORMATION_SCHAME.JOBSからスキャン量やSlot消費量を参照 ● ん・・？approximate? 🤔 ● 罠: Standard/Enterprise Editionのコストは概算でしか分からない

© ZOZO, Inc. 11 Standard/Enterprise Editionsのコストは概算(の前に) ● BigQuery費用は抽象度が高いのでVirtual Machine(GCE, EC2など)で例えます ● 問題 ○ タロウ君はVirtual MachineをCPU使用率25%で使っています ○ このVirtual Machineの価格は$100です ○ さて、タロウ君はクラウドベンダーにいくら支払うでしょうか？ $100のVirtual Machine タロウ君 CPU使用率25% 使う

© ZOZO, Inc. 12 Standard/Enterprise Editionsのコストは概算(の前に) ● 答え: $100 ● CPU使用率が0%でも100%でも支払額は$100 ○ 課金対象のCPU($100) = 実際に使用したCPU($25) + Idle状態のCPU($75) ● 先程のapproximateな費用は、Idle状態のCPU費用に相当するものが入っていない $100のVirtual Machine タロウ君 CPU使用率25% 使う

© ZOZO, Inc. 13 Standard/Enterprise Editionsのコストは概算 ● 課金対象のSlot = 使用されたSlot + 余剰Slot ○ 課金対象のSlot: Commitment + AutoScale Capacity ○ 使用されたSlot: Total Usage ● 余剰Slotが生じる主な理由 ○ 課金対象のSlotは50Slot単位・1分単位でしかAutoScaleしない ○ Slotを使い終えてからAutoScalerがSlotを解放するまでのタイムラグ ● これらの効果で実際のコストは概算値から2〜3割程度高くなる ○ 概算値と実際のコストの差分の計測が大事 ○ ワークロードによって何割増しになるのかは異なる

© ZOZO, Inc. 14 Standard/Enterprise Editionsのコストは概算 ● 余剰Slotが生じる効果を組み入れて行けば正確な値になるのでは？ ○ 答え: 部分的にそう ● 実際の請求金額に近づきはするが、どうしても減らせない誤差もある ● Reservation(Slotを共有する論理的な入れ物)単位でSlotのAutoScaleが行われる ○ 例: 1つのReservation内に10Slotを使用するクエリが5つあった場合 ■ ❌ それぞれのクエリに対して50単位で切り上げてから合算 ■ ⭕ 合算してから切り上げ ○ 他のクエリの影響で合算結果が変化する ○ クエリ単体/プロジェクト単体でのコストを集計するという方法の限界 ● 実際に実施した後に、概算値と実際のコストの差分の計測が大事(再掲) 󰨄

© ZOZO, Inc. 15 クエリ実行履歴からそれぞれのプランの費用の見積もり ● 実際に3モデルでいくらのコストが発生するのかは実行後でないと分からない ○ 実行前に見積もりをしたい ● INFORMATION_SCHEMA.JOBSを使えば過去のクエリに対する概算値は分かる ● 定期実行バッチに対して以下を仮定する ○ クエリ(SQL文)は前回までと概ね等しい ○ テーブル内のデータは前回までと概ね等しい ● 過去30日分の実行履歴からそれぞれのプランの実行費用を見積もり ○ WHERE destination_table = 書き込み先のテーブル という条件で抽出 ● 3モデルそれぞれの費用見積もりを計算

© ZOZO, Inc. 16 Standardモデルでクエリ実行できるか ● ZOZOのデータ基盤では個人情報などを含む列に列レベルセキュリティを実施 ● それらのテーブルに対するクエリはStandard Editionで実行できない ○ たとえ個人情報の含まれない列のみを取得する場合でも無理 ● 個人情報が含まれているテーブル一覧マスタを作成 ○ INFORMATION_SCHEMAやCloud Asset Inventoryなどから取得できない ○ 多少の抜け漏れはあり、100%正確に網羅している訳ではない ● INFORMATION_SCHEMA.JOBSのreferrenced_tablsが上記のテーブルを含んでいるかをチェック ○ 過去のジョブ情報に基づいている ○ 今まさに実行しようとしているクエリに対して正確な結果は返してくれない ● この手法でStandardモデルでクエリが実行できるかどうかが、概ね正しく判断できる ○ 概ねなので、誤判断の場合を救う必要がある

© ZOZO, Inc. 17 クエリ実行の課金モデル変更の前準備 ● 課金モデルはReservationに紐づく一方でクエリを実行するときに指定するのはプロジェクト ● 以下の3つのReservationを用意して、実行時にどのプロジェクトを使用するのかを指定 ○ On-demand (Explicit On-demand) ○ Standard Edition ○ Enterprise Edition ● 注: プロジェクト間でのデータの移動は不要 ○ 実行時のプロジェクトとデータが保管されているプロジェクトは完全に別物 # projectAに保管されているデータに対するクエリをprojectBで実行 client = bigquery.Client() sql = "SELECT * FROM `projectA.dataset.table" client.query(project_id="projectB", query=query)

© ZOZO, Inc. 18 どのモデルで実行するのかを決定する疑似コード if Standardで実行不可能なクエリ : if On-demandのコスト > Enterpriseのコスト * 1.2: Enterpriseモデルでクエリ実行 else: On-demandモデルでクエリ実行 else: if On-demandのコスト > Standardのコスト * 1.2: Standardモデルでクエリ実行 if 失敗: Enterpriseモデルでクエリ実行 # 実はStandardで実行不可能だった場合の救済 else: On-demandモデルでクエリ実行 ● クエリ単位でこの最適化を実施

© ZOZO, Inc. 19 クエリ実行の課金モデル変更の効果 ● 日次集計バッチのコストが約4割削減できました ● 変更前(クエリ数の比率) ○ Enterprise Edition: 100% ● 変更後(クエリ数の比率) ○ Standard Edition: 50% ○ Enterprise Edition: 30% ○ On-demand: 20% ● 詳細はこちらで公開 ○ 「BigQueryを使った集計バッチの費用をActive Metadata Managementの知見を活かして4割減ら した話」

© ZOZO, Inc. 20 目次 ● クエリ実行の課金モデル変更 ● ストレージの課金モデル変更 ● テーブルのパーティショニング・クラスタリング ● 余剰Slotのみを使用するプロジェクトの活用 ● コミットメントの購入 ● オンデマンド課金のプロジェクトのQuota設定 ● 参照頻度の低いテーブルの削除 ● スプレットシート経由のクエリの棚卸し ● Pub/SubのBigQuery Subscription機能への移行 ● まとめ

© ZOZO, Inc. 21 ストレージの課金は2軸・4象限がある 非圧縮データで容量計算 圧縮済みデータで容量計算 過去90日間で変更された パーティション Active Logical 0.02 USD/GB/month Active Physical 0.04 USD/GB/month 90日間連続して変更されてい ないパーティション Long-term Logical 0.01 USD/GB/month Long-term Physical 0.02 USD/GB/month ● (Active or Long-term) * (Logical or Physical)で合計4つの組み合わせ ● 本発表ではLogical or Physicalという軸のみを扱います

© ZOZO, Inc. 22 Logical StorageとPhysical Storageの違い Logical Storage Physical Storage 単価 0.02 USD/GB/month(Active) 0.01 USD/GB/month(Long-term) 0.04 USD/GB/month(Active) 0.02 USD/GB/month(Long-term) 課金対象のデータ量 非圧縮で計算 圧縮済で計算 過去データ 課金対象外 課金対象 コスパが良いデータの特徴 低圧縮率・高更新頻度 高圧縮率・低更新頻度 ● BigQueryの圧縮アルゴリズムは平均的にデータ量を約1/4にする(at ZOZOのデータ基盤) ○ 平均的にはPhysicalの方が費用が半分になる(単価が2倍 * データ量が1/4倍) ● 過去データに対する課金の有無は見落としやすい仕様なので要注意 ○ 数行上の費用が半分の話は過去データを無視した計算 ● 圧縮かどうかは費用計算時にのみ考慮され、実際に圧縮して保存するかどうかとは関係なし ○ 切り替えてもクエリパフォーマンスは変わらない

© ZOZO, Inc. 23 Logical Storage課金の場合の費用計算方法 SELECT *, active_logical_tb * 20 + long_term_logical_tb * 10 AS monthly_logical_cost_usd, FROM ( SELECT table_schema AS dataset_name, SUM(active_logical_bytes) / POW(2, 40) AS active_logical_tb, SUM(long_term_logical_bytes) / POW(2, 40) AS long_term_logical_tb, FROM `プロジェクトID`.`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE GROUP BY table_schema ) ● INFORMATION_SCHEMA.TABLE_STORAGEに費用計算に必要な情報が入っている ● 過去データに対する課金が発生しないのでシンプル

© ZOZO, Inc. 24 Physical Storage課金の場合の費用計算方法 SELECT *, (active_without_time_travel_physical_tb + time_travel_physical_tb + fail_safe_physical_tb) * 40 + long_term_physical_tb * 20 AS monthly_physical_cost_usd, FROM ( SELECT table_schema AS dataset_name, SUM(active_physical_bytes - time_travel_physical_bytes) / POW(2, 40) AS active_without_time_travel_physical_tb, SUM(long_term_physical_bytes) / POW(2, 40) AS long_term_physical_tb, SUM(time_travel_physical_bytes) / POW(2, 40) AS time_travel_physical_tb, SUM(fail_safe_physical_bytes) / POW(2, 40) AS fail_safe_physical_tb, FROM `プロジェクト ID`.`region-us`.INFORMATION_SCHEMA.TABLE_STORAGE GROUP BY table_schema ) ● 過去データに対する課金が発生するため要注意 ○ 過去データにはTime travelとFail safeの2種類が存在 ○ DROPされたテーブルも課金対象

© ZOZO, Inc. 25 ストレージの課金モデル変更方法 ● 先程の費用を各データセットに対して計算して安価な方を求める ● 切り替え方法 ○ Web UI, SQL(ALTER SCHEMA文), bqコマンドなどで切り替え可能 ○ 注1: 実際の切り替わりには24時間程度かかる ○ 注2: いったん切り替えると14日間は切り替えができない ● さらなる最適化 ○ Time travel windowを調整することでPhysicalの場合の費用を削減可 ○ Logicalの方が安価なテーブルとPhysicalの方が安価なテーブルでデータセットを分離

© ZOZO, Inc. 26 ストレージの課金モデル変更 ● 年間で数千万円のコストカット ● 詳細はこちらで公開 ○ 「BigQueryのストレージ料金プランを変更して、年間数千万円を節約する」

© ZOZO, Inc. 27 目次 ● クエリ実行の課金モデル変更 ● ストレージの課金モデル変更 ● テーブルのパーティショニング・クラスタリング ● 余剰Slotのみを使用するプロジェクトの活用 ● コミットメントの購入 ● オンデマンド課金のプロジェクトのQuota設定 ● 参照頻度の低いテーブルの削除 ● スプレットシート経由のクエリの棚卸し ● Pub/SubのBigQuery Subscription機能への移行 ● まとめ

© ZOZO, Inc. 28 テーブルのパーティショニング・クラスタリング ● 基本的にはBigQuery Recommenderにオススメされた通りに切り替え ○ パーティショニングの方がコストの予測可能性が高いのでパーティショニングを優先 ● DML一発でパーティショニングできないのでパーティショニングするストアドも作成 ○ https://qiita.com/shiozaki/items/6ef880a8e787ee6a7e23 ● 特定のバッチのコストが激減する効果があった ○ その他にも全体的なコストカットになった

© ZOZO, Inc. 29 コミットメントの購入 ● 1年・3年のコミットメントを購入するとSlot単価が大きく安くなる ● 前払い(Upfront)はなく支払いは月ごとに発生 ● Slot Recommenderがオススメを教えてくれる ● 途中キャンセルはできないのでご利用は計画的に

© ZOZO, Inc. 30 余剰Slotのみを使用するプロジェクトの作成 ● Slot数をゼロにしたReservationに紐づけたプロジェクト ○ AutoScaling SlotもBaseline Slotもゼロ ● 一見するとクエリの実行ができなさそうだが・・・ ○ 他のReservationの余剰Slotを「お裾分け」してもらえる ● どの程度のSlotをお裾分けしてもらえるのかは不明なので実行時間保証は一切なし ○ 実行時間の制約が緩い処理をこのプロジェクトで実行 ○ 実質無料で集計できるように

© ZOZO, Inc. 31 オンデマンドプロジェクトのQuota設定 ● 費用削減とはちょっと異なりますが、クラウド破産防止のために設定 ● プロジェクト全体のQuotaとユーザー毎のQuotaの両方がある ○ Query usage per day: プロジェクト全体 ○ Query usage per day per user: ユーザー毎 ● Quotaに達するとクエリが実行できなくなるので本番ワークロードで設定するときには要注意

© ZOZO, Inc. 32 参照頻度の低いテーブルの削除 ● 以下の2つの情報をJOINする ○ INFORMATION_SCHEMA.JOBS: どのテーブルがいつ参照されたのか ○ INFORMATION_SCHEMA.TABLES_STORAGE: テーブルのデータ量 ● 参照頻度が低いかつデータ量の大きいテーブル洗い出して作成者に削除を促す ○ 数カ月間参照されていないテーブルは、先に削除してしまうのも一つの手 ○ 必要だった場合はTime travelで復元

© ZOZO, Inc. 33 スプレッドシートから実行されているクエリの棚卸し ● スプレッドシートからの実行は便利な一方で資産管理がおざなりになることも ○ 既に不要になったクエリのスケジュール実行設定が解除されてないことも ● スプレッドシートから実行されているクエリを洗い出して棚卸し ● スプレッドシート経由で実行されているクエリの探し方はこちら ○ https://qiita.com/shiozaki/items/302aa331c16c5e1badf7

© ZOZO, Inc. 34 Pub/SubのBigQuery Subscription機能への移行 ● Pub/Sub→Dataflow→BigQueryという経路でログを入れていた ○ Dataflowで行っているデータ変換処理はほとんどゼロ ● 僅かに残っているデータ変換処理をAppEngineに移行した後にBigQuery Subscriptionへ移行 BigQuery Dataflow Pub/Sub AppEngine データ変換処理を AppEngineにオフロード

© ZOZO, Inc. 35 まとめ ● 色々な方法でBigQueryのコストを削減しました ○ データ基盤チームは個別の分析案件から距離をおいている ○ 個々の案件には依存しない全体的な最適化 ○ 裏で「いい感じ」に最適化するのがが得意なチームです ● 以下の情報にはコスト削減のヒントが詰まっているのでお友達になりましょう ○ INFORMATION_SCHEMA ○ Cloud Audit Log ● 話尽くせなかったネタも多くあるので、BigQuery使っている方々は懇親会でお声がけください ○ 今回の話を更に応用させた話も色々あります

No content