オンラインでのプログラミング系の演習をGoogle Colab & Google Driveで行なう

Colaboratory 機械学習

去年に引き続き、東京都立大学の非常勤講師の依頼をid:mamorukさん(小町先生)からして頂いたので、今年も講義を担当してきました。講義の内容としては

  • Mackerelでのロール内異常検知を題材に、機械学習をプロダクトに取り込んでいく際、どういった視点が必要になるのか
  • 実際の開発はどういった形式やツールで行なわれているのか、擬似的に体験してもらう

といった内容(講義 & 演習)で行ないました。内容としては昨年とほぼ一緒ですが、新型コロナウイルスの影響で演習パートがオフラインの対面ではなく、オンラインで行なう点が一番違いました。演習系のサポートは学生さんの手元の環境がそれぞれ違う、などあって去年も苦戦しました。今年は同じ感じでいくとさらに大変そう(というか見切れない...)だろうなと思って、やり方を考えてみました。

他にいいやり方があったら誰か教えて & 自分用の今後*1のメモという感じのエントリです。

演習パートの前提

  • 学生さんは人によってはカメラやマイクをオンにするのが厳しい人が多い
    • ネットワーク環境だったり、ハードウェア的なものだったり
    • ネットワーク環境によっては画面共有も厳しい、という人もいる
  • 学生さんの手元環境は様々。プログラミングへの慣れ方もかなり幅はある
    • 普段から慣れている人は勝手に進んでもらえるとよいが、講義の主旨的には環境設定で脱落者をなるべく出したくない
  • 講師1名 + TAさん2名のサポート体制
    • TAさんは去年の講義の履修者で内容はある程度把握してもらってる
  • 演習時間は2.5~3時間程度
  • 夏の集中講義の中の1日分の担当

実行環境はGoogle Colab

これはすでに割とやられているところ多そうですね。学生さんの手元の環境はMac / Windows / Linuxだったりするわけですが、それぞれのトラブルシューティングはかなり大変。昨今の話題的には、コンテナを使った開発も体験して欲しくてdocker上での実行も考えたのですが、この人数をリモートで対応するのはちょっと厳しい...ということで今年は採用を見送りました...(ちなみに去年はdocker上で作業をしてもらっていました)。Google Colabだと、Pythonのコードがしゅっと動く環境が手に入りますし、shellが動いてjqなどちょっとしたツールのインストールも楽、ということもあり採用しました。

雛形のColabのNotebookを用意しておいて、自分のアカウントにコピーしてもらってそこで各自実行してもらう、という形を取りました。Colabは「リンクを知っている全員に共有」ということが簡単にできるので、うまく動かない場合はNotebookを共有してもらって様子を見るということができました。これはリモートならでは感がありますね。

あと、採点するときにメールがzipファイル地獄にならずに済むというメリットもあります...。

課題のリポジトリはGitHubで管理、Google DriveでマウントしてColab上で修正

ちょっとしたデータ分析するだけであればColabだけで十分なのですが、今回の演習では

  • テストを書く
  • テストこける
  • プログラム修正する
  • テスト通る
  • プログラムを改良する
  • テスト通す
  • ...

といった一般的な開発のフロー(の一部)を学生さんに体験してもらうのが主目的でした。そのため、Colabだけでは不十分*2。そこで、以下のやり方を採用しました。

  • Colab上でGoogle Driveをマウント
  • マウントしたパス上にGithubの演習課題リポジトリをgit clone
    • Colabは何の追加インストールもせずにgit cloneが動いて助かる...
  • Colab上からマウントしたファイルを編集
    • Google Drive上のファイルも編集されるので、変更が永続化される
      • Colabではセッションストレージも使えるが、ランタイムのリセットなどで消えてしまうため、今回の目的に適さない
    • VSCodeなどみたいにリッチな補完が効くわけではないが、最低限のコードのシンタックスハイライトなどはColabがやってくれる
      • 演習で使うリポジトリはコードサイズは大きくないので、これで十分ではある
  • Colab上のマウントしたディレクトリでmake testを実施
    • ブラウザ上だけでテストを体験してもらえる環境が完成
  • 修正してもらったGoogle Driveのディレクトリを採点者やTAの方に共有してもらう
    • 学生さんがはまったときの手元の様子がリモート越しでも分かる
    • 実際、演習中に結構な人数の方の手元の様子をこれで一緒に見ることができました

学生さんによってはZoomの画面共有も環境的に厳しいという方もいらっしゃいましたが、Google Driveの共有リンクを共有してもらうことで何とか最低限のサポートはできたかなと思います。演習課題の提出時に講義の感想も一緒に送ってもらいましたが、Colab & Google Driveを使ったやり方はまあまあ満足してもらえたようでした。

実際のColab上の編集風景は↓のような感じ。右側でコードを書いて、左側でテストを実行します。IDEっぽさがありますね。

難しかったこと

受講者がありがたいことに40名近くいらっしゃったのですが、オンラインで学生さんの顔の様子もなかなか分からないこともあり、みんなスムーズに行っているのか、はまっているのか、最初は様子を掴みかねました。オフラインの講義の場合、教室をうろついて画面の様子を後ろから見てコメントしたり、といったことで様子を把握していましたが、オンラインだとそういうことが気軽にはできない。

40人の前では学生さんもなかなか質問しにくいところもあるかも、ということで途中からZoomのブレークアウトルームの機能を使って3チームに分かれ質疑を行なうようにしました。その結果、結構質問をしてもらって、それはそれでよかったのです。ただ、ブレークアウトルームが違うと、他の部屋でどういった質問が出たかが聞けない、などの悩ましさがありました。

演習系の講義を担当されている先生で「自分はこういう感じにしたところ、リモートでもある程度うまくいった!」というのがあったら是非教えて欲しいです。

参考

*1:予定は特にないです

*2:ファイル分割などがNotebookだとやりにくい

Twitter検索結果の語義曖昧性を解消するsaba_disambiguatorのアップデートを行ないました: その2

Mackerel Go

タイトルだけ見ると何のこっちゃという感じですが、前提としてはこんな感じです。

今回はこのツールのアップデートに関する話です。

複数の設定をできるように

当初はmackerelというクエリのみを定期的に見ていたのですが、周辺のサービスやOSSのtweetも見たいという要望がありました。例えば、Prometheusとか。Prometheusは意外と曖昧性があって、そのままTwitterで検索すると、荒野行動のチーム名に関するtweetがたくさん出てきます。世の中、色んなところに曖昧性が存在する。saba_disambiguatorは、曖昧性を解消した上で自分の欲しい結果に絞り込むツールなので、どんぴしゃな領域です。

mackerelやPrometheusをTwitterの検索クエリとしてそれぞれsaba_disambiguatorで設定したいところですが、これまではリソースを一つしか作れませんでした(deployで使っているSAMのパラメータが決め打ちだったため)。今回のアップデートで、適当に環境変数を変更することで複数の設定をできるようにしました。

色んなキーワードで設定できるようになったので、是非お試しください。

Secretの値を設定ファイルに書くのではなく、AWSパラメータストアから読むように変更

saba_disambiguatorを使うためには、TwitterのAPIを叩くためのconsumer_secretやaccess_secretを設定する必要があります。チームで管理する際には設定ファイルをgit管理したくなりますが、secretの生の値が入った設定ファイルをリポジトリに入れて管理するのはあまりよろしくありません。

そこで、secretをリポジトリに置くのではなく、AWSのパラメータストアに置くようにし、saba_disambiguatorの実行時にパラメータストアからsecretの値を引くように変更しました(設定ファイルにはパラメータストアのkeyの名前を書くようになりました)。

所感

saba_disambiguator、地味にアップデートを続けています。一昨年にリリース、昨年にアップデート、そして今回と約一年毎に何かしらいいアップデートができているので、来年くらいにまたさらによくできているといいな...!

SQLレクチャー会をチーム内でやっている話

データ分析

ここ最近、チーム内でSQLのレクチャー会をやっています。世間的にはプランナーの人や営業の方がSQLを書くのもそれほど珍しいことではなくなってきていると思いますが、チーム内ではまだまだ一般的ではないです。なんとかしていきたい。

SQLレクチャー会の目的はこんな感じです。

  • チーム内のSQL / 分析力の底上げ
    • データの民主化的なやつ
  • データライフサイクルの改善
    • 集計側であれこれ無理に頑張るより、入力データを集計側に合わせてもらうほうが圧倒的に省力化されることが多い
    • データの入力側と集計側の意識を合わせることで、チームのデータ分析のしやすさを高めていきたい
  • 毎月、毎期末作っているスプレッドシートの自動化
    • 手間を減らしたり、手作業によるミスを減らしたり

このエントリをきっかけに「うちでも似たことやってるけど、この取り組みをやってみたらさらにうまくいったよ」といったことが知れるとうれしいです。

背景: レクチャー会のパートナー

営業事務チームのid:shiozaki-aさんと一緒にやっています。バックグラウンドはこういった感じの方です。

  • 様々な仕事で助けて頂いてますが、このエントリの文脈だとSalesforceの管理やレポート作成などをしてくださっています
    • 主にセールスチームやプロデューサー向け
  • スプレッドシートでの集計やダッシュボードの作成は慣れておられる
    • レクチャー会スタート時点ではSQLを含むプログラムの経験はなかった
  • 混み入ったレポートをSalesforce上で作るのがなかなか難しいという課題感
    • できるできないで言えばSalesforce上でもきっとできると思うのですが、Salesforceエンジニア的な人がチームにいるわけではない
  • 毎月、毎期末作っているある程度定型のスプレッドシートやダッシュボードがいくつかあり、それらを自動化したい

id:syou6162がSQLの解説をして、id:shiozaki-aさんに練習問題を解いてきてもらって、という感じで週1~2回(一回1時間程度。6月から始めて、今日で13回目でした)のペースでやっています。

レクチャー会の内容

こんな感じでやっています。

  • レクチャーパート
    • id:syou6162が今日のお題(例: GROUP BY)を解説
    • 普段の業務に関連しているようなレポートの簡易版をSQLで書いてみたり
  • 実践パート
    • 毎回5問ほどid:shiozaki-aさんに練習問題を解いてきてもらう
    • 完璧に解けている必要はもちろんなくて、できたところまでをscrapboxに張ってもらったり、考えた過程を書いてきてもらう
    • 「こういう捉え方いいですね」とか「ここは一気にやるのは難しいから、小さい問題に解きほぐしていってみましょうか」という感じで、わいわいSQLを書く

約二ヶ月前のスタート時点はSELECT ... FROMから始まり、最近だと以下のような内容をご自身で解いてもらえるようになりつつあります(すごい!)。

  • ORDER BYやLIMIT
  • GROUP BYを用いた集約
  • WHERE句を使ったフィルタ
    • サブクエリとかも一緒に
  • DATE_TRUNCなど頻出のデータ変換の方法
  • JOINを使って複数のテーブルを結合した分析
  • BigQueryで抽出したデータをData Studioやスプレッドシートを使って可視化、分析
  • ウィンドウ関数を使った過去の時点との変化の分析など
    • ここは難しいところもあるので、もう一周しようかなと思ってる

10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く (Informatics &IDEA)をベースに、自分たちの業務を題材にレクチャーしたり、練習問題を解いてもらったり、という感じです。

レクチャー会にあたって気を付けていること

実務で使うデータを元に練習問題を作る

巷にはSQLの本が山程出ています。そういった本をベースに勉強してもらう、ということも考えたのですが、このレクチャー会ではそれは意図的にしませんでした。本をベースにすると、サンプルデータセットが付いてくると思うのですが、それが実務のデータと近いとは限らないからです。SQLも覚えないといけないのに、仕事とも関係ないデータについて頭に入れておくのは大変だし、そもそもデータに対する興味を持ちづらいと思いました*1。

今回のレクチャー会では、Mackerelの業務で使う馴染のあるデータ(具体的にはSalesforceの商談やキャンペーンの情報などをBigQueryに転送したもの)を元に、id:shiozaki-aさんが課題に思っておられる題材になるべく近いものを練習問題として手作りしました。こうすることで

  • データセットに対する理解はすでに頭にある状態からスタートできる
    • どういうテーブルがあって、あれとこれはJOINできそうとか関連付いている、といったことをイチイチ意識しなくてもよい程度にはデータのことは知っている
  • 直近の課題を解決できるかもしれないし、普段の仕事も楽になるかも...という感触を持ってもらえると、自分事として取り組んでもらいやすい
  • 集計結果がおかしい、といったことに対する肌感がある
    • 集計結果に矛盾がありそうだから、なんかSQLミスってる...?といった感覚は馴染のあるデータでないと持つのはなかなか難しいです
    • 小難しい分析テクよりも、ドメイン知識のほうがずっと大事

恐怖心を持たずにSQLを書けるように整備する

プログラムを書いたことのない人にとって、謎の英単語が並ぶSQLを書いていくのは最初は怖いことだと思います。恐怖心があるとなかなか勉強が進まないと思うので、雑に触ってもらっても大丈夫なように環境を整備しました。

  • GCPやBigQueryの権限は最低限に絞る
    • 既存のビューやテーブルは変更 / 削除できないようになっている
    • どれだけ触ってもらっても他の人の成果物を壊したりすることはないので、自由に使ってください
    • 今のところ、BigQueryのWebコンソールからSQL叩いてもらってます
  • BigQueryを使う上では、大量のデータのスキャンが走って課金が大変なことに...という恐怖もあると思う
    • スキャン量の上限が設定されていることや、定期的にスキャン量の多いクエリをデータ基盤側で監視しているので、もしまずかったらid:syou6162から言いに行くので大丈夫ですよ、と伝える
    • 触ってもらうデータは営業系のデータなので、スキャン量は気にする必要もほとんどない、といった背景もあります
  • 画面共有をしながら、細かいハマりどころを一緒に潰していく
    • 赤文字の英語で何か怒られてしまって、頭がフリーズしてしまう、というのは最初はあることかと思います
    • エラーが出たらしばらく自分で考えてもらって、難しそうであればエラーが何を言っているか、どうやると解消できるか、一緒に画面を見ながら探っていきます
    • Google Meetの画面共有をしながらやっています
      • 元々、東京と京都で地理的に離れていることもあり、コロナはあんまり関係なくリモートで画面共有しながらやってます

相手の背景知識に合わせて説明する

id:shiozaki-aさんは営業事務をずっとやられているので、スプレッドシートの扱いは慣れておられます。SQLとスプレッドシートは考え方が似ているところが多々あるので、「SQLについて説明する」というよりは「スプレッドシートでこういう操作のことを、SQLで書くとこうなるんですよー」という説明を多用するようにしています。「スプレッドシートの裏側では実はこういうSQL相当のことが行なわれているんだ」ということが頭の中で分かってもらえたら、SQLの理解も早まったように感じました。

あと、全てをSQLで完結させることに拘らないように気を付けています。ある程度の集計までやってしまえば、後は慣れたスプレッドシートでやってもらってもいいです。例えば、縦持ちから横持ちへの変換はSQL単独でやると割と面倒ですが、スプレッドシートやData Studioのピボットテーブルでやってしまえば一瞬ですよね。ツールは適材適所かつ相手の得意を生かせる形で、を意識してます。

正解に辿り着くまでの過程を共有する

慣れている人間がSQLを書くとばっと書けることも多いですが、慣れていない人はもちろんそんなことはできません。また、最終的なSQLだけ見てそれが理解できたとしても、それを一から自分で構成するのは難しかったりします。

そういったこともあり、レクチャー会では正解に辿り着くまでの過程でどう思考しているか、を丁寧に説明することを心掛けました。長くなったSQLのデバッグはエンジニアでもしんどいので、なるべく細かくステップに分割するように勧めました。WITH句を多用することで個々のSQLはなるべくシンプルに、BigQueryに怒られたとしてもどこで失敗しているのか考えるべきスコープが小さくなるように、という意図です。例えば「担当者×月毎の商談数を出す」というお題なら

  • ひとまず、担当者と商談を一緒に眺められるようにしよう
    • JOINだけやってみる
  • 月毎に商談数を出したいので、月で丸める必要がありそうですね
    • DATE_TRUNCだけやって、月で丸められているか確かめる
    • 例えばこの辺でWITHでまとめておく
  • 担当者×月毎に出したいので、GROUP BYと集約関数使うとよさそうですね
  • ちょっと見にくいので、日付でORDER BYしてみますか
  • セールスの人からはピボットテーブルで見たいと要望がいかにもきそうなので、SQLで出した結果をスプレッドシートを使って横持ちに変換してみましょうか

といった細かいステップに分割しながら説明しています。WITH句を説明するときも、スプレッドシートのアナロジーで

  • WITHの中身の一個一個のクエリは、スプレッドシートのタブに相当しているものだと思ってもらって大丈夫です
  • スプレッドシートの個々のタブでは、シンプルな集計や変換だけをやりますよね
    • それを繰り返して最終的に欲しい集計結果を作っていく
  • SQLを書くときも同様で、WITH句で各ステップは簡単なSQLで構成して、徐々に積み上げていくとよいです

という感じで説明しています。

教える側 / 教わる側という構図はなるべくしない

巷のSQLの本をベースにレクチャー会をやると、SQLを教える側と教わる側という構図にどうしてもなってしまいがちかなと思います。しかし、実際の仕事のデータを元に練習問題を作ってみると、作る側にとってもメリットがありました。

  • データや分析のことをきっかけに、普段の業務の課題意識を共有しやすい
    • お互いが「あれどうにかしたいなー」と思っていること、実はそんなに大変じゃないとか、一緒にやるともっと簡単にできるとかありますよね
    • SQLやデータ分析は職種が違ってもコミュニケーションを取りやすくする一種のツールや共通言語みたいなものだと思っています
      • 共通言語があると会話もしやすくなる
  • データ基盤の生の使われ具合を見ることができる
    • これは僕の今の仕事がデータ基盤を整備する人間である、という背景があります
    • データ基盤は単独では価値を生まなくて、分析する人が正確かつスピーディーに分析結果を意思決定に役立ててもらって、初めて価値が出るものです
    • レクチャー会や練習問題を解いてもらっているのを横で見ることで「ああ、ここ確かに紛らわしいな。メタデータに意味書いておかないと...」「この条件の集計苦労されていることが多いから、データウェアハイスに集計済みのものを提供したほうがいいな」など、分析者がどこが使いにくそうかといったデータ基盤の管理者的にはとても重要なフィードバックが得られます

こういった背景もあり、教える側 / 教わる側というより、双方にとって意義のある会にできれば〜ということを考えながらやっていますし、id:syou6162としても毎回学びが多いです。

shiozaki-aさんのレクチャー会の感想

ご本人の許可をもらったので、転載します。

SQLの勉強会をしてもらってるとセールスチームに言うと、そこまで頑張るんだって驚かれます。
わたし的には、知らないことを知れるのは楽しいので特に頑張ってる感じではないけど周りの感覚だとそうなんだな～と不思議な感じです。
syouさんも書いてたけど、「謎の英単語を書き連ねていくSQLを書いていくのは最初は怖いことだと思います」は確かにそうだと思います。
PCの設定さえ、会社のシステム担当にやってもらうのが当たり前で育ってきた事務畑の人間には、
黒い画面（エンジニアの人がコード書いてる画面のことを勝手にこう呼んでますww）みたいで、
私が触って大丈夫か！？？？という不安と、自分とは別世界のお話という感覚があり、自分で独学勉強する分野だとは思えないww
触って大丈夫だと太鼓判を押してもらって、その上教えてもらえる天国のような状況です！！！　
今回はsyouさんに声をかけてもらえて、勉強できる環境（時間・説明資料・練習問題）を整えてもらえたから出来てる。という状況です。
SQLの勉強してきて、セールスフォースの項目やいろんな実装についても考える事や、気づきがあった。
（例：受注予定月の項目を「●月」という文字列で持ってるけど、データ集計する時にはそれじゃ意味がないな。とか）
実際に使っているデータを元に説明してもらったり、練習問題やったりできるのはめっちゃありがたい。
この間JOINのところを本で読んでる時に例として書かれているデータの項目とか並びとかをを見とくだけでも大変で、
途中で飽きて後で再読ってなったので知ってるデータだと理解しやすいってことを余計に感じました。

参考: 他社さんの事例

*1:なぜなら普段使っていないサービスのデータに対して、自分は全く興味を持てないから...!

データ分析を元にFAQサイトを継続的に改善する

データ基盤 BigQuery

FAQサイト、サポート問い合わせをせずとも自分で疑問を解決できて便利ですよね。でも、検索した単語が一件もヒットしないと、ちょっとガッカリしてしまします。そういったガッカリを減らすために、簡単なデータ分析を使ってFAQサイトを継続的に改善する話を書いてみます。

...というのも、自分が仕事で関わっているMackerelでは最近FAQをリニューアルしたからなのでした。

MackerelのFAQではZendesk Guideを利用していますが、Zendesk Guideは便利なAPIが用意されているので、それと既存のデータ基盤を組み合わせて改善していく形です。

FAQサイト内の検索語を列挙する

まず、FAQサイト内でどういった単語が検索されているのかを列挙します。Google Tag Manager経由でFirebase Analyticsにデータを飛ばすと閲覧状況が分かりますが、そのログをBigQueryにexportしておくと調理しやすくなります。

あとは以下のようなSQL書いてあげれば、どういった検索がサイト内で行なわれているかが分かります。

SELECT
  *
FROM (
  SELECT
    (SELECT value.string_value FROM UNNEST(event_params) AS x WHERE x.key = "page_location") AS page_location,
    (SELECT value.string_value FROM UNNEST(event_params) AS x WHERE x.key = "search_term") AS search_term,
    *
  FROM
    `my-project.my_dataset.events_*`
  WHERE
    event_name = "view_search_results"
  )
WHERE
  page_location LIKE "https://support.example.com%"

これをBigQuery上でmy-project.my_dataset.zendesk_guide_search_termにviewとして定義したと仮定して、以降の話を進めます。

特定の検索語にヒットする記事を調べる

次にZendesk Guide内で検索語が何件ヒットするのかを調べましょう。Zendesk Guideはその辺がしっかりしていて、ある単語に対してヒットする記事の一覧を返してくれるAPIが存在しています。

検索された単語を全部APIに投げてるのはさすがにアレなので、検索回数が一定以上の上位N件の記事のヒット件数を取得して、BigQueryにアップロードします。

set -eu -o pipefail

COUNT_FILE=zendesk_articles_count_by_search_term.txt

bq query --max_rows 10000 --format json --use_legacy_sql=false \
  'SELECT
    search_term,
    COUNT(*) AS searches_count
  FROM
    my-project.my_dataset.zendesk_guide_search_term
  GROUP BY
    search_term
  HAVING
    searches_count >= 3
  ORDER BY
    searches_count DESC
  LIMIT
    100' | 
jq -r '.[] | .search_term' | while read -r search_term; do
  COUNT=$(curl -s "https://my_site.zendesk.com/api/v2/help_center/articles/search.json?query=${search_term}" | jq -r .count)
  sleep 1
  echo -e "${search_term}\t${COUNT}"
done > "${COUNT_FILE}"

bq load --source_format=CSV --encoding=UTF-8 --field_delimiter='\t' --replace \
  my-project.my_dataset.articles_count_by_search_term "${COUNT_FILE}" search_term:string,articles_count:integer

ヒット件数が0件の検索語を定期的にSlackに投稿する

材料が揃ったので、ヒット件数が0件の検索語を定期的にSlackに投稿します。Cloud Functionsを使うのですが、詳細は↓に書いています。

これを使うと、以下のような簡単なyamlテンプレートを書くだけで、簡単に投稿までいけます。

description: Zendesk Guide(FAQ)で検索されているが、ヒット件数が0件のものを通知する
webhook_url: https://hooks.slack.com/services/XXX/YYY/ZZZ
cron: "45 9 * * 1" # 月曜の9:45
sql: |-
  SELECT
    articles_count_by_search_term.search_term,
    MAX(articles_count_by_search_term.articles_count) AS articles_count,
    COUNT(*) AS searches_count
  FROM
    my-project.my_dataset.articles_count_by_search_term
  LEFT OUTER JOIN
    my-project.my_dataset.zendesk_guide_search_term
  ON
    articles_count_by_search_term.search_term = zendesk_guide_search_term.search_term
  GROUP BY
    articles_count_by_search_term.search_term
  HAVING articles_count = 0
  ORDER BY
    articles_count,
    searches_count DESC
template: |-
  以下の検索語がFAQでヒット件数0件でした。FAQの記事追加してみませんか?
  {%- for row in data %}
  ## 検索語: 「{{ row.search_term }}」
  - 検索回数: {{ row.searches_count }}
  {%- endfor %}

その他

Zendeskはその他、upvotesやdownvotesの数を記事毎に取得できるAPIなどもサポートされています(downvotesが多い記事は改善したほうがいい可能性が高い)。この辺りは丸っと取得してきたい & BigQueryにそのまま投下したいので、Embulkのプラグインを自前で用意しています。

Cloud Functionsを使って、BigQueryのクエリ結果をSlackに定期的に投稿する

BigQuery GCP

N番煎じネタです。Google Apps Scriptでやる例をよく見る気がするけど、Cloud Functionsを使ってやりたかったのじゃ。

以下のような設定をyamlに書いておくと、クエリ結果をテンプレートに従ってテキストに展開して、定期的にSlackに投稿(cron likeな設定ができる)してくれる。通知を増やしたければ、こういうyamlファイルを真似して書けばいいだけ。

description: テスト用のサンプルです!
webhook_url: https://hooks.slack.com/services/XXX/YYY/ZZZ
cron: "45 9 * * 1" # 月曜の9:45
sql: |-
  SELECT
    name,
    created_at,
  FROM
    `my-project.my_dataset.my_table`
  ORDER BY
    created_at
  LIMIT
    3
template: |-
  こんにちは〜。サンプル情報だよ
  {%- for row in data %}
  - name: {{ row.name }}
  - created_at: {{ row.created_at }}
  {%- endfor %}

利用例

こんな感じで使ってる or 使おうと思ってます。

  • 定時前に昨日のBigQueryのスキャン量やスキャン回数が多いユーザートップ3を通知
    • BigQuery警察
  • N日前とのユーザーエンゲージメントスコアの増減を週1で流す
  • 特定施策のKPI(例: XXXをクリックしたユニークユーザー数)を定例ミーティングの前に流す
  • FAQサイト内でヒット件数が0件だったユーザーが検索したクエリを隔週で流す
    • FAQサイトのカバレッジ改善

Slackに頑張って全部の数字やグラフを流すのではなく、大雑把な数字とその変化だけ流して興味を持ってもらい、あとはData Studioに誘導してリッチなグラフを見てもらう、という感じでやっています。

中身

起動するための手順としてはこんな感じで動かしてます。この辺読めば大体分かると思います。

  • 1: Cloud Schedulerに定期実行スケジュールなどを登録
  • 2: Cloud SchedulerがCloud Pub/Subトピックにメッセージを送信
  • 3: Cloud Pub/SubをトリガーとするCloud Functionsを起動
  • 4: Cloud Functions内でBigQueryのクエリを実行、テンプレートに従ってテキストに変換し、Slackに投稿

Cloud Schedulerに登録

1のCloud Schedulerに登録はこんな感じのシェルスクリプトでやってます(create_or_update_scheduler_jobとか名前を付けておく)。途中で出てくるyqはjqのyaml版って感じのやつです。テンプレートやSQLは改行込みで書きたい & jsonで書きたくない(yamlでやりたい)ため、yqを使っています。

#!/bin/bash
set -eu -o pipefail

[[ $# == "0" ]] && exit 1

FILENAME=$1

SCHEDULER_JOBS_COMMAND="create"
SCHEDULER_JOB_NAME="bq_slack_notification_${FILENAME}"

SCHEDULE=$(cat "templates/${FILENAME}".yml | yq -r '.cron')
DESCRIPTION=$(cat "templates/${FILENAME}".yml | yq -r '.description')

if gcloud scheduler jobs describe "${SCHEDULER_JOB_NAME}" > /dev/null; then
  SCHEDULER_JOBS_COMMAND="update"
fi

gcloud scheduler jobs "${SCHEDULER_JOBS_COMMAND}" \
  pubsub "${SCHEDULER_JOB_NAME}" \
  --description "${DESCRIPTION}" \
  --schedule "${SCHEDULE}" \
  --time-zone "Asia/Tokyo" \
  --topic bq_slack_notification_topic \
  --message-body "$(printf '{"filename":"templates/%s.yml"}' "$FILENAME")"

Cloud Pub/Subトピックにmessage-bodyの内容をscheduleに従って投稿してくれる。トピックはgcloud pubsub topics create bq_slack_notification_topicなどで作ります。

余談ですが、Cloud Schedulerはtime-zone指定できるのいいですね。自分でタイムゾーン変換しなくてよい。CloudWatch Eventsも指定できるようにならないかなぁ...。

Cloud FunctionsでBigQueryのクエリ実行結果をSlackに投稿

Pub/Subのeventで設定ファイル名がfilenameで渡されてくるので、それを読んでBigQueryでクエリ叩いて、 Jinjaのテンプレートに従ってテキストに変換してSlackに投稿するだけです。eventはbase64でエンコードされているので、デコードしてから使う。

import requests
import json
import json
import base64
import yaml
import os
import sys
from google.cloud import bigquery
from jinja2 import Template


def bq_slack_notification(event, context):
    dir_path = os.path.dirname(os.path.realpath(__file__))
    event_data = json.loads(base64.b64decode(event['data']).decode('utf-8'))
    with open(dir_path + "/" + event_data["filename"], 'r') as f:
        try:
            yml = yaml.safe_load(f)
        except yaml.YAMLError as exc:
            print(exc)
            sys.exit(1)
    template = Template(yml["template"])

    bq = bigquery.Client('my-project')
    data = bq.query(yml["sql"]).to_dataframe()

    txt = template.render(data=data.itertuples())
    requests.post(yml["webhook_url"], data=json.dumps({'text': txt}))

テンプレートファイルを置いたらCloud {Scheduler, Functions}に反映するラッパーをMakefileに用意しておきます。設定ファイル名がsample.ymlならmake deploy schedule-job-sampleと叩けば反映完了。お手軽。

deploy:
  gcloud functions deploy bq_slack_notification --runtime python37 \
        --trigger-topic bq_slack_notification_topic

schedule-job-%:
  $(eval FILENAME := $(subst -,_, $*))
  ./create_or_update_scheduler_job $(FILENAME)

最終的なディレクトリ構成

こんな感じです。

% tree .
.
├── Makefile
├── create_or_update_scheduler_job
├── main.py
├── requirements.txt
└── templates
    ├── sampleA.yml
    └── sampleB.yml

1 directory, 12 files

BigQueryのテーブルのメタデータをCloud Data Catalogで管理する

データ基盤 Data Catalog BigQuery

自分が使いたいと思ったBigQuery上のリソース(tableやview)、内容を事前に完全に把握できている、ということは結構少ないのではないかと思います。そういったときに手助けをしてくれるのがメタデータです。BigQueryのリソースに対するメタデータを、Cloud Data Catalogのタグとして付与する方法を紹介します。Cloud Data Catalogを使うことで、分析者が必要なリソースに素早く辿り付いたり、正確な分析をするためのサポートができます。

Cloud Data Cagalogとは

BigQueryと比べると認知度は大分まだ低いと思うので、概要を先に紹介します。Cloud Data CagalogはGCPが面倒を見てくれるフルマネージドなメタデータ管理サービスです。フルマネージドというのがポイントで、チームにデータ整備人(データエンジニア)が一人しかいない!という状況でもオペレーションなど追加的な負荷少なく使うことができます。先日GAにもなったので、安心して使えますね。

BigQueryの{テーブル, カラム}のdescriptionにあれこれメタデータを書き込んでおけば、自動的にCloud Data Cagalogで検索できるようになります。(個人ではなく)チームでメタデータを整備していく体制を整える、という話題については先日ブログにも書きました。

BigQueryだけでなく、Cloud Pub/Sub、Cloud Storageのメタデータの管理もしてくれます*1。

また、「このカラムは個人情報が入っているから、チーム内でも特定の人しかクエリ叩けないようにしたい」ということ、割とあると思いますが、そういったこともData Catalogを使うと簡単にできるようになります。

もう少し構造化してメタデータを保持したい: Tag Template

descriptionに書き込むのはいいのですが、全部テキストでフラットに情報を持つのは段々厳しくなってくると思います。「最後にクエリを打たれた人をDATETIME型で」とか「クエリを叩かれた回数をINT型で」持ちたいという欲求は自然と出てくると思います。Cloud Data CagalogではそれをTagとして表現でき、Tagにどういった情報を持たせるかの雛形(この項目はSTRING型で必須、などを指定できる)をTag Templateとして表現します。

私はバッチ処理の中でTagを洗い替えするので十分なケースだったので、すでにTag Templateが存在すれば消して新規に作るようにしました。

#!/bin/bash
set -ux

TAG_TEMPLATE=resource_usage_info
LOCATION=asia-northeast1

gcloud data-catalog tag-templates describe ${TAG_TEMPLATE} --location=${LOCATION}
if [ $? -eq 0 ]; then
  gcloud data-catalog tag-templates delete ${TAG_TEMPLATE} --location=${LOCATION} --force --quiet
fi

gcloud data-catalog tag-templates create ${TAG_TEMPLATE} --location=${LOCATION} \
  --display-name="Resource usage info" \
  --field=id=last_queried_person,display-name="Last queried person",type=string \
  --field=id=queries_counts,display-name="Queries count",type=double \
  --field=id=last_queried_at,display-name="Last queried timestamp",type=timestamp

必要なメタデータをAudit logから取得する

次に考えたいのは「最後にクエリを投げられた日」や「クエリを投げられた回数」といった情報をどこから取ってきてTagにするか、です。このような情報はAudit logからBigQueryで取得できるようにしておくと簡単です。マジで10分でできる。

結構色んな情報が入ってくるので、Tagを作るときにやりやすいよう、以下のSQLをviewとして保存しておきます。名前は何でもいいですが、my_project.my_mart.tables_last_accessed_atみたいな感じで。

Audit logの情報からクエリの統計量を計算するクエリ

WITH 
  raw_resource_usage AS (
    SELECT
      protopayload_auditlog.authenticationInfo.principalEmail,
      timestamp AS last_queried_at,
      info.resource,
      ROW_NUMBER() OVER (PARTITION BY info.resource ORDER BY timestamp DESC) AS rank,
    FROM
      `my_project.source__cloudaudit__bigquery.cloudaudit_googleapis_com_data_access`
    CROSS JOIN
      UNNEST(protopayload_auditlog.authorizationInfo) AS info 
    WHERE
      NOT REGEXP_CONTAINS(info.resource, r"LOAD_TEMP_") -- Embulk関係で呼ばれるものは計算に入れない
      AND NOT REGEXP_CONTAINS(info.resource, r"jobs")
  ),

  latest_access_by_resource AS (
    SELECT
      * EXCEPT(rank)
    FROM
      raw_resource_usage
    WHERE
      rank = 1
  ),

  resource_called_counts AS (
    SELECT
      resource,
      COUNT(*) AS count
    FROM 
      raw_resource_usage
    GROUP BY
      resource
    ORDER BY
      count DESC
  ),

  existing_tables AS (
    SELECT
      project_id,
      dataset_id,
      table_id,
      FORMAT("projects/%s/datasets/%s/tables/%s", project_id, dataset_id, table_id) AS table_full_name
    FROM (
      SELECT
        *
      FROM
        `my_project`.project__source__db.__TABLES__
      UNION ALL
      SELECT
        *
      FROM
        `my_project`.project__warehouse.__TABLES__
      UNION ALL
      SELECT
        *
      FROM
        `my_project`.project__mart.__TABLES__
    )
  )

SELECT
  dataset_id,
  table_id,
  principalEmail AS last_accessed_person,
  last_queried_at,
  resource_called_counts.count AS queries_counts
FROM
  existing_tables
INNER JOIN
  latest_access_by_resource
ON
  latest_access_by_resource.resource = existing_tables.table_full_name
INNER JOIN
  resource_called_counts
ON
  resource_called_counts.resource = existing_tables.table_full_name
ORDER BY
  last_queried_at

Tagとしてメタデータを登録

必要な情報がこのviewで簡単に取得できるようになったので、実際にTagとして登録していきます。

メタデータをTagとして登録するシェルスクリプト

#!/bin/bash
set -ux

PROJECT_ID=my_project
TAG_TEMPLATE=resource_usage_info
LOCATION=asia-northeast1
MY_TABLE=${PROJECT_ID}.my_mart.tables_last_accessed_at

bq query --max_rows 10000 --format json --use_legacy_sql=false "SELECT * FROM ${MY_TABLE}" | jq -c '.[]' > resource_usage_info.json

cat resource_usage_info.json | while read -r json; do
  echo $json | jq '
  {
    "last_queried_person": .last_accessed_person,
    "queries_counts": .queries_counts | tonumber,
    "last_queried_at": .last_queried_at
  }
  ' > tag_file.json

  DATASET=$(echo $json | jq -r .dataset_id)
  TABLE=$(echo $json | jq -r .table_id)
  ENTRY_NAME=$(gcloud data-catalog entries lookup "//bigquery.googleapis.com/projects/${PROJECT_ID}/datasets/${DATASET}/tables/${TABLE}" --format="value(name)")
  TAG_NAME=$(gcloud data-catalog tags list --entry "projects/${PROJECT_ID}/locations/${LOCATION}/entryGroups/@bigquery/entries/$(echo ${ENTRY_NAME} | tr '/' '\n' | tail -n 1)" --format="value(name)")

  if [ -n "$TAG_NAME" ]; then
    gcloud data-catalog tags delete ${TAG_NAME} --quiet
  fi

  gcloud data-catalog tags create \
    --entry=${ENTRY_NAME} \
    --tag-template=${TAG_TEMPLATE} --tag-template-location=${LOCATION} --tag-file=tag_file.json --format=json
done

これで完了です。こうしておくと、Cloud Data Catalogで検索した結果、このようにメタデータを登録することができます。このリソースの最近の利用状況、より詳しいことは誰に聞けばいいかといった情報が提供できるため、利用者がやりたかった分析に少し早く、より正確に到達できる状況にできるのではないかな、と思います。

*1:自分はまだBigQueryに対してだけしか使えていないけど

カスタマーサクセスのためのデータ整備人の活動記録というタイトルでオンライン登壇しました

データ基盤 登壇

第3回 データアーキテクト（データ整備人）を”前向きに”考える会という勉強会で、CREとしてデータ基盤を整備する活動についてオンライン登壇しました。

イベント登壇はまあまあやってきたはずなんですが、今回の登壇は初めて要素が満載でした。

  • CREとして初めての登壇
    • これまでは研究者 or アプリケーションエンジニアとして登壇
    • 今年の2月にCREになったばかりなので、私がCREについて語ってもいいんかいな...みたいなところはありますよね
      • と言いつつ、偉そうに語ってしまった
  • データ基盤に関する初めての登壇
  • 初めてのオンライン登壇
    • 意図せず(?)YouTuberデビューを果してしまった...!
    • ピーク時には400人以上(?)の方が聴講してくださっていた様子

かなり手探りだったのですがTwitterの様子を見ている限り、結構好評だったようでほっとしています。データ基盤に関するアウトプットは今後も定期的にしていきたいなと思っているので、引き続き頑張っていきます。

Twitter上でのみなさんのコメント

たくさんコメントを頂いて、嬉しかったので自分の発表に関するコメントを載せておきます。

MackerelのCREチームについて

データパイプラインの整備について

データ基盤でできること / 出せる価値を感じてもらうためにやったことについて

分析者が自走できる環境 / チームで継続的にメタデータを整備できる体制作りについて