Logo
About

gitでBigQueryのScheduledQueryを管理する

2021/09/25

はじめに#

BigQueryのScheduledQuery（スケジュールされたクエリ），使ってる人もいれば使っていない人も多く感じています． 理由としてはGCPの人ちゃんと開発してる？という雰囲気を感じ，他のGCPサービスに比べ使うメリットを感じないからかもしれません．

個人的には日時の書き込みクエリなどはScheduledQueryで統一されていると見易くて嬉しいなくらいにしか使うメリットを感じておりません． 大体のプロジェクトは中規模くらいになればWorkflowEngineやCronJobのようなタスク自動化サービスを使うようになるのでそこに組み込めば問題ないという一面もあると思います． 最近はpub/subで通知する機能ができましたが，以前は失敗通知を届けることすらできなかったのも原因の一つかもしれません．

ここまでディスりながらも弊社や世の中ではScheduledQueryを使う人がいます．私もその中の1人です． 理由は簡潔で，フルマネージドだからです．（言葉が曖昧で申し訳ありません．クラウドサーバーの設定が必要ないという意味で使ってます．） ワークフローやジョブに組み込むには多少ながら追加する必要もあり（必要ないように設計することもできますが基盤設計が重要），メンテの必要性も出てきます．

結果的には簡単にスケジュールで動くクエリを作ることができ，設定も楽ちんだからと使う人も多いのでしょう． しかし，登録・管理方法が如何せん適当なUIなので放置しておくと管理が大変になります． UIで簡単に登録できるので，数ヶ月前に登録したScheduledQueryが現在使っているのかどうか分からず止めるのも難しい． クエリもベタ書きなのでメンテもできず，アップデートすることがあっても手動でバージョン管理も曖昧．

上記のようにデメリットも多く存在するのでScheduledQueryを使う際のベストプラクティスをgit+bqコマンドで実践したいと思います．

Reference

前提#

ScheduledQueryに登録するクエリはストアドプロシージャ(Stored procedure)の実行クエリだと思っています． 基本的に定期バッチで動かすクエリはCREATEやINSERTのステートメントを入れるため，DDLが複雑になりクエリを直で登録するとUIからもgitからも何をしてるのか分からないと人が続出するからです． 好みの部分ではあるのでここは無理に通さなくても大丈夫です．

Reference

StoredProcedure#

ひとまず簡単なStatementを複数使ったストアドプロシージャを作ってみます．

CREATE OR REPLACE PROCEDURE
  `project.dataset_id.procedure_name`()
BEGIN

# Statement 1
CREATE TEMP TABLE TEST_TABLE AS
SELECT *
FROM `bigquery-public-data.covid19_open_data.covid19_open_data`
LIMIT 10;

# Statement2
SELECT * FROM TEST_TABLE;

END

例なので特に意味がないストアドプロシージャを作成しました．

CALL `project.dataset_id.procedure_name`();

上記を実行するとStatement1，Statement2を順に実行します．

Reference

bqコマンド：ScheduledQueryの作成#

bqコマンドでScheduledQueryを作成する方法は2種類あります．

  1. bq mk
  2. bq query

詳しく説明

  1. bq mk: DDLを実行する際，ScheduledQueryは宛先データセットの指定など必要なく登録することが可能です． その際，--location='US'といったようにlocation情報が必須になります． 実際に作成するためのコマンドは以下のようになります．たぶんこれが必須な情報．．．
bq mk \
  --transfer_config \
  --location='US' \
  --display_name='UI上で表示される名前' \
  --schedule='every 24 hours' \
  --params='{"query": "CALL `project.dataset_id.procedure_name`();"}' \
  --data_source=scheduled_query
  1. bq query: このコマンドでも作成することは可能です．
bq query \
  --use_legacy_sql=false \
  --location='US' \
  --display_name='UI上で表示される名前' \
  --schedule='every 24 hours' \
  'CALL `project.dataset_id.procedure_name`();'

Reference

bqコマンド：ScheduledQueryの更新#

ScheduledQueryの更新方法は一種類しかなく，やや特殊な方法になります． この更新する対象は作成時のようなdisplay_nameで指定できずnameと呼ばれる，例としてprojects/862514376110/locations/us/transferConfigs/5dd12f26-0000-262f-bc38-089e0820fe38のようなIDが必要になります． これはbqコマンドで直接取得することは私が調べる限り不可能でした． gcloudコマンドにはglobalオプションでfilterやformatで出力形式を指定できるのですが，bqコマンドでは見つからず困ってました． そこで，transfer_configの情報をjson形式で取得し，key-valueの抽出やfilterをshellで行うことにしました．

bashでjsonを扱う際にjqコマンドがかなり扱いやすかったです． 以下のコマンドでdisplayNameからこのupdateに必要なnameを取得することができます．

bq ls --format=json --transfer_config --transfer_location='US' |
    jq -r ".[] | select(.displayName == "UIで表示される名前") | .name"

この値をresource_nameなどの変数に代入し，ScheduledQueryの更新で利用します．

bq update \
    --transfer_config \
    --schedule="every 12 hours" \
    --params='{"query": "CALL `project.dataset_id.procedure_name`();"}' \
    "${resource_name}"

Reference

git管理#

jsonファイルを管理することで，mainブランチにマージされたタイミングで差分があったjsonのスケジュールドクエリをCIで作成・更新するようにします．

schedulesディレクトリなどにschedules/*.jsonという形で保存してもらい，bqコマンドで利用するjsonを管理します． 問題はtransfer_configを作成する際に必要なkey以外がjsonに含まれていたらエラーになることが一つ． そして，--schedule='every hogehoge'といったようにjson中ではなくパラメータとしてコマンドにスケジュール情報を与えなければならないところにあります．

jsonでgit管理したいがjson内にクエリ情報しかないと一覧性が悪い+jsonのクエリに対するスケジュールをどこで管理するのか悩ました． 結果的にscheduleのkeyをjsonに入れることを認め，コマンドを叩く際にjqコマンドで削除するという方針にしました． よって以下のようなjsonで管理します．

{
  "schedule": "every 24 hours",
  "query": "CALL `project.dataset_id.procedure_name`();"
}

これをこのままparamに渡すとエラーになるので，このscheduleをjqで受け取り，

  1. jsonのschedule値を--scheduleに渡す．
  2. jsonからscheduleを除いたjsonを--paramにjson形式で渡す． といったことを行いました．

簡潔にまとめると以下のようなshellができあがりました．

# SCHEDULE_NAME：任意の表示名（今回の場合ファイル名などを使った）
resource_name=$(
  bq --format=json ls --transfer_config --transfer_location='US' |
    jq -r ".[] | select(.displayName == \"${SCHEDULE_NAME}\") | .name"
)

# SCHEDULE_FILE：schedules/*.jsonファイル
if [ -n "${resource_name}" ]; then
  bq update \
    --transfer_config \
    --schedule="${schedule}" \
    --params="$(< "${SCHEDULED_FILE}" jq 'del(.schedule)')" \
    "${resource_name}"
fi

このshellをCIの差分があったjsonファイルごとに実行し，スケジュールクエリを更新することにしました． 本当はelseでbq mkをしてますが割愛させていただきます．

終わりに#

スケジュールクエリをgitで管理することで，スケジューリング頻度大丈夫？といったレビューやクエリ自体のレビューもし易くなったのでUIで適当に作るより遥かによくなったと思います．

comment#