👋

gitでBigQueryのScheduledQueryを管理する

2021/09/25に公開

GitHub

BigQuery

tech

はじめに

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

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

ここまでディスりながらも弊社や世の中ではScheduledQueryを使う人がいます。私もその中の1人です。
理由は簡潔で、フルマネージドだからです。
他のサービスはワークフローやジョブに組み込むには多少ながら追加する必要もあり（必要ないように設計することもできますが基盤設計が重要）、メンテの必要性も出てきます。

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

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

Reference

Scheduling queries

前提

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

CREATE PROCEDURE statement

bqコマンド：ScheduledQueryの作成

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

bq mk
bq query

詳しく説明

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
```

bq query: このコマンドでも作成することは可能です．

bq query \
  --use_legacy_sql=false \
  --location='US' \
  --display_name='UI上で表示される名前' \
  --schedule='every 24 hours' \
  'CALL `project.dataset_id.procedure_name`();'

Reference

Scheduling queries

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

jqコマンド

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で受け取り、

jsonのschedule値を--scheduleに渡す。
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で適当に作るより遥かによくなったと思います。

呟き

bq updateでparamに不要なカラムを無視してくれるオプションあったら知りたいです。（調べきれていない）
terraformで管理してそっちの方が良いよ！って場合は連絡やブログで表現していただけると大変参考になります。

はじめに

前提

StoredProcedure

bqコマンド：ScheduledQueryの作成

詳しく説明

bqコマンド：ScheduledQueryの更新

git管理

終わりに

呟き

Discussion