BigQueryのデータを元に戻したい
期間を間違えた、同じファイルをもう一度読み込ませてしまった、消してしもうた!、アップデートでWHERE
忘れた、コンソールで前に書いてたDML残したままクエリ実行してしもうたとか。
そんなときに元に戻す方法を。そんなに難しいことやってません。
"FOR SYSTEM_TIME AS OF"があるじゃないか
FOR SYSYTEM_TIME AS OF で指定した日時時点のデータを見ることが出来ます。
ただし、7日前までしか保存していません。それ以前はもう無いです。
こんな感じでクエリを実行します。
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
だけすれば良いと思います。こんな感じ。
#全部のデータを消す
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とか、時々書きますね。ばいなら。