実践！BigQuery で DELETE！

Repro インフラチーム (SRE + 分析基盤) の山下です。 BigQuery で特定のレコードを削除する際の注意点やある程度大きなテーブル (20 TB・300 億レコード・500 パーティション程度) で実際に削除してみた際の挙動を共有します。

注意

2019 年 1 月頃に確認した仕様や挙動を書いています。現在の仕様や挙動とは異なる可能性があります。

BigQuery の DELETE サポート

以前は利用できなかったようですが、現在はベータ機能として提供されています。

cf. https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax?hl=ja#delete_statement

制限

BigQuery には様々な制限がありますが、特に DELETE に関係しそうな次の制限について引用を交えて説明します。

データ操作言語のステートメント
- 実行数
クエリジョブ
- 実行時間
- クエリの最大長
- スロット数
分割テーブルの制限
- パーティション数
- 同時実行

その他の制限や詳細は次のページを参照してください。

割り当てと制限 | BigQuery | Google Cloud

データ操作言語のステートメント

実行数

UPDATE、DELETE、および MERGE の各ステートメントを組み合わせた実行数の 1 日あたりのテーブルあたり最大値 - 200

1 日あたりの実行数が制限されています。都度 DELETE クエリを実行するのではなく、削除対象がある程度溜まってからまとめて削除する仕組みが必要になる可能性があります。

クエリジョブ

実行時間

クエリ実行時間の制限 - 6 時間

データを書き換えているためだと思いますが、SELECT よりも実行時間は長いです。

実行時間はスキャンするテーブルサイズに依存しているわけではなく、実際に DELETE により影響をうけるパーティションのサイズと数に依存します。テーブル全体をスキャンしても、削除対象レコードが含まれているパーティションが少ないと処理時間は短いです。

たとえば、ログインユーザーの行動ログを日付で分割して記録しているテーブルがあったとします。次のようなクエリで特定のユーザーのログを削除しようとした場合、「毎日ログインしているユーザーのログ」よりも「一週間に一回しかログインしていないユーザーのログ」を削除する方が削除対象レコードが含まれているパーティションが少ないので処理時間は短いです。なお、スキャンするテーブルサイズが同じ場合、処理時間や削除されるレコード数に関係なく料金は同じです。

#standardSQL
DELETE FROM sample_db.UserActivities
-- UserActivities の全パーティションから user_id が 1 のユーザーの行動ログをすべて削除する。一週間に一回しかログインしていないユーザーの場合、書き換え対象となるパーティションが少ないので、毎日ログインしているユーザーにくらべて処理時間は短い。
WHERE user_id = 1

弊社で次の条件のテーブルで実験した際は 50 分程度かかりました。

テーブルサイズ: 20 TB 程度
カラム数: 20 カラム
レコード数: 300 億レコード程度
削除対象レコード数: 500 万レコード程度
パーティション数: 500 パーティション程度
削除対象レコードが含まれているパーティション: すべてのパーティション

クエリの最大長

未解決クエリの最大長 - 256 KB 解決済みクエリの最大長 - 12 MB

解決済みクエリの長さに対する制限では、クエリで参照しているすべてのビューとワイルドカードテーブルの長さも対象になります

次のようなクエリで、対象 user_id が増えてクエリが長くなっていくと問題になる可能性があります。

#standardSQL
DELETE FROM sample_db.Users
WHERE user_id in (1,2,3,4,5,6,7...)

クエリが長くなる場合、ドキュメントの例にもあるように削除対象を指定するためのテーブルを別途用意するのが良いと思います。

cf. https://cloud.google.com/bigquery/docs/updating-data?hl=ja#deleting_data

#standardSQL
DELETE FROM sample_db.UserSessions
WHERE user_id in (SELECT id from sample_db.DeletedUsers)

スロット数

オンデマンド料金のプロジェクトあたり同時実行最大スロット数 - 2,000

オンデマンドクエリに対するデフォルトの個数のスロットは、同じプロジェクトで実行するすべてのクエリで共有されます。一般的に、一度に処理するクエリの容量が 100 GB 未満であれば、2,000 個のスロットを使い切る可能性はほとんどありません。現在使用しているスロットの個数を確認するには、Stackdriver を使用して BigQuery をモニタリングするをご覧ください。2,000 個を超えるスロットが必要な場合は、営業担当者にお問い合わせいただき、定額料金が適切かご検討ください。

スロット制限はプロジェクト全体に影響し、同時に実行している他のクエリのパフォーマンスに影響を与えるようなので注意してください。大きなテーブルからの削除処理はスロット利用数が高い状態が続くので、他の集計バッチ処理等と時間帯をずらすことを検討したほうが良いです。

スロットについての詳しい説明は次のページを参照してください。

スロット | BigQuery | Google Cloud

分割テーブルの制限

パーティション数

1 つのジョブで変更される最大パーティション数 - 2,000

ジョブオペレーション（クエリまたは読み込み）ごとに対象にできるパーティションは最大 2,000 です。2,000 を超えるパーティションを対象とするクエリまたは読み込みジョブは、Google BigQuery で拒否されます。

1 クエリで DELETE できるのは 2000 日 (5.47 年) 分までに制限されています。最大パーティション数を超えた場合、パーティション範囲を限定し逐次実行すれば良いでしょう。

パーティションに対するオペレーションの最大レート - 10 秒ごとに 50 回のパーティションオペレーション

実行時間に影響を与えそうですが、弊社で実験したところ、この制限が律速になっていることはなさそうでした。パーティションあたりのデータ量が少なく、処理速度が速い場合は影響があるかもしれません。

同時実行

公式ドキュメント内では確認できませんでしたが、同じテーブルに同時に Delete 文を発行することはできないようです。処理時間短縮のために削除対象とするパーティションを分割して並列で実行することを検討しましたが、次のようなエラーが発生しました。

Could not serialize access to table project_name:data_set_name.table_name due to concurrent update

料金

料金は、次の条件で計算されたデータ量に対してオンデマンドクエリの料金 ($5.00 per TB) がかかります。

クエリでスキャンされたテーブルのすべてのパーティションで参照しているすべての列で処理されたバイトの合計 + DELETE の実行開始時点で、変更対象テーブルの変更済みパーティションまたはスキャン済みパーティションにあるすべての列のバイトの合計。

cf. https://cloud.google.com/bigquery/pricing#dml_pricing_for_partitioned_tables

結果として 1 行も削除されなかったとしても、削除しようとしてスキャンしたパーティションサイズ分課金されます。

また、分割テーブルの場合、スキャンした範囲は長期保存ストレージの無編集日数計数タイマーがゼロになるので注意してください。

cf. https://cloud.google.com/bigquery/pricing#long-term-storage

復元について

万が一間違って削除してしまった場合、BigQuery のスナップショットデコレータを利用してデータを復元できる可能性があります。詳細は別の機会に共有しようと思います。

求人情報

インフラチーム (SRE + 分析基盤) では一緒に働く仲間を探しています！