Redashの動的クエリをJavascriptから呼び出す

社内ではRedashを活用しています。Redashには固定のSQLを実行してビジュアライズする以外にも、一部の値をパラメータ化した動的なクエリを実行する機能もあります。
この動的クエリを外部からAPIとして、ChromeExtensionから使おうとしたらドハマリしたのでまとめておきます。

Redashの動的クエリ

以下のようにSQL内に{{hoge}}と書いたSQLを指定することで利用できます。

SELECT account_name
FROM member
WHERE login_id = '{{login_id}}'

このようなパラメータ付のSQLを指定すると以下の様な入力欄が自動で生成されます。

実は入力欄に値を設定する以外にもURLのクエリパラメータとして指定することができます。

http://your.redash.host/queries/<query_id>/source?p_login_id=100

指定する値はp_parameter=valueです。login_idというパラメータに100を指定するのであればp_login_id=100をURLに加えればよいわけです。

これを外部から叩くことができれば自前のサーバを用意せずにAPIとして利用できる....
と思ったら一筋縄では行きませんでした。

外部からの叩き方

パラメータを使わないクエリであれば、クエリ編集画面(source)の以下から出てくるURLを叩くだけでJSONなりCSVなりで結果が取得できます。

しかし、パラメータ付クエリの場合同じ方法を用いると期待した結果を得られません。少なくともパラメータを指定することができず、最後に成功したクエリの結果が戻る動作になっているようです。

[Feature Request] Add support for query parameters in the API

というissueが存在し、Closeになっていたのでそれ用のAPIがあるのかなと思ったら

Actually this is already possible with the refresh API. See this for example:
https://gist.github.com/arikfr/e3e434d8cfd7f331d499ccf351abbff9

と閉められていました。どうやらrefreshAPIを用いて実現できるからそれをしろとのことでした。意外とこれが面倒です。

ざっくりと流れ的には以下でした。

• 指定したIDのクエリにパラメータを渡して結果を更新するようリクエスト(refresh)
• リクエストに対してJOBのIDが戻るためJOBのステータスをポーリング
• ジョブが完了するとquery_result_idが戻るためそれを用いて結果を取得

ただパラメータ付の結果がほしいだけなのに3回以上リクエストしないといけないのです。

issueで示されているPythonでのsampleやRedash APIで動的クエリ発行するモジュールの流れを参考に、Javascriptで実行してみました。

USER Tokenの発行

この手順には各クエリのshow API Keyで生成されるキーではなく、ユーザ単位で発行されるAPI Keyが必要です。
Settings > Users > ユーザ名 > API KEYからAPI Keyを確認しておきます。

Javascriptでの実装

Ajaxだけできればいいので、jQuery等は利用せずfetchを使います。もちろん$.ajaxでもaxiosでも同じ様な流れになると思います。3回以上Ajaxしなければいけないので、普通にやるとコールバックだらけで辛いのでasync await で同期的に書いています。

以下はクエリID:99に対してlogin_idというパラメータを渡して実行しています。

async function accessRedash(accountId){
    const API_TOKEN = 'YOUR USER TOKEN not query token.';
    // クエリパラメータ付でrefresh apiを実行
    let response = await fetch('http://your.redash.host/api/queries/99/refresh?p_login_id=' + accountId, {
        method: 'POST',
        headers: {
            'Authorization': `Key ${API_TOKEN}`,
            'Accept': 'application/json',
            'Content-Type': 'application/json'
        }
    }).then(res => {
        return res.json();
    })
    // refresh apiの戻り値にはjobが入っている
    let job = response['job'];
    // job のステータスが3,4になるまで実行(refreshが完了すると脱出する)
    while (job.status !== 3 && job.status !== 4) {
        // job apiで最新のジョブの状況をチェック
        response = await fetch('http://your.redash.host/api/jobs/' + job['id'], {
            headers: {
                'Authorization': `Key ${API_TOKEN}`,
                'Accept': 'application/json',
                'Content-Type': 'application/json'
            }
        }).then(res => {
            return res.json();
        })
        job = response['job'];
        // 0.5秒とりあえず待つ
        await sleep(500);
    }
    // jobが完了している場合query_result_idが手に入る
    const resultId = job['query_result_id'];
    // query_result_id で指定される結果をJSONで受け取る(.jsonと指定することでJSON形式になる)
    response = await fetch(`http://your.redash.host/api/queries/99/results/${resultId}.json`,{
        headers: {
            'Authorization': `Key ${API_TOKEN}`,
            'Accept': 'application/json',
            'Content-Type': 'application/json'
        }
    }).then(res => {
        return res.json();
    })

    // query_result.dataに配列で結果が格納されているので後は好きにする
    const resultSet = response.query_result.data;
    console.log(resultSet)

}

// sleep helper
function sleep(msec){
    return new Promise(resolve => setTimeout(resolve, msec));
}

async awaitで楽にかけますが、素直に書いてたら大変ネストが深くなって辛かっただろうなと思います。Redash 3.0で試しましたがとにかくこのあたりのドキュメントが少なくて苦労しましたのでQiitaに記事として残させていただきます。