{GCP BigQuery}タイムトラベルを使用した履歴データへのアクセス

GCP

https://cloud.google.com/bigquery/docs/time-travel?hl=ja

https://qiita.com/zaburo/items/6929bea1f094c8c6a4fc

 

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

テーブルが削除されている場合や期限切れの場合でも、タイムトラベル期間内にテーブルを復元する限り、データをコピーできます。


-- 1. 前作業

gcloud init
gcloud auth list

gcloud --version

gcloud projects create project01-9999999 \
--name="project01"

gcloud config list
gcloud config set project project01-9999999
gcloud config set compute/region asia-southeast1 --quiet
gcloud config set compute/zone asia-southeast1-a --quiet

gcloud beta billing accounts list
gcloud beta billing projects link project01-9999999 --billing-account=111111-111111-111111

gcloud services enable compute.googleapis.com --project project01-9999999

gcloud components update


-- 2. BigQuery API有効化

gcloud services list --enabled


gcloud services enable bigquery.googleapis.com \
--project project01-9999999

 

-- 3. BigQueryデータセットを作成する

CREATE SCHEMA ds01;

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
order by schema_name
;

 


-- 4. テストテーブルの作成

CREATE TABLE ds01.tab1
(
  col1 NUMERIC
, col2 STRING
, col3 TIMESTAMP
)
;

 

select * from ds01.INFORMATION_SCHEMA.TABLES
order by table_name
;

SELECT * FROM ds01.INFORMATION_SCHEMA.COLUMNS
order by table_name,column_name
;


insert into ds01.tab1 values(100,'A',current_timestamp());

select current_timestamp();
2023-03-21 12:14:55.252048 UTC


insert into ds01.tab1 values(200,'A',current_timestamp());

select * from ds01.tab1 order by col1;


-- 5. 動作確認

-- 5.1 特定の時点のデータをクエリで取得する

SELECT * FROM ds01.tab1
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 MINUTE)
order by col1;

SELECT * FROM ds01.tab1
FOR SYSTEM_TIME AS OF '2023-03-21 21:14:56+09:00'
order by col1;

 


-- 5.2 特定の時点のテーブルを復元する

-- date -d '2023/03/21 21:14:56' "+%s"
-- date -d @1679400896 "+%Y/%m/%d %H:%M:%S"

macの場合

date -j -f "%Y-%m-%d %H:%M:%S" "2023-03-21 21:14:56" +%s
date -r 1679400896


drop table ds01.tab1;

 

bq cp ds01.tab1@-180000 ds01.tab2

bq cp ds01.tab1@1679400896000 ds01.tab3

 

select * from ds01.INFORMATION_SCHEMA.TABLES
order by table_name
;

select * from ds01.tab2 order by col1;
select * from ds01.tab3 order by col1;

 

 

-- 6. クリーンアップ

 

gcloud projects list

gcloud projects delete project01-9999999 \
--quiet