タイムトラベルで BigQuery の削除済みデータにアクセス＆復元してみた

みかみ

2020.08.11

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、みかみです。

BigQuery では削除後のデータもスナップショットで保存されていて、7 日以内であれば削除済みのデータにも SQL で簡単にアクセスできるそうです。どうすれば削除後のデータにアクセスできるのか、削除後のテーブルを復元するにはどうすればいいのか、実際に確認してみました。

やりたいこと

BigQuery では本当に削除済みのデータを参照できるのか確認したい
BigQuery で削除済みのデータやテーブルを復元するにはどうすればよいのか知りたい

前提

動作確認時には、GCP 管理コンソールの他に、bq コマンドと BigQuery Python クライアントライブラリを使用しています。

bq コマンドとクライアントライブラリは、CLOUD SHELL で実行しました。

制限事項

過去データを参照できるのはデータ削除後 7 日以内までです。データやテーブル削除後 8 日以上経ってしまうと、削除済みデータにはアクセスできなくなるのでご注意ください。

また、FROM 句で UNNEST 構文や WITH 句を使用できなかったり、複数時点のテーブルを同時に参照できないなど、過去データ参照時のクエリには制限があるそうです。

削除済みのレコードを参照

タイムトラベル機能で、削除済みのレコードを参照することができます。

あるテーブルから、gender = "F" の count の昇順上位 3 レコードを SELECT した結果が以下です。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT name, count FROM dataset_1.table_delete_recoeds WHERE gender = "F" ORDER BY count LIMIT 3'
Waiting on bqjob_r40cfbe5c0323bffb_00000173c81ee890_1 ... (0s) Current status: DONE
+---------+-------+
|  name   | count |
+---------+-------+
| Shahira |     5 |
| Karynne |     5 |
| Dariany |     5 |
+---------+-------+

このテーブルから、gender = "F" のレコードを全て削除します。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'DELETE FROM dataset_1.table_delete_recoeds WHERE gender = "F"'
Waiting on bqjob_r1f85797c903ab344_00000173c825cb57_1 ... (0s) Current status: DONE   
Number of affected rows: 19814

先ほどの SELECT 文を再度実行してみます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT name, count FROM dataset_1.table_delete_recoeds WHERE gender = "F" ORDER BY count LIMIT 3'
Waiting on bqjob_r646bd84bb7d8157b_00000173c825fdd1_1 ... (0s) Current status: DONE

gender = "F" のレコードは削除してしまったので、SELECT 結果は 0 件です。

ですが、FOR SYSTEM_TIME AS OF でテーブルの履歴を参照すれば、削除済みのレコードを参照することができます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT name, count FROM dataset_1.table_delete_recoeds FOR SYSTEM_TIME AS OF "2020-08-07 00:00:00+09:00" WHERE gender = "F" ORDER BY count LIMIT 3'
Waiting on bqjob_r2c8d52cd2afe5af7_00000173c831e2c8_1 ... (0s) Current status: DONE   
+---------+-------+
|  name   | count |
+---------+-------+
| Shahira |     5 |
| Karynne |     5 |
| Dariany |     5 |
+---------+-------+

履歴バージョンの指定は、以下のように現在の日時との相対値で指定することもできます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT name, count FROM dataset_1.table_delete_recoeds FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) WHERE gender = "F" ORDER BY count LIMIT 3'
Waiting on bqjob_rd17c83f44858898_00000173c8266e76_1 ... (0s) Current status: DONE   
+---------+-------+
|  name   | count |
+---------+-------+
| Shahira |     5 |
| Karynne |     5 |
| Dariany |     5 |
+---------+-------+

例えば、現在のレコード削除済みの状態で count 値が MAX のレコードを取得すると以下です。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT name, gender, count FROM dataset_1.table_delete_recoeds WHERE count = (SELECT MAX(count) FROM dataset_1.table_delete_recoeds)'
Waiting on bqjob_r46342d45ac47ffe_00000173c838848d_1 ... (0s) Current status: DONE   
+-------+--------+-------+
| name  | gender | count |
+-------+--------+-------+
| Jacob | M      | 22127 |
+-------+--------+-------+

履歴バージョンを指定して、削除済みレコード含めて count 値が MAX のレコードを取得すると以下になります。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT name, gender, count FROM dataset_1.table_delete_recoeds FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) WHERE count = (SELECT MAX(count) FROM dataset_1.table_delete_recoeds FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR))'
Waiting on bqjob_r1ac6f53431f8b12_00000173c838afba_1 ... (0s) Current status: DONE   
+----------+--------+-------+
|   name   | gender | count |
+----------+--------+-------+
| Isabella | F      | 22913 |
+----------+--------+-------+

データの検証時等、削除済みの過去データと比較する場合などに便利ですね。

削除済みのレコードを復元

履歴を参照すれば、データを間違って削除してしまった場合に復元することも可能です。

gender = "F" のレコード削除前のテーブルの件数は以下でした。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_delete_recoeds'
Waiting on bqjob_r63332b5c6fafd33a_00000173c3a149f1_1 ... (0s) Current status: DONE   
+-------+
|  f0_  |
+-------+
| 34071 |
+-------+

削除した gender = "F" のレコードを、履歴から取得してテーブルに INSERT してみます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'INSERT INTO dataset_1.table_delete_recoeds (SELECT name, gender, count FROM dataset_1.table_delete_recoeds FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR
) WHERE gender = "F")'
Waiting on bqjob_r155cc80691610bc4_00000173c83c2f1e_1 ... (0s) Current status: DONE   
Number of affected rows: 19814

テーブル件数をカウントしてみます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_delete_recoeds'
Waiting on bqjob_r384df7617c0be1e3_00000173c83fd246_1 ... (0s) Current status: DONE   
+-------+
|  f0_  |
+-------+
| 34071 |
+-------+

レコード削除前と同じ件数に戻りました。

また、履歴指定なしの SQL で、一度削除した gender = "F" のレコードが参照できる状態に戻ったことも確認できました。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT name, gender, count FROM dataset_1.table_delete_recoeds WHERE count = (SELECT MAX(count) FROM dataset_1.table_delete_recoeds)'
Waiting on bqjob_r3d08ac52289f1874_00000173c841665e_1 ... (0s) Current status: DONE   
+----------+--------+-------+
|   name   | gender | count |
+----------+--------+-------+
| Isabella | F      | 22913 |
+----------+--------+-------+

削除済みテーブルを復元

BigQuery ではテーブルの有効期限を設定することが可能で、有効期限を過ぎたテーブルは自動的に削除されます。

テーブルが削除された場合は、FOR SYSTEM_TIME AS OF で履歴を指定してもデータを参照することはできません。

テーブル有効期限切れで 2020/08/06 23:59:59 に自動削除されたテーブルがあります。

テーブルが削除されているので、テーブルデータを参照しようとしても not found エラーとなります。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_expired'
BigQuery error in query operation: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r72e9657e785aff38_00000173c856d88e_1': Not found: Table cm-da-mikami-yuki-258308:dataset_1.table_expired was not found in location asia-northeast1

FOR SYSTEM_TIME AS OF でテーブル削除前の日時を指定して SELECT してみます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_expired FOR SYSTEM_TIME AS OF "2020-08-06 23:59:58+09:00"'
BigQuery error in query operation: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r6590ff7020c4782d_00000173c85fc9c0_1': Not found: Table cm-da-mikami-yuki-258308:dataset_1.table_expired was not found in location asia-northeast1

現時点で存在しないテーブルの場合、履歴を指定してもデータを参照することはできませんでした。

ですが、削除後 7 日以内であれば、削除済みのテーブルを復元することが可能です。

削除したテーブルの復元｜ BigQuery ドキュメント

bq cp コマンドでコピー元テーブルに UNIX タイムスタンプ（ミリ秒単位）を指定することにより、指定した時点のテーブルをコピーしてみます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq cp dataset_1.table_expired@1596725998000 dataset_1.table_expired_recovery
Waiting on bqjob_r7a11006479054b1a_00000173c86d9004_1 ... (0s) Current status: DONE   
Table 'cm-da-mikami-yuki-258308:dataset_1.table_expired@1596725998000' successfully copied to 'cm-da-mikami-yuki-258308:dataset_1.table_expired_recovery'
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_expired_recovery'
Waiting on bqjob_r4b6920980580aeea_00000173c880adb6_1 ... (0s) Current status: DONE   
+-------+
|  f0_  |
+-------+
| 34071 |
+-------+

削除前のテーブルが復元できました。

DROP 文でテーブルを手動削除してしまった場合でも、同様に復元可能です。

以下のテーブルを DROP 文で削除した後、復元してみます。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_droped'
Waiting on bqjob_r7a57b10c7920650a_00000173c896a738_1 ... (0s) Current status: DONE   
+-------+
|  f0_  |
+-------+
| 34071 |
+-------+
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'DROP TABLE dataset_1.table_droped'
Waiting on bqjob_re42b664b8ade56f_00000173c8976120_1 ... (0s) Current status: DONE
Dropped cm-da-mikami-yuki-258308.dataset_1.table_droped
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_droped'
BigQuery error in query operation: Error processing job 'cm-da-mikami-yuki-258308:bqjob_r1193dcf6435a3483_00000173c89796e4_1': Not found: Table cm-da-mikami-yuki-258308:dataset_1.table_droped was not found in location asia-northeast1

削除前のテーブル件数を確認後、テーブルを DROP し、SEELCT 文が not found エラーになることを確認しました。

先ほどは bq cp コマンドで削除前のテーブルをコピーして復元しましたが、クライアントライブラリを使用してもテーブルの復元が可能です。

以下の Python コードを実行します。

from google.cloud import bigquery

client = bigquery.Client()

table_id = "cm-da-mikami-yuki-258308.dataset_1.table_droped"
snapshot_epoch = 1596725998000
snapshot_table_id = "{}@{}".format(table_id, snapshot_epoch)
recovery_table_id = "{}_recovered".format(table_id)

job = client.copy_table(
    snapshot_table_id,
    recovered_table_id,
)
job.result()

print(
    "Copied data from deleted table {} to {}".format(table_id, recovered_table_id)
)

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ python3 table_recovery.py
Copied data from deleted table cm-da-mikami-yuki-258308.dataset_1.table_droped to cm-da-mikami-yuki-258308.dataset_1.table_droped_recovered
gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_droped_recovered'
Waiting on bqjob_r3eb15eb39a43aef2_00000173c8a4ac2b_1 ... (0s) Current status: DONE   
+-------+
|  f0_  |
+-------+
| 34071 |
+-------+

DROP 前のテーブルが復元されたことが確認できました。

アクセス制御設定を変更した場合

FOR SYSTEM_TIME AS OF で削除済みのレコードを参照する場合、現在のアクセス制御設定が適用されます。

タイムトラベルへの影響｜ BigQuery ドキュメント

例えば、以下のアクセスポリシーをテーブル ACL で設定している場合、サービスアカウント table-acl-data-viewer でテーブルデータの参照が可能です。

gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ bq get-iam-policy --format=prettyjson \
>  cm-da-mikami-yuki-258308:dataset_1.sample_with_table_acl
{
  "bindings": [
    {
      "members": [
        "serviceAccount:table-acl-data-viewer@cm-da-mikami-yuki-258308.iam.gserviceaccount.com"
      ],
      "role": "roles/bigquery.dataViewer"
    }
  ],
  "etag": "BwWsSGDZnOQ=",
  "version": 1
}

from google.cloud import bigquery
from google.oauth2 import service_account
import argparse
import os.path

parser = argparse.ArgumentParser(description='project')
parser.add_argument('file', help='account key file')
args = parser.parse_args()
key_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), args.file)
credentials = service_account.Credentials.from_service_account_file(
    key_path,
    scopes=["https://www.googleapis.com/auth/cloud-platform"],
)
client = bigquery.Client(
    credentials=credentials,
    project=credentials.project_id,
)

query = 'SELECT name FROM dataset_1.sample_with_table_acl ORDER BY count DESC LIMIT 3'
query_job = client.query(query)
results = query_job.result()
for row in results:
    print("name: {}".format(row.name))

gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ python3 select.py ../keys/table-acl-data-viewer.json
name: Isabella
name: Jacob
name: Sophia

テーブルから gender = "F" のレコードを削除して、再度同じ SELECT 文を実行してみます。

gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'DELETE FROM dataset_1.sample_with_table_acl WHERE gender = "F"'
Waiting on bqjob_r17588f29c34b7c71_00000173c8d0fa0c_1 ... (0s) Current status: DONE   
Number of affected rows: 19814
gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ python3 select.py ../keys/table-acl-data-viewer.json
name: Jacob
name: Ethan
name: Michael

レコードが削除されたので、削除前とは異なる SELECT 結果となりました。

FOR SYSTEM_TIME AS OF で削除前のデータを参照してみると、レコード削除前と同じ結果が取得できます。

（省略）
#query = 'SELECT name FROM dataset_1.sample_with_table_acl ORDER BY count DESC LIMIT 3'
query = 'SELECT name FROM dataset_1.sample_with_table_acl FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE) ORDER BY count DESC LIMIT 3'
query_job = client.query(query)
results = query_job.result()
for row in results:
    print("name: {}".format(row.name))

gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ python3 select.py ../keys/table-acl-data-viewer.json
name: Isabella
name: Jacob
name: Sophia

ここで、アクセスポリシーからサービスアカウント table-acl-data-viewer を削除し、データ参照権限を削除しました。

gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ bq get-iam-policy --format=prettyjson \
>  cm-da-mikami-yuki-258308:dataset_1.sample_with_table_acl
{
  "etag": "BwWsSIp0QTg=",
  "version": 1
}

再度現在のテーブルデータを参照してみると、参照権限がなくなったため、permission エラーが発生します。

（省略）
query = 'SELECT name FROM dataset_1.sample_with_table_acl ORDER BY count DESC LIMIT 3'
#query = 'SELECT name FROM dataset_1.sample_with_table_acl FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE) ORDER BY count DESC LIMIT 3'
query_job = client.query(query)
results = query_job.result()
for row in results:
    print("name: {}".format(row.name))

gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ python3 select.py ../keys/table-acl-data-viewer.json
Traceback (most recent call last):
  File "select.py", line 22, in <module>
    results = query_job.result()
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/job.py", line 3207, in result
    super(QueryJob, self).result(retry=retry, timeout=timeout)
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/job.py", line 812, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/usr/local/lib/python3.7/dist-packages/google/api_core/future/polling.py", line 130, in result
    raise self._exception
google.api_core.exceptions.Forbidden: 403 Access Denied: Table cm-da-mikami-yuki-258308:dataset_1.sample_with_table_acl: User does not have permission to query table cm-da-mikami-yuki-258308:dataset_1.sample_with_table_acl.
(job ID: 5fc883ba-3ddd-44d7-9593-7b2453fad852)
                         -----Query Job SQL Follows-----
    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |
   1:SELECT name FROM dataset_1.sample_with_table_acl ORDER BY count DESC LIMIT 3
    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |

まだ参照権限が付与されていたレコード削除前の履歴を参照してみます。

（省略）
#query = 'SELECT name FROM dataset_1.sample_with_table_acl ORDER BY count DESC LIMIT 3'
query = 'SELECT name FROM dataset_1.sample_with_table_acl FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 MINUTE) ORDER BY count DESC LIMIT 3'
query_job = client.query(query)
results = query_job.result()
for row in results:
    print("name: {}".format(row.name))

gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ python3 select.py ../keys/table-acl-data-viewer.json
Traceback (most recent call last):
  File "select.py", line 22, in <module>
    results = query_job.result()
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/job.py", line 3207, in result
    super(QueryJob, self).result(retry=retry, timeout=timeout)
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/job.py", line 812, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/usr/local/lib/python3.7/dist-packages/google/api_core/future/polling.py", line 130, in result
    raise self._exception
google.api_core.exceptions.Forbidden: 403 Access Denied: Table cm-da-mikami-yuki-258308:dataset_1.sample_with_table_acl@1596801846796: User does not have permission to query table cm-da-mikami-yuki-258308:dataset_1.sample_with_table_acl@1596801846796.
(job ID: 27d63f58-c2dc-4ddc-ac4a-78ae9b50fe8e)
                                                               -----Query Job SQL Follows-----
    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |
   1:SELECT name FROM dataset_1.sample_with_table_acl FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 MINUTE) ORDER BY count DESC LIMIT 3
    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |    .    |

参照権が付与されていた当時の履歴データであっても、やはり permission エラーで参照できませんでした。

同様に、データセットの ACL を変更した場合に、削除済みテーブルの復元を試みた場合の挙動も確認してみます。

以下、有効期限切れで 2020/08/06 23:59:59 に自動削除されたテーブルがあります。

テーブル削除前から、データセットには以下のポリシーが設定されていました。

gcp_da_user@cloudshell:~ (cm-da-mikami-yuki-258308)$ bq show --format=prettyjson \
>  cm-da-mikami-yuki-258308:dataset_1
{
  "access": [
（省略）
    {
      "role": "OWNER",
      "userByEmail": "table-acl-test@cm-da-mikami-yuki-258308.iam.gserviceaccount.com"
    },
（省略）
  ],
（省略）
}

自動削除済みのテーブルを、サービスアカウント table-acl-test で復元します。

from google.cloud import bigquery
from google.oauth2 import service_account
import argparse
import os.path

parser = argparse.ArgumentParser(description='project')
parser.add_argument('file', help='account key file')
args = parser.parse_args()
key_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), args.file)
credentials = service_account.Credentials.from_service_account_file(
    key_path,
    scopes=["https://www.googleapis.com/auth/cloud-platform"],
)
client = bigquery.Client(
    credentials=credentials,
    project=credentials.project_id,
)

table_id = "cm-da-mikami-yuki-258308.dataset_1.table_with_role_expired"
snapshot_epoch = 1596725998000
snapshot_table_id = "{}@{}".format(table_id, snapshot_epoch)
recovery_table_id = "{}_recovered".format(table_id)

job = client.copy_table(
    snapshot_table_id,
    recovery_table_id,
)
job.result()

print(
    "Copied data from deleted table {} to {}".format(table_id, recovery_table_id)
)

gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ python3 recovery.py ../keys/table-acl-test.json
Copied data from deleted table cm-da-mikami-yuki-258308.dataset_1.table_with_role_expired to cm-da-mikami-yuki-258308.dataset_1.table_with_role_expired_recovered
gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_with_role_expired_recovered'
Waiting on bqjob_r4fe57194a84fcc64_00000173c8eeb5f3_1 ... (0s) Current status: DONE   
+-------+
|  f0_  |
+-------+
| 34071 |
+-------+

テーブルが復元されたことが確認できました。

データセットのアクセスポリシーから、サービスアカウント table-acl-test のアクセス権限を削除して再実行してみます。

（省略）
table_id = "cm-da-mikami-yuki-258308.dataset_1.table_with_role_expired"
snapshot_epoch = 1596725998000
snapshot_table_id = "{}@{}".format(table_id, snapshot_epoch)
#recovery_table_id = "{}_recovered".format(table_id)
recovery_table_id = "{}_recovered_2".format(table_id)

job = client.copy_table(
    snapshot_table_id,
    recovery_table_id,
)
（省略）

gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ python3 recovery.py ../keys/table-acl-test.json 
Traceback (most recent call last):
  File "recovery.py", line 27, in <module>
    recovery_table_id,
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/client.py", line 2285, in copy_table
    copy_job._begin(retry=retry, timeout=timeout)
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/job.py", line 638, in _begin
    retry, method="POST", path=path, data=self.to_api_repr(), timeout=timeout
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/bigquery/client.py", line 574, in _call_api
    return call()
  File "/usr/local/lib/python3.7/dist-packages/google/api_core/retry.py", line 286, in retry_wrapped_func
    on_error=on_error,
  File "/usr/local/lib/python3.7/dist-packages/google/api_core/retry.py", line 184, in retry_target
    return target()
  File "/usr/local/lib/python3.7/dist-packages/google/cloud/_http.py", line 423, in api_request
    raise exceptions.from_http_response(response)
google.api_core.exceptions.Forbidden: 403 POST https://bigquery.googleapis.com/bigquery/v2/projects/cm-da-mikami-yuki-258308/jobs: Access Denied: Table cm-da-mikami-yuki-258308:datas
et_1.table_with_role_expired@1596725998000: User does not have bigquery.tables.getData permission for table cm-da-mikami-yuki-258308:dataset_1.table_with_role_expired@1596725998000.

permission エラーで復元できませんでした。

逆に、テーブル削除時にはアクセス権限がなかったサービスアカウントのアクセス権を追加して、削除済みテーブルを復元してみます。

gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ bq show --format=prettyjson \
>  cm-da-mikami-yuki-258308:dataset_1
{
  "access": [
（省略）
    {
      "role": "OWNER",
      "userByEmail": "table-acl-data-owner@cm-da-mikami-yuki-258308.iam.gserviceaccount.com"
    },
（省略）
  ],
（省略）
}
gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ python3 recovery.py ../keys/table-acl-data-owner.json
Copied data from deleted table cm-da-mikami-yuki-258308.dataset_1.table_with_role_expired to cm-da-mikami-yuki-258308.dataset_1.table_with_role_expired_recovered_2
gcp_da_user@cloudshell:~/timetravel (cm-da-mikami-yuki-258308)$ bq query --nouse_legacy_sql \
> 'SELECT COUNT(*) FROM dataset_1.table_with_role_expired_recovered_2'
Waiting on bqjob_r34d08973e51ceb68_00000173c8fb78a4_1 ... (0s) Current status: DONE   
+-------+
|  f0_  |
+-------+
| 34071 |
+-------+

無事削除済みテーブルが復元できました。

過去時点のデータを参照する場合でも、BigQuery へのアクセスには、現時点のアクセスポリシーが適用されることが確認できました。

まとめ（所感）

タイムトラベル機能で、削除後のデータも SQL で簡単に参照できることが確認できました。デイリーバッチ処理結果の検証等で前日のデータと比較する場合などに便利だと思います。

また、過去データの復元も可能なので、間違ってテーブルを DROP してしまった場合や、意図せず有効期限切れでテーブルが自動削除されてしまった場合などにも安心です。

ただし、現在のところ、削除済みの過去データ保持期間は 7 日間です。もし長期休暇中にテーブルの有効期限の見落としやバッチの不正処理などが発生してデータが削除されてしまった場合、8 日以上経った後だとリカバリができなくなってしまいます。

とても便利なタイムトラベル機能ですが、当てにしすぎることなく、実運用では時間軸を考慮した上で自動リカバリなどのリカバリ方法の検討も必要ではないかと思いました。

タイムトラベルで BigQuery の削除済みデータにアクセス＆復元してみた

やりたいこと

前提

制限事項

削除済みのレコードを参照

削除済みのレコードを復元

削除済みテーブルを復元

アクセス制御設定を変更した場合

まとめ（所感）

参考

イベント

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS/システムアーキテクト/Android案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

タイムトラベルで BigQuery の削除済みデータにアクセス＆復元してみた

やりたいこと

前提

制限事項

削除済みのレコードを参照

削除済みのレコードを復元

削除済みテーブルを復元

アクセス制御設定を変更した場合

まとめ（所感）

参考

イベント

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS/システムアーキテクト/Android案件特集〜 を開催します

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

関連記事

「【4/17(水)ハイブリッド】クラスメソッドGoogle Cloud Next ’24ふり返り勉強会」に登壇しました

Google Cloud Next’24で発表されたBigQueryのアップデート情報をまとめました

【新機能】BigQuery data canvasを早速触ってみた #GoogleCloudNext

【速報】Google Cloud NextでGemini in BigQueryが追加されました

EVENT【4/18リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS/システムアーキテクト/Android案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩