Excel
redash

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

More than 1 year has passed since last update.


これはなに?

RedashのデータをAPI経由でエクセルに挿入する方法の紹介です。

技術に明るくないディレクター・分析担当者向けにわかりやすく書いていこうと思います。

また、Redashのデモサイトを使って解説するので、エクセルさえあれば誰でも実験できるようになっています。

※ちなみに、タイトルはエクセルと書いていますが、Googleスプレッドシートの解説もします(笑)


準備


Redashのクエリを確認します

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

「Redashはこれから導入する予定」など、Redashの環境がない人は、下記のRedashのデモサイトにアカウントを作りましょう。Google認証ですぐにアカウントを作成できます。

以下このデモサイトのクエリを使いながら解説します。

https://demo.redash.io/login

ログインできたら下記のクエリを見てください。

https://demo.redash.io/queries/4307#5984

image

これをエクセルに流し込んでいきます。

ちなみにこれは、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

image

からのShow API Key で確認できます。

APIキーがモーダルででてくるのでその値をメモしておきます

image

image

データの取得にはこの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キー>

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

image

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


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

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

{

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

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

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


エクセルへの取り込み方

まず簡単に流れを紹介します。

エクセルにはクエリという機能があり、外部ソースを比較的簡単に引っ張ってこれるのです。

つまり、先程のjsonを外部ソース先に指定し、希望の形になるように整形(パース)してやればよいのです。


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

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

image

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

image

初回接続する場合は下記のようなWindowがでます。

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

image

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


クエリエディタでの操作

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

これを画面からポチポチできるのがクエリエディタです。

たちあげると、最初に下記のような画面になります。

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

image

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

image

どんどんいきます

image

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

image

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

image

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

image

そうすると下記のような画面になり、目的の値を得ることができました。

閉じて読み込むボタンをクリックして、閉じましょう。

image

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

image

よいエクセルライフを。


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キー>")

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

image

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

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