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

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

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

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

権限とロール

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

権限

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

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

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

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

ロール

サービス アカウントを作成するには、少なくとも次の事前定義ロールが必要です。

ロール リソース リソースの種類
roles/iam.serviceAccountAdmin PROJECT プロジェクト

クエリのスケジュールを設定するには、少なくとも次の事前定義ロールが必要です。

ロール リソース リソースの種類
roles/bigquery.user PROJECT プロジェクト

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

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

Console

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

    [サービス アカウント] に移動

  2. PROJECT プロジェクトを選択します。

  3. snapshot-bot サービス アカウントを作成します。

    1. [サービス アカウントを作成] をクリックします。

    2. [サービス アカウント名] フィールドに、「snapshot-bot」と入力します。

    3. [作成して続行] をクリックします。

  4. BigQuery ジョブの実行に必要な権限をサービス アカウントに付与します。

    1. [このサービス アカウントにプロジェクトへのアクセスを許可する] セクションで、BigQuery ユーザーのロールを選択します。

    2. [完了] をクリックします。

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

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

Console

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

  1. Cloud Console で、[サービス アカウント] ページに移動します。

    [サービス アカウント] に移動

  2. PROJECT プロジェクトを選択します。

  3. snapshot-bot@PROJECT.iam.gserviceaccount.com をクリックします。

  4. [サービス アカウントのステータス] メッセージで、サービス アカウントがアクティブになっていることを確認します。

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

  1. Cloud Console で [リソースの管理] ページに移動します。

    [リソースの管理] に移動

  2. [PROJECT] をクリックします。

  3. [情報パネルを表示] をクリックします。

  4. [権限] タブで、[BigQuery ユーザー] ノードを開きます。

  5. snapshot-bot サービス アカウントがリストにあることを確認します。

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

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

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

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

Console

  1. Cloud Console で、[BigQuery] ページを開きます。

    BigQuery に移動

  2. [エクスプローラ] ペインで、PROJECT プロジェクト ノードを開きます。

  3. DATASET データセット ノードを開きます。

  4. TABLE テーブルを選択します。

  5. [共有] をクリックします。[共有] ペインが開きます。

  6. [メンバーを追加] をクリックします。[メンバーの追加] ペインが開きます。

  7. [新しいメンバー] フィールドに、サービス アカウントのメールアドレス(snapshot-bot@PROJECT.iam.gserviceaccount.com)を入力します。

  8. [ロールを選択] プルダウンで、BigQuery データ編集者のロールを選択します。

  9. [保存] をクリックします。

  10. [共有] ペインで、[BigQuery データ編集者] ノードを開き、snapshot-bot@PROJECT.iam.gserviceaccount.com サービス アカウントが表示されていることを確認します。

  11. [閉じる] をクリックします。

bq

  1. Google Cloud Console で、Cloud Shell をアクティブにします。

    Cloud Shell をアクティブにする

  2. 次の 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 サービス アカウントに付与します。

Console

  1. Cloud Console で、BigQuery ページに移動します。

    BigQuery に移動

  2. [エクスプローラ] ペインで、PROJECT プロジェクト ノードを開きます。

  3. BACKUP データセット ノードのメニューをクリックして、[開く] を選択します。

  4. [共有データセット] をクリックします。[データセットの権限] ペインが表示されます。

  5. [メンバーを追加] フィールドに、サービス アカウントのメールアドレス(snapshot-bot@PROJECT.iam.gserviceaccount.com.)を入力します。

  6. [ロールを選択] プルダウンで、BigQuery データ編集者のロールを選択します。

  7. [追加] をクリックします。

  8. [データセットの権限] ペインで、snapshot-bot@PROJECT.iam.gserviceaccount.com サービス アカウントが [BigQuery データ編集者] ノードに表示されていることを確認します。

  9. [完了] をクリックします。

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

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

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

DDL ステートメントを使用してスクリプトを作成する

このセクションでは、DATASET.TABLE テーブルのテーブル スナップショットを作成するスクリプトの作成方法について説明します。スクリプトには CREATE SNAPSHOT TABLE DDL ステートメントが含まれています。

Console

  1. Cloud Console で、BigQuery ページに移動します。

    BigQuery に移動

  2. [エディタ] ペインで、次の CREATE SNAPSHOT TABLE クエリを入力します。

    DECLARE snapshot_name STRING;
    DECLARE expiration TIMESTAMP;
    DECLARE query STRING;
    SET expiration = DATE_ADD(current_timestamp(), INTERVAL 40 DAY);
    SET snapshot_name = CONCAT("BACKUP.TABLE_",
     FORMAT_DATETIME("%Y%m%d", current_date()));
    SET query = CONCAT("CREATE SNAPSHOT TABLE ", snapshot_name,
       " CLONE PROJECT.DATASET.TABLE OPTIONS(expiration_timestamp=TIMESTAMP '",
       expiration,"');");
    EXECUTE IMMEDIATE query;
    
  3. [実行] をクリックしてクエリをテストします。

  4. [エクスプローラ] ペインで BACKUP データセットを開き、TABLE_YYYYMMDD テーブル スナップショットが作成されたことを確認します。ここで、YYYYMMDD は現在の日付と時刻です(例: TABLE_20220521)。

  5. テスト後にテーブル スナップショット名をクリックして、[削除] をクリックし、テーブルのスナップショットを削除します。

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

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

bq

  1. Google Cloud Console で、Cloud Shell をアクティブにします。

    Cloud Shell をアクティブにする

  2. 次の 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;'
    
  3. BigQuery によってクエリがスケジュール設定されます。

bq コマンドライン ツールのコマンドのスクリプトは、Cloud Console で実行したスクリプトとは次の点で異なります。

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

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

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

  1. 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
  2. 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 で月次テーブル スナップショット クエリがスケジュールされたことを確認するには、次の手順を行います。

Console

  1. Cloud Console で、[スケジュールされたクエリ] ページに移動します。

    [スケジュールされたクエリ] に移動

  2. [Monthly snapshots of the TABLE table] をクリックします。

  3. [構成] をクリックします。

  4. クエリ文字列にクエリが含まれていることと、クエリが毎月 1 日に実行されるようスケジュールされていることを確認します。

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

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

Console

  1. Cloud Console で、[スケジュールされたクエリ] ページに移動します。

    [スケジュールされたクエリ] に移動

  2. [Monthly snapshots of the TABLE table] というクエリの説明をクリックします。

  3. [実行履歴] をクリックします。

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

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

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

Console

  1. Cloud Console で、BigQuery ページに移動します。

    BigQuery に移動

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

    次に例を示します。

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

次のステップ