BigQueryであの頃のデータに戻りたい（ただし、1週間前まで）

BigQueryのデータを元に戻したい

期間を間違えた、同じファイルをもう一度読み込ませてしまった、消してしもうた！、アップデートでWHERE忘れた、コンソールで前に書いてたDML残したままクエリ実行してしもうたとか。
そんなときに元に戻す方法を。そんなに難しいことやってません。

"FOR SYSTEM_TIME AS OF"があるじゃないか

FOR SYSYTEM_TIME AS OF で指定した日時時点のデータを見ることが出来ます。
ただし、7日前までしか保存していません。それ以前はもう無いです。
こんな感じでクエリを実行します。

for_sysytem_time_as.sql

SELECT
  *
FROM
  `project_name.dataset_name.table_name`
FOR SYSTEM_TIME AS OF '2020-02-01 10:00:00 Asia/Tokyo'

パーティション分割テーブルを使っている場合は、いつもどおりWHEREで指定すればその部分だけを見てくれます。

DELETEとINSERTを実行する

テーブルのデータを削除して、戻したい日時時点のデータを入れていきます。
消してしまった場合はINSERTだけすれば良いと思います。こんな感じ。

for_sysytem_time_as.sql

#全部のデータを消す
DELETE
FROM
  `project_name.dataset_name.table_name`
WHERE
  1 < 2;

#戻したい日時時点を指定して、そのデータを入れる
INSERT INTO `project_name.dataset_name.table_name`
SELECT
  *
FROM
  `project_name.dataset_name.table_name`
FOR SYSTEM_TIME AS OF '2020-02-01 10:00:00 Asia/Tokyo';

ここでも同じようにパーティション分割テーブルを使っている場合は、いつもどおりWHEREで指定すればその部分だけを操作することが出来ます。

削除したテーブルの復元もある

削除したテーブルの復元 で、削除から 2 日以内の指定した日時時点のテーブルを復活することが出来ます。2 日以上前のテーブルはもう無い他、同名テーブル、データセットを作った場合も復活出来ません。
"FOR SYSYTEM_TIME AS OF" の 7 日より短い 2 日です。

bq cp dataset.deleted@1579878000000 dataset.table

最後に

つまりBigQueryではデータを消そうが、更新しようが、7日前までは全部データが残っているんだなぁと。
一昔前はLegacy SQLでしか出来ないと思ってたけど、StandardSQLだとDMLが使えるのでこんなことが出来るようになりました。
自分はこれを思い出したりするのが面倒なのでstored procedureにまとめて、テーブルのリカバリバッチを作りました。
あぁ、久しぶりに書いた。業務で使ってるTipsとか、時々書きますね。ばいなら。