こんにちは。SCSKの山口です。
皆さん、誰しも一度は「過去に戻ってやり直したい」と思ったことがあるはずです。（唐突）
学校の花瓶を割ってしまったときや、大事なプレゼンで盛大に噛んでしまったとき、そして、BigQuery テーブルの大事なデータを消してしまったとき。
大丈夫、３つ目は救えます。
今回はそんなブログです。
BigQueryのタイムトラベル機能
BigQueryには、過去データへアクセスできる「タイムトラベル機能」という大変便利な機能が備わっています。
この機能を活用することで、以下のことが可能です。
- 特定の時点のデータをクエリで取得
- 特定の時点のテーブルを復元
ただし、タイムトラベル機能を使用できる期間は7日間（デフォルト）となっています。
タイムトラベル期間の長さは2~7日の範囲で、データセットレベルで設定することができます。
救えます。と堂々と前述しましたが、7日というタイムリミットはあります。人生そこまで甘くはありませんね。
過去のデータを一定期間保持しているとなると、気になるのが課金です。
デフォルトでは7日間のデータを保持していますが、必要に応じてタイムトラベル期間を短くすることで、物理ストレージの課金モデルを採用している場合に限り、「ストレージ費用」を節約できます。
論理ストレージの課金モデルを使用する場合は例外となるので、ご注意ください。（詳細は参考資料をご参照ください。）
過去のデータへのアクセス：https://cloud.google.com/bigquery/docs/time-travel?hl=ja
タイムトラベルとフェイルセーフによるデータの保持：https://cloud.google.com/bigquery/docs/access-historical-data?hl=ja
ストレージの課金モデル：https://cloud.google.com/bigquery/pricing?hl=ja#storage
実践①：特定の時点のデータをクエリで取得
ここから実践です。
まずは特定の時点のデータをクエリで取得してみます。
準備：テーブル
下記テーブルをテスト用として準備します
「INSERT_TIME」カラムには、データを入れた日時を入れています。
クエリ実行
では、準備したテーブルの【—- Check Point —-】の時点のデータを取ってみましょう。
過去のデータへのアクセス | BigQuery | Google Cloudに記載のあるSQLを参考に、日時を指定してデータを取得します。
| 実行SQL | 実行結果 |
SELECT * FROM `evocative-entry-277709.yamaguchi_test.TIMETRAVEL_TEST` FOR SYSTEM_TIME AS OF "2024-04-17 15:41:00+09:00" |
 |
※実行SQLでは、【—- Check Point —-】も含めるために敢えて15:41を指定しています。
結果として、【—- Check Point —-】より後のデータが入っていない状態に戻りました。大成功です。
（トリミングでギリギリを攻め過ぎましたが、ほんとに戻りました。）
実践②：特定の時点のデータをクエリで復元
今度は、一度削除してしまったデータを復元してみます。
準備：データを一部削除
実践①で使用したデータの【—- Check Point —-】以前に投入されたデータを一度削除します。
| 削除前 | 削除後 |
 |
 |
データの順序が前後していてかなりわかりづらいですが、【—- Check Point —-】以前に挿入されたCLM1が日本語のデータが消えている状態です。
この状態から、削除されたデータを復元します。
クエリ実行
| 実行SQL | 実行結果 |
INSERT INTO `evocative-entry-277709.yamaguchi_test.TIMETRAVEL_TEST` SELECT * FROM `evocative-entry-277709.yamaguchi_test.TIMETRAVEL_TEST` FOR SYSTEM_TIME AS OF "2024-04-17 15:39:00+09:00" |
エラー：
Table ‘evocative-entry-277709.yamaguchi_test.TIMETRAVEL_TEST’ 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. |
エラーを吐かれました。
どうやら、復元先と復元元に同じテーブルを指定することはできないようです。
一度別名のテーブルにデータを復元し、その後元のテーブルに挿入する方法をやってみます。
| 実行SQL① | 実行結果 |
INSERT INTO
`evocative-entry-277709.yamaguchi_test.tmp_TIMETRAVEL_TEST`
SELECT
*
FROM
`evocative-entry-277709.yamaguchi_test.TIMETRAVEL_TEST`
FOR SYSTEM_TIME AS OF "2024-04-17 15:39:00+09:00"
※一時テーブルを別途作成する必要があります |
 |
| 実行SQL② | |
INSERT INTO
`evocative-entry-277709.yamaguchi_test.TIMETRAVEL_TEST`
SELECT
*
FROM
`evocative-entry-277709.yamaguchi_test.tmp_TIMETRAVEL_TEST`
|
 |
データの順序が逆転していますが、一度削除されたデータを復元し、テーブルを初期状態に戻すことができました。大成功です。
あとは元テーブル（TIMETRAVEL_TEST）を削除して、ALTER文でリネームとかしてあげると完全に元の状態に戻りますね。
（テーブルの名前を変更する：https://cloud.google.com/bigquery/docs/managing-tables?hl=ja#renaming-table）
実践③：削除されたテーブルを復元
今度は、一度削除されてしまったテーブルを復元します。
実践①,②で使用したテーブルを削除します。
過去のデータへのアクセス | BigQuery | Google Cloudに記載のある方法で復元してみます。
| コマンド |
| bq cp [データセット名].[テーブル名1]@XXXXXXX [データセット名].[テーブル名2] |
「XXXXXXX」部分には、「相対オフセット」もしくは「Unixエポック時刻」を指定します。
- 相対オフセット：現在の時刻からの相対時間（ミリ秒単位）
- Unixエポック時刻：Unix エポック時刻からの経過時間（ミリ秒単位）
Unixエポック秒は下記で取得可能です。
| 実行SQL | 実行結果 |
|
SELECT
UNIX_MILLIS(TIMESTAMP(‘2024-04-17 18:00:00’,”Asia/Tokyo”))
|
 |
下記コマンドを実行し、テーブルを別名の一時テーブルへ復元します。
| 実行コマンド | 実行結果 |
bq cp yamaguchi_test.TIMETRAVEL_TEST@-3600000 yamaguchi_test.tmp_TIMETRAVEL_TEST
※3600000 ms前（＝1時間前）を指定 |
|
bq cp yamaguchi_test.TIMETRAVEL_TEST@1713344400000 yamaguchi_test.tmp_TIMETRAVEL_TEST
|
|
どちらのコマンドでも削除されたテーブルを復元できました。大成功です。
まとめ
今回はBigQuery のタイムトラベル機能を使って、特定時間のデータの取得、削除されたデータやテーブルの復元をやってみました。
私は、今回実践したSQLをプロジェクトクエリに保存しており、いつでも使えるようにしています。
大変便利な機能ですので、皆さんも是非ご活用ください。