BigQuery のスナップショット ~ ビビリ屋さんのスナップショット活用例

# はじめに

こんにちは、日本情報通信（NI+C）の種田です。

この記事は[NI+C Advent Calendar 2021](https://qiita.com/advent-calendar/2021/niandc_teamgcp) 21日目の記事です。

[2021年10月末のリリース](https://cloud.google.com/bigquery/docs/release-notes#October_28_2021)で BigQuery テーブルのスナップショットがGAになりました。
BigQuery は[タイムトラベル](https://cloud.google.com/bigquery/docs/time-travel)機能が自動で有効化されていて、7日前までのどの時点のデータに対してもアクセスできるということはご存知の方も多いと思います。だったらスナップショットっている？と思っている方にちょっとしたスナップショットの活用例（やっちまった対策）とその設定方法ご紹介します。

# タイムトラベルとの比較
まずはタイムトラベルとの比較です。

||タイムトラベル|スナップショット|
|---|---|---|
|取得方法|自動|手動実行/スケジュール実行|
|データの保管期間|7日間|無制限（たぶん）|
|やっちまった時（レコード削除）|7日以内ならデータを復旧できる|保管期間次第でデータを復旧できる|
|やっちまった時（テーブル削除）|2日以内ならデータを復旧できる|保管期間次第でデータを復旧できる|
|やっちまった時（テーブル削除後、同名のテーブルを作成）|**復旧できない**|保管期間次第でデータを復旧できる|

長期保管したい場合はもちろん、エンジニアとしてありえないんですが、誤ってテーブルを削除して更に焦って同名のテーブルを作っちゃった場合に有効なようです。長期保管について書いても面白くないのでやっちまった対策としてスナップショットを使うケースについて掘り下げていこうと思います。

# スナップショットの料金
やっちまった対策として活用するにしてもべらぼうに費用がかかると考えものなので、料金についても触れておきます。
[公式ページ](https://cloud.google.com/bigquery/docs/table-snapshots-intro#pricing)の図がわかりやすかったので拝借させてもらいます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2367061/b006dc80-d047-6430-1865-0eac13204233.png)
要約するとこんな感じです。安心して利用できそうですね。

* クエリ料金は通常と同じ
* スナップショットを作成した時点では**お金はかからない**
* 元テーブルにデータを追加しても**お金はかからない**
* データを更新/削除したときにお金がかかる

分析用のトランザクションデータを溜めていくためのテーブルだとやっちまわない限りタダで使えるってこと？？
ただし、全件洗い替え（Truncate & Insert）るようなテーブルについては元データの保管の方が良さそうですね。

# スナップショットの（ちょっとケチ臭い）活用例
さて、本題に入ります。
あくまでやっちまった対策としての活用方法ですが、オペミスによりデータを消してしまったらどうしようというと言う不安を拭い去ってくれると考えてます。しかも無料で。

## やること
* バッチ処理後にスナップショットを取得する（最新状態のデータを安全に保持）
* 次回処理直前までスナップショットを保持する（料金が発生する更新処理の前にスナップショット削除）

## 前提
* テーブルに対する操作は日次のバッチ処理のみ
* バッチはAM 0 時に起動し、AM 1 時までには終了する
* バッチ処理ではレコードの更新/削除が含まれる

## 手順
### 準備
Cloud Shell からバックアップ対象のテーブルを作成するデータセットとスナップショットを作成するデータセットを作成します。

```bash
bq mk test
bq mk backup
```

クエリエディタに次の`CREATE TABLE`文を入力し、[Run]ボタンをクリックします。

```SQL
CREATE TABLE test.test AS SELECT "スナップショットテスト" AS memo
```

テーブルが作成されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2367061/2bed5c4d-db88-4d62-c83a-3964f45a595e.png)

Cloud Shell からスナップショット取得用のサービスアカウントを作成します。

```bash
gcloud iam service-accounts create snapshot-bot \
    --description="スナップショット取得用のサービスアカウント" \
    --display-name="snapshot-bot"
```

Cloud Shell からサービスアカウントに `BigQuery User` ロールを紐付けます（プロジェクトレベル）。

```bash
PROJECT_ID=プロジェクトIDを指定
gcloud projects add-iam-policy-binding ${PROJECT_ID} \
    --member="serviceAccount:snapshot-bot@${PROJECT_ID}.iam.gserviceaccount.com" \
    --role="roles/bigquery.user"
```

Cloud Console からスナップショット対象のテーブルを含むデータセットと、スナップショット取得先のデータセットに対して、サービスアカウントに`BigQuery Data Editor`(`roles/bigquery.dataEditor`)ロールを紐付けます。

### テスト（動作確認なので興味のない方は[スナップショットの設定](#スナップショットの設定)にどうぞ）
テスト実行のために、クエリエディタに次の`CREATE SNAPSHOT TABLE`文を入力し、[Run]ボタンをクリックします。
※ プロジェクト ID は実際のプロジェクト ID に置換

```sql
DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
SET expiration = DATE_ADD(current_timestamp(), INTERVAL 23 HOUR);
SET snapshot_name = CONCAT("backup.test_",
 FORMAT_DATETIME("%Y%m%d", current_date()));
SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
   " CLONE プロジェクトID.test.test OPTIONS(expiration_timestamp=TIMESTAMP '",
   expiration,"');");
EXECUTE IMMEDIATE query;
```

はい、スナップショットできました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2367061/5ed18b84-7756-c2da-6a01-fd46a59ac04d.png)

ちゃんとクエリもできます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2367061/5a80acbc-4e5b-5dc5-9e30-cf8027049f45.png)

次の`DDL`を実行し、試しにデーブルを削除して同名のテーブルを作成してみます。

```sql
DROP TABLE `プロジェクトID.test.test`;
CREATE TABLE test.test AS SELECT "やばっ" AS memo
```

はい、やばいことになってます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2367061/4371fd58-1a4b-ae41-1dbc-c027a459db0b.png)

安心してください。スナップショットテーブルを開き、[Restore]ボタンをクリックします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2367061/f4922695-9739-7a35-bb22-594ae7a84dbc.png)

リストア先のデータセットとテーブルを指定して、`overwrite table if exists`にチェックを入れて[SAVE]ボタンをクリックします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2367061/1021e8cc-a4a3-5e4b-8e65-edaec97cdba8.png)

ちゃんと復旧してますね。
ここでは記載してませんがスナップショットを取ってない場合、**タイムトラベルからは復旧はできません。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2367061/eec75798-7e5e-f296-1b67-e0c4769c2c0c.png)
### スナップショットの設定
クエリエディタに次の`CREATE SNAPSHOT TABLE`文を入力します。（保持期間は23時間を指定）
※ プロジェクト ID は実際のプロジェクト ID に置換

```sql
DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
SET expiration = DATE_ADD(current_timestamp(), INTERVAL 23 HOUR);
SET snapshot_name = CONCAT("backup.test_",
 FORMAT_DATETIME("%Y%m%d", current_date()));
SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
   " CLONE プロジェクトID.test.test OPTIONS(expiration_timestamp=TIMESTAMP '",
   expiration,"');");
EXECUTE IMMEDIATE query;
```
`SCHEDULE` → `Create new scheduled query` の順に選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2367061/20101e18-2d1f-d0cf-1f10-45ace81f8758.png)

適当な名前を付けて毎日 AM 1 時に起動するように設定し、作成したサービスアカウントを指定して` Schedule` ボタンをクリックします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2367061/a4ab0224-1aab-c41a-1da1-6390d0dc908c.png)
以上で設定は終了です。

# まとめ
* スナップショットを利用することでタイムトラベルでは復旧できないデータを復旧することも可能
* `Scheduled Query` と組み合わせて使用することで簡単にバックアップが取得可能
* テーブルに対する処理がレコードの追加のみのテーブルでは無料で利用が可能
* テーブルに対する処理がレコードの更新/削除を含む場合も、処理前にスナップショットを削除してしまえば無料で利用が可能


出典：
* 費用について：https://cloud.google.com/bigquery/docs/table-snapshots-intro#pricing