スケジュールされたクエリを使用してテーブル スナップショットを作成する
このドキュメントでは、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.serviceAccountAdminroles/editorroles/owner |
PROJECT |
プロジェクト |
クエリのスケジュール設定に必要な権限を持つ BigQuery 事前定義ロールは次のとおりです。
| ロール | リソース | リソースの種類 |
|---|---|---|
次のいずれか:roles/bigquery.userroles/bigquery.jobuserroles/bigquery.admin |
PROJECT |
プロジェクト |
snapshot-bot サービス アカウントを作成する
以下の手順で、snapshot-bot サービス アカウントを作成し、PROJECT プロジェクトでクエリを実行するために必要な権限を付与します。
コンソール
Google Cloud コンソールで、[サービス アカウント] ページに移動します。
PROJECTプロジェクトを選択します。snapshot-botサービス アカウントを作成します。[サービス アカウントを作成] をクリックします。
[サービス アカウント名] フィールドに、「snapshot-bot」と入力します。
[作成して続行] をクリックします。
BigQuery ジョブの実行に必要な権限をサービス アカウントに付与します。
[このサービス アカウントにプロジェクトへのアクセスを許可する] セクションで、BigQuery ユーザーのロールを選択します。
[完了] をクリックします。
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] ページを開きます。
[エクスプローラ] ペインで、
PROJECTプロジェクト ノードを開きます。DATASET データセット ノードを開きます。
TABLE テーブルを選択します。
[共有] をクリックします。[共有] ペインが開きます。
[プリンシパルを追加] をクリックします。アクセス権の付与ペインが開きます。
[新しいプリンシパル] に、サービス アカウントのメールアドレス（snapshot-bot@PROJECT.iam.gserviceaccount.com）を入力します。
[ロールを選択] プルダウンで、BigQuery データ編集者のロールを選択します。
[保存] をクリックします。
[共有] ペインで、[BigQuery データ編集者] ノードを開き、snapshot-bot@PROJECT.iam.gserviceaccount.com サービス アカウントが表示されていることを確認します。
[閉じる] をクリックします。
bq
Google Cloud コンソールで、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] ページに移動します。
[エクスプローラ] ペインで、
PROJECTプロジェクト ノードを開きます。BACKUP データセット ノードのメニューをクリックして、[開く] を選択します。
[共有データセット] をクリックします。[データセットの権限] ペインが表示されます。
[メンバーを追加] フィールドに、サービス アカウントのメールアドレス（snapshot-bot@PROJECT.iam.gserviceaccount.com.）を入力します。
[ロールを選択] プルダウンで、BigQuery データオーナーのロールを選択します。
[Add] をクリックします。
[データセットの権限] ペインで、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 をアクティブにします。
次の
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
出力は次のようになります。
namedisplayNamedataSourceIdstateprojects/12345/locations/us/transferConfigs/12345Monthly snapshots of the TABLE tablescheduled_queryRUNNINGnameフィールドの 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] ページに移動します。
[エクスプローラ] ペインで、
BACKUPデータセットを開き、TABLE_YYYYMMDDスナップショットが作成されていることを確認します。ここで、YYYYMMDDは毎月 1 日です。次に例を示します。
TABLE_20220601TABLE_20220701TABLE_20220801
次のステップ
- テーブルのスナップショットの詳細については、テーブル スナップショットの使用をご覧ください。
- クエリのスケジュール設定の詳細については、クエリのスケジュール設定をご覧ください。
- Google Cloud サービス アカウントの詳細については、サービス アカウントをご覧ください。