概要

Google BigQuery で、テーブルデータの定期バックアップを取得するシンプルな例です

Google BigQuery ではデフォルトで７日間のタイムトラベルによるデータ保存もされますが、タイムトラベル以前のデータにアクセスしたいという要件はありうるため、定期的なスナップショット取得が必要になるケースはあるのではないでしょうか

スナップショット取得はテーブル単位の操作のため、スケジュール実行するとしても、テーブルの増減に応じてバックアップ対象を更新する必要があるのは面倒です
本記事はデータセットのリストのみメンテナンスするシンプルな管理で、データバックアップをスケジュールするシンプルな方法を共有します

前提

サンプルとして以下の構成を想定します

バックアップを取得する元のプロジェクト： target-project
バックアップを保管する先のプロジェクト： backup-project
Google BigQuery のスケジュールクエリに、指定のデータセット以下の全テーブルについてスナップショットをとるクエリを登録する
設定は terraform コードで行う
- データセットリストを terraform でループして、データセットの数だけスケジュールクエリを登録する
terraform version : 1.8.2
terraform google provider version : 5.24

参考

設定内容

terraform 設定

構成

path/to/terraform/environments
├── backup
│   ├── variables.tf
│   ├── iam.tf
│   ├── analysis.tf
│   └── backup_query.sql
└── target
    ├── variables.tf
    └── iam.tf

backup-project

バックアップデータを保管するためのプロジェクトの設定

バックアップを実行するサービスアカウントを作成し、適切な権限を設定します

environments/backup/variables.tf

variable "backup-project" {
  default = "backup-project"
  type    = string
  description = "データバックアップを保管するための Google Project 名"
}

variable "target-project" {
  default = "target-project"
  type    = string
  description = "データバックアップを取得する対象の Google Project 名"
}

variable "backup-datasets" {
  default = [
    "target_dataset_1",
    "target_dataset_2",
    "target_dataset_3",
  ]
  type        = list(string)
  description = "target プロジェクト内の、バックアップ対象データセット群のリスト"
}

バックアップ実行アカウントの設定

次の権限を付与

environments/backup/iam.tf

# バックアップ実行サービスアカウント
resource "google_service_account" "backup" {
  project      = var.backup-project
  account_id   = "backup"
  display_name = "backup workflow service account"
}

# バックアップ実行サービスアカウントへの権限付与
resource "google_project_iam_member" "backup" {
  for_each = toset(var.roles-backup)
  project = var.backup-project
  role    = each.value
  member  = "serviceAccount:${google_service_account.backup.email}"
}

variable "roles-backup" {
  description = "バックアップを実行するIAMユーザ backup に必要な権限セット"
  default = [
    "roles/bigquery.user",
    "roles/bigquery.dataOwner", 
  ]
}

スケジュールクエリの設定

毎週月曜 12:00 実行の例

environments/backup/analysis.tf

# バックアップ実行するスケジュールクエリ群の定義
resource "google_bigquery_data_transfer_config" "backup_query_configs" {
  for_each = {
    for dataset in toset(var.backup-datasets):
      dataset => dataset
  }

  data_source_id = "scheduled_query"
  display_name   = "backup_${replace(each.key, ".", "_")}"
  service_account_name = var.service-account-backup-email
  location = var.region
  schedule = "every monday 03:00" # Tokyo時間の月曜日正午 (UTC+9)
  data_refresh_window_days = 0

  params = {
    query = templatefile("./backup_query.sql", {
      target_dataset: "${var.backup-target-project}.${each.key}",
      backup_dataset: "${var.project}.${each.key}",
    }),
  }
}

バックアップ取得クエリテンプレート

バックアップの保管期間を設定：例は 365 日間
任意のリージョンを設定：例は東京

environments/backup/backup_query.sql


DECLARE backup_dataset_ymd STRING;
DECLARE target_table_name STRING;
DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
DECLARE tables ARRAY<STRING>;

-- table type 1:TABLE
-- https://cloud.google.com/bigquery/docs/information-schema-tables?hl=ja
SET backup_dataset_ymd = CONCAT(
  "${backup_dataset}_", FORMAT_DATETIME("%Y%m%d", CURRENT_TIMESTAMP()));
SET expiration = TIMESTAMP_TRUNC(
  TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 365 DAY), DAY, 'Asia/Tokyo');
SET tables = ( 
  SELECT ARRAY_AGG(table_id) 
  FROM `${target_dataset}.__TABLES__` 
  WHERE type = 1 );

SET query = CONCAT(
  "CREATE SCHEMA IF NOT EXISTS `", backup_dataset_ymd, "`",
  " OPTIONS(location = 'asia-northeast1');");
EXECUTE IMMEDIATE query;

FOR table IN ( select * from UNNEST(tables) ) DO
  SET snapshot_name     = CONCAT(backup_dataset_ymd, ".", table.f0_);
  SET target_table_name = CONCAT("${target_dataset}.", table.f0_);
  SET query = CONCAT(
    "CREATE SNAPSHOT TABLE IF NOT EXISTS `", snapshot_name, "`",
    " CLONE `", target_table_name, "`",
    " OPTIONS(expiration_timestamp=TIMESTAMP \"", expiration, "\");");
  EXECUTE IMMEDIATE query;
END FOR;

target-project

バックアップ取得対象プロジェクトの設定

environments/target/variables.tf

variable "service-account-backup-email" {
  default = "backup@backup-project.iam.gserviceaccount.com"
  type = string
  description = "バックアップ処理を実行するサービスアカウント"
}

iam 設定

バックアッププロジェクトで作成したサービスアカウントに対して、 target-project の BigQuery 参照権限として以下を付与します

environments/target/iam.tf

# バックアップ実行サービスアカウントへの権限付与
resource "google_project_iam_member" "backup" {
  for_each = toset(var.roles-backup)
  project = var.backup-project
  role    = each.value
  member  = "serviceAccount:${var.service-account-backup-email}"
}

variable "roles-backup" {
  description = "バックアップを実行するIAMユーザ backup に必要な権限セット"
  default = [
    "roles/bigquery.user",
    "roles/bigquery.dataEditor",
    "roles/bigquery.metadataViewer", # __TABLES__ データへのアクセスのため bigquery.metadataViewer が必要
  ]
}

terraform 設定の適用

backup, target それぞれの環境に対して terraform の設定を適用してください

$ terraform plan 
$ terraform apply

設定の結果

Google Cloud コンソールから、 backup プロジェクトで指定のデータセット群に対するスケジュールクエリが登録されることが確認できます
定期的にスナップショットの取得クエリが実行され、指定のデータセットに属するすべてのテーブルのバックアップが作成されます
backup プロジェクトの BigQuery で、バックアップ取得対象のデータセット群のスナップショット群が、 target_dataset_YYYYMMDD という形式のデータセットとして記録されることが確認できます

メンテナンス

取得対象のデータセットが増えた場合は、 terraform のコードで backup-datasets を変更します
テーブルの増減はメンテナンス不要なため運用が楽です（自動で対象が増減します）

以上