スケジュールされたクエリを使用してテーブル スナップショットを作成する
このドキュメントでは、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
サービス アカウントを作成します。[サービス アカウントを作成] をクリックします。
[サービス アカウント名] フィールドに、「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
出力は次のようになります。
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] ページに移動します。
[エクスプローラ] ペインで、
BACKUP
データセットを開き、TABLE_YYYYMMDD
スナップショットが作成されていることを確認します。ここで、YYYYMMDD
は毎月 1 日です。次に例を示します。
TABLE_20220601
TABLE_20220701
TABLE_20220801
次のステップ
- テーブルのスナップショットの詳細については、テーブル スナップショットの使用をご覧ください。
- クエリのスケジュール設定の詳細については、クエリのスケジュール設定をご覧ください。
- Google Cloud サービス アカウントの詳細については、サービス アカウントをご覧ください。