Help us understand the problem. What is going on with this article?

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とか、時々書きますね。ばいなら。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした