BigQueryで不要なカラムを削除してコストを削減する方法

はじめに

こんにちは。アカツキゲームスの河野です。

弊社では、ゲームの運用においてプレイヤーのプレイ履歴などをBigQueryに保管しております。 またこのデータを用いることで、不具合の発見やよりプレイヤーが楽しめる企画や機能開発に役立てております。

一方で、長く運用していく中でこのような保存コストの問題にあたっていくことがあります。

• 特定のカラムのデータが活用されなくなっている
• にも関わらず容量が何TB、何十TB…と溜まって毎月の保存コストが高くかかってしまっているため、費用対効果が全く見合ってない

BigQueryの場合、テーブルごとデータを削除するのは容易ですが、大量のデータの入ったテーブルの特定のカラムだけを削除したいときの対処方法が、これから紹介する手順のように実は複雑になることがあります。

新規に作成される際はこのような悩みに当たらないよう、要件や想定利用量に応じて意図的にテーブルを分けることもオススメします。

※ 以下の情報は2024年9月現在の情報を基にしています。実行は2023年〜2024年前半ごろに実行したため、その後内部の挙動や性能が変わっている可能性もございます。

使えなかった手順：ALTER TABLE DROP COLUMN を使用する

BigQueryにはカラムを削除する「ALTER TABLE DROP COLUMN」ステートメントが用意されています。

大容量のテーブルでもほとんどダウンタイム無く利用できましたが、ドキュメント上では「カラムが削除されてもストレージが解放されることを保障するものではない」旨が書かれています。

Data definition language (DDL) statements in GoogleSQL  |  BigQuery  |  Google Cloud

Since DROP COLUMN is not a data cleanup operation, there is no guaranteed time window within which the data will be deleted.

私たちも実行後数ヶ月ほど待ってみましたが、見かけ上はカラムが削除されていてもストレージは解放されませんでした。

また待っている間も削除されたはずのカラムへのストレージコストが継続して請求されてしまっている状況でしたため、待つのを諦めて他の手順を利用することにいたしました。

実際に実行した手順

代わりに私たちのプロジェクトでは、「SELECT * EXCEPT クエリを用いてテーブルを上書きする」という操作を利用することにしました。 先程のドキュメントにも1番目の方法として紹介されています。

There are two options for immediately reclaiming storage:

・Overwrite a table with a SELECT * EXCEPT query.

・Export the data to Cloud Storage, delete the unwanted columns, and then load the data into a new table with the correct schema.

手順1：該当テーブルへのレコードの挿入を停止する。

私たちが試した結果ではテーブルの上書きが発生している期間中に挿入されたレコードについては、上書きされた後には反映されていませんでした。

そのためメンテナンスに入れるもしくは挿入予定のデータを一時的に別の場所に退避するなど、リプレイス対象のテーブルへのレコードの挿入を止める必要があります。

手順2：BigQueryのスロットを予約する。

確実に大容量のテーブルを作り直すためには、「スロット予約」を用いてBigQueryの計算資源を確保する必要があります。

通常のクエリ実行では予約がなくてもオンデマンドで計算資源が自動的に確保されていますが、大容量のテーブルの上書きではかなりの計算資源を利用するため、充分かつ安定的な確保ができない場合は下記のようなエラーが途中で発生してしまい、やり直しになる可能性が高いです。

Resources exceeded during query execution: Your project or organization exceeded the maximum disk and memory limit available for shuffle operations. Consider provisioning more slots, reducing query concurrency, or using more efficient logic in this job.

そのため下記のドキュメントに従ってスロット予約を実行します。なおスロット予約の容量については、リプレイス後のテーブル容量にもよりますが、なるべく確実に実行する観点では上限まで予約することをお勧めいたします。

スロット予約の操作方法

スロット予約の上限

なお料金については、例えばUSリージョンで10,000スロットの予約の場合、1時間につき $0.04 × 10000 = $400 利用料がかかります。

スロット予約の料金

また実行にかかった時間についてですが、私たちのプロジェクトで実行した実績としては「リプレイス後」の「論理バイト数」でそれぞれ

約 7TB の場合 → 9分（スロット予約無くても実行可）

約 150TB の場合 → 4時間

でした。 事前の正確な予測は難しいですが、容量によっては相当な時間と金額がかかることになります。

手順3：タイムトラベルの保持期間を確認する。

BigQueryには、一定の期間は容易にデータを変更前に復元することが可能になるという、タイムトラベル機能があります。

タイムトラベルについての詳細

これはテーブルを上書きした場合であっても復元可能であるため、万一の失敗時にも安心です。

ただし一つ問題になるのがストレージ料金です。

タイムトラベル期間については2〜7日間の範囲で設定できますが、タイムトラベル期間中 + フェイルセーフ期間中(7日間)は引き続き削除されたデータに対しても保存コストがかかります。テーブルの上書きを実行したあとも、引き続き9〜14日分は料金がかかり続けることになることに留意する必要があります。

そのため、コストと万一の際のデータ復元タイミングを鑑みて期間を見直して設定することを推奨します。

また単価もテーブルを上書きした直後であることによって「長期保存」料金ではなく、全てより割高な「アクティブストレージ」料金としてかかります。

BigQueryのストレージ料金詳細

手順4：CREATE OR REPLACE TABLE クエリを実行する。

例

CREATE OR REPLACE TABLE (テーブル名) AS (SELECT * except (削除したいカラム名) from  (テーブル名))

※ クラスタリングやパーティション等を利用している場合は、その分の設定もクエリに必要です。

手順5：手順1〜3で行った操作を元に戻す。

スロット予約を解除し、該当テーブルへのレコードの挿入を再開します。

またタイムトラベルの保持期間も必要あれば元に戻します。

まとめ

BigQueryのテーブルで不要なカラムが生じた場合でも、このようにカラムを削除して保存コストを削減することは可能です。

しかし、現状ではテーブルのリプレイスが必要になり、またテーブルを作り直すために様々な設定や一時的な対応コストがかかることになります。

状況が変化しても不要になった特定カラムの保存コストで頭を悩ませることが無いよう、可能な範囲で先を見据えて設計しようと思うきっかけにもなりました。