BigQueryのタイムトラベルなお話
投稿者：中根 洋平
日本情報通信の中根です。
今回はBigQueryのタイムトラベル機能のご紹介となります。
タイムトラベル機能とは
BigQueryでは過去7日間であれば任意のタイミングのデータにバックアップやスナップショットなどを取得することなくデータへアクセスすることができます。
とくにユーザー側での設定等はなく利用できる機能となります。
以下の例を見てみましょう。
1つ目は通常通りクエリを実行しています。
参照しているテーブルには6月7日のデータまでが含まれていることがわかります。
SELECT
date,
COUNT(date) as row_count
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
WHERE date >= "2022-06-01"
GROUP BY date;
2つ目は6月1日時点でのレコードをタイムトラベルしてみます。
結果から6月1日分のレコードしか含まれていないことがわかります。
SELECT
date,
COUNT(date) as row_count
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
FOR SYSTEM_TIME AS OF "2022-06-01 00:00:00 UTC"
WHERE date >= "2022-06-01"
GROUP BY date;
このようにタイムトラベルを使うことで過去の任意の時点のデータをバックアップやスナップショットを使うことなくアクセスすることができます。
タイムトラベルのTIPS
便利なタイムトラベルですがいくつかTIPSがあります。
時間指定はタイムゾーンまで意識する
タイムトラベルは時間の指定はタイムスタンプ形式です。
ですのでもちろんタイムゾーンを意識する必要があります。
先程のクエリはUTCでしたがこれを指定しない場合はどうなるでしょうか。
SELECT
date,
COUNT(date) as row_count
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
FOR SYSTEM_TIME AS OF "2022-06-01 00:00:00"
WHERE date >= "2022-06-01"
GROUP BY date;
6月2日のレコードが増えています。
つまりタイムゾーンを指定しなかった場合のタイムゾーンはUTCではないようです。
レコードの更新時間を元に時間をずらしていくとわかりますがこのタイムゾーンはPDT(アメリカ太平洋時間)になります。
同一クエリ内の同一テーブルで利用できるタイムトラベルは1つのみ
タイムトラベルが便利なので複数の断面から情報を取得しようとしてもこれはできません。
同一クエリ内では1つのテーブルに対して1つの断面からしかアクセスできません。
以下のクエリはエラーになります。
SELECT
"2022-06-01" as dim,
date,
COUNT(date) as row_count
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
FOR SYSTEM_TIME AS OF "2022-06-01 00:00:00"
WHERE date >= "2022-06-01"
GROUP BY date
UNION ALL
SELECT
"2022-06-02" as dim,
date,
COUNT(date) as row_count
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
FOR SYSTEM_TIME AS OF "2022-06-02 00:00:00"
WHERE date >= "2022-06-01"
GROUP BY date;
もちろん別々のテーブルであれば時間は異なっていても大丈夫ですし、スクリプティングであれば1回の実行でも可能です。
CREATE TEMPORARY TABLE temp_1 AS
SELECT
"2022-06-01" as dim,
date,
COUNT(date) as row_count
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
FOR SYSTEM_TIME AS OF "2022-06-01 00:00:00"
WHERE date >= "2022-06-01"
GROUP BY date;
CREATE TEMPORARY TABLE temp_2 AS
SELECT
"2022-06-06" as dim,
date,
COUNT(date) as row_count
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
FOR SYSTEM_TIME AS OF "2022-06-06 00:00:00"
WHERE date >= "2022-06-01"
GROUP BY date;
SELECT * FROM temp_1
UNION ALL
SELECT * FROM temp_2
;
削除されたテーブルはSQLでは取得できない
これは特に注意が必要ですがテーブルが削除された場合はFOR SYSTEM_TIME AS OF句ではアクセスができません。
ただしbqコマンドの場合のみアクセスが可能となります。
タイムトラベルのウィンドウ期間について
前述の通りタイムトラベル機能はユーザー側が準備しなくとも7日前までのデータに気軽にアクセスできる機能です。
このアクセスできる期間をデータセット単位で設定可能な機能が4月よりプレビューしています。
便利なのに短くするメリットは？と思うこともありますがセキュリティやデータ保持期間ルールが決まっている場合に有効な機能になるかと思います。
スナップショット機能との比較
昨年秋頃にBigQueryのスナップショットがリリースされ利用されている方も多いかと思います。
こちらはタイムトラベルの制約である7日間よりもデータを保持したい場合に利用します。
まとめ
タイムトラベル機能は非常に便利な機能です。
ついうっかり個人で利用していたテーブルを上書きしちゃった、といった時に役立ちますし、データパイプラインをリランするときに元に戻したい、といったことも可能になります。
しかし指定する時間はタイムスタンプのみなので本当に参照した時間が戻したい時なのかは常に気をつける必要がありますので気をつけましょう。
