RedashのScriptデータソースで独自のデータソースを作成する

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

qiita.com

昨日はhideji2さんの 「RedashのPythonDataSourceを使ってデータのヘルスチェックをしてみた話」でした。

qiita.com

Scriptデータソースとは

RedashにはScriptデータソースという、任意のスクリプトをデータソースとして使える機能が含まれています。

この記事では、Scriptデータソースを使って自由にデータソースを定義する方法を紹介します。

Dockerで環境構築

Dockerを使用して環境構築をします。

以下のYAMLを docker-compose.yml として保存してください。

version: '2'
services:
  server:
    image: redash/redash:latest
    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: 4
      REDASH_ADDITIONAL_QUERY_RUNNERS: "redash.query_runner.script"
    restart: always
  worker:
    image: redash/redash:latest
    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: 2
      REDASH_ADDITIONAL_QUERY_RUNNERS: "redash.query_runner.script"
    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

REDASH_ADDITIONAL_QUERY_RUNNERS という環境変数を使用してScriptデータソースを使えるようにします。

環境変数については2日目の記事に詳しく書かれていますので、興味がある方はぜひチェックしてください。

qiita.com

docker-compose.yml が準備できたら、Redashのデータベースを初期化するため、以下のコマンドを実行します。

$ docker-compose run --rm server bash -c "sleep 15 && /app/bin/docker-entrypoint create_db"
$ docker-compose up

Redashが起動したらhttp://localhostにアクセスし、admin ユーザーを作成します。

サンプルスクリプト

以下のスクリプトを wrestlers.sh として保存します。

#!/bin/bash
JSON=$(cat << EOS
{
  "columns": [
    {"friendly_name": "Name", "type": "string", "name": "name"},
    {"friendly_name": "Ring name", "type": "string", "name": "ring_name"},
    {"friendly_name": "finishing moves", "type": "string", "name": "finishing_move"},
    {"friendly_name": "Born on", "type": "string", "name": "born_on"}
  ],
  "rows": [
    {"name": "Kanji Inoki", "ring_name": "Antonio Inoki", "finishing_move":"Enzuigiri", "born_on": "1943-02-20"},
    {"name": "Baba Shōhei", "ring_name": "Giant Baba", "finishing_move":"Big boot", "born_on": "1938-01-23"}
  ]
}
EOS
)
/bin/echo $JSON

ファイル名やデータの内容については割愛しますが、RedashのAPIなどで使用されているフォーマットに従ったJSONを返すことが必須となっています。

このフォーマットについては公式ドキュメントにも記載があります。

Data Source Results Format · Redash Help Center

wrestlers.sh を保存したら、以下のコマンドでスクリプトをRedashのWorkerコンテナにコピーし、実行権限を付与します。

$ docker-compose exec worker mkdir /tmp/redash
$ docker cp wrestlers.sh `docker-compose ps | grep worker | awk '{print $1}'`:/tmp/redash/
$ docker-compose exec -u root worker chmod a+x /tmp/redash/wrestlers.sh

データソースの設定

続いて、以下のコマンドでデータソースを追加します。

$ docker-compose exec server ./manage.py ds new --type insecure_script --options '{"path": "*", "shell": true}' wrestlers

path には * を指定していますが、本来はスクリプトの実行を許可するパスを明示的に /tmp/redash のように指定することができるようですが、現時点ではパスを指定すると動作しなくなってしまうため、この記事では * と指定しています。

ここまでで環境構築は完了です。

データソース名から、安全ではない(Insecure)機能だということもわかりました。

本番での使用を検討する際には、任意のスクリプトを実行できるという意味で非常に危険な機能ではありますが、この記事ではScriptデータソースの紹介が主目的なので、先に進みます。

Scriptデータソースを使用したクエリを実行する

動作確認に使用するクエリをRedashの画面上で作成します。

新規クエリをデータソースとして wrestlers を選択し、以下の内容で作成してください。

 /tmp/redash/wrestlers.sh

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

指定したスクリプトの標準出力をクエリの結果として取得できることがわかりました。

この記事の例ではbashで書いていますが、PHPでもGoでもRubyでも、出力がRedashのフォーマットに準じたものであれば、どんな言語で書いても良いというのは、強みになることがあるかもしれません。

まとめ

簡単にまとめます。

• Scriptデータソースを使用することで、任意のスクリプト言語などでデータソースを独自に定義することができる
• Scriptデータソースの利用時はセキュリティリスクを含むため、使用を検討する場合は権限などを正しく設定するなどの注意が必要

本番導入へのセキュリティリスクもあり、使い方が難しい機能ですが、うまく使いこなせるとRedashをより快適に使えるようになるかもしれません。

明日はtkmotekiさんが何か書いてくれるようです。気になりますね。

宣伝

Redash Advent Calender 2017を作成した id:kakku22 と一緒にRedash Meetupを開催します。

connpass.com

初回は初学者向けのハンズオンとしていますが、次回以降は導入事例などの情報共有もできるように続けていければと考えていますので、興味を持っていただけたら Twitter あたりでお声がけいただけると嬉しいです。

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

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

qiita.com

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
;

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

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

内部的には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パラメータ一覧」について記事を書いてくださるようです。お楽しみに。

サービスアカウントのJSON鍵ファイルでPythonからGoogleのAPIを使用する

バッチを書くときにサービスアカウントを使う機会が多いのでメモ。

公式ドキュメントではサービスアカウントを使う例が紹介されていないので、Sheets APIのサンプルコードを少し改変してサービスアカウントで認証するようにしてみる。

Python Quickstart  |  Sheets API  |  Google Developers

from __future__ import print_function

import httplib2
from apiclient import discovery
from oauth2client import tools
from oauth2client.service_account import ServiceAccountCredentials

try:
    import argparse

    flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
    flags = None

# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/sheets.googleapis.com-python-quickstart.json
SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'
CREDENTIALS_FILE = 'credentials.json' # サービスアカウントの鍵ファイル名
APPLICATION_NAME = 'Google Sheets API Python Quickstart'


def get_credentials():
    """サービスアカウントで認証する"""
    return ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, SCOPES)


def main():
    """Shows basic usage of the Sheets API.

    Creates a Sheets API service object and prints the names and majors of
    students in a sample spreadsheet:
    https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit
    """
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                    'version=v4')
    service = discovery.build('sheets', 'v4', http=http,
                              discoveryServiceUrl=discoveryUrl)

    spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
    rangeName = 'Class Data!A2:E'
    result = service.spreadsheets().values().get(
        spreadsheetId=spreadsheetId, range=rangeName).execute()
    values = result.get('values', [])

    if not values:
        print('No data found.')
    else:
        print('Name, Major:')
        for row in values:
            # Print columns A and E, which correspond to indices 0 and 4.
            print('%s, %s' % (row[0], row[4]))


if __name__ == '__main__':
    main()

実行してみると、公式ドキュメントのサンプルコードだとURLが表示されてブラウザで認証する形になるが、サービスアカウントを使った場合は認証をパスしたものとして処理が続行される。

$ python main.py
Name, Major:
Alexandra, English
Andrew, Math
Anna, English
Becky, Art
Benjamin, English
...略...

最近BigQueryを使ったときもこのやりかたを使った。

Google BigQuery

business-rulesを使ってルールエンジンを使った処理を書く

ルールエンジンをPythonで使えると仕事上便利なことがありそうなので調査してみたところ、 business-rules が目的にあっているように思えたので、READMEを読みつつ試してみた。

使ったモジュール

Venmoが開発した business-rules を使うことにした。VenmoのことはRebuildで聞いたことがあるぐらいで使ったことはない。

github.com

• Pythonで書かれたルールエンジンを探していた
• Venmoの中でも使われているのかも。と考えて使ってみたくなった
• READMEのサンプルコードが分かりやすかった

というのが選定理由。

コミットログを見るとしばらく活動がなさそうだけど、こういうのは一度書いて(安定して)動いてたら、よほどのことがなければ書き換えるものでもないかなと思って気にしないことにした。

サンプルコード

一度ルールエンジンを使って処理を書いたことがあれば、business-rules がなにをするためにものなのかは容易に理解できると思う。

経験がなくても、動かしながらいろいろ試してみればどういったものなのかの感覚はつかめるはず。

仕事柄広告のデータを扱うことが多いので、広告っぽいデータを処理するルールを書いたのがこちら。

from business_rules import run_all
from business_rules.actions import BaseActions, rule_action
from business_rules.fields import FIELD_TEXT
from business_rules.variables import BaseVariables, numeric_rule_variable


class Ad():
    def __init__(self, name, status, **kwargs):
        self.name = name
        self.status = status
        self.spend = kwargs.get('spend', 0)
        self.impressions = kwargs.get('impressions', 0)
        self.clicks = kwargs.get('clicks', 0)

    def save(self):
        print(self.name, self.status)


class AdVariables(BaseVariables):
    def __init__(self, ad):
        self.ad = ad

    @numeric_rule_variable
    def spend(self):
        return self.ad.spend

    @numeric_rule_variable
    def cpc(self):
        return self.ad.spend / self.ad.clicks

    @numeric_rule_variable
    def ctr(self):
        return self.ad.clicks / self.ad.impressions


class AdActions(BaseActions):
    def __init__(self, ad):
        self.ad = ad

    @rule_action(params={"status": FIELD_TEXT})
    def change_status(self, status):
        self.ad.status = status
        self.ad.save()


def main():
    rules = [{
        'conditions': {
            'all': [
                {'name': 'spend', 'operator': 'greater_than_or_equal_to', 'value': 200000},
                {'name': 'cpc', 'operator': 'greater_than_or_equal_to', 'value': 100},
                {'name': 'ctr', 'operator': 'less_than', 'value': 0.03},
            ]
        },
        'actions': [
            {'name': 'change_status', 'params': {'status': 'PAUSED'}},
        ],
    }]

    ads = [
        Ad(name='Ad1', status='ACTIVE', spend=100000, impressions=10000, clicks=1000),
        Ad(name='Ad2', status='ACTIVE', spend=200000, impressions=10000, clicks=500),
        Ad(name='Ad3', status='ACTIVE', spend=400000, impressions=20000, clicks=500),
    ]
    for ad in ads:
        run_all(rule_list=rules,
                defined_variables=AdVariables(ad),
                defined_actions=AdActions(ad),
                stop_on_first_trigger=True)


if __name__ == '__main__':
    main()

サンプルコードの説明

• Ad は広告そのものを表現する
• AdVariables はAd を business-rules が評価できる形式にするためのもの
  • ここではspend(消化金額)と合わせて、CPC(クリック単価)やCTR(クリック率)を評価対象にしている(簡略化のためにゼロ割は考慮していない)
  • CPCやCTRのように、もとのオブジェクトに無いものでも、計算によって求められた値を評価できる
  • どんな値でも BaseVariables を継承したクラスでラップするような形になるので、対象のデータの形式によらずルールを適用できる
• AdActions は評価した結果、ルールに一致したものをどのように処理するかを表現する
  • 今回はルールに一致した広告のステータスを変更するようなサンプルにしているので、ステータスを変更するような処理として change_status を定義した
• main でルールを定義し、与えられたデータをルールに従って評価する
  • ルールには conditions と actions が定義できる
  • conditions では、 less_than や greater_than_or_equal_to などの演算子を用いて、先に定義した AdVariables の値を評価することができる
  • actions では、ルールに一致したものをどのように処理するかを定義する。今回はルールに一致した広告を停止するようなサンプルにしているので、 AdActions に定義した change_status のstatusに、広告の停止状態を表す PAUSED を渡したものを実行する

実行

サンプルコードでは、以下の conditions すべてに一致したものを actions に従って処理する。

• 消化金額が¥200,000以上
• CPC(消化金額 / クリック数)が¥100以上
• CTR(クリック数 / 表示回数)が3%未満

これらを満たすのは Ad3 (消化金額¥400,000、CTR¥800、CTR2.5%)なので、実行結果は以下となる。

$ python main.py
Ad3 PAUSED

まとめ

• business-rules はPythonにおけるルールエンジンとして実用的かつシンプルで良い
• Pandas、scikit-learnなどのデータ分析、機械学習の仕組みと組み合わせると面白そう

Pythonではじめる機械学習 ―scikit-learnで学ぶ特徴量エンジニアリングと機械学習の基礎

Pythonでgoogle-cloud-bigqueryを使用してLegacy SQLを実行する

google-cloud-bigquery はデフォルトでStandard SQLを使用することになっているため、Legacy SQLを使用する場合は明示的に指定する必要がある。

github.com

さっとソースを読んでみたところ、QueryJobConfigというのを使うといいらしい。

サンプル

from google.cloud import bigquery

if __name__ == '__main__':
    client = bigquery.Client.from_service_account_json('./credentials.json')

    query = 'SELECT * FROM [project:dataset.table]'
    config = bigquery.QueryJobConfig()
    config.use_legacy_sql = True

    rows = client.query(query, job_config=config).result()
    for row in rows:
        print(row)

歴史があるサービスだとLegacy SQLを使うこともあるので覚えておく。

Google BigQuery