この記事は Redash Advent Calendar 2017 1日目の記事です。

Query Resultsデータソースとは

v3.0.0 で追加されるデータソースで、文字通り「クエリの実行結果」をデータソースとして扱うことができるものです。

有料版ではすでに存在していたものがv.3.0.0でOSSになったようで、有料版での使用例は、過去にGunosyさんの記事でも紹介されています。

data.gunosy.io

この記事では、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
----
server:
image: redash/redash:latest
command: server”
----
こちらredash/redash:latestのimageだとRedashのバージョンが2.0.1b3080となるため、下記にてresultsデータソースをサポートしていない旨のエラーとなりました。
— nntsugu (@nntsugu) 2017年12月26日

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を送っているので、もしかしたら今後は起こらなくなるかもしれません。

github.com

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](小文字でも良い)という記法で対象のクエリを指定します。

redash.io

以下の例は、データソースが異なるクエリ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
;

実行すると、以下のような結果が表示されます。

f:id:ariarijp:20171130211401p:plain

異なるデータソースを使用したクエリの「結果」に対してクエリを実行できていることがわかりました。

内部的にはSQLiteをインメモリで使用しているので、SQLiteで使えるSQLをそのまま使えます。

テーブル名の書き方について

現時点ではテーブル名の前に改行やタブなど、半角スペース以外の空白文字が入ってしまうと、該当のクエリを発見できない問題がありますが、PRをマージしていただいたので、4.0.0で修正されると思います。

github.com

注意点

ドキュメントに記載があるように、現時点では以下の注意点があります。

Query Resultsデータソースを使用したクエリは、クエリ内で参照する他のクエリを常に再実行する
オンメモリのSQLiteに結果を保持するため、メモリ不足が起きると失敗する
クエリ内で参照する他のクエリが使用しているデータソースすべてに対して権限を持っていないと実行できない

使い所

すべてのケースで有用かというとそうでもないかもしれませんが、異なるデータソースの結果を結合する以外にも、複雑な集計をする際に、中間テーブルの代替としてケースで活用することができると考えました。

まとめ

v3.0.0の機能の中でも大きな機能のひとつだと言えるQuery Resultsデータソースについて、簡単にまとめます。

v3.0.0でQuery Resultsデータソースが使えるようになる
クエリの「結果」に対してクエリを実行するため、参照するクエリのデータソースがなんであろうと使用できる
複数のデータソースをまたがるクエリを書く場合や、中間テーブルの代替としても使用することができる
まだ安定リリース前かつ、使用上の注意点もあるため、本番運用を検討する場合は検証が必要

2日目の記事

明日は kyoshidajp さんが「Redashパラメータ一覧」について記事を書いてくださるようです。お楽しみに。

ariarijp’s blog

Redash v3.0.0で追加されるQuery Resultsデータソースについて