bqコマンドでBigQueryの定期的なスケジューリング設定をしてみよう

Dentsu Digital Tech Blog

2021年12月6日 10:00

電通デジタルアドベントカレンダー2021 6日目の記事です。

はじめに

こんにちは。電通デジタル開発部エンジニアの石原です。

データの分析基盤として、大規模データを扱えて処理が高速なBigQueryはとても便利ですよね。弊社でも日々活用しています。

特にBigQueryで中間テーブルを日々更新したり、分析用のテーブルを定期的に最新化するために、クエリのスケジューリング設定をよく使用しています。

この記事では、bqコマンドを使用してスケジューリング設定を行う際に、特にサービスアカウントを利用し、かつ特定の時間を指定する方法をご紹介します。

よくあるユースケースだと思いますが、Google Cloudの公式ドキュメントの該当ページには詳しく載っていないユースケース（2021年11月時点）なので、ぜひ皆さんの参考になればと思います！

特に、これまでコマンドからスケジューリング設定したことがない方は試してみてください。

事前準備

設定の前にまずは事前準備を行います。4つの準備が必要です。細かくは記載しませんが、関連する公式ドキュメントへのリンクをまとめておきますので参考にしてください。

1. bqコマンドのインストール
https://cloud.google.com/sdk/docs/install?hl=ja

2. サービスアカウントの作成
https://cloud.google.com/iam/docs/creating-managing-service-accounts?hl=ja#creating
※ サービスアカウントは<SERVICE_ACCOUNT_NAME>@<PROJECT_ID>.iam.gserviceaccount.com の形式で作成されます

3. サービスアカウントへ必要なロールを付与https://cloud.google.com/bigquery/docs/scheduling-queries#required_permissions

4. サービスアカウントキー（JSON形式）のダウンロードhttps://cloud.google.com/iam/docs/creating-managing-service-account-keys?hl=ja#creating_service_account_keys

サービスアカウントとしてログイン

スケジューリング設定は、一度設定すると長い期間残り続けることが多いです。日々更新されるテーブルが毎日の分析に利用され、他のシステムの土台になります。

スケジューリング設定する人が異動や退職してしまったら更新の手間がかかってしまいますよね。特定の個人に紐付いたアカウントよりも、サービスアカウントを利用して設定しましょう。（もちろん、GCPのプロジェクトも組織で管理されていることが前提です）

コマンド

gcloud auth activate-service-account を実行します。

$ gcloud auth activate-service-account \

    <SERVICE_ACCOUNT_NAME>@<PROJECT_ID>.iam.gserviceaccount.com \

    --key-file  <SERIVICE_ACCOUNT_KEY_FILE>.json \

    --project <PROJECT_ID>

必要な箇所は適宜置き換えてください。実行後、下記のような表示が出れば成功です。

Activated service account credentials for: [<SERVICE_ACCOUNT_NAME>@<PROJECT_ID>.iam.gserviceaccount.com]

これでサービスアカウントの有効化が完了し、各種設定を行えるようになりました。

確認

gcloud auth list を実行します。サービスアカウントの前に「*」が付いていれば、サービスアカウントとしてログインできています。

$ gcloud auth list

                      Credentialed Accounts
ACTIVE  ACCOUNT
        example@example.co.jp
*       <SERVICE_ACCOUNT_NAME>@<PROJECT_ID>.iam.gserviceaccount.com

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

クエリのスケジューリング設定

サービスアカウントでのログインができたら、スケジューリングの設定を行います。SQLファイル（例：your-query.sql）を用意して、次のコマンドを実行してください。

$ bq query \
    --project_id=<PROJECT_ID> \
    --destination_table=<TARGET_DATASET>.<TARGET_TABLE> \
    --display_name='このスケジューリング設定の表示名（自由に指定）' \
    --schedule='every day 21:00' \
    --use_legacy_sql=false \
    --replace=true \
< your-query.sql

実行後、下記のような表示が出れば成功です。これでスケジューリング設定ができました。

Transfer configuration 'projects/XXXXXXXXXXXX/locations/us/transferConfigs/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX' successfully created.

オプション

コマンドのオプションを一つ一つ確認しましょう。

--project_id=<PROJECT_ID>

クエリのスケジューリング設定をするプロジェクトを指定します。省略可能ですが、誤って別のプロジェクトに設定することがないように明示的に設定しましょう。

--destination_table=<TARGET_DATASET>.<TARGET_TABLE>

宛先のデータセットとテーブルをピリオドで区切って指定します。データセットが存在しない場合は失敗するので、予め作成してください。

--display_name='このスケジューリング設定の表示名（自由に指定）'

追って管理画面などで設定を確認する際に、ここの名前で識別します。一意になるように指定してください。

--schedule='every day 21:00'

クエリを実行する頻度を指定します。この例は日次実行ですが、'every 3 hours' といったように他の頻度も設定できます。

また、ここで指定する時間は UTC です。日本時間から9時間を引いて設定してください。（公式ドキュメントのbqコマンドの箇所には載っていないので、注意してください！）
例えば、毎日日本時間の朝6時にクエリを実行したい場合は 'every day 21:00' のように指定します。文字列内に半角スペースが入るので、シングルクォーテーションやダブルクォーテーションで文字列を囲むことを忘れないようにしてください。

--use_legacy_sql=false

BigQueryの標準SQLを使用する場合、falseを指定してください。

--replace=true

このフラグを設定すると、毎回のクエリ実行時にテーブルを上書きします。代わりに --append_table=true を指定すると、テーブルにデータを追加します。

設定されたかの確認

ちゃんと設定できたか確認しましょう。bq ls コマンドを使用して、現在設定されているスケジューリングの一覧を表示できます。

先程オプションで設定したdisplay_nameが表示されていれば、無事に登録されています！

$ bq ls --transfer_config --transfer_location=US

                                           name                                                              displayName                      dataSourceId       state    
 ----------------------------------------------------------------------------------------- ------------------------------------------------ ----------------- ----------- 
  projects/XXXXXXXXXXXX/locations/us/transferConfigs/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX              スケジュールドクエリテスト1                   scheduled_query   SUCCEEDED  
  projects/XXXXXXXXXXXX/locations/us/transferConfigs/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX              スケジュールドクエリテスト2                   scheduled_query   SUCCEEDED 
  projects/XXXXXXXXXXXX/locations/us/transferConfigs/XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX              スケジュールドクエリテスト3                   scheduled_query   SUCCEEDED 
…

※ --transfer_location=US を指定して表示されない場合は、Webのコンソール画面から直接確認してみてください。リージョンもコンソールから確認できます。

※ bq ls --format=prettyjson --transfer_config --transfer_location=US
を実行すると、設定した時間やクエリの内容など、より詳細な情報を表示できるので確認してみてください。

終わりに

bqコマンドでスケジューリング設定したことがない方は、ぜひこの記事を参考に試してみてください。コマンドに慣れてきたら、シェルスクリプトを書いてクエリを一括登録すると便利かもしれません！ここまで読んでいただきありがとうございました。

みんなにも読んでほしいですか？

オススメした記事はフォロワーのタイムラインに表示されます！