BigQueryのタイムトラベルでのテーブル復元方法について考えてみた

データアナリティクス事業本部の鈴木です。

BigQueryでマートテーブルなどを運用する際に、タイムトラベルでタイムトラベル期間内の過去データを復元できると非常に便利です。

タイムトラベルを使えばタイムトラベル期間内であれば履歴データへアクセスできることは知っているのですが、どうやって履歴データを取得しテーブルへ反映させるのかについては具体的にイメージができていなかったため、その方法について考えてみました。

タイムトラベルとは

デフォルトでは、過去7日間のタイムトラベル期間内であれば、変更または削除されたどの時点のデータにもアクセスできる機能です。

DevelopersIOでは以下の記事で紹介されています。

なにかしらの理由で過去時点でのデータにアクセスしたい場合に非常に心強い機能です。

履歴データにアクセスする方法

履歴データを使ってその時点のテーブルを復元する場合、そのテーブルが既に削除されているかどうかでアクセスする方法が変わります。

アクセスの方法について以下のように整理してみました。

テーブルがまだ存在している場合は、FOR SYSTEM_TIME AS OFで履歴を指定してアクセスできます。一方で、テーブルが既に削除されている場合はbq cpコマンドでテーブル自体を復元する必要があります。

検証するデータの復元方法

今回は以下の2パターンのデータの復元方法を考えて試してみました。

• その1： 今のテーブルの名前を変更して、履歴データからテーブルを復元する
• その2： 今のテーブルのデータを削除した後、履歴データを再格納する

その1の方は履歴データから新しくテーブルを再作成する方法です。その2の方は今あるテーブルからデータを消して入れ直す方法です。

やってみる

テーブルの準備

以前別のブログで使った、簡単なフルーツの価格のデータを持ったテーブルを用意しておきました。

CREATE TABLE sample_dataset.original_table
(
    id INTEGER OPTIONS(description="商品ID"),
    name STRING OPTIONS(description="商品名"),
    price INTEGER OPTIONS(description="商品価格")
);

INSERT sample_dataset.original_table (id, name, price)
VALUES(1, 'Apple', 150),
      (2, 'Orange', 120),
      (3, 'Grape', 390);

その1. 今のテーブルの名前を変更して、履歴データからテーブルを復元する

まず、テーブルの名前を変更します。

ALTER TABLE sample_dataset.original_table
RENAME TO new_table;

以下のように名前を変えることができました。

なお、テーブルの名前の変更は『テーブルの管理  |  BigQuery  |  Google Cloud - テーブルの名前を変更する』に記載があります。

その後、Cloud Shellで以下のコマンドを実行しました。このときは名前変更の作業をした後すぐの操作だったので2分前の履歴テーブルにアクセスしました。

#　@<time> 時間デコレータに相対オフセットを指定する場合はミリ秒単位であることに注意する。
bq cp sample_dataset.original_table@-120000 sample_dataset.original_table

以下のように復元されました。 テーブルの説明も残っていますね。

最後に名前を変更していた元のテーブルを削除しました。

DROP TABLE sample_dataset.new_table;

これで元通りですね。

その2. 今のテーブルのデータを削除した後、履歴データを再格納する

分かりやすさのため、以下のように追加のデータを格納しました。

INSERT sample_dataset.original_table (id, name, price)
VALUES(3, 'Grape', 390),
      (4, 'Pineapple', 400),
      (5, 'Peach', 350);

次に以下のように、データの削除および履歴データの取得と格納をしました。

BEGIN TRANSACTION;

-- 今のテーブルのデータを削除する。トランザクション内のためDELETEを使う。
-- https://cloud.google.com/blog/ja/products/data-analytics/bigquery-explained-data-manipulation-dml
DELETE FROM `sample_dataset.original_table` WHERE true;

-- 履歴データを取得して一時テーブルに格納する。
CREATE TEMP TABLE tmp_original_table
AS 
  SELECT 
    id,
    name,
    price
FROM sample_dataset.original_table FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 MINUTE);

-- 取得した履歴データをInsertする
INSERT `sample_dataset.original_table` (id, name, price)
SELECT 
  id,
  name,
  price
FROM tmp_original_table;

-- 一時テーブルの削除
DROP TABLE tmp_original_table;

COMMIT TRANSACTION;

以下のように元のデータを復元できました。

トランザクション内では履歴データを直接取得せず一時テーブルを挟みました。理由は補足として説明します。

補足

以下のように、同じテーブルの履歴テーブルから直接データを取得してINSERTすることはできませんでした。

エラーの内容は以下の通りです。

Table 'sample_dataset.original_table' is referenced with multiple snapshot read timestamps. For example, if 'FOR SYSTEM_TIME AS OF' expressions are used on the table, they should use the same TIMESTAMP value. Using the table as DML destination table references the table at query start time.

最後に

タイムトラベルを使って履歴データを使って元のテーブルに入れ直す方法を2つ試してみました。

障害発生時に直近のデータに巻き戻したいときに心強いですし、作業実施時も切り戻しを細かく考えなくてよいのでとても楽になりますね。