Colaboratoryを使ったSQLレビューのすヽめ

この記事は Opt Technologies Advent Calendar 2019 の 10日目の記事です。

#はじめに

みなさんSQLレビューってどうされてますか？
(ここでいうSQLは分析用だったりELT用のSQLを指します）

SQLレビューが辛いという声は割と多く聞くような気がしてます。
僕自身も人の書いた長いSQLを見るとウッとなります。
プログラミングコードを見てこうはならないのですがなぜSQLレビューは辛いのでしょうか。

なぜSQLレビューは辛いか

僕が思うに以下の3つの理由がある気がします。
① 構文(関数)がよくわからない
② 途中処理でデータがどういう状態になっているか分からない
③ そもそも扱うデータの性質が分からない

①に関しては覚えてください、としかいうほかありあせん。。
③に関しては難しい問題ですが、本来であればレビュワーとレビュイーが同程度の知識があること(どちらも詳しい状態)が望ましいです。が、そうならないパターンも多いのでレビュイー、ないしレビュワーがレビューを通して知識がつくといいですね

②に関してはレビュワーが調べるのは手間ですし、レビュイーがある程度エビデンスを示すことが親切なのではと考えました。

Google Coloaboratoryを活用したSQLレビュー

そこで思い立ったのがGoogle Colaboratoryを使ったSQLレビューです。
(https://colab.research.google.com/notebooks/welcome.ipynb?hl=ja)
Colaboratoryとはクラウド環境に構築されたjupytor notebookです。
スプレッドシートやgoogleドキュメントみたいな感覚で扱えます。もちろん無料です。

Colaboratoryレビューのメリット・デメリット

Colaboratoryを使ったレビューにはいい面も悪い面もあります。
メリデメを書くと以下のようになるでしょう。

• メリット
• 途中処理を可視化しながらコードを眺められる
• テキスト(マークダウンとか)が書けるのでコード内のコメントで説明をする必要がなく見やすい
• 当然ながらpythonが書けるので結果の統計情報の取得や可視化などができる
• デメリット
• コメントができない(厳密にはできるがgitのように見やすくはない)
• 途中処理の可視化を1個ずつやらないといけないので手間
• pythonで何かをするとき、データが大きいとメモリ不足でできない(ここは本質的な問題ではないが)
  　

全てのケースにおいてColaboratoryでのレビューが適すかというとそうではないと思います。
以下のようなケースにおいてはColaboratoryレビューが有効でしょう。

• with句が大量に出てくる時
• window関数を使いまくって状態がいまいちわからない時
• SQLレビューする人がいないが説明責任を果たさないといけない時 (稀かもしれませんが僕はそこそこ遭遇してしまいました)

またレビューのみならず、仕様の伝達 にも使えるのでうまくコメントを書きながらナレッジの共有ができるとなおいいですね。

例

※ SQLはかけるがプログラミングがかけない人も実践できるようにしたいので可能な限りpythonコードは書きません。(python使った方が効率がいい場面もありますが無視します)

お題

アプリのインストール日からユーザーが何日連続で起動しているかを出す
※以下のコードは全てBigqueryを使用する前提です。

テーブル

• id
  • ユーザー識別子
• event
  • install/sessionが入る
• event_date
  • eventが発生した日付が入る

お題の詳細

• id単位でインストールから何日連続して起動しているか出す
  • 例えば idがaの人は1/1にインストールして1/1,1/2,1/4と起動していますが、連続起動は1/2で途絶えているので
    連続起動日は 1
• もしインストール後1回も起動していない or インストール後の初回起動が2日以上空いていた場合、連続起動日は 0

結果① SQLのみの場合

with
-- サンプルデータ
table as (
select * from unnest([struct
("a" as id, "install" as event, DATE("2019-01-01") as event_date),
("a","session","2019-01-01"),
("a","session","2019-01-02"),
("a","session","2019-01-04"),
("b","install","2019-01-03"),
("b","session","2019-01-08"),
("c","install","2019-01-02"),
("c","session","2019-01-03"),
("c","session","2019-01-04"),
("c","session","2019-01-04"),
("c","session","2019-01-06"),
("d","install","2019-01-07"),
("d","session","2019-01-08"),
("d","session","2019-01-09"),
("d","session","2019-01-10"),
("d","session","2019-01-14"),
("e","install","2019-01-01"),
("e","session","2019-01-02"),
("e","session","2019-01-03"),
("e","session","2019-01-04"),
("e","session","2019-01-05"),
("e","session","2019-01-06"),
("e","session","2019-01-07"),
("e","session","2019-01-09"),
("f","install","2019-01-03"),
("f","session","2019-01-03"),
("f","session","2019-01-05"),
("g","install","2019-01-01")
])
)

-- idごとにインストール日を取得する
,get_install_date as (
select 
  id,
  event_date as install_date
from  
  table
where
  event = "install"
)
/*
idごとにインストール日をとセッション日の差を出す
install_to_session_day　はインストール後何日後に起動しているかを表す
※ インストールイベント時とインストール日も出るが問題なし
*/
,get_diff_day as (
select distinct
  id,
  event_date,
  install_date,
  date_diff(event_date, install_date , DAY) install_to_session_day
from
  table 
  inner join get_install_date using (id)
 
 )
 
 /*
 install_to_session_day 　と  次のinstall_to_session_dayの差分を取得する(=dif)
 これが2以上であれば連続起動が途切れたと言える
 */
 ,get_retention_dif as (
 select 
  id,
  event_date,
  install_to_session_day,
  lead(install_to_session_day) over (partition by id order by event_date) next_install_to_session_day,
  lead(install_to_session_day) over (partition by id order by event_date)  - install_to_session_day as dif
 from
  get_diff_day
)

/*
  idごとにdifの最初の値を取得する。
  もしこれが1以外だった場合フラグを立てる。
 のちの処理でそのユーザーはリテンションの計算上は0とする
*/
, get_no_ret_flg as(
select distinct 
  id,
  if(first_value(dif) over(partition by id order by event_date) !=1 ,true,false) as no_ret_flg 
 from 
  get_retention_dif
)

/*
difが1より大の時の初回のinstall_to_session_dayを取得する。
これがそのユーザーがインストールしてからの連続起動日を表す。
もしフラグが立っていればそのユーザーは当日離脱とみなすため0にする
*/
select distinct 
  id,
  if(no_ret_flg,0,first_value(install_to_session_day) over(partition by id order by event_date)) as retention_day
from
  get_retention_dif 
  inner join get_no_ret_flg using (id)
where 
  dif>1 or dif is null
 order by 1

結果

いかがでしょうか。
この記述量ならさして問題ないように感じますが、window関数を使ったりと
ちょっと込み入った作業が入ると把握しづらいような気がしなくもありません。

結果② Colaboratoryを使った場合。

全画面スクショが取れなかったので閲覧権限のみのcolabをベタ張り
https://colab.research.google.com/drive/1sTIg43CtO4QNG298hRKnY7vq835Fxl16

ColabからBigqueryを叩くのにまず下記のコードを実行して認証を通します。

from google.colab import auth
auth.authenticate_user()

あとは下記のように書いたのちSQL文を書けば実行されます

%%bigquery --project プロジェクト名 [dataframe名]
SQL

Colabレビューのいいところは何と言っても途中のviewが可視化できる点です。
そもそもサブクエリを使うとネストが深くなるので基本はviewを作ってオブジェクト指向っぽくSQLを組み立てていきたいのですが、viewの数が多くなると状態がよくわからなくなります。
なので途中途中でviewを可視化させると、いちいちコメントアウトしてSQL叩いて結果見て・・・みたいな作業が無くなりますし、レビュワーも状態がわかるのでレビューがしやすくなります。(この行為を中断の書 と勝手に呼んでいます)
ただご覧の通り、一回ごとに必要なところまでのSQLを実行しているので見た目は長くなりがちです。
(今回はSQLしかかけない人も使えるという想定の元書いてますが、python使いならもっとすっきり書けると思います。)

またちょっと応用ですが結果をDataFlameとして扱えるので要約統計量や可視化をして異常値がないかを確認することができます。
(これに何度か救われたことがあります)

#まとめ
SQLレビューはつらみが多いです。
今回の例はviewの数がかなり少ないですが、実際の現場ではviewが10個,20個できるのはよくあることなのでそういったときに威力を発揮するかなも思います。
そもそも正解データがない場合がほとんどなので途中の処理を見ながらこまめに確認することが
結果の確からしさの精度をあげる方法の1つになりうるでしょう。

あとは自分が過去に書いたSQLをみると何やってるかわからん。。。ということが僕はあるのでそういうときに説明が書いてあるとすぐに思い出せますね笑