BigQueryで削除後のテーブルと同名テーブルを作成した場合も、タイムトラベル可能か調べてみた

タイムトラベルしてみたい。

ニューシロ

2023.10.31

クラスメソッド株式会社データアナリティクス事業本部所属のニューシロです。
今回はGoogle CloudのサービスであるBigQueryで、削除後のテーブルと同名テーブルを作成した場合もタイムトラベルが使えるか調べてみました。

タイムトラベルとは

デフォルトでは、過去 7 日間のタイムトラベル期間内であれば、どの時点のデータにもアクセスできます。タイムトラベルを使用すると、更新されたデータや削除されたデータのクエリを実行すること、削除されたテーブルや期限切れのテーブルを復元できます。

DevelopersIOでも、このタイムトラベルについて検証した記事があります。
以下の2つの記事で勉強させていただきました、ありがとうございます。

この機能により、誤ってデータを消してしまった場合や、テーブルを削除してしまった場合にもデータを復元することができます。
しかし、どんな状況でも本当に復元できるのか気になります。特に削除後に同名テーブルを作り直した場合、過去のデータも上書きされたりしないのかな、と気になってしまいました。あらかじめ手を動かして調べておくことで、万が一の際にも備えられると思い今回この記事を書いてみました。

タイムトラベルの検証

準備

以下のSQLでデータセットDevelopersIO下にテーブルtime_travel_01をあらかじめ作成しておきました。後の作業でも利用するため、テーブルtime_travel_02、time_travel_03、time_travel_04も同スキーマで作成し、同データをINSERTしておきます。

CREATE TABLE DevelopersIO.time_travel_01 (
  id INT64,
  name STRING
);

INSERT DevelopersIO.time_travel_01 (id, name)
VALUES(1, 'apple'),
      (2, 'banana'),
      (3, 'orange');

既存データを削除した場合のタイムトラベル

データを削除してみます。

DELETE FROM DevelopersIO.time_travel_01
WHERE id = 3;

データを確認します。

SELECT * FROM DevelopersIO.time_travel_01
ORDER BY id;

結果

id = 3のデータが消えていますが、SQLを用いてタイムトラベルでアクセスしてみます。

SELECT * FROM DevelopersIO.time_travel_01
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

結果

消してしまったid = 3のデータを見ることができました！

既存テーブルを削除した場合のタイムトラベル

テーブルを削除した場合もタイムトラベルが使えます。
検証のため、DROP文でこのテーブルを削除します。

DROP TABLE DevelopersIO.time_travel_01;

削除後は先ほど行ったSQLを用いてタイムトラベルする方法が使えません。
この場合は、bqコマンドでのタイムトラベルを使うことで、テーブルを復元することができます。

ドキュメント通り、1時間前のテーブルをtime_travel_01_copyという名前で復元してみましょう。

bq cp DevelopersIO.time_travel_01@-3600000 DevelopersIO.time_travel_01_copy

このコマンドでtime_travel_01_copyというテーブルが作成できたので、データを確認するためにSQLを実行します。

SELECT * FROM DevelopersIO.time_travel_01_copy
ORDER BY id;

結果

復元できています。
このように、テーブルを削除した場合もデータを復元できるタイムトラベルは非常に便利な機能です。

本題

削除したテーブルと同名のテーブルを作り直した場合のタイムトラベル可否

本題です。先ほど検証したテーブルと同スキーマ、同データがINSERTされたテーブルtime_travel_02を利用します。このテーブルを削除し、その後同名テーブルを作り直します。スキーマ定義のみで、データは入れません。

DROP TABLE DevelopersIO.time_travel_02;
CREATE TABLE DevelopersIO.time_travel_02 (
  id INT64,
  name STRING
);

SQLでタイムトラベルを行います。

SELECT * FROM DevelopersIO.time_travel_02
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

この場合、SQLでのタイムトラベルはできませんでした。
ログによると、対象のテーブルが見つからない、とのことでした。同名テーブルでも別のテーブル扱いのようです。

しかしながら、bqコマンドでのタイムトラベルは成功しました。

bq cp DevelopersIO.time_travel_02@-3600000 DevelopersIO.time_travel_02_copy

ログ上では成功したので、このbqコマンドで作り直したテーブルについて、SQLでデータを確認します。

SELECT * FROM DevelopersIO.time_travel_02_copy
ORDER BY id;

結果

タイムトラベルでテーブルの再作成はできているようです。一安心です。

CREATE OR REPLACE文でテーブルを作り直した場合のタイムトラベル可否

念の為他のパターンも調査してみます。CREATE OR REPLACE TABLEで、既存テーブルを作り直し、タイムトラベルをしてみましょう。
使用するテーブルはtime_travel_03です。格納されているデータはこれまでと同じです。

CREATE OR REPLACE TABLE DevelopersIO.time_travel_03 (
  id INT64,
  name STRING
);

まずはSQLでのタイムトラベル。

SELECT * FROM DevelopersIO.time_travel_03
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

結果

SQLでのタイムトラベルに成功しました。
前述のように、テーブルを削除して再作成だとエラーが起きたので、ここは失敗すると予想していました。もちろん、bqコマンドでもタイムトラベル可能でした。

TRUNCATE文でデータを削除した場合のタイムトラベル可否

こちらも気になったのでやってみました。おそらくできるだろうな、と思いつつも、手を動かしてみました。

使用するテーブルはtime_travel_04です。格納されているデータはこれまでと同じです。

TRUNCATE TABLE DevelopersIO.time_travel_04;

テーブル内のデータを消去できたので、SQLでタイムトラベルしてみます。

SELECT * FROM DevelopersIO.time_travel_04
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

予想通り、TRUNCATE文で削除した場合もSQLでタイムトラベルできています。bqコマンドでもタイムトラベル可能でした。

終わりに

万が一のためにも、タイムトラベルの機能について把握しておきたかったので、試してよかったです。これからも手を動かして、扱う技術について気になったことは積極的に調べていきたいです。

引用・参照まとめ

customer-story-analytics

関連記事

[初心者向け]pandasでCloud StorageのCSVファイルを読み込んでみた

根本夏瑠

2024.04.26

Google Cloud Next ’24 振り返り勉強会でCloud RunとGKEのコスト最適化のRecapを話しました #GoogleCloudNext

もこ

2024.04.25

Workflowsでエクスポネンシャルバックオフを実装してみた

根本夏瑠

2024.04.23

初めてGoogle Cloud のコミュニティで登壇してきた感想を述べます。（in Jagu’e’r ）

室井靖成

2024.04.23