pandashでRedashのAPIが返す結果をPandasのDataFrameとして取得する
今日はRedash Advent Calendar 2017 17日目ですね。
上記の記事ではRedashのAPIとPandasを組み合わせる例が紹介されていますが、私も似たようなことをやっているので、この記事では私が実務で使っている方法についても紹介してみます。
事前準備
Redash Advent Calendarでは定番になりつつある以下のリポジトリを使用して、Redashが利用できるようになっていることを前提にします。
クエリID1として、以下のクエリを登録しておきました。
SELECT * FROM city WHERE CountryCode = '{{CountryCode}}' ORDER BY Population DESC;
また、RedashユーザーのAPIキーが必要になりますが、その手順については、先程も紹介した記事に記載がありますので、そちらを参照してください。
RedashのAPI
業務でもヘビーにRedashを使っているのですが、Redashの /api/queries を例に取ると、アクセスはブラウザからでなくスクリプトからのアクセスが7割を超えています。
Redashのキラーフィーチャーは各社の導入目的によって異なるものだというのをAdvent Calendarに参加して強く感じていますが、私は以下の特徴についてRedashに大きな魅力を感じています。
- 様々なデータソースに対応している
- Webアプリとして提供されるためアプリをインストールする必要がなく、誰でもすぐに使い始められる
- フォーク機能やクエリパラメータを使用して、エンジニアでなくてもある程度クエリをカスタマイズできる
- APIが提供されているため、外部スクリプトでクエリ結果を使用した処理が作れる
あえて太字にしましたが、特にAPIが提供されているところが、私たちの生産性を大きく押し上げています。
私の場合、APIがなかったらここまでRedashにどっぷりハマることもなかったでしょう。
さらにRedashのAPI、特にクエリ実行をクエリパラメータつきで実行することができる、RedashDynamicQueryがPythonのモジュールとして使用できるので、Redashの便利な機能を気軽に使うことができます。
余談ですが、RedashDynamicQueryを見つけるまでは、APIではなくスクレイピングでCSVダウンロードしていたこともあります。
RedashとPandas
さて、ここまではRedashとRedashDynamicQueryの紹介でしたが、Pythonでデータを扱うとなると、Pandasも関わりの強いモジュールといえます。
Python Data Analysis Library — pandas: Python Data Analysis Library
先程紹介したAdvent Calendarの記事でも、APIの結果をPandasのDataFrameにしてJupyter Notebookで扱うといった例になっていました。
Pandasはデータ操作の機能が便利なのはもちろんのこと、scikit-learnなどの機械学習ライブラリとの相性もいいので、どんなデータでもDataFrameに持ち込んで、あとはPandasで自由にデータ操作できるのがとても便利です。
私の場合、現時点ではRedashのクエリ結果をJOINすることが必要になった場合、RedashのPythonデータソースは使わずにAPIでクエリ結果を取得し、Pandas上で結合するようにしています。
余談ですが、PandasやDataFrameについて、個人的にはRebuildの以下のエピソードの中で出てきた、「DataFrame is JSON for Data Science」という表現がしっくりきました。
DataFrame is JSON for Data Science ですよ！ R 発祥だけど Julia にあるし、Scala だと Spark になるし、など。 #rebuildfm
— Hajime Morita (@omo2009) 2017年1月8日
とにかくDataFrameにしてしまえばあとはどうにでもなる感じがしています。
pandash を使ってRedashのAPIの結果をPandasのDataFrameにする
では、実際にRedashのクエリ結果をPandasのDataFrameとして取得する例を紹介します。
Pandasだけでもそれほど難しい処理にはなりませんが、私は自作の pandash というモジュールを使っています。
前フリがかなり長くなりましたが、このモジュールを使ってRedashの結果を取得してみます。
インストール
環境に合わせて virtualenv や venv を使っていただくとして、モジュールはREADMEの通り以下のコマンドでインストールできます。
pandash は今のところPyPIには公開していません。
$ pip install redash-dynamic-query $ pip install git+https://github.com/ariarijp/pandash
サンプルコード
以下のコードを main.py として保存します。
from redash_dynamic_query import RedashDynamicQuery from pandash import query_to_df redash = RedashDynamicQuery(endpoint='http://localhost', apikey='REDASHのユーザーAPIキー', data_source_id=1, max_wait=60) df = query_to_df(redash, 1, {'CountryCode': 'JPN'}) print(df.head(10)[['District', 'Name', 'Population']])
このコードでは、Redash上のクエリID1をクエリパラメータを指定して実行し、結果を上位10件の都道府県名、都市名、人口を表示しています。
これを実行してみると以下のような結果が表示されます。
$ python main.py
District Name Population
0 Tokyo-to Tokyo 7980230
1 Kanagawa Jokohama [Yokohama] 3339594
2 Osaka Osaka 2595674
3 Aichi Nagoya 2154376
4 Hokkaido Sapporo 1790886
5 Kyoto Kioto 1461974
6 Hyogo Kobe 1425139
7 Fukuoka Fukuoka 1308379
8 Kanagawa Kawasaki 1217359
9 Hiroshima Hiroshima 1119117
とてもシンプルなコードでRedashのクエリ結果をPandasのDataFrameにすることができました。
まとめ
Redashのクエリ結果をPandasのDataFrameとして扱えるようになると、データをPandasの統一されたインターフェースで操作できるため、クエリ結果にひと手間かけたい、別の処理に渡す前の前処理をしたいなど、データ活用の幅が広がると思います。
よろしければRedash活用の際にRedashDynamicQueryやPandasとあわせて、 pandash も検討していただけるとうれしいです。
SchemaSpyを使ってRedashのテーブル定義確認を捗らせる
Redashのテーブル定義を確認しようとしたとき、 psql コマンドでも十分だけど、何か便利なツールを探してみたらSchemaSpyがよさそうだったのでメモ。
SchemaSpy • Database Documentation Built Easy.
ツール自体はJavaで書かれているようだけど、Dockerイメージが公開されているので以下の記事を参考にしてDocker上で動作させた。
使ってみる
Redashの検証環境を構築するときはDocker Composeを使っているので、Redashの docker-compose.production.yml を以下のように変更した。
diff --git a/docker-compose.production.yml b/docker-compose.production.yml index f0b9812d..d4911ae0 100644 --- a/docker-compose.production.yml +++ b/docker-compose.production.yml @@ -50,3 +50,10 @@ services: links: - server:redash restart: always + schemaspy: + depends_on: + - postgres + image: schemaspy/schemaspy:snapshot + command: /entrypoint.sh -t pgsql -host postgres:5432 -db postgres -u postgres -hq + volumes: + - $PWD/schemaspy/output:/output
これでRedashを起動すると schemaspy/output ディレクトリーに結果が出力される。
index.html を開くと、テーブルの定義やER図が表示されて便利。
画面サンプル
テーブル一覧
テーブル定義
ER図
まとめ
DockerイメージになっているとJREをインストールしたりしなくて良いので楽。
もちろんRedash以外でも使えるツールなので、テーブル定義をさっと確認したいときは便利だと思う。
redashmanを使ってRedashのクエリをお手軽にバックアップする
今日はRedash Advent Calendar 16日目ですが、この記事はAdvent Calendarの記事ではありません。
12/16の記事はvankobeさんによる、Redashの結果をGoogleスプレッドシートで扱う事例の記事です。
Redashの便利さと、その裏にあるつらみ
Redashを活用しはじめて1年半ほど経ちますが、今ではRedashなしで業務が回らなくなるほどに使い倒しています。
しかし、Redashの活用が進んだことによって「Redashが落ちたら全てが止まる」といった状況になりつつあることも事実で、たまにRedashの収容サーバーからアラートが飛んでくると、背筋が凍る思いをしています。
それだけでなく、Redashの利点でもある「誰でもクエリを実行・編集できる」というのが、運用上問題になることも何度か経験してきました。
この記事ではRedashのクエリのバックアップについて、自作のツール redashman の宣伝もしつつ紹介していきます。
Redashのバックアップ
ざっくりですが、すぐ思いつくバックアップ方法は以下の2つと考えています。
- Redashインスタンスのディスクイメージのスナップショットを取得する
- Redashの管理DB(PostgreSQL)のDBバックアップを取得する
構成にもよりますが、ディスクイメージのバックアップはIaaSを使っていれば、もっとも簡単かつ安心できる方法かと思います。
管理DBのバックアップは、バックアップスクリプトや保存先を用意する必要がありますが、PostgreSQLについて少し知識があれば簡単に実現できる方法と言えます。
上記のように、ディスクやデータベースの単位でバックアップをとっておくことは運用上重要なことですが、「クエリ」のバックアップについても考えてみます。
Redashは誰でも気軽にクエリを実行・編集することができる一方で、悪意の有無に関わらず、誤ってクエリを編集してしまい、クエリが実行できなくなってしまうことが考えられます。
しかし、先に紹介したディスクやデータベースによるバックアップは「クエリ」のバックアップという点で考えるとリストアの手間もあり、少し大げさに感じる部分があります。
redashman について
現在、私は redashman という自作のツールを用いて、Redashのクエリを定期的にバックアップしています。
Redashを管理するので redashman という名前にしましたが、なぜか青いロボットが針に当たってティウンティウンする光景が目に浮かんだかたは個別にご連絡ください。私はX4が好きです。
このツールはクエリの一覧、取得、作成などをCLIから実行するためのツールとして作成しましたが、その中にクエリの定義を取得するものがあるので、それを使ってクエリをバックアップしています。
インストール
redashman はGoで書いたので、go get で簡単にインストールできます。
$ go get -u github.com/ariarijp/redashman
redashman を使用したクエリのお手軽バックアップ
redashman 自体の使い方についてはREADMEを参照していただくとして、付属のPythonスクリプトを使用してバックアップをしてみます。
$ mkdir redash_backup $ cd redash_backup $ cp $GOPATH/src/github.com/ariarijp/redashman/scripts/backup_queries.py ./ $ export REDASH_URL=http://localhost $ export REDASH_API_KEY="Redashのadmin権限があるユーザーのAPIキー" $ python ./backup_queries.py $ ls -la backup total 48 drwxr-xr-x 8 ariarijp wheel 272 12 16 14:34 . drwxr-xr-x 4 ariarijp wheel 136 12 16 14:34 .. -rw-r--r-- 1 ariarijp wheel 23 12 16 14:34 0001.sql -rw-r--r-- 1 ariarijp wheel 51 12 16 14:34 0002.sql -rw-r--r-- 1 ariarijp wheel 89 12 16 14:34 0003.sql -rw-r--r-- 1 ariarijp wheel 83 12 16 14:34 0004.sql -rw-r--r-- 1 ariarijp wheel 89 12 16 14:34 0005.sql $ cat backup/0001.sql SELECT * FROM country;
これでクエリのバックアップを取得することができるようになりました。
定期的にバックアップし、変更履歴も残す
定期的にバックアップをするには、もう一手間かける必要があります。
そのために、以下のスクリプトを backup.sh として保存し、実行権限を与えておきます。
#!/bin/bash export REDASH_URL=http://localhost export REDASH_API_KEY="Redashのadmin権限があるユーザーのAPIキー" cd `dirname $0` if [ ! -d '.git' ]; then git init fi python backup_queries.py git add . && git ci -a -m `date '+%Y%m%d%H%M%S'`
これを実行すると、先ほどのPythonスクリプトでクエリのバックアップを取得し、そのファイルがGitリポジトリにコミットされます。
$ ./backup.sh Initialized empty Git repository in /private/tmp/redash_backup/.git/ [master (root-commit) 820ae32] 20171216144840 8 files changed, 49 insertions(+) create mode 100755 backup.sh create mode 100644 backup/0001.sql create mode 100644 backup/0002.sql create mode 100644 backup/0003.sql create mode 100644 backup/0004.sql create mode 100644 backup/0005.sql create mode 100644 backup_queries.py
このスクリプトを cron などを使って適当な間隔で定期実行すれば、クエリのバックアップを定期的に、かつ、Gitを使用することで履歴とタイムスタンプ付きで保存することができるようになります。
まとめ
Redashの気軽さを維持しつつ、redashman でクエリを定期的にバックアップすることで、クエリを誤って編集してしまったなどのトラブルに対処できるようになり、副産物としてクエリの編集履歴もGitという多くの開発者の手に馴染んだツールで管理できるようになります。
Redashの利用をこれから検討している方にも、ぜひ redashman の導入を検討してみてほしいです。
宣伝
id:kakku22 と私の主催で、ハンズオン形式のRedash Meetup #0 を12/19(火)に開催しますが。ありがたいことにキャンセル待ちが出ています。
ハンズオンイベントについては来年1月に再演を計画していますので、今回予定が合わなかったり、キャンセル待ちから繰り上がらなかった方も、改めて参加を検討していただけるとうれしいです。
また、今後はハンズオンイベントではなく、各社の導入事例を成功も失敗も含めて共有できるような企画も検討しておりますので、Redashに興味があるかたは、よろしければConnpassのRedash Meetupグループに参加してみてください。
このグループに参加していると、次回のハンズオンも含め、今後のイベント日程もチェックしやすくなると思います。
Redashのクエリエディターのテーマを変える
この記事はRedash Advent Calendar 2017の記事ではありません。
9日目の記事は take4_k さんの「RedashのAzure Table StorageのQuery Runnerを作った 」です。この記事を読む前にぜひ読んでください。
やりたいこと
この記事では、こんな感じにクエリエディターのテーマを好みのものに変えてみます。
環境構築
おなじみの以下のドキュメントにそって開発環境を構築してある前提です。
Docker Based Developer Installation Guide · Redash Help Center
JSを書き換える
Redashのクエリエディターは Brace というNPMモジュールで作られています。
Braceの中で使用していると思われる Ace Editor がテーマ切り替えをサポートしているので、その機能を使ってテーマを切り替えます。
Ace - The High Performance Code Editor for the Web
テーマをデフォルトのものから Tommorow Night Bright に変えるため、 client/app/components/queries/query-editor.js を書き換えます。
diff --git a/client/app/components/queries/query-editor.js b/client/app/components/queries/query-editor.js index 445be461..29bf5494 100644 --- a/client/app/components/queries/query-editor.js +++ b/client/app/components/queries/query-editor.js @@ -3,6 +3,8 @@ import 'brace/mode/python'; import 'brace/mode/sql'; import 'brace/mode/json'; import 'brace/ext/language_tools'; +import 'brace/theme/tomorrow_night_bright'; + import { map } from 'underscore'; // By default Ace will try to load snippet files for the different modes and fail. @@ -46,6 +48,8 @@ function queryEditor(QuerySnippet) { editor.commands.bindKey('Cmd+L', null); editor.commands.bindKey('Ctrl+L', null); + editor.setTheme('ace/theme/tomorrow_night_bright'); + QuerySnippet.query((snippets) => { window.ace.acequire(['ace/snippets'], (snippetsModule) => { const snippetManager = snippetsModule.snippetManager;
変更できたらビルドします。
$ npm run build
動作確認
docker-compose でRedashを起動し、管理ユーザーやデータソースを作成した後にクエリ作成画面を表示すると、この記事の冒頭の画像のようにテーマが Tomorrow Night Brightになっています。
他のテーマを使用したい場合は以下を参考にしてみてください。
まとめ
実用上はもう少し工夫して実装した方が良さそうですが、今後Redashの設定ファイルでテーマが切り替えられたりすると面白いかもしれませんね。
Redashで独自のQuery Runnerを作る
この記事はRedash Advent Calendar 2017 8日目の記事です。
独自のQuery Runnerを作る
いきなり元ネタを出しますが、Redashの開発者Arikさんによる、以下の投稿を見るとだいたい作り方の雰囲気がわかってきます。
上記の記事を参考に、クエリの代わりに文字列を貼り付けると、その文字列をCSVのようにパースして結果を返すような、ちょっと変わったQuery Runnerを作成します。
開発環境
細かい開発環境構築方法の説明は割愛しますので、ご了承ください。
以下の公式ドキュメントに開発環境の構築手順が記載されているため、この手順に沿って、Docker上の開発環境が整っていることを前提に進めます。
Docker Based Developer Installation Guide · Redash Help Center
なお、使用しているRedashのバージョンは master ブランチの以下のコミット時点のもので、v4.x系に該当するものだと思います。
サンプルコード
以下のスクリプトを redash/query_runner/csv_parser.py として保存します。
import csv as csv
import json
from redash.query_runner import BaseQueryRunner, register
class CsvParser(BaseQueryRunner):
@classmethod
def configuration_schema(cls):
return {
'type': 'object',
'properties': {
'delimiter': {
'type': 'string',
'title': 'Delimiter'
}
}
}
@classmethod
def annotate_query(cls):
return False
def __init__(self, configuration):
super(CsvParser, self).__init__(configuration)
def test_connection(self):
pass
def run_query(self, query, user):
data = {
'columns': [],
'rows': [],
}
delimiter = str(self.configuration.get('delimiter'))
for row in csv.DictReader(query.strip().splitlines(), delimiter=delimiter):
if len(data['columns']) == 0:
for key in row.keys():
data['columns'].append({'name': key, 'friendly_name': key})
data['rows'].append(row)
return json.dumps(data), None
register(CsvParser)
コードの詳細は割愛しますが、クエリ文字列をCSVとしてパースし、Redashのクエリ結果の形式に準拠した形に整形します。
CSVの1行目はヘッダーとして扱うようにしています。
区切り文字はデータソースの設定として定義できるようになっており、デフォルトは,(半角カンマ)としています。
本来はカラムには string などの型を明示することも可能ですが、このスクリプトでは省略しています。
docker-compose.ymlの変更
独自のQuery Runnerを使用するため、 docker-compose.yml を編集します。
以下は docker-compose.yml の差分になります。
diff --git a/docker-compose.yml b/docker-compose.yml index 536dd446..2f948d90 100644 --- a/docker-compose.yml +++ b/docker-compose.yml @@ -17,6 +17,7 @@ services: REDASH_LOG_LEVEL: "INFO" REDASH_REDIS_URL: "redis://redis:6379/0" REDASH_DATABASE_URL: "postgresql://postgres@postgres/postgres" + REDASH_ADDITIONAL_QUERY_RUNNERS: "redash.query_runner.csv_parser" worker: build: . command: scheduler @@ -31,6 +32,7 @@ services: REDASH_DATABASE_URL: "postgresql://postgres@postgres/postgres" QUEUES: "queries,scheduled_queries,celery" WORKERS_COUNT: 2 + REDASH_ADDITIONAL_QUERY_RUNNERS: "redash.query_runner.csv_parser" redis: image: redis:3.0-alpine restart: always
環境変数 REDASH_ADDITIONAL_QUERY_RUNNERS で追加のQuery Runnerを指定できます。環境変数についてはAdvent Calendar 2日目の記事がとても参考になるのでおすすめです。
ここまでで独自Query Runnerを使うための準備は完了です。
動作確認
早速、 docker-compose up して動作確認します。
データベースの作成や管理ユーザの作成については済んでいるものとして進めます。
データソースの作成
データソースの作成画面でTypeのプルダウンをクリックすると、以下のように CsvParser が追加されています。
CsvParser を選択すると、以下のようなフォームが表示されます。
データソース名は CSV として、区切り文字はデフォルトで , となっているので、あえて :(半角コロン)にしてデータソースを保存します。
クエリの実行
クエリの作成画面に移動し、データソースとして CSV を選択ます。
クエリには以下のような半角コロン区切りの文字列を入力します。
name:ring_name:finishing_move:born_on Kanji Inoki:Antonio Inoki:Enzuigiri:1943-02-20 Baba Shohei:Giant Baba:Big boot:1938-01-23
テストデータの内容については特に触れず、実行してみます。
ここまでの手順に問題がなければ、上のように文字列をパースしてクエリ結果として表示することができます。
カラムの並び順が入力したものと違っているのは、PythonのDictが挿入順を保持しないからでしょうか。Redashは現時点でPython2.7を使用していますが、Python3.6あたりで順序が保持されるようになるので、Python3対応されたら少し結果が変わると思います。
1日目の記事で私が紹介した Query Results データソースで別の結果と結合することもできたりして面白いかもしれませんね。
まとめ
この記事ではあまり実用性を考えずに独自のQuery Runnerを作成しましたが、Redashをチームや事業にフィットするようにカスタマイズしたいと考えられる場合、Query Runnerを作るというのは選択肢にいれてみてはいかがでしょうか。
明日は take4_k さんの「azure table storageのquery runner作ってみたので書きます」です。この記事がいい前振りになることを願っています。
