Redash v3.0.0で追加されるQuery Resultsデータソースについて
この記事は Redash Advent Calendar 2017 1日目の記事です。
Query Resultsデータソースとは
v3.0.0 で追加されるデータソースで、文字通り「クエリの実行結果」をデータソースとして扱うことができるものです。
有料版ではすでに存在していたものがv.3.0.0でOSSになったようで、有料版での使用例は、過去にGunosyさんの記事でも紹介されています。
この記事では、Query Resultsデータソースの利用例を環境構築からクエリ実行までの手順とあわせて紹介します。
環境構築
Redashの環境構築はDockerを利用することが推奨されているので、Dockerを使って環境構築をしますが、Query Resultsデータソースの動作を確認するため、Redash標準のセットアップ手順に加えて、MySQL、PostgreSQL、SQLiteそれぞれのデータソースを使えるようにします。
サンプルデータとして、MySQLのSakila、それをPostgreSQL向けにしたPagila、さらにSQLite向けのsqlite-sakilaを使用します。
この3つの異なるデータソースのQuery Resultsデータソースを使って扱えるようにしていきましょう。
検証に使用する docker-compose.yml
は以下のようなものになります。詳細は割愛しますので、ご了承ください。
version: '2' services: server: image: redash/redash:3.0.0.b3147 command: server depends_on: - postgres - redis ports: - "5000:5000" environment: PYTHONUNBUFFERED: 0 REDASH_LOG_LEVEL: "INFO" REDASH_REDIS_URL: "redis://redis:6379/0" REDASH_DATABASE_URL: "postgresql://postgres@postgres/postgres" REDASH_COOKIE_SECRET: veryverysecret REDASH_WEB_WORKERS: 2 volumes: - "sqlite_sakila:/sqlite:rw" restart: always worker: image: redash/redash:3.0.0.b3147 command: scheduler environment: PYTHONUNBUFFERED: 0 REDASH_LOG_LEVEL: "INFO" REDASH_REDIS_URL: "redis://redis:6379/0" REDASH_DATABASE_URL: "postgresql://postgres@postgres/postgres" QUEUES: "queries,scheduled_queries,celery" WORKERS_COUNT: 4 volumes: - "sqlite_sakila:/sqlite:ro" restart: always redis: image: redis:3.0-alpine restart: always postgres: image: postgres:9.5.6-alpine restart: always nginx: image: redash/nginx:latest ports: - "80:80" depends_on: - server links: - server:redash restart: always sakila: image: rnoennig/sakiladb environment: MYSQL_ALLOW_EMPTY_PASSWORD: "yes" restart: always pagila: image: mujz/pagila restart: always volumes: sqlite_sakila:
2017-12-27追記
server サービスで使用しているバージョンが redash:latest
になっていたため、 redash/redash:3.0.0.b3147
に修正しました。
コメントいただきありがとうございました!
docker-compose.yaml
— nntsugu (@nntsugu) 2017年12月26日
----
server:
image: redash/redash:latest
command: server”
----
こちらredash/redash:latestのimageだとRedashのバージョンが2.0.1b3080となるため、下記にてresultsデータソースをサポートしていない旨のエラーとなりました。
docker-compose.yml
が準備できたら、Redashのデータベースを初期化するため、以下のコマンドを実行します。
$ docker-compose run --rm server bash -c "sleep 15 && /app/bin/docker-entrypoint create_db" $ docker-compose up
公式ドキュメントで説明されているコマンドと少し違っていますが、PostgreSQLの起動を待たずにマイグレーションスクリプトが動いてしまうことがあるので、その対策としてsleepを15秒挟んでいます。
ちなみにこの問題はPRを送っているので、もしかしたら今後は起こらなくなるかもしれません。
Redashが起動したらhttp://localhost
にアクセスし、admin
ユーザーを作成します。
admin
ユーザーの作成後、データソースを追加するため、docker-compose
を実行しているものとは別のターミナルで、以下のコマンドを実行します。
$ docker-compose exec -u root server chmod a+rw /sqlite $ docker-compose exec server wget -O /sqlite/sqlite-sakila.sq https://github.com/wallymathieu/sakila-sample-database-ports/raw/master/sqlite-sakila-db/sqlite-sakila.sq $ docker-compose exec server ./manage.py ds new --type mysql --options '{"db": "sakila", "host": "sakila", "user": "root"}' sakila $ docker-compose exec server ./manage.py ds new --type pg --options '{"dbname": "pagila", "host": "pagila", "password": "admin", "user": "root"}' pagila $ docker-compose exec server ./manage.py ds new --type sqlite --options '{"dbpath": "/sqlite/sqlite-sakila.sq"}' sqlite-sakila $ docker-compose exec server ./manage.py ds new --type results results
これらのコマンドはRedashのCLIを使用してデータソースを追加しています。
クエリの作成
Query Resultsデータソースの動作確認に使用するクエリをRedashの画面上で作成します。
クエリIDを変えてしまうと後述のサンプルクエリが動かなくなってしまうため注意してください。
クエリID1としてsakilaデータソース(MySQL)を使用して作成
SELECT country_id, country FROM country WHERE country_id BETWEEN 100 AND 110 ;
クエリID2としてpagilaデータソース(PostgreSQL)を使用して作成
SELECT country_id, COUNT(*) num_of_cities FROM city GROUP BY country_id ;
クエリID3としてsqlite-sakilaデータソース(SQLite)を使用して作成
SELECT city.country_id, COUNT(*) num_of_addresses FROM address JOIN city ON address.city_id = city.city_id GROUP BY city.country_id ;
ここまでで環境構築は完了です。
Query Resultsデータソースを使用したクエリを実行する
公式ドキュメントにも説明があるとおり、QUERY_[クエリID]
(小文字でも良い)という記法で対象のクエリを指定します。
以下の例は、データソースが異なるクエリ1、2、3それぞれの結果をJOINして、各テーブルのカラムをSELECTする例です。
SELECT mysql.country, pgsql.num_of_cities, sqlite.num_of_addresses FROM query_1 mysql JOIN query_2 pgsql ON mysql.country_id = pgsql.country_id JOIN query_3 sqlite ON pgsql.country_id = sqlite.country_id ;
実行すると、以下のような結果が表示されます。
異なるデータソースを使用したクエリの「結果」に対してクエリを実行できていることがわかりました。
内部的にはSQLiteをインメモリで使用しているので、SQLiteで使えるSQLをそのまま使えます。
テーブル名の書き方について
現時点ではテーブル名の前に改行やタブなど、半角スペース以外の空白文字が入ってしまうと、該当のクエリを発見できない問題がありますが、PRをマージしていただいたので、4.0.0で修正されると思います。
注意点
ドキュメントに記載があるように、現時点では以下の注意点があります。
- Query Resultsデータソースを使用したクエリは、クエリ内で参照する他のクエリを常に再実行する
- オンメモリのSQLiteに結果を保持するため、メモリ不足が起きると失敗する
- クエリ内で参照する他のクエリが使用しているデータソースすべてに対して権限を持っていないと実行できない
使い所
すべてのケースで有用かというとそうでもないかもしれませんが、異なるデータソースの結果を結合する以外にも、複雑な集計をする際に、中間テーブルの代替としてケースで活用することができると考えました。
まとめ
v3.0.0の機能の中でも大きな機能のひとつだと言えるQuery Resultsデータソースについて、簡単にまとめます。
- v3.0.0でQuery Resultsデータソースが使えるようになる
- クエリの「結果」に対してクエリを実行するため、参照するクエリのデータソースがなんであろうと使用できる
- 複数のデータソースをまたがるクエリを書く場合や、中間テーブルの代替としても使用することができる
- まだ安定リリース前かつ、使用上の注意点もあるため、本番運用を検討する場合は検証が必要
2日目の記事
明日は kyoshidajp さんが「Redashパラメータ一覧」について記事を書いてくださるようです。お楽しみに。