概要
BigQueryでINSERT処理直後に頻繁にUPDATE・DELETE処理を行うとエラーが起こり、
30分ほど処理が行えない問題に直面したので、それを回避した方法をまとめます。
直面したエラー
pythonを使ってBigQueryのデータを操作していたのですが、以下のようなエラーが発生しました。
from Exception: query job failed error: [{'reason': 'invalidQuery', 'location': 'query', 'message': 'UPDATE or DELETE statement over table project.dataset.xxxx_table would affect rows in the streaming buffer, which is not supported'}]
INSERTやUPDATEのクエリには問題がなく、何度もクエリを実行していると発生しました。
公式ドキュメントをみると以下のような制限があるようです。
Rows that were written to a table recently by using streaming (the tabledata.insertall method or the Storage Write API) cannot be modified with UPDATE, DELETE, or MERGE statements. The recent writes are those that occur within the last 30 minutes. All other rows in the table remain modifiable by using UPDATE, DELETE, or MERGE statements. The streamed data can take up to 90 minutes to become available for copy operations.
(日本語訳)
ストリーミング（tabledata.insertallメソッドまたはStorage Write API）を使用して最近テーブルに書き込まれた行は、UPDATE、DELETE、MERGE文では変更できません。最近の書き込みとは、過去30分以内に発生したものです。テーブル内の他の行はすべて、UPDATE、DELETE、MERGE文を使用して変更可能なままです。ストリーム・データがコピー操作に使用できるようになるまで、最大90分かかることがあります。
メソッドを使ってストリーミング挿入した行に頻繁に更新をかけていたことが問題のようでした。
回避策
回避策の一つとして、ストリーミング挿入ではなくバッチ読み込みで
JSONやCSVのファイルから挿入する方法があります。
しかし、ファイルの作成やGCSにアップロードする処理の実装が面倒なので、UPDATEを使わなくて良いように設計し直すことにしました。
元々の設計では、タスクとステータス、更新日時等のカラムを持ったタスクを管理するテーブルが一つあり、
タスクができたらINSERTし、数分おきにタスクのステータスを確認して、UPDATE文でステータスと更新日時を更新するようにしていました。
そのため数分おきにUPDATE文を実行する状況になっていました。
イメージ
task
| id | task | status | modify_dt |
|---|---|---|---|
| 1 | task_a | success | 2023-07-01 10:00:00 |
| 2 | task_b | running | 2023-07-01 10:00:00 |
そこで、UPDATE処理を実行しないで済むように以下のようにテーブルを二つに分けるようにしました。
task
| id | task | regist_dt |
|---|---|---|
| 1 | task_a | 2023-07-01 10:00:00 |
| 2 | task_b | 2023-07-01 10:00:00 |
task_log
| uuid | id | status | regist_dt |
|---|---|---|---|
| uuid1 | 1 | running | 2023-07-01 10:00:00 |
| uuid2 | 2 | running | 2023-07-01 10:00:00 |
| uuid3 | 1 | running | 2023-07-01 10:05:00 |
| uuid4 | 2 | running | 2023-07-01 10:05:00 |
| uuid5 | 1 | success | 2023-07-01 10:10:00 |
| uuid6 | 2 | running | 2023-07-01 10:10:00 |
更新していた項目を別テーブルで管理し、その都度INSERTしていくようにしました。
まとめ
- メソッドやAPIでストリーミング挿入をした後に時間が経たない間に頻繁にUPDATEやDELETEを行うと
テーブルに対して処理が行えない状態になる。 - テーブル設計を見直すことで頻繁にUPDATEを行わないようにし、エラーを回避した。