RedashでBigQueryとRedshiftのクエリ結果を組み合わせて使いたいときはないでしょうか。
RedashではQuery Results (Alpha) Data Source という機能もあるのですが、 OSS版では2017/4/11時点ではまだ提供されていないようです。
そこでPython pandasを使い、複数のクエリ結果を組み合わせてデータの可視化をしてみます。
以下、PythonのData Sourceを選択しているものとします。
redash編
他のクエリ結果を取得する
<クエリID>はクエリのURL末尾の数字です。
get_query_result で他のクエリで作成した結果を取得できます
get_query_result(<クエリID>)['rows']
結果を出力する
カラムを設定する
add_result_column でカラムを設定できます
result = {}
add_result_column(result, 'date', '', 'date')
add_result_column(result, 'hoge_count', '', 'integer')
add_result_column(result, 'piyo_count', '', 'integer')
データをつめる
add_result_row でデータを詰めて行くことが出来ます。
add_result_row(result, { hoge: 'ほげほげー' }
pandas編
別にpandasは必須ではないですが、結果をマージしたりグルーピングしてカウントしたいとかだと使った方が良さそうです。
DataFrameにデータをつめる
pandasではDataFrameというのに値を詰めて色々とゴニョるらしい
import pandas as pd
rows = get_query_result(<クエリID>)['rows']
df = pd.DataFrame(rows)
クエリの結果をマージする
同じ user_id というカラムを持つクエリ結果を取得してマージしたいときは以下のようにすればよい。
left joinするとき、値にNanがあると集計処理とかでpandasがエラーを吐いたりして辛いので、 fillna すると良さそうです。
a_rows = get_query_result(<クエリID>)['rows']
b_rows = get_query_result(<クエリID>)['rows']
# inner join
pd.merge(a_rows, b_rows)
# left join
pd.merge(a_rows, b_rows, how='left')
# left join (Nanになる箇所を0で穴埋め)
pd.merge(a_rows, b_rows, how='left').fillna(0)
条件に一致する結果のみ取得する
df[df['hoge_flag'] == True]
XX別に集計してDataFrameにする
Jupyter Notebook とかでやるならこんなことする必要あんまりないと思うのですが、最終的にRedashで出力することを考えると、 groupby count とかしたあとにもう一度DataFrame形式にしたほうが色々と都合がよいです。カウントした結果を以下のようなDataFrame形式で再度扱いたいときは、こうします。
| user_id | count |
|---|---|
| 1 | 50 |
| 3 | 24 |
| 5 | 13 |
pd.DataFrame({'count': df.groupby('user_id').size()}).reset_index()
# 日付別集計とか
pd.DataFrame({'count': df.groupby(pd.to_datetime(df['created_at']).dt.strftime('%Y-%m-%d')).size()}).reset_index()
DataFrameから一行ずつ取り出してデータに詰める
df.iterrows() とすると for index, row in... の形式で一行ずつ中身を add_result_row にしていくことができます
for index, row in df.iterrows():
add_result_row(result,
{
'date': row['created_at'],
'hoge_count': row['hoge_count'],
'piyo_count': row['piyo_count'],
})
これで後は普通にSQLを書いたときと同じくグラフ化したりすることが可能です。