こんにちは、DATA-SREチームの塩崎です。最近気になるニュースは「ネコがマタタビを好む理由が蚊を避けるためだった1」です。
さて、皆さんはデータ基盤で集計した結果をどのようにして確認していますか。LookerやPower BIなどのBIツールを使って綺麗なダッシュボードを作成している方も多いかと思います。しかし、全員が毎日確認すべき数値はSlackなどの全員が日常的に目にする場所へ掲げたいです。本記事ではBigQueryとSlackを連携させる機能をノーコードで作成する方法を紹介します。
従来手法
BigQueryで集計した結果をSlackに通知するためにはGoogle Apps Script（以下、GAS）を用いるやり方が現在では主流です。GASの文法はJavaScriptとほぼ同じであり、普段分析をメインで担当している人たちには馴染みの薄い言語です。また、Cloud FunctionsとCloud Schedulerを組み合わせて定期的に集計結果をSlackへ通知できますが、これも同様に分析メインな人たちにとっては難易度が高いです。
そのため、Slack通知するためのBotの作成と運用をエンジニアに依頼するという業務フローを採っている組織もあるかと思います。この工数が非常に大きいわけではありませんが、可能ならばエンジニアリソースを使わずにSlackへの通知を実現させたいです。
提案手法
今回提案する手法の全体図を以下に示します。
BigQuery→Google Sheetsの連携にはConnected Sheetsを使い、Google Sheets→Slackへの連携にはSlack Workflow Builderを使います。Google Sheetsを仲介させることで、SQLのみで集計結果をSlackに通知することが実現できます。
Connected Sheets
Connected SheetsはBigQueryとGoogle Sheetsを繋ぐ機能です。BigQueryに対してクエリを実行した結果をGoogle Sheetsに挿入したり、Google Sheetsにおけるピボットテーブルを自動的にSQLに変換したりできます。今回はクエリの実行結果をGoogle Sheetsへ挿入するために使用しています。
Slack Workflow Builder
Slack Workflow Builderは定型的なプロセスをワークフロー化して、Slackで実行するための機能です。デフォルトの状態では、メッセージの送信やフォームの表示などしかできませんが、サードパーティ製のアプリを導入すると外部サービスと連携できます。
今回は以下のアプリを使ってGoogle Sheetsとの連携をします。
手順
それでは、実際にやってみましょう。今回はお題として「毎朝10時にBigQueryのログを確認し、昨日の利用費が多い人Top3を通知する」を実現させます。
BigQueryでのジョブの実行履歴は、INFORMATION_SCHEMAのJOBS_BY_ORGANIZATIONから取得します。
完成したクエリを以下に示します。第1列に行番号を入れているのは、Google SheetsとSlackを連携させる時に必要なためです。
select row_number() over (order by sum(total_bytes_billed) desc) as row_num, user_email, cast(sum(total_bytes_billed) / 1024 / 1024 / 1024 / 1024 * 5 as int64) as total_cost_in_usd from `region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION where date(creation_time, 'Asia/Tokyo') = current_date('Asia/Tokyo') - 1 and reservation_id is null group by user_email order by total_cost_in_usd desc
BigQueryとGoogle Sheetsの連携
まずはBigQueryとGoogle Sheetsを連携させます。
メニューバーから「Data」→「Data connectors」→「Connect to BigQuery」を選択します。
課金プロジェクトの選択画面が表示されるので、適当なプロジェクトを選択したあとに「Write Custom Query」を選択してクエリエディタを開きます。ここに先程のクエリを入力して、「Connect」を選択します。
すると、クエリを実行した結果がGoogle Sheetsに挿入されます。
次に、「Refresh options」から定期実行の設定をします。実行時刻を詳細に指定できず、4時間程度の幅の中から選ぶ必要があります。今回はSlackへの通知時刻が10時なので、それ以前の時間帯であればどの時間でも大丈夫です。
最後に「Extract」ボタンを選択して、別シートへ結果の書き出しを行います。Data Connectorで自動的に作成されたシートは直接Slackに連携できないので、一旦通常のシートへの書き出しが必要です。
Google SheetsからSlackへの連携
次にSlackへ連携させます。Google SheetsとSlack Workflow Builderを連携させるためには以下のアプリが必要なので、予めSlackのワークスペースにインストールする必要があります。
Slack Workflow Builderを起動し、新規のワークフローを作成します。トリガーは「Scheduled date & time」に設定し、毎日AM 10:00に起動するように設定します。
ここから、Google SheetsとSlack Workflow Builderを連携するための設定を入れていきます。「Add Step」を選択して、Google Sheetsからデータを取得するStepを追加します。
「Select a spreadsheet row」を選択します。もし、この時にGoogle Sheets関連のStepが見つからない場合は「Google Sheets for Workflow Builder」のインストールが必要です。
このStepの設定は以下のようにします。「Sheet」はData Connectorが自動的に作成したシートではなく、Extractをして生成したシートにする必要があります。このStepは「Choose a column to search」に設定した列の値が「Define a cell value to find」になっている行をシートから読み取ります。この例ではrow_numが1の列を読み取ることになるので、前述したクエリと併せると、BigQueryの課金額が1番多い人の情報を読み取っています。
同様に「Add Step」であと2つのStepを作成します。「Define a cell value to find」をそれぞれ2と3の値にする以外は、1つ目と同じ設定のStepにします。これにより、BigQueryの課金額が2番目と3番目に多い人の情報を読み取ります。
最後に、取得したデータをSlackに投稿するためのStepを作成します。「Add Step」から「Send a message」を選択します。
この時に「Insert a variable」をクリックすると以前のStepで読み取った値を参照できます。同じ名前が3つずつあり少し分かりにくいですが、上のものから順に1番目、2番目、3番目のStepで読み取った値を表しています。
これらの変数を埋め込み、メッセージを整えていきます。最終的には以下のようなメッセージが出来上がりました。
あとは、このWorkflowをPublishすれば毎日定期的にBigQueryの高額課金者を通知するBotが完成します。実際に動作している様子を以下に示します。
メリット・デメリット
従来のGASを使ったやり方に対する、今回の手法のメリット・デメリットをまとめます。
メリット
メリットとして挙げられるのは、SQLだけを知っていればOKという点です。
GASを使う手法で必要だったJavaScriptに関する知識が今回の手法では不要になります。そのため、エンジニアの工数を消費することなく、集計結果の定期的な通知機能を実現できます。このような通知機能は1回作ったらおしまいになることは少なく、プロダクトの成長に併せて確認すべき数値が変わることもしばしばあります。最近では非エンジニアでもSQLを書ける人材が多いので、SQLさえ知っていればOKである仕組みにすると継続的にエンジニアの工数を削減できます。
デメリット
一方で、デメリットもあります。デメリットは大きく分けると2つあり、柔軟性と信頼性が劣るという点です。
まず柔軟性が劣る点について説明します。GASを使ってSlackに連携する場合はSlackのIncoming Webhook機能を使っているケースが多いかと思います。Incoming Webhookで送信するメッセージはBlock Kitに対応しているためリッチな通知ができます。今回の手法でも多少のメッセージの装飾はできますが、標準的なメッセージで可能なものに限ります。
また、クエリの実行時刻についても柔軟性が劣っています。Connected Sheetsの仕様により、クエリの実行時刻は最大で4時間の誤差が生じることを考慮に入れる必要があります。
さらに、通知の頻度についても柔軟性が劣ります。Slack Workflow BuilderとConnected Sheets両方の仕様により、日次よりも高い頻度では通知できません。
次に信頼性が劣る点について説明します。Slack WorkflowがGoogle Sheetsから値を読み取る時に、BigQueryで実行されているクエリの完了を待ち合わせることができません。クエリの実行が完了していない場合は前日分の集計結果をSlackに通知されてしまいます。そのため、クエリの実行タイミングとSlack Workflowの起動タイミングの間に十分なバッファを用意する必要があります。
また、クエリの実行中にエラーが発生したことを検知する方法がありません。そのため、「通知が来ない」ことによってしかエラーの検知ができません。
これらのデメリットは今後Connected SheetsやSlack Workflowの機能が充実することで解消される可能性があるので、今後に期待したいです。
まとめ
BigQueryで集計をした結果を定期的にSlackに通知する機能をノーコードで作ることができました。GASで作成する場合に比べると柔軟性や信頼性では劣りますが、エンジニアの工数を使わずに通知が実現可能という点が大きなメリットです。簡単な通知Botならば非エンジニアでも作れるようになるので、データ基盤を社内の多くの職種に解放してデータ活用を更にすすめることに貢献できる機能です。
最後に
ZOZOテクノロジーズでは一緒にサービスを作り上げてくれる仲間を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください！
