Bigqueryのテーブルを誤って更新してしまった際の対応

こんにちはkiyonoエンジニアです。

Bigqueryのテーブルを誤って更新してしまった、削除してしまった….そんな経験はないでしょうか。
また実際にこれらの経験がなくとも、実際に起こってしまったらゾッとしますよね。

本日は、上記の課題を解決するためのソリューションを解説します。
ハンズオン形式にしておりますので、ぜひみなさんのお手元の環境でお試しください。

タイムトラベル機能について

Bigqeuryには、過去に遡ってデータを取り出せるタイムトラベル機能があります。

タイムトラベルは、最大7日以内の任意の日時に遡ってデータを取り出せる機能です。この機能を使うことで誤って「Bigqueryのテーブルを更新してしまった」となっても復旧することができます。

この後ハンズオン形式で実際にタイムトラベル機能を試してみましょう。

まずBigqeuryのコンソール画面にて今回ハンズオンをするためのデータセットを作成します。以降のハンズオンではデータセット名を「time_travel」、テーブルを「example」とし作成します

データセットを作成した後は、SQLワークスペースにて以下コマンドを実行してください。（データセットやテーブル名は調整してください。）

CREATE TABLE time_travel.example(
  id INT64,
  user STRING
);

SQLワークスペースにて以下コマンドを実行し、テスト用のレコードを挿入します。

INSERT　time_travel.example(id,user)
SELECT *
FROM UNNEST([
  (1,'Mukai'),
  (2,'Tabuchi'),
  (3,'Nakao'),
  (4,'Inazawa')
]);

テーブルが以下添付のようになっていたらOKです。

SQLワークスペースにて以下コマンドを実行し、先ほど作成したテーブルのレコードを書き換えます。

UPDATE time_travel.example
SET user = CASE
 WHEN id = 2 THEN 'Saito'
 WHEN id = 3 THEN 'Komukai'
END
WHERE id IN(2,3);

添付のようにid=2、id=3のユーザーをそれぞれTabuchi→Saito、Nakao→Komukaiに変更しました。

復元するにあたって対象のテーブルが変更された日時を確認しましょう。

以下添付の下部にある個人履歴またはプロジェクト履歴をクリックして確認しましょう。今回のハンズオンの場合どちらで確認しても問題ないですが、他の人が誤って変更してしまったものをもとに戻す場合はプロジェクト履歴の方から確認する必要があります。

以下添付の通りジョブの履歴を確認できます。今回はuserを変更「2022/09/24 15:23:22」する前のレコードをインサートした状態「2022/09/24 14:44:54」に戻します。

以下のようにクエリを実行する子で、変更前のテーブルに戻すことが可能です。

CREATE OR REPLACE TABLE time_travel.example AS
SELECT *
FROM time_travel.example
FOR SYSTEM TIME AS OF
'2022-09-24 14:45:00+9:00'

ポイントとしては、SELECT * FORM 対象のテーブル FOR SYSTEM TIME AS OF ‘タイムトラベルで戻したい日時’とすることで、タイムトラベル機能を使い変更前の情報にアクセスできます。

今回は変更前の情報にアクセスし、CREATE OR REPLACE TABLEによりテーブルを上書きする処理を行なっています。

プレビューでデータを確認した際に、以下添付のように元のデータに戻っていたら成功です。

なお今回使用したクエリは、誤ってテーブルを削除してしまった場合は使用できません。

誤ってテーブルを削除してしまった場合は、bqコマンドにてテーブルを復元する必要があります。

bq cp time_travel.example@1664000602000 time_travel.example

上記のようにbqコマンドにてテーブルを復元できます。@数字の部分にテーブルが存在していた際のタイムスタンプを記述しコマンドを実行します。 UNIX タイムスタンプ（ミリ秒単位）なのでご注意ください。

こちら変換サイトを使えば、簡単に UNIX タイムスタンプ（ミリ秒単位）を算出できます。

入力例としては以下のようなイメージです。

また以下のように相対的な時間を使って復元も可能です。以下の場合だと1時間前という意味になります。

bq cp time_travel.example@-3600000 time_travel.example

いかがだったでしょうか。本日はBigqueryのタイムトラベル機能について解説しました。データ分析基盤を使うデータエンジニアや、Bigquery上でSQLを書いて分析をするデータアナリストにとっては必見の内容だったかと思います。ぜひ一度お試しください。

またタイムトラベル以外でBigqueryの活用にてお困りの場合はぜひ一度KIYONOにお問い合わせください。