スケジュールされたクエリを使用してテーブルスナップショットを作成する

このドキュメントでは、DDL クエリのスケジュールを実行するサービスアカウントを使用して、テーブルのスナップショットを毎月作成する方法について説明します。このドキュメントでは、次の例を通して説明します。

PROJECT プロジェクトで、snapshot-bot という名前のサービスアカウントを作成します。
snapshot-bot サービスアカウントに、DATASET データセットにある TABLE テーブルのテーブルスナップショットを取得し、そのテーブルスナップショットを BACKUP データセットに保存するために必要な権限を付与します。
TABLE テーブルのスナップショットを毎月作成して BACKUP データセットに配置するクエリを記述します。テーブルの既存のスナップショットは上書きできないため、各スナップショットには一意の名前を付ける必要があります。このため、クエリではテーブルのスナップショット名に現在の日付を追加します（例: TABLE_20220521）。テーブルのスナップショットは、40 日で期限が切れます。
毎月 1 日にクエリが実行されるように snapshot-bot サービスアカウントをスケジュール設定します。

このドキュメントは、BigQuery と、BigQuery のテーブルスナップショットについての知識があるユーザーを対象としています。

権限とロール

このセクションでは、サービスアカウントの作成やクエリのスケジュール設定に必要な Identity and Access Management（IAM）の権限と、それらの権限を付与する IAM 事前定義ロールについて説明します。

権限

サービスアカウントを使用するには、次の権限が必要です。

権限	リソース	リソースの種類
`iam.serviceAccounts.*`	`PROJECT`	プロジェクト

クエリのスケジュールを設定するには、次の権限が必要です。

権限	リソース	リソースの種類
`bigquery.jobs.create`	`PROJECT`	プロジェクト

ロール

サービスアカウントの操作に必要な権限を付与する事前定義ロールは次のとおりです。

ロール	リソース	リソースの種類
次のいずれか: `roles/iam.serviceAccountAdmin` `roles/editor` `roles/owner`	`PROJECT`	プロジェクト

クエリのスケジュール設定に必要な権限を持つ BigQuery 事前定義ロールは次のとおりです。

ロール	リソース	リソースの種類
次のいずれか: `roles/bigquery.user` `roles/bigquery.jobuser` `roles/bigquery.admin`	`PROJECT`	プロジェクト

`snapshot-bot` サービスアカウントを作成する

以下の手順で、snapshot-bot サービスアカウントを作成し、PROJECT プロジェクトでクエリを実行するために必要な権限を付与します。

コンソール

Google Cloud コンソールで、[サービスアカウント] ページに移動します。

[サービスアカウント] に移動
PROJECT プロジェクトを選択します。
snapshot-bot サービスアカウントを作成します。
1. [サービスアカウントを作成] をクリックします。
2. [サービスアカウント名] フィールドに、「snapshot-bot」と入力します。
3. [作成して続行] をクリックします。
BigQuery ジョブの実行に必要な権限をサービスアカウントに付与します。
1. [このサービスアカウントにプロジェクトへのアクセスを許可する] セクションで、BigQuery ユーザーのロールを選択します。
2. [完了] をクリックします。

BigQuery がメールアドレス snapshot-bot@PROJECT.iam.gserviceaccount.com を使用してサービスアカウントを作成します。

指定した権限で BigQuery によってサービスアカウントが作成されたことを確認するには、次の手順を行います。

Console

BigQuery によってサービスアカウントが作成されていることを確認します。

Google Cloud コンソールで、[サービスアカウント] ページに移動します。

[サービスアカウント] に移動
PROJECT プロジェクトを選択します。
[snapshot-bot@PROJECT.iam.gserviceaccount.com] をクリックします。
[サービスアカウントのステータス] メッセージで、サービスアカウントがアクティブになっていることを確認します。

BigQuery がクエリの実行に必要な権限をサービスアカウントに付与していることを確認します。

Google Cloud コンソールで [リソースの管理] ページに移動します。

[リソースの管理] に移動
[PROJECT] をクリックします。
[情報パネルを表示] をクリックします。
[権限] タブで、[BigQuery ユーザー] ノードを開きます。
snapshot-bot サービスアカウントがリストにあることを確認します。

サービスアカウントに権限を付与する

このセクションでは、BACKUP データセットで DATASET.TABLE テーブルのテーブルスナップショットを作成するために必要な権限を snapshot-bot サービスアカウントに付与する方法について説明します。

ベーステーブルのスナップショットを取得する権限

DATASET.TABLE テーブルのスナップショットを取得するために必要な権限を snapshot-bot サービスアカウントに付与するには、次の操作を行います。

コンソール

Google Cloud コンソールで、[BigQuery] ページを開きます。

[BigQuery] に移動
[エクスプローラ] ペインで、PROJECT プロジェクトノードを開きます。
DATASET データセットノードを開きます。
TABLE テーブルを選択します。
[共有] をクリックします。[共有] ペインが開きます。
[プリンシパルを追加] をクリックします。アクセス権の付与ペインが開きます。
[新しいプリンシパル] に、サービスアカウントのメールアドレス（snapshot-bot@PROJECT.iam.gserviceaccount.com）を入力します。
[ロールを選択] プルダウンで、[BigQuery データ編集者] ロールを選択します。
[保存] をクリックします。
[共有] ペインで、[BigQuery データ編集者] ノードを開き、snapshot-bot@PROJECT.iam.gserviceaccount.com サービスアカウントが表示されていることを確認します。
[閉じる] をクリックします。

bq

Google Cloud コンソールで、Cloud Shell をアクティブにします。

Cloud Shell をアクティブにする

次の bq add-iam-policy-binding コマンドを入力します。

bq add-iam-policy-binding \
--member=serviceAccount:snapshot-bot@PROJECT.iam.gserviceaccount.com \
--role=roles/bigquery.dataEditor DATASET.TABLE

BigQuery は、新しいポリシーバインディングが追加されたことを確認します。

宛先データセットにテーブルを作成する権限

次のようにして、BACKUP データセットにテーブルスナップショットを作成するために必要な権限を snapshot-bot サービスアカウントに付与します。

コンソール

Google Cloud コンソールで [BigQuery] ページに移動します。

[BigQuery] に移動
[エクスプローラ] ペインで、PROJECT プロジェクトノードを開きます。
BACKUP データセットノードのメニューをクリックして、[開く] を選択します。
[共有データセット] をクリックします。データセットの権限ペインが表示されます。
[メンバーを追加] フィールドに、サービスアカウントのメールアドレス（snapshot-bot@PROJECT.iam.gserviceaccount.com）を入力します。
[ロールを選択] プルダウンで、[BigQuery データオーナー] ロールを選択します。
[追加] をクリックします。
データセットの権限ペインで、snapshot-bot@PROJECT.iam.gserviceaccount.com サービスアカウントが [BigQuery データオーナー] ノードに表示されていることを確認します。
[完了] をクリックします。

これで、snapshot-bot サービスアカウントに次のリソースに対する IAM ロールが付与されました。

ロール	リソース	リソースの種類	目的
BigQuery データ編集者	`PROJECT:DATASET.TABLE`	テーブル	`TABLE` テーブルのスナップショットを取得します。
BigQuery データオーナー	`PROJECT:BACKUP`	データセット	`BACKUP` データセットのテーブルスナップショットを作成および削除します。
BigQuery ユーザー	`PROJECT`	プロジェクト	テーブルのスナップショットを作成するスケジュールされたクエリを実行します。

これらのロールは、snapshot-bot サービスアカウントが DATASET.TABLE テーブルのテーブルスナップショットを作成するクエリを実行し、テーブルスナップショットを BACKUP データセットに配置するために必要とする権限を提供します。

複数ステートメントのクエリを作成する

このセクションでは、CREATE SNAPSHOT TABLE DDL ステートメントを使用して DATASET.TABLE テーブルのテーブルスナップショットを作成する複数ステートメントのクエリを作成する方法について説明します。スナップショットは BACKUP データセットに保存され、1 日で期限切れになります。

-- Declare variables
DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;

-- Set variables
SET expiration = DATE_ADD(current_timestamp(), INTERVAL 1 DAY);
SET snapshot_name = CONCAT(
                      "BACKUP.TABLE_",
                      FORMAT_DATETIME('%Y%m%d', current_date()));

-- Construct the query to create the snapshot
SET query = CONCAT(
              "CREATE SNAPSHOT TABLE ",
              snapshot_name,
              " CLONE mydataset.mytable OPTIONS(expiration_timestamp = TIMESTAMP '",
              expiration,
              "');");

-- Run the query
EXECUTE IMMEDIATE query;

月単位のクエリをスケジュールする

次のように、毎月 1 日の午前 5 時に実行されるようにクエリをスケジュールします。

bq

Google Cloud コンソールで、Cloud Shell をアクティブにします。

Cloud Shell をアクティブにする

次の bq query コマンドを入力します。

bq query --use_legacy_sql=false --display_name="Monthly snapshots of the TABLE table" \
--location="us" --schedule="1 of month 05:00" \
--project_id=PROJECT \
'DECLARE snapshot_name STRING;
DECLARE expiration TIMESTAMP;
DECLARE query STRING;
SET expiration = DATE_ADD(@run_time, INTERVAL 40 DAY);
SET snapshot_name = CONCAT("BACKUP.TABLE_",
  FORMAT_DATETIME("%Y%m%d", @run_date));
SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
  " CLONE PROJECT.DATASET.TABLE OPTIONS(expiration_timestamp=TIMESTAMP \"",
  expiration, "\");");
EXECUTE IMMEDIATE query;'

BigQuery によってクエリがスケジュール設定されます。

bq コマンドラインツールコマンドの複数ステートメントクエリは、Google Cloud コンソールで実行したクエリと次の点が異なります。

bq コマンドラインツールのクエリは、current_date() ではなく @run_date を使用します。スケジュールされたクエリでは、@run_date パラメータに現在の日付が含まれます。ただし、インタラクティブクエリでは、@run_date パラメータはサポートされていません。インタラクティブクエリのスケジュールを設定する前に、@run_date ではなく current_date() を使用してクエリをテストできます。
同様の理由で、bq コマンドラインツールのスクリプトでは current_timestamp() ではなく @run_time を使用します。@run_time パラメータはインタラクティブクエリではサポートされていませんが、インタラクティブクエリをテストする場合は、@run_time の代わりに current_timestamp() が使用されます。
コマンドラインツールのスクリプトでは、クエリを一重引用符で囲むため、一重引用符 ' ではなく、スラッシュと二重引用符 \" を使用します。

スケジュールされたクエリを実行するようにサービスアカウントを構成する

このクエリは現在、認証情報を使用して実行されるようスケジュールされています。次のようにスケジュールされたクエリを更新して、snapshot-bot サービスアカウントの認証情報を使用して実行します。

bq ls コマンドを実行して、スケジュールされたクエリジョブの ID を取得します。
```
bq ls --transfer_config=true --transfer_location=us
```
出力は次のようになります。

name displayName dataSourceId state

projects/12345/locations/us/transferConfigs/12345 Monthly snapshots of the TABLE table scheduled_query RUNNING

`name`	`displayName`	`dataSourceId`	`state`
`projects/12345/locations/us/transferConfigs/12345`	`Monthly snapshots of the TABLE table`	`scheduled_query`	`RUNNING`

name フィールドの ID を使用して、次の bq update コマンドを実行します。

bq update --transfer_config --update_credentials \
--service_account_name=snapshot-bot@PROJECT.iam.gserviceaccount.com \
projects/12345/locations/us/transferConfigs/12345

Cloud Shell で、スケジュールされたクエリが正常に更新されたことを確認します。

実施したタスクを確認する

このセクションでは、クエリが正しくスケジュールされていることを確認する方法、クエリの実行時にエラーが発生したかどうかを確認する方法、スナップショットが毎月作成されていることを確認する方法について説明します。

スケジュールされたクエリを表示する

BigQuery で月次テーブルスナップショットクエリがスケジュールされたことを確認するには、次の手順を行います。

コンソール

Google Cloud コンソールで、[スケジュールされたクエリ] ページに移動します。

[スケジュールされたクエリ] に移動
[Monthly snapshots of the TABLE table] をクリックします。
[構成] をクリックします。
クエリ文字列にクエリが含まれていることと、クエリが毎月 1 日に実行されるようスケジュールされていることを確認します。

スケジュールされたクエリの実行履歴を表示する

スケジュールされたクエリが実行された後、それが正常に実行されたかどうかは、次の操作で確認できます。

コンソール

Google Cloud コンソールで、[スケジュールされたクエリ] ページに移動します。

[スケジュールされたクエリ] に移動
[Monthly snapshots of the TABLE table] というクエリの説明をクリックします。
[実行履歴] をクリックします。

クエリの実行日時、実行の成否、失敗した場合は発生したエラーを確認できます。特定の実行の詳細を表示するには、[実行履歴] テーブルでその行をクリックします。[実行の詳細] ペインに詳細が表示されます。

テーブルのスナップショットを表示する

テーブルのスナップショットが作成されていることを確認するには、次の手順を行います。

コンソール

Google Cloud コンソールで [BigQuery] ページに移動します。

[BigQuery] に移動
[エクスプローラ] ペインで、BACKUP データセットを開き、TABLE_YYYYMMDD スナップショットが作成されていることを確認します。ここで、YYYYMMDD は毎月 1 日です。

次に例を示します。
- TABLE_20220601
- TABLE_20220701
- TABLE_20220801

次のステップ

テーブルのスナップショットの詳細については、テーブルスナップショットの使用をご覧ください。
クエリのスケジュール設定の詳細については、クエリのスケジュール設定をご覧ください。
Google Cloud サービスアカウントの詳細については、サービスアカウントをご覧ください。

スケジュールされたクエリを使用してテーブル スナップショットを作成する

権限とロール

権限

ロール

snapshot-bot サービス アカウントを作成する

コンソール

Console

サービス アカウントに権限を付与する

ベーステーブルのスナップショットを取得する権限

コンソール

bq

宛先データセットにテーブルを作成する権限

コンソール

複数ステートメントのクエリを作成する

月単位のクエリをスケジュールする

bq

スケジュールされたクエリを実行するようにサービス アカウントを構成する

実施したタスクを確認する

スケジュールされたクエリを表示する

コンソール

スケジュールされたクエリの実行履歴を表示する

コンソール

テーブルのスナップショットを表示する

コンソール

次のステップ

スケジュールされたクエリを使用してテーブルスナップショットを作成する

`snapshot-bot` サービスアカウントを作成する

サービスアカウントに権限を付与する

スケジュールされたクエリを実行するようにサービスアカウントを構成する