BigQueryのバックアップ機能（スナップショット デコレータ）を利用する方法

2020年4月11日2020年8月17日BigQuery

スナップショット デコレータ（snapshot decorators）はBigQueryの標準機能として自動で取得されるバックアップ機能です。

BigQueryのバックアップとして簡単に利用できる良い機能なのですが、注意点もありますので合わせて紹介します。

参考ページ https://cloud.google.com/bigquery/table-decorators#snapshot_decorators

スナップショット デコレータの利用方法

相対値を指定する方法

現在の時刻からの相対値を指定する方法で、以下の３パターンの指定方法があります。

現在時刻から何ミリ秒前

[PROJECT_ID.DATASET.TABLE@-millisecond]

例．１時間前のテーブルの件数を取得する場合

SELECT COUNT(*) FROM [PROJECT_ID.DATASET.TABLE@-3600000]

現在時刻から何ミリ秒前から何ミリ秒前の間

[PROJECT_ID.DATASET.TABLE@-millisecond_from--millisecond_to]

例．２時間前から１時間前のテーブルの件数を取得する場合

SELECT COUNT(*) FROM [PROJECT_ID.DATASET.TABLE@-7200000--3600000]

現在時刻から何ミリ秒以降

[PROJECT_ID.DATASET.TABLE@-millisecond-]

例．１時間以降のテーブルの件数を取得する場合

SELECT COUNT(*) FROM [PROJECT_ID.DATASET.TABLE@-3600000-]

絶対値を指定する方法

取得したい時間のタイムスタンプ値(timestamp)を指定する方法です。

[PROJECT_ID.DATASET.TABLE@timestamp]

例．2020-04-05 12:30:00のテーブルの件数を取得する場合

 SELECT INTEGER(TIMESTAMP("2020-04-05 12:30:00")/1000)
 > 1586089800000

 SELECT COUNT(*) FROM [PROJECT_ID.DATASET.TABLE@1586089800000]

スナップショット デコレータを利用する場合の注意点

スナップショット デコレータをBigQueryのバックアップとして利用する場合には以下の３つに注意しましょう。

• レガシー SQL （legacySQL）のみサポート
• 分割テーブルは利用できない
• 過去7日以内のデータのみ復元可能

レガシーSQL（legacySQL）のみサポート

GCPのWebコンソールから実行する場合は、レガシーSQL（legacySQL）を指定する必要があります。

標準SQL（standardSQL）はサポートされていませんので、SQLの構文も多少変わってきます。

例えば、データセットとテーブル名はバッククォート「`」ではなく「[]」を利用する必要があります。

具体的には、1時間前のスナップショットを取得する場合、

SELECT COUNT(*) FROM `PROJECT_ID.DATASET.TABLE@-3600000`

と入力すると次のようなエラーメッセージが表示されます。

Invalid table name: `PROJECT_ID.DATASET.TABLE@-3600000` [Try using standard SQL (https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql)].

　

正しくは

SELECT COUNT(*) FROM [PROJECT_ID.DATASET.TABLE@-3600000]

と入力します。

分割テーブルは利用できない

分割テーブルを指定して実行すると

SELECT COUNT(*) FROM [PROJECT_ID.DATASET.分割テーブル@-3600000]

以下のようなエラーメッセージが表示されます。

Querying tables partitioned on a field is not supported in Legacy SQL: 999999999999:DATASET.TABLE@9999999999999.

過去7日以内のデータのみ復元可能

7日以前のスナップショットについては参照することができません。

7日以上前を指定した場合は以下のエラーメッセージが表示されます。

Invalid snapshot time 1585744200000 for table PROJECT_ID:DATASET.TABLE@1585744200000. Cannot read before 1585806121001

絶対値していの場合はこのようなエラーになります。

SELECT INTEGER(TIMESTAMP("2020-04-09 14:10:00")/1000)
> 1586441400000
  
SELECT INTEGER(TIMESTAMP("2020-04-09 14:20:00")/1000)
> 1586442000000
  
SELECT count(*) FROM [PROJECT_ID:DATASET.TABLE@-1586441400000--1586442000000] LIMIT 1000
  
エラーメッセージ
Illegal table ID: TABLE@-1586441400000--1586442000000