Redashで1つのカラムに保存されたJSONデータを取り扱う

Redashでデータを加工する際に、1つのカラムに含まれるJSONの中身を取り扱う必要がありました。しかし、単純にクエリを叩くだけではJSONの中身を参照できません。

そこで、Redashの機能の一つであるPython Data Sourceを利用してJSONを解析し、各データを1つのカラムで取得・加工できるようにしてみました。

準備

Python Scriptを有効化するために/opt/redash/.envを修正

export REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.python

設定を反映

$ sudo supervisorctl restart all

Data Sourceを追加

f:id:lcl-engineer:20171231005613p:plain

以上でPython Data Sourceを選択できるようになります。

使い方

Python Data Sourceでは以下の記述でデータの取得・表示を行います。

データの取得

Redashで既に定義済みのクエリを利用する場合

例: https://localhost/queries/123

get_query_result(123)

直接クエリを叩く場合

query='select * from queries'
execute_query('<DATA_SOURCE_NAME>', query)

行の定義

add_result_row(result, {
  '<COLUMN_NAME_1>': '値', 
  '<COLUMN_NAME_2>': '値'
})

列の定義

add_result_column(result, 'COLUMN_NAME_1', '', 'string')
add_result_column(result, 'COLUMN_NAME_2', '', 'integer')

JSONの解析

それでは、本題のJSONの解析をします。

今回は例として予約時の日付とブラウザの種類を取得します。

元データ

ID	ACTION	DETAIL
1	book	{"date": "20180124", "browser": "safari"}
2	book	{"date": "20180201", "browser": "webview"}

クエリ

Data Sourceに「Python」を選択します。

f:id:lcl-engineer:20171231005601p:plain

下記のように記述にして実行します。

import json

# 既に定義済みのクエリの結果を参照
q_res = get_query_result(123)

result = {}
for row in q_res["rows"]:
    detail = json.loads(row["detail"])
    
    add_result_row(result, {
        "id": row["id"],
        "action": row["action"],
        "date": detail["date"],
        "browser": detail["browser"]
    })

add_result_column(result, 'id', '', 'integer')
add_result_column(result, 'action', '', 'string')
add_result_column(result, 'date', '', 'string')
add_result_column(result, 'browser', '', 'string')

結果

ID	ACTION	DATE	BROWSER
1	book	20180124	safari
2	book	20180201	webview

（おまけ）割合を計算

上記ではJSONを解析しましたが、Pythonを書いてデータの加工も可能です。

from collections import Counter

q_res = get_query_result(123)

result = {}
browserArr = []

for row in q_res["rows"]:
    browserArr.append(row["browser"])

counter = Counter(browserArr)
for key, value in counter.most_common():
    add_result_row(result, {
        "browser": key,
        "total": value,
        "割合": str(float(value) / len(browserArr) * 100) + "%"
    })

add_result_column(result, 'browser', '', 'string')
add_result_column(result, 'total', '', 'integer')
add_result_column(result, '割合', '', 'string')