Redashのデータを毎日エクセルへコピペしている人へささげる -APIを利用した自動更新方法-

これはなに？

RedashのデータをAPI経由でエクセルに挿入する方法の紹介です。
技術に明るくないディレクター・分析担当者向けにわかりやすく書いていこうと思います。
また、Redashのデモサイトを使って解説するので、エクセルさえあれば誰でも実験できるようになっています。
※ちなみに、タイトルはエクセルと書いていますが、Googleスプレッドシートの解説もします(笑)

準備

Redashのクエリを確認します

普段Redashを使っている人は、いつも見ているそのクエリを開きましょう。

「Redashはこれから導入する予定」など、Redashの環境がない人は、下記のRedashのデモサイトにアカウントを作りましょう。Google認証ですぐにアカウントを作成できます。
以下このデモサイトのクエリを使いながら解説します。
https://demo.redash.io/login

ログインできたら下記のクエリを見てください。
https://demo.redash.io/queries/4307#5984

これをエクセルに流し込んでいきます。
ちなみにこれは、Redashデモ環境の日毎のテーブル作成数のグラフです。
下記のSQLをヴィジュアライズしています。

SELECT DATE(created_at) AS date,
       count(*) AS value
FROM queries
GROUP BY DATE(created_at)
ORDER BY DATE(created_at);

RedashのAPIキーを確認します

今回の方法ではRedashのAPIを用いてエクセルやGoogleスプレッドシートに転記します。
RedashはAPIが豊富に用意されていて幸せですね(エンドポイント一覧)
データの取得は、CSVやjsonなどの形式で受け取ることができます。

APIキーの確認はShow Source

からのShow API Key で確認できます。
APIキーがモーダルででてくるのでその値をメモしておきます

データの取得にはこのAPIキーと、このクエリのID(URLに含まれている)を使います。今回のデータでいうとIDは4307です。

ちなみに、queriesテーブルの中にapi_keyというカラムがあるので一気に複数のクエリのAPIキーを確認したい方はこちらを直で覗くのが便利ですね。

RedashのAPIキーが有効か確認

ではブラウザを用いてAPIキーが有効かどうかテストしてみましょう。
一度Redashをログアウト、もしくは異なるブラウザを立ち上げ、下記をブラウザに打つと、

https://demo.redash.io/api/queries/4307/results.json?api_key=<APIキー>

このようなデータがかえってくれば正常です。

ここで失敗する場合はなんらか問題があるのでキーを見直したり、詳しそうな人にきいたりして解決しましょう。

失敗する時の挙動＆ここ気をつけろ

上記がうまくいかないときは、下記のレスポンスがかえってきています

{
  "message": "Couldn't find resource. Please login and try again."
}

大体はAPIキーのスペルミスだと思うんで、まずそこを確認しましょう。

ほか気をつけれるポイントとしては、Redashの仕様上、ログインしている間はAPIキーが誤っていても正しいレスポンスが返ってくるので、かならずログアウトしてからテストしましょう。(余談：ログイン中はAPIキーの中身を評価せず正しい処理を返すというのはそれはそれで設計思想としてアリだと思うので、この仕様にケチをつけているわけではない

エクセルへの取り込み方

まず簡単に流れを紹介します。
エクセルにはクエリという機能があり、外部ソースを比較的簡単に引っ張ってこれるのです。
つまり、先程のjsonを外部ソース先に指定し、希望の形になるように整形(パース)してやればよいのです。

クエリエディタに取り込むまで

クエリはデータメニューの中にあります。今回は「その他のデータソース」を選択し、「Webから」を選択します。

するとURLを聞かれるので、先程テストで使用したURLを入力します。

初回接続する場合は下記のようなWindowがでます。
ここでログインの設定などができます。RedashのAPIはログインせずに使用できるため、認証情報を入力する必要がありません。接続をおしましょう。

そうするとクエリエディタが立ち上がります。

クエリエディタでの操作

さと、これからやることは、構造化されたデータを整形し、エクセル形式(2次元のテーブルにしていくということをやります。
これを画面からポチポチできるのがクエリエディタです。

たちあげると、最初に下記のような画面になります。
キャプチャにも書いていますが、基本的には真ん中にある情報をブレイクダウンしていき、欲しい粒度になったときにテーブルへ変換します。

どんどんブレイクダウンします

どんどんいきます

data[rows]が目的のデータです。(なぜこれが目的のデータになるのかは、jsonの構造をみればよく理解できると思います。ただ、記事の目的と若干それていくのでここでは深入りはしません。)

というわけで、このRecordをテーブルに変換します。左上のテーブルへ変換ボタンをおします。すると、ダイヤログがでてくるのでこれもOKします。

その次は、カラムの右にあるエキスパンドボタンをおします。そうすると、下記のようなダイヤログがでてきます。このダイヤログに表示されているものが、Redashの軸や凡例に相当します。不要な列があれば削っておいてもいいでしょう。

そうすると下記のような画面になり、目的の値を得ることができました。
閉じて読み込むボタンをクリックして、閉じましょう。

無事、エクセルで表示することができました。Redashのデータを反映させたい時は、更新ボタンを押すことで更新できます。その他、ブックを開くたびに更新などいろんな便利なオプションがあるので便利なように設定しましょう。

よいエクセルライフを。

Googleスプレッドシートへの取り込み方

では同じことをスプレッドシートではどうやるのでしょうか？
実は非常に簡単で、IMPORTDATA関数を使用するだけです(リファレンス)

一つ注意点なのが、json形式ではなくCSV形式でデータを引っ張ってくる点。
なので、叩くAPIは下記のようになります。

https://demo.redash.io/api/queries/4307/results.csv?api_key=<APIキー>

この文字列をIMPORTDATAに入れるだけです。
具体的には、下記のような形で入力します。

=IMPORTDATA("https://demo.redash.io/api/queries/4307/results.csv?api_key=<APIキー>")

そうすれば、下記のように値が埋まります。

しかしながらUpdateのタイミングはコントロールすることができず、スプレッドシートがよしなに更新してくれるのを待つ必要があります。これは今見えている情報が最新の情報かどうかわからないので、データ分析には痛い仕様だなーと思います。

よいGoogleスプレッドシートライフを。