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

このドキュメントでは、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
roles/editor
roles/owner
PROJECT プロジェクト

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

ロール リソース リソースの種類
次のいずれか:

roles/bigquery.user
roles/bigquery.jobuser
roles/bigquery.admin
PROJECT プロジェクト

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

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

コンソール

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

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

  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. Google Cloud コンソールで、[サービス アカウント] ページに移動します。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

コンソール

  1. Google Cloud コンソールで、[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 コンソールで、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 サービス アカウントに付与します。

コンソール

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

    [BigQuery] に移動

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

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

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

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

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

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

  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 データセットに配置するために必要とする権限を提供します。

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

このセクションでは、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

  1. Google Cloud コンソールで、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 コマンドライン ツール コマンドの複数ステートメント クエリは、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 サービス アカウントの認証情報を使用して実行します。

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

コンソール

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

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

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

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

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

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

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

コンソール

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

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

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

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

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

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

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

コンソール

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

    [BigQuery] に移動

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

    次に例を示します。

    • TABLE_20220601
    • TABLE_20220701
    • TABLE_20220801

次のステップ