Carpe Diem

備忘録

BigQueryのデータをUpdateする

BigQuery

概要

BigQueryは基本的にはログなど解析目的で利用するため、データ自体を更新することはありません。ただ

  • 一部間違ったログが入ってしまった
  • データの洗い替えが必要になった

みたいなケースが起き、解析結果が期待するものにならない場合は更新処理が必要です。
2016/11からDML(データ操作言語)が使えるようになったので、それを使って更新します。

注意事項

BigQueryは基本的に取得系メソッドをメインで利用するものなので、DMLに関しては以下のような制限があります。

f:id:quoll00:20180323112546p:plain

ref: Data Manipulation Language  |  BigQuery  |  Google Cloud

なので１レコードずつ更新するクエリをループで回すなんてことはせずに、一気に複数のレコードを更新するクエリを発行するようにしてください。

更新方法

ケース

今回

  • 特定のユーザの持つステータスに間違った値（status=0）が入ってしまった
  • status=1としたい
  • 対象ユーザのリストは持っている

というケースを想定します。

オプションの設定

更新クエリはLegacySQLでは使えないので、以下のようにShow OptionsでチェックをOFFする必要があります。

f:id:quoll00:20180323113039p:plain

SELECTで確認

念のため対象ユーザを表示して、更新対象かどうかを確認します。

SELECT *
FROM
  `project.dataset.table`
Where
  _PARTITIONTIME = TIMESTAMP('2018-03-09')  AND 
  user_id IN("user001", "user002")

UPDATEで更新

問題なさそうであれば、データを更新します。
UPDATEにテーブル名を、SETに更新するカラムと値を入れます。

UPDATE
  `project.dataset.table`
SET
  status=1
WHERE
  _PARTITIONTIME = TIMESTAMP('2018-03-09') AND 
  user_id IN("user001", "user002")

データ量に比例して時間がかかるので、パーティションテーブルなどを利用してなるべく対象のデータ量を少なくすると良いです。

実行が完了すると更新したレコード数が表示されます。 f:id:quoll00:20180323114240p:plain

ソース