Zenn
📄

Redashのテキストパラメーターをハックする

2022/02/21に公開
SQL
presto
Redash
tech

redashを使うとビジネスサイドの人たちでもクエリを書くことなしに簡単にデータ分析ができるようになります。しかし、redashのパラメーター指定でできることは限られています。そのため、複雑な条件を指定したい場合はクエリを変更しなくてはいけなくなり、ビジネスサイドのみでデータ分析を完結させることは難しくなってしまいます。
じゃあ結局はビジネスサイドの人たちがクエリを書かなきゃいけないのかというとそういうわけでもなく、テキストパラメーターに入力された文字列をSQL（UDF含む）を使ってparseすることで、クエリの変更をせずに複雑な条件を外部から指定することが可能です。今回はPrestoを使った事例をご紹介します。

動的に変化する条件

例えば、１週間ごとに商品を変えてキャンペーンを行い、キャンペーンごとの購買結果を一覧でみたいとします。この場合複数の商品IDと計測期間の組み合わせが必要になります。

redashでは上の画像のように商品IDと期間を指定するパラメーターを商品数だけ用意する必要があります。仮に４つ目の商品を追加で分析したい場合はパラメーターを追加してクエリを書き直す必要があります。このように動的に検索条件が変わる場合、redashの既存のパラメーター機能では対応できません。

テキストパラメーターを使う

redashにはテキストを扱えるパラメーターがあります。こちらであれば動的に条件を渡すことができます。例えば下のような文字列を渡します。

商品ID=1,期間=2022/01/01~2022/01/07|
商品ID=2,期間=2022/01/08~2022/01/14|
商品ID=3,期間=2022/01/15~2022/01/21

商品IDと期間というワードが存在し、=によってそれぞれの値が渡されます。また商品ごとの区切り文字に|を使っています。このような文字列であれば商品と期間の組み合わせを自由に追加削除することが可能です。この文字列は改行を含んでいますがredashのテキストパラメーターに渡すと改行は半角スペースに置き換わるので、最終的には商品ID=1,期間=2022/01/01~2022/01/07| 商品ID=2,期間=2022/01/08~2022/01/14| 商品ID=3,期間=2022/01/15~2022/01/21という文字列がパラメーターとして渡されます。

テキストをparseする

それではこのテキストをparseしていきます。
まずは|でテキストを分割して縦持ちにし、余分な空白は削除します。

WITH a1 AS(
  SELECT 
    TRIM(text) AS t1
  FROM
    unnest(
      REGEXP_SPLIT(
        TRIM('商品ID=1,期間=2022/01/01~2022/01/07| 商品ID=2,期間=2022/01/08~2022/01/14| 商品ID=3,期間=2022/01/15~2022/01/21'),
        '\|'
      )
    ) AS t(text)
)

a1は以下のようになります。

t1
商品ID=1,期間=2022/01/01~2022/01/07
商品ID=2,期間=2022/01/08~2022/01/14
商品ID=3,期間=2022/01/15~2022/01/21

次に,で分割して配列にします。

a2 AS(
  SELECT 
    REGEXP_SPLIT(
      t1,
      ','
    ) AS t2
  FROM
    a1
)

a2は以下のようになります。

t2
["商品ID=1","期間=2022/01/01~2022/01/07"]
["商品ID=2","期間=2022/01/08~2022/01/14"]
["商品ID=3","期間=2022/01/15~2022/01/21"]

次に「商品ID」や「期間」という文字列が存在する配列の要素をフィルターにかけて取り出す操作をして商品IDと期間を横持ちにします。

a3 AS(
  SELECT 
    ELEMENT_AT(
      FILTER(
        t2,
        x -> x LIKE '商品ID%'
      ),
      1
    ) AS item_id,
    ELEMENT_AT(
      FILTER(
        t2,
        x -> x LIKE '期間%'
      ),
      1
    ) AS date_range
  FROM
    a2
)

a3は以下のようになります。

item_id date_range
商品ID=1 期間=2022/01/01~2022/01/07
商品ID=2 期間=2022/01/08~2022/01/14
商品ID=3 期間=2022/01/15~2022/01/21

次に商品ID=と期間=という文字列を取り除きます。

a4 AS(
  SELECT 
    REPLACE(item_id,
      '商品ID=',
      '') AS item_id,
    REPLACE(date_range,
      '期間=',
      '') AS date_range
  FROM
    a3
)

a4は以下のようになります。

item_id date_range
1 2022/01/01~2022/01/07
2 2022/01/08~2022/01/14
3 2022/01/15~2022/01/21

次にdate_rangeを~で分割し、期間の開始日と終了日を配列に格納します。

a5 AS(
  SELECT 
    item_id,
    REGEXP_SPLIT(
      date_range,
      '~'
    ) AS date_range_array
  FROM
    a4
)

a5は以下のようになります。

item_id date_range_array
1 ["2022/01/01","2022/01/07"]
2 ["2022/01/08","2022/01/14"]
3 ["2022/01/15","2022/01/21"]

最後にdate_range_arrayから要素を取り出し、開始日と終了日を横持ちにします。

a6 AS(
  SELECT 
    item_id,
    ELEMENT_AT(
      date_range_array,
      1
    ) AS date_start,
    ELEMENT_AT(
      date_range_array,
      2
    ) AS date_end
  FROM
    a5
)

a6は以下のようになります。

item_id date_start date_end
1 2022/01/01 2022/01/07
2 2022/01/08 2022/01/14
3 2022/01/15 2022/01/21

最終的に

商品ID=1,期間=2022/01/01~2022/01/07|
商品ID=2,期間=2022/01/08~2022/01/14|
商品ID=3,期間=2022/01/15~2022/01/21

という文字列を

item_id date_start date_end
1 2022/01/01 2022/01/07
2 2022/01/08 2022/01/14
3 2022/01/15 2022/01/21

というテーブルに変換できました。

あとはこのテーブルを抽出したいテーブルにINNER JOINして抽出条件を絞り込むだけです。

SELECT 
  id.item_id,
  id.date,
  id.purchase
FROM
  item_data id INNER
JOIN
  a6
  ON id.item_id = a6.item_id
  AND a6.date_start <= id.date
  AND id.date <= a6.date_end

まとめ

redashのテキストパラメーターを使いこなせばクエリの変更なしで柔軟に抽出条件を変更することができます。いろんなことができるので興味がある方は是非試してみてください。

Discussion