Redash上からBigQueryスクリプトを利用してみた

この記事は Akatsuki Advent Calendar 2019 - Adventar 4日目の記事です。

こんにちは！ suwahime です。

昨今どの業界を見渡しても、アクティブユーザー数や入会数といったデータは、当たり前のように日々追っているかと思います。 私の所属するチームでは、主にRedashを用いてKPIを可視化しています。

今日は、先月BigQueryにBetaリリースされたスクリプトとプロージャ機能を使って、RedashのQuery作成を、よりシンプルに、管理しやすい形で書いてみたことをお話しさせていただきます。

RedashはQueryごとに定義が分散してしまいがち

例として、「期間内にサービスを訪れたユーザー」の利用デバイスを調べる、以下のようなQueryをRedashに作成していたとします。

SELECT B.device_name, COUNT(DISTINCT A.user_id) FROM
  (SELECT * FROM dataset.login_user_ids
    WHERE timestamp >= TIMESTAMP("{{start_time}}")
    AND timestamp <= TIMESTAMP("{{end_time}}")
    GROUP BY user_id) AS A
  LEFT JOIN
  (SELECT user_id, device_name FROM dataset.user_devices) AS B
  ON A.user_id = B.user_id
GROUP BY B.device_name

テーブルの内容等は割愛しますが、利用者はstart_timeとend_timeをRedash上のGUIから入力することで、数字が見られるというイメージです。

Redashはこのように愚直なSQLを書いても簡単にグラフ化までできる魔法のようなツールなのですが、いかんせん簡単すぎて、考えなしに様々なQueryを作成してしまい、後々の定義変更が大変だったりすることがあります。

たとえば、以下のような要求がきた場合はどうなるでしょうか。

• 期間ごとの不正なユーザーIDを検知することができたので、その一覧をKPIから削減したい。
• ログインしたユーザーではなく、サービスをプレイしたユーザーから取るように変えてほしい。

中間テーブルを作成するなど、やり方はいろいろあると思いますが、ここではまた愚直に以下のように修正してみます。

SELECT B.device_name, COUNT(DISTINCT A.user_id) FROM
(SELECT * FROM dataset.play_user_ids -- 参照するテーブルを置き換え
  WHERE timestamp >= TIMESTAMP("{{start_time}}")
  AND timestamp <= TIMESTAMP("{{end_time}}")
  AND user_id NOT IN -- 不正ユーザーIDを弾く
  (SELECT user_id FROM dataset.wrong_user_ids
    WHERE timestamp >= TIMESTAMP("{{start_time}}")
    AND timestamp <= TIMESTAMP("{{end_time}}"))
  GROUP BY user_id) AS A
LEFT JOIN
(SELECT user_id, device_name FROM dataset.user_devices) AS B
ON A.user_id = B.user_id
GROUP BY B.device_name

Redashの埋め込み記法である {{}} が増えて少々見づらくなりましたが、なんとか簡単にできました。Redashはこのフットワークの軽さが良いですね。

では、更に次のような要求が来た場合はどうでしょう？

• 「期間内にサービスを訪れたユーザー」を元に調査している他の全てのQueryについても、同様に置き換えて欲しい。

…これは少々困りました。Query数が少ないプロジェクトなどは、そんなにコストがかからず置き換え可能なのかもしれません。しかし、誰でも好き勝手にQuery作成が可能なプロジェクトにおいては、どこでこの指標を利用しているのか、検索して見ていくしかありません。

もし「期間内にサービスを訪れたユーザー」の定義が一元管理できていたら、こんな手間は無くなると思いませんか？

RedashとBigQueryスクリプトを組み合わせて使ってみる

ではここで、BigQueryスクリプトを使った解決策を試してみましょう。

まずは「期間内にサービスを訪れたユーザー」をaccess_user_idsというTEMP TABLEに吐き出すプロシージャを、以下のスクリプトをBigQuery上で実行することで登録してみます。

CREATE PROCEDURE dataset.create_access_user_ids (start_date TIMESTAMP, end_date TIMESTAMP)
BEGIN
  CREATE TEMP TABLE access_user_ids AS
  SELECT * FROM dataset.play_user_ids
    WHERE timestamp >= start_date
    AND timestamp <= end_date
    AND user_id NOT IN
    (SELECT user_id FROM dataset.wrong_user_ids
      WHERE timestamp >= start_date
      AND timestamp <= end_date)
    GROUP BY user_id;
END;

これをCALL関数で呼び出すことで、RedashのQueryは以下のように書けるようになります。

CALL dataset.create_access_user_ids(TIMESTAMP("{{start_time}}"), TIMESTAMP("{{end_time}}"));

SELECT B.device_name, COUNT(DISTINCT access_user_ids.user_id) FROM
access_user_ids
LEFT JOIN
(SELECT user_id, device_name FROM dataset.user_devices) AS B
ON access_user_ids.user_id = B.user_id
GROUP BY B.device_name;

見た目がだいぶスッキリしましたね。しかも、今後定義変更があった場合にはプロシージャ側を編集するだけで、access_user_idsを使っているすべてのQueryが同じ指標に置き換わります。

注意すべきは、1クエリではなくスクリプトになるため、区切り文字「;」が必要になることと、プロシージャ内で作られるTEMP TABLEの名前がRedashからだと隠蔽されてしまうため、命名規則などで対応する必要があることです。

RedashからBigQueryスクリプトを使うことのメリット、デメリット 

プロシージャを利用することの最大のメリットは、RedashのQueryごとに指標がバラけることなく一元管理することができることです。他にも、BigQueryからRedashにSQLをコピペしてから、時間範囲の部分だけを {{start_time}} と {{end_date}} で置き換えて…なんていう手間を省くこともできます。最終的にはプロシージャや中間テーブルだけでクエリを作成して、RedashからはCALLするだけという運用にすれば、ダッシュボードツールに縛られることのない未来もありそうです。

デメリットは、BigQueryスクリプト内で書かれたSQLに関して、処理される推定バイト数が実行前にわからないことです。想定外に検索費用がかかってしまうこともあるかもしれません。これに関しては、たとえば先にdry runを実行して処理する推定バイト数を算出し、一定以上の費用がかかりそうなら実行しない、ということができるようになればありがたいですね。（現時点では、コンソール上からdry runをすることはできないようです。）

BigQueryスクリプトで可能になることはまだまだありそうなので、今後も追ってみたいと思います。

参考

cloud.google.com

cloud.google.com

cloud.google.com