inFablic | Fablic, inc. Developer's Blog.

フリマアプリ フリル (FRIL) を運営する Fablic の公式開発者ブログです。Fablic のデザイナー・エンジニア・ディレクターが情報発信していきます。

RedashでMySQLとBigQueryを組み合わせたデータ分析を行う(Python DataSource)

f:id:shoby:20170823170543p:plain

こんにちは。shobyです。

現在フリルでは、Redashを分析基盤として、CM効果検証などの様々な分析に利用しています。

今回は、Redashを使い、MySQL+BigQueryなど、複数のDataSourceを組み合わせた分析を行う方法をご紹介します。

※以下の情報は、Redash 1.0.3を想定しています。

概要

  • フリルの分析におけるMySQLとBigQueryの使い分け
  • RedashのPython DataSource
  • Python DataSourceの準備
  • 実行済みクエリの結果を組み合わせる
  • 複数のクエリを実行して組み合わせる
  • Python DataSourceのデバッグについて

フリルの分析におけるMySQLとBigQueryの使い分け

フリルでは現在、Redashを分析基盤とし、主にMySQL*1とBigQueryを用いたデータ分析をしています。

redash.io

商品やユーザー、取引に関する分析はMySQL、サーバーログやアプリのイベントログを用いた分析はBigQueryを利用しています。

ここで問題となるのが、サーバーログをユーザーデータとを組み合わせて分析したい場合など、MySQLとBigQueryを組み合わせた分析が必要なケースです。

フリルでは、以前まではRedashの実行結果をSpreadSheetにimportした上で分析を行なっていましたが、大量のデータに対する関数の多用によって分析用のシートが重くなり、開くだけでも苦労するような場合も発生してきました。

そのため、最近ではRedashでPython DataSourceという機能を使い、Redashのみで分析を行うことも増えてきました。

RedashのPython DataSource

Python DataSourceという仕組みを使うことで、Redash上でPythonのコードを実行できるようになります。 Python DataSourceでは主に以下のようなことができます。

  • 実行済みクエリの結果を取得
  • 複数のDataSourceに対するクエリの実行
  • pandasなどのPython Libraryの利用

これらの機能を活用することで、複数のDataSourceに対してクエリを実行し、データ加工をする、といった複雑な集計をすることができます。

Python DataSourceの準備

Python DataSourceはセキュリティのため、デフォルトでは有効になっていません。

そのため、 /opt/redash/.envREDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.python を追記する必要があります。

また、moduleも管理画面で管理者が指定した物しかimportできない仕組みになっています。 許可するmoduleによってはセキュリティのリスクがあることを理解した上で、安全なmoduleだけを利用可能にするようにしましょう。

f:id:shoby:20170823121922p:plain

詳しくはこちらをご覧ください。

Data Sources Options and Requirements · Redash Help Center

実行済みクエリの結果を組み合わせる

Redashはクエリの実行結果をキャッシュしてくれますが、Python DataSourceを用いることで、複数の実行済みクエリの結果を取得し、組み合わせることできます。

Python DataSourceでは、 get_query_result というfunctionにRedash上のQuery IDを渡すことで、実行済みクエリの結果を取得することができます。

詳しくはこちらの公式sampleをご覧ください。

re:dash Python datasource join example · GitHub

MySQLからユーザー情報、BigQueryからアクセスログを取得し、Pythonでjoinするケースを考えると、実際のクエリは以下のようになります。

ユーザー情報の取得(MySQL)

/* Query ID: 1 */
SELECT
  id AS user_id,
  user_info
FROM
  users
WHERE ...

アクセスログの取得(BigQuery)

/* Query ID: 2 */
SELECT
  DATE(date) AS access_date,
  user_id
FROM
  access_log
WHERE ...

クエリを組み合わせる(Python DataSource)

import pandas as pd

# 実行済みクエリの結果を取得
user_info_query_result = get_query_result(1)
access_log_query_result = get_query_result(2)

user_info = pd.DataFrame(user_query_result['rows'])
access_log = pd.DataFrame(access_log_query_result['rows'])

# pandasを使ってjoin
merged_result = pd.merge(user_info, access_log, on='user_id')

# 結果の出力
result = {}

for index, row in merged_result.iterrows():
    add_result_row(result, {'access_date': row['access_date'], 'user_id': row['user_id'], 'user_info': row['user_info']})

add_result_column(result, 'access_date', '', 'string')
add_result_column(result, 'user_id', '', 'integer')
add_result_column(result, 'user_info', '', 'string')

以上のようなPythonコードを書くことによって、異なるDataSourceでの実行結果をjoinすることができます。

結果の出力には add_result_row 及び、 add_result_column の呼び出しが必要になりますのでご注意ください。

複数のクエリを実行して組み合わせる

実行済みクエリを組み合わせる方法でもMySQLとBigQueryを組み合わせた分析を行うことはできますが、Dashboard等の用途でクエリを定期実行したい場合には向いていません。

関連する全てのクエリの定期実行が必要であり、Python DataSourceから参照するクエリは先に実行しておく、などクエリの実行順序も考える必要があるためです。

これに対しては、 execute_query というメソッドを使い、直接Python DataSource上でクエリを実行することで解決ができます。

execute_query の仕様に関してはRedashのソースコードをご覧ください。

github.com

同様に、MySQLからユーザー情報、BigQueryからアクセスログを取得し、Pythonでjoinするケースを考えると、実際のクエリは以下のようになります。

import pandas as pd

# 異なるDataSourceに対してクエリを実行
user_info_query = 'SELECT ...'
access_log_query = 'SELECT ...'

user_info_query_result = execute_query('MySQL', user_info_query) # DataSourceのIDまたは名前を指定する
access_log_query_result = execute_query('BigQuery', access_log_query)

user_info = pd.DataFrame(user_info_query_result['rows'])
access_log = pd.DataFrame(access_log_query_result['rows'])

# pandasを使ってjoin
merged_result = pd.merge(user_info, access_log_data, on='user_id')

# 結果の出力
# (以下略)

以上のようなコードを書くことで、Redash上では1つのPython DataSource Queryを定期実行するだけで済むようになります。

Python DataSourceのデバッグについて

Python DataSource上では、ログ出力が有効になっており、printした結果がそのまま表示されるようになっています。

そのため、pandasのDataFrameを適宜printしていくことで、分析用のコードのデバッグをすることができます。

Python DataSourceの導入による効果

Python DataSourceを利用することにより、複雑であり手動での管理が必要だった一部のSpread Sheetを、Redashの結果をimportするだけの簡単な仕組みに変えることができました。

また、以下の記事のような複雑なCM効果分析の大部分を、Redash上だけで行うことができるようになりました。

in.fablic.co.jp

まとめ

RedashのPython DataSourceを利用することにより、MySQL+BigQueryなど、複数のDataSourceを組み合わせて分析を行うことができます。 また、pandasといったPython製のLibraryを使用した柔軟なデータ加工も行うこともできます。

複数の実行済みクエリの結果を参照したり、異なるDataSouceに対してクエリを発行することもできます。

フリルでは、Python DataSourceを活用することで、手動管理が必要で複雑化していたSpreadSheetを簡易化でき、CM効果分析といった複雑な分析もRedash上で行うことができるようになりました。

*1:実際フリルで使っているのはAmazon Auroraですが、説明の簡易化のためにMySQLと書きました。MySQLと互換性があるため、ほぼ違いを意識せず分析に用いることができています。