はじめに
こんにちは、株式会社Macbee Planet で3D ADという広告配信プラットフォームを開発・運用している山口（@tyykid16）です。
当プロダクトでは広告配信という性質上、秒間数万~のトラフィックを捌くと同時に大量のログが吐き出されます。このデータはBigQueryに保存しています。BigQueryでは物理ストレージの使用量に応じたActive storageとLong-term storageのコストが発生します。
コスト削減文脈で調査していたところ、BigQueryのストレージコストが想定を大きく超えていることが判明したため、コスト削減を実施しました。その結果、ストレージコスト（Active storage, Long-term storage）を約53%削減できたので、その方法と注意点をご紹介します。
想定読者
- BigQueryのストレージコストに悩んでいる方
- 月間数十TB以上のデータをBigQueryで扱っている方
- FinOps:クラウドコストの最適化・管理 に取り組んでいる方
結論
以下の2つのアプローチでストレージコストを半減させました。
- 不要なストレージデータの削除
- 長期保存が必要なデータのGCSエクスポート
対象となったデータと対応方針について
以下の表がテーブル種別とそれぞれの対応方針になります。
| テーブル種別 | 対応方針 |
|---|---|
| ① 中間テーブル, ログB, C | 1. 有効期限後を自動削除 |
| ② 生ログA | 1. 有効期限後を自動削除 2. 有効期限2日前をGCSへエクスポート |
削除対象となったテーブルは4つあり、利用要件の違いにより、異なる方針を採用しました。
①については、中間テーブルのデータやビジネス的に直接的な利用価値が薄いデータに関しては一定期間経過後に削除される設定としました。
②については、一定期間を過ぎた後のデータ削除に加えて、プロダクト的にで重要度の高いデータについては一定期間が過ぎた後についても、保存コストの安いストレージに移行する方針を採りました。
処理の全体像
＊なお、今回のコスト削減の対象としたテーブルは全てPhysicalストレージ課金モデル（実際に保存されているデータ量に基づいて課金される方式）を採用しております。BigQueryには他にLogicalストレージ課金モデルもあり、利用モデルによって課金形態が異なる点にご注意ください。
補足：本番環境での実装について
本記事ではコスト削減の手法に絞って実装等を解説していますが、実際の本番環境では適切なエラーハンドリング・セキュリティ観点の考慮も重要になります。これらについては別途適切な設計・実装が必要ですので、皆様の環境に合わせて、セキュリティチームやSREチームと連携して実装することをお勧めします。
やったこと
1. パーティションテーブルの自動削除設定
まず先の表の①、②に共通するデータの自動削除についてご説明します。データにはDAYパーティションが適用されており、それゆえ最もシンプルで効果的だったのがpartition_expiration_daysの設定です。この施策だけで、今回のストレージコストの削減は達成されました。
-- 例えば30日後に各パーティションを自動削除する場合
ALTER TABLE `project.dataset.table_name`
SET OPTIONS (
partition_expiration_days = 30
);
解説：partition_expiration_days とは
これは、指定した時間以降の古いパーティションを自動的に削除するためのパラメータになります。スキャン量は発生せず、自動的に条件に当てはまる partition データを削除するという点が特徴です。
🕳️ はまりポイント
この方針に決めるまでや実行中にはまったポイントが2つあります。
1. データ削除の際に思わぬクエリコストが発生する可能性
当初は、WHEREを使って特定の日付以降のデータを削除するクエリをバッチ処理で実行する方針を検討していました。しかし、調査・検証の結果、PARTITIONを明示的に指定した場合でも、クエリの書き方によってはデータスキャンを発生することがわかりました。これにより予期せぬコストを生じる場合があります。
そのため、最終的にはテーブル設定としてpartition_expiration_daysを適用する方針を選択しました。
また、バッチジョブによる削除をやめた理由は、コストに加えて、ジョブの管理・監視・エラーハンドリングの運用コストが膨らむことを避けたかった意図もあります。
解説：DELETE文の課金条件について
先述した実は請求されるパターンを解説します。
BigQueryでは、qualifying DELETE statementがパーティション内の全行をカバーする場合のみ、バイトスキャンやスロット消費なしでパーティション全体を削除できます。
一部に過ぎないですが、具体例としては以下のSQLのとおりです。
-- 読み込みが発生しない：パーティション削除
DELETE FROM mydataset.mytable WHERE _PARTITIONDATE = '2024-01-01';
DELETE FROM mydataset.mytable WHERE _PARTITIONDATE IN ('2024-01-01', '2025-06-27');
-- 読み込みが発生：パーティション内の一部データ削除（複雑な条件）
DELETE FROM `project.dataset.table`
WHERE DATE(timestamp) < DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)
一部の削除クエリでは、対象パーティションがスキャンされることで課金が発生する可能性があります。削除データのサイズによっては、1回の削除クエリで高額な請求につながる可能性があります。不要データ削除をされる際は実行前にdry runで読み込み料を見積もったり、小さい範囲のデータ削除から実行するよう注意すると安心できます。
2. 削除したデータに対する請求
Active StorageやLong-term Storageに保持しておくことに比べたら大きな金額ではないですが、削除されたデータも、一定期間は課金対象となる仕様になっています。
BigQueryの物理ストレージ課金モデルでは、データを削除しても即座にストレージから消去されるわけではありません。削除されたデータは以下の2つの期間、引き続きストレージを消費します。
- Time Travel期間（2〜7日間、設定可能）
- 削除したデータを復旧可能な期間
- この期間中はActive Physical Storage料金が適用される
- データセット単位で期間を設定可能
- Fail-safe期間（7日間、固定）
- 災害復旧のために保持する期間
- この期間中もActive Physical Storage料金が適用される
- ユーザーは直接アクセスできない
削除されたデータの流れ
time travel期間は2〜7日の範囲で設定でき、設定するとデータセット内のすべてのテーブルに適用されます。
コスト軽減策としてtime travel期間を48時間（2日間）まで短縮できますが、誤削除時の復旧可能期間が短くなる点に注意が必要です。
-- データセット全体のTime Travel期間を2日間に設定
ALTER SCHEMA `project.dataset`
SET OPTIONS (
max_time_travel_hours = 48
);
2. 有効期限以降の重要データのGCSへの一括エクスポート
続いて、これ自体は直接的にコスト削減に寄与しないのですが、コストを削減しつつ、ビジネスに影響を出さないようにするための施策になります。具体的には、先の表で言う②のように直近利用しないがビジネス上重要なデータを含むケースは、万が一の復旧や分析範囲を広げてのデータ参照に備えてGCSにエクスポートしてから削除しました。
エクスポートスクリプト詳細
以下のScriptを手動で実行することで、月単位でデータのGCS移行をしました。
手動で実行した意図としては、クォータ（Maximum number of exported bytes per day）に抵触すると他のジョブが実行できなくなり障害となることや、エクスポートジョブによって消費されるスロット量を正確に見積もれないといった背景がありました。
しかし、振り返ってみるとジョブ実行中に現状の利用量を動的に取得しつつ、実行を制御できるとよりスマートだったと考えます。
#!/bin/bash
# ------------------ 固定値 ------------------
PROJECT_ID="your-project-id"
DATASET="your_dataset"
TABLE="your_table"
BUCKET="gs://your-bucket-name"
START_DATE="20230607" # 開始日付 (YYYYMMDD)
END_MONTH="202406" # 終了月 (YYYYMM)
# -------------------------------------------
# START_DATEから年月を抽出
START_MONTH="${START_DATE:0:6}"
current_month="${START_MONTH}"
# START_MONTH から END_MONTH までループ
while [ "$(date -d "${current_month}01" +%Y%m)" -le "$(date -d "${END_MONTH}01" +%Y%m)" ]; do
TARGET_MONTH="$(date -d "${current_month}01" +%Y%m)"
# 最初の月の場合は指定されたSTART_DATEから開始
if [ "${TARGET_MONTH}" = "${START_MONTH}" ]; then
MONTH_START_DATE="${START_DATE}"
else
MONTH_START_DATE="${TARGET_MONTH}01"
fi
# 月の最終日を計算
MONTH_END_DATE="$(date -d "${TARGET_MONTH}01 +1 month -1 day" +%Y%m%d)"
# 日数を計算
start_timestamp=$(date -d "${MONTH_START_DATE}" +%s)
end_timestamp=$(date -d "${MONTH_END_DATE}" +%s)
days=$(( (end_timestamp - start_timestamp) / 86400 ))
echo "▶︎ Processing month ${TARGET_MONTH} (${MONTH_START_DATE}–${MONTH_END_DATE})"
for i in $(seq 0 "${days}"); do
TARGET_DATE=$(date -d "${MONTH_START_DATE} +${i} days" +%Y%m%d)
echo " └ Exporting partition ${TARGET_DATE} …"
if bq extract \
--project_id="${PROJECT_ID}" \
--destination_format=PARQUET \
--compression=ZSTD \
"${PROJECT_ID}:${DATASET}.${TABLE}\$${TARGET_DATE}" \
"${BUCKET}/monthly/${TARGET_MONTH}/${TARGET_DATE}/*.parquet"; then
echo " ✓ Successfully exported ${TARGET_DATE}"
else
echo " ✗ Failed to export ${TARGET_DATE}" >&2
exit 1
fi
done
echo "✓ Month ${TARGET_MONTH} done."
# 次の月へ
current_month="$(date -d "${current_month}01 +1 month" +%Y%m)"
done
echo "✅ All months completed."
💡 工夫ポイント
BQのデータをGCSへエクスポートする上で工夫したことを2つ紹介します。
1. データexport Quotaの引き上げ申請
データエクスポートはクォータ上限により50TiB per day（約50TB/日）と決まっています。今回移行対象となったテーブルは非常に大きかったため、移行作業短縮のためGoogle CloudへQuota引き上げしました。今回は承認いただけたので、上限を引き上げ、移行工数の圧縮を実現できました。移行データ量が多い場合は、事前にクォータ引き上げ申請を行うことをお勧めします。
2. 圧縮形式: ZSTDを選択
今回BigQueryからGCSへエクスポートするデータ形式として選択したParquetでは、SNAPPY・GZIP・ZSTDの3つの圧縮形式から選択できました。
ZSTD（Zstandard）を選択した理由は、他社の事例でSNAPPYと比較して圧縮率改善を実現しながら、読み取り性能は落とさないという事例があったためです。今回のデータ移行においても、この圧縮率の向上は本件でも有効と考えました。
3. 有効期限切れ直前データの日次エクスポートバッチジョブの作成
Cloud Run Functionsによる有効期限2日前のデータエクスポート
最後に先の表で言う②のように直近利用しないがビジネス上重要なデータは、1. の対応によって日次で有効期限のデータが削除されていきます。そこで、2. の対応移行に削除されうるデータを対象にバックアップ目的で有効期限が来る前に日次でGCSにエクスポートするようにしました。
アーキテクチャの選択
有効期限2日前のデータを自動でエクスポートするため、以下のアーキテクチャを採用しました。
Cloud Scheduler → Pub/Sub Topic → Cloud Run Functions → BigQuery/GCS
(毎日定時実行) (メッセージ発行) (エクスポート処理) (データ操作)
このアーキテクチャを選択した最大の理由は、信頼性とコスト効率のバランスです。Pub/Subを介することで、一時的な障害が発生してもメッセージが自動的にリトライされるため、データの取りこぼしを防げます。また、各コンポーネントが疎結合になっているため、将来的に処理内容を変更する際も影響範囲を限定できます。コスト面では、日次1回という実行頻度を考慮すると、Cloud Run Functionsの従量課金モデルが最も経済的になると考えました。
なぜ、有効期限の2日前なのか
エクスポートのタイミングを「有効期限-2日」に設定したのは、リスク管理の観点からです。パーティションは有効期限後に自動削除されるため、削除の2日前にエクスポートすることで、万が一エクスポートが失敗した場合でも翌日にリトライする余地を確保できます。この時間的バッファで、週末や祝日の障害発生時でも、翌営業日対応でデータ欠損を防ぎやすくなります。
まとめ
今回の取り組みで以下の成果を達成しました。
- BigQueryストレージコスト（Active storage, Long-term storage）を53%削減
コスト削減がうまくいった4つのポイント
今回のストレージコスト削減で学んだ重要なポイントをまとめました。
同じような課題に取り組まれる方の参考になれば幸いです 🙏
1. 現状を見える化する
- どのテーブルにいくらコストがかかっているか、まずは現状を正確に把握する。
-
INFORMATION_SCHEMA.TABLE_STORAGEを使えば、テーブルごとのストレージコストを簡単に確認できます。
2. データの重要度を見極める
- すべてのデータを同じように扱う必要はない。
- ビジネスに直結する重要なデータは残し、中間テーブルや内部ログなど再作成可能なものは削除対象にするなど、データの種類ごとに適切な保存期間を決めること。
3. データに応じた最適な方法を選ぶ
重要度に応じて対応を変える。
- すぐに使わない重要データ → GCSなど安価なストレージへ移行
- 再作成可能な中間データ → 削除
- 分析で頻繁に使うデータ → BigQueryに残す
このように、ビジネスへの影響を最小限に抑えながらコストを削減する方法を、データごとに検討する。
4. 小さく始めて段階的に広げる
いきなり本番環境で実施するのではなく、以下のような段階的なアプローチを取る。
- ステージング環境でテスト
- 影響の小さいテーブルから開始
- テストデータで動作確認
このような段階的なアプローチで、問題を早期発見できるはずです！
📣 最後に宣伝 📣
弊社では、大規模なトラフィックやデータに起因する課題をひとりひとりがオーナーシップを持ってフルスタックに取り組める機会がたくさんあります。
ご興味をお持ちいただけた場合は、私のX（@tyykid16）までお気軽にメッセージをいただけますと幸いです。まずはカジュアルにお話しましょう。
Discussion