【BigQuery初心者の雑記 vol.3】Re:dash 入力値を IN 句の条件で使う〜実務の分析で使いやすくした話

株式会社オズビジョンのユッコ (@terra_yucco) です。
最近ずっと BigQuery でクエリを書く生活をしていたため、今日もその関係となります。

前提

オズビジョンでは、AWS 上に立てた MySQL Aurora や Google Analytics といったデータソースの閲覧のため、Re:dash を立てて運営しています。
最近 BigQuery がそのデータソースに仲間入りし、その桁違いの演算能力に惚れ込む人が続出。そのため、大量データを走査するような集計は BigQuery にデータを定期同期し、そのデータソースから参照するという使われ方をしています。

きっかけ

スプレッドシートも併用してデータをフィルタリグしているメンバからのフィードバックがきっかけでした。
※この元になる抽出もクエリでやれればいいのですがそこは目を瞑ってください

カンマ区切りにするだけなら

split でいいんじゃんと思ったのですが

@xxxx 使う先がわからないのですけど、replaceってので行けるかと思いますよ。

サンプル
select replace("{{ value }}", " ", ",")

redashのvalue窓には 1 2 3 を入れました。
スプレッドシートで縦に 1 2 3 って入れたものをコピペした状態です。

これだとあくまでカンマで区切られた文字列になるだけで、その後他のクエリと結合して IN 句として利用するようなことはできませんでした。

IN 句として使う方法

他の Redash クエリでもコピペ＆小改変で使えるようにしたかったので命名が雑いですが、最終的にはこれで動かすことが出来ました。

WITH
  -- {{ input1 }} の文字列に対して以下の順番で操作をして、WITH の対象にできるデータ次元に変換
  -- 1. スペース分割で配列に変換
  -- 2. 配列を展開する UNNEST() 関数を利用し SELECT で取得
  -- 3. このままでは STRING 型
  -- 4. 今回は数値と比較したいので INT64 に変換
  -- 5. data1 という名前で結合先で使えるようになる
  INPUT1 AS (
    SELECT CAST(raw1 AS INT64) AS data1
      FROM UNNEST(split("{{ input1 }}", " ")) AS raw1
  )
-- この後使いたい SELECT などのクエリをここに書く

コメントの中にも書いたように、利用するときに STRING であれば CAST は要りません。

まとめ

件数が少ない (get パラメータで渡せる範囲) のデーであれば、スプレッドシートからコピーしてきて Re:dash の変数窓に入れるだけで、クエリの条件に使うことができるようになりました。