BigQueryでスケジュールの実行ユーザーを設定する

はじめに

Google Cloudのサービスの中でも特に人気のあるBigQueryの中に、「スケジュールされたクエリ」というものがあります。

クエリを手動で随時実行することなく、かつアプリケーションや独自のシェルなど書くことなく、GoogleCloud側で任意のタイミングにクエリを自動実行させることができる、便利な機能です。

日次でバッチ処理する必要がある場合などに、特に活用しやすいのではないかと思います。

今回のトピック - 実行ユーザー

このスケジュールクエリ、は画面上で簡単に設定することが出来ます。

BigQueryのクエリエディタを開くと、「スケジュール」という項目があります。

その中にいくつか設定項目があるうちの「サービスアカウント」が今回気にする箇所です。

記載されている通り デフォルトでは、サービスアカウントが選択されていない場合、リクエスト元のユーザーの認証情報を使用しますので、サービスアカウントが無いと実行できないわけではありません。

注意事項

デフォルト設定における「リクエスト元のユーザーの認証情報」はすなわち、このスケジュールクエリを作成したユーザーのアカウント(メールアドレス)ということです。普段の利用時は特に問題ないと考えるかもしれませんが、その作成したユーザーが離任などでアカウント削除されると、スケジュールクエリが実行できないことになります。システムの運用にあたって担当者が変わるということは多々ありますので、個人アカウントに依存した設定というのはリスクになります。スケジュールクエリ実行のためのサービスアカウントを用意してそれを用いることを推奨します。

設定方法

事前準備 - サービスアカウント

スケジュールクエリ実行のためのサービスアカウントはあらかじめ用意しておきましょう。

権限としては公式ページに記載の通りです

bigquery.datasets.get（抽出先データセットに対する権限）
bigquery.jobs.create

スケジュールされたクエリを変更または削除するには、次のいずれかの IAM 権限が必要です。

bigquery.transfers.update
bigquery.jobs.create とスケジュールされたクエリの所有権

BigQueryに関する権限は roles/bigquery.admin (BigQuery管理)」を付与するといずれのメソッドも実行可能です。それ以外の事前定義ロールはいずれも権限不足ですので、カスタムロールを準備しない環境であればこの付与が必須です。

実施事項(新規スケジュールクエリ)

新規でスケジュールクエリを作成する際は、上記で提示した通り、スケジュールクエリ作成のタイミングで「サービスアカウント」の設定をするだけです。画面上で操作できるので特別なことはありません。

bq_schedule_query-1

なお、画面からサービスアカウントを選択する際には、スケジュールクエリを作成するサービスアカウント(画面を操作するユーザー)に以下の権限を付与しておく必要があります

iam.serviceAccounts.list

事前定義ロールとしては roles/iam.serviceAccountViewer (サービスアカウントの表示)がこの権限を持ちます。

実施事項(既存スケジュールクエリ)

既に作成したスケジュールクエリの実行ユーザーを変更する場合、画面上からの変更は出来ません。(画面からは設定された内容の確認と、クエリ自体の変更までしかできません)

bq_schedule_query-3

したがって、変更方法はBQコマンドを用いる必要があります。

対象とするスケジュールクエリのIDを取得し、そのクエリに対する更新のコマンドを実行するという手順です。

PROJECT_ID="GCPプロジェクトのID" REGION="クエリリージョン" # スケジュールクエリのIDを取得 bq ls --project_id=$PROJECT_ID --transfer_config --transfer_location=$REGION --filter='dataSourceIds:scheduled_query' SA_NAME="事前作成したサービスアカウントのアドレス" QUERY_ID="# 取得したID: 形式 projects/<PROJECT_NUMBER>/locations/asia-northeast1/transferConfigs/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX" bq update \ --project_id $PROJECT_ID \ --transfer_config \ --update_credentials \ --service_account_name=$SA_NAME $QUERY_ID

このコマンド実行においては、前述の新規作成時に権限のあったroles/iam.serviceAccountViewer (サービスアカウントの表示)では足りず、roles/iam.serviceAccountUser (サービスアカウントユーザー)が無いとエラーになるようです。

エラー出力には具体的に権限不足しているメソッドは出力されませんが、公式ページの記述を見る限りですとiam.serviceAccounts.actAsメソッドの実行権限が必要なのではないかと思われます。

Bigquery service returned an invalid reply in update operation: Error reported by server with missing error fields. Server returned: {'error': {'code': 403, 'message': 'The caller does not have permission', 'status': 'PERMISSION_DENIED'}}. Please make sure you are using the latest version of the bq tool and try again. If this problem persists, you may have encountered a bug in the bigquery client. Please file a bug report in our public issue tracker: https://issuetracker.google.com/issues/new?component=187149&template=0 Please include a brief description of the steps that led to this issue, as well as any rows that can be made public from the following information:

コマンドが成功すると、以下のような出力になります

Transfer configuration 'projects/XXXXXXXXXXXX/locations/asia-northeast2/transferConfigs/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' successfully updated.