BigQuery ストリーミングバッファに関連する UPDATE/DELETE エラーの対応方法
BigQuery ストリーミングバッファに関連する UPDATE/DELETE エラーの対応方法
BigQuery でストリーミングバッファに書き込まれたデータを UPDATE や DELETE しようとすると、特定のエラーが発生することがあります。
本記事では、そのエラーの原因と対応方法を解説します。
発生したエラー
ストリーミングバッファに存在するデータに対して UPDATE や DELETE を実行しようとした際に、以下のエラーが発生しました。
UPDATE or DELETE statement over table <project_id>.<dataset_id>.<table_id> would affect rows in the streaming buffer, which is not supported
エラーの原因
BigQuery のストリーミングバッファに書き込まれたデータは、最大 90 分間 DML（データ操作言語）を使用して変更することができません。
データ操作言語（DML）でデータを変換する | BigQuery | Google Cloud
tabledata.insertallストリーミング メソッドを使用して最近書き込まれた行は、UPDATE、DELETE、MERGE、TRUNCATEステートメントなどのデータ操作言語（DML）を使用して変更することはできません。 最近の書き込みとは、30 分以内に行われたものを指します。 テーブル内の他のすべての行は引き続き、UPDATE、DELETE、MERGE、TRUNCATEステートメントを使用して変更できます。 ストリーミング データがコピー オペレーションで使用可能になるまでに最大 90 分かかることがあります。
つまり、ストリーミングバッファに存在するデータは、最大 90 分間は変更できないという制約があります。
対応方法
エラーを回避するためには、ストリーミングバッファに存在しないデータを対象にする必要があります。
具体的には、現在の時刻から 95 分以上前に書き込まれたデータを対象にすることで、エラーを防ぐことができます。
以下はその条件を SQL に反映した例です。
where
created_at < datetime_sub(current_datetime('Asia/Tokyo'), interval 95 minute)
SQL の説明
下記の条件を UPDATE や DELETE 文に追加することで、ストリーミングバッファに存在するデータを除外し、エラーを回避できます。
current_datetime('Asia/Tokyo'): 現在の日時を取得（タイムゾーンはAsia/Tokyo）datetime_sub: 指定した日時から一定の時間を引くinterval 95 minute: 現在の時刻から95分前を計算created_at:created_at(レコードの作成日時) が 95 分以上前のデータを対象とする
