これは VOYAGE GROUP Advent Canlendar 2016 の 9日目のエントリです。
VOYAGE MARKETINGの @katzchum (ちゃむ）です。
先日のLTのネタで作成したアプリの技術的なフォローです。
過去記事ですが、LTの様子はこんな感じです。
今回のLTの中でつくってみた系として発表したのが、
チェックインされた場所の一覧を時系列に検索するアプリです。
LTおもしろネタの題材として過去のチェックインリストを紹介する為に、視覚化を行うアプリケーションとして作成しました。 *1
（データの元ネタは自虐的なものなので、敢えて明言しませんが。。）
システム構成
LTなのでわざわざコストをかけて環境を構築するのもなんだかな〜と思いserverless チックなシステム構成としました。
| Web Server | github.io |
|---|---|
| Application | JavaScript （Framework : JQuery(-ui)） |
| Cache system | WebStorage * sessionStorage * localStorage |
| BaaS | Google Map API v.3 Rakuten WEB SERVICE（楽天トラベル系API） |
| Database | Google SpreadSheet （Query language : Google Visualization API） |
| Data seed | データ収集 / Scraper （Google Chrome Extension） データ集計・加工 / COUNTIF, SUMIF, VLOOKUP, DATEDIF（Google SpreadSheet） スクレイピング / IMPORTXML（Google SpreadSheet） |
Webサーバーもgithub.ioを利用して構築手間を省く、ずぼらっぷりとなりました。
アプリケーションの配信の手間もはぶけて楽チンです。
データベース周り
視覚化対象のデータは楽天トラベルの過去予約照会から引っ張ってきました。
データはGoogle SpreadSheetへ入力していきました。
ただデータの入力方法を考える必要がありました。
自身が考えていたよりもデータ件数が多かったのと、チェックインの履歴と住所情報を紐付けるのに、照会ページのUI（HTML構造）では手数が多く難しそうでした。
スクリプトを組んでデータ収集することも考えましたが見送りました。
認証をパスしないといけないのが手間なのと、ワンオフで問題ないのでコストが見合わないとの判断です。
ここでも手間を省くために以下のアプローチを採用しました。
- Google Chromeで楽天トラベルへアクセスし、extensionで必要なデータだけ抽出する
- Google SpreadSheetへ入力して、データの加工及び補足情報の追加はGoogle SpreadSheet側に任せる
１のデータ抽出はScraperを利用しました。
Scraperを利用すれば、認証後のページでもちょこっとしたスクレイピングは難なくこなせます。
使い方は、抜き出したいページの要素をXPathで指定するだけです。
こんなページを
こんな感じで抜けます。
指定するXPath自体も Chromeのデベロッパーツールで調べられます。
前回の記事でも書きましたが、Chromeはこういう所でもやっぱり便利です。
２のデータ加工はスプレッドシートの関数で対応出来ます。
収集した予約履歴ではそのままではGoogle SpreadSheet側に正しい日付として判断されないので、そちらの加工等にも利用しました。
続いて補足情報の追加は IMPORTXML というExcelにはない便利な関数を利用します。
=IMPORTXML("http://techlog.voyagegroup.com/entry/2016/07/25/080000", "//title")
とすると
モバイルファーストなサービス開発におけるDockerの活用術 - VOYAGE GROUP techlog
指定したURLのタイトルを取得できたりします。
こちらの関数を利用してデータ加工で抜き出した施設IDをRakuten WEB SERVICEのREST API のエンドポイントURLを組み立てて呼び出します。
レスポンスをXML形式で取得できるのでXPathで必要な項目のみを抽出します。
同じ施設IDが複数存在した場合を考慮して、ユニークな施設IDのみを別シートにして IMPORTXML で補足情報を読み込んでおいて、実際のデータは VLOOKUP で補足情報を関連付けしておきました。
その他、チェックイン回数など事前集計できるものについては集計関数で計算を行っておきました。
最終的にはこんな感じのデータを作成しました。
フロント周り
フロント周りは日付範囲のスライダー（jQRangeSlider）を利用したかったので JQueryを採用しました。
JQueryでゴリゴリとバックエンドのAPIを呼び出しして地図表示を行っています。
GoogleSpreadsheetにはデータアクセス用のAPIが幾つか用意されていますが、今回はSQLライクに参照ができる Google Visualization API を利用しました。
var query = new google.visualization.Query('//spreadsheets.google.com/tq?key=' + key + '&pub=1'); query.setQuery('SELECT B, E, MAX(N), MAX(O), SUM(M), COUNT(F) WHERE ((G >= ' + min_serial + ' AND G <= ' + max_serial + ') OR (J >= ' + min_serial + ' AND J <= ' + max_serial + ')) GROUP BY B, E ORDER BY MAX(O) DESC');
上記がクエリ発行部分になります。
基本的な選択、集計、ソートを行うことができます。
ただやはり複雑なサブクエリの発行や複数シートを跨るクエリ発行までは行えないので、一工夫が必要となります。
上述のデータベース側の対応で記載したとおり、検索やデータ出力し易い様にVLOOKUP関数等でデータを加工しておくことでカバーが出来るかと思います。
今回の実装例では、Google SpreadSheetの関数とGoogle Visualization APIのクエリを組み合わせを行って
- 全期間でのチェックイン回数（滞在期間）
- 対象期間でのチェックイン回数（滞在期間）
- 対象期間内でのチェックイン回数順位
を表示させることを実現しています。
施設名と住所の一覧をGoogle Visualization APIから取得した後の地図（住所のピン）の表示はGoogle Map APIを利用しています。
住所のピン表示は住所から緯度経度をGeocoding APIで求めてから行っています。 *2
ピンをクリックして施設名のリンク表示する際もRakuten WEB SERVICEから情報を取得して行っています。
データ件数が多いので、そのままAPIを呼び出すと、 query over limit に引っかかるので２つの対策を行っています。
- APIをレスポンスをキャッシュする
- キャッシュヒットミスした場合にWait追加
レスポンスキャッシュはWebStorageを利用しました。
KeyValueでキャッシュすることができますので、APIの検索キーをハッシュ化してKey指定、 value はAPIのレスポンスのJSONを stringify して格納しました。
Javascript でwait処理をUIをロックさせない方法で実装するのは悩んだのですが、APIの呼び出し中の回数をカウントして、閾値を超えた場合にAPIを遅延実行する秒数を調整する方法を取りました。
ここら辺の query over limit を超えない範囲で、うまくAPIの並列性を保つ方法をもしご存知の方がいらっしゃいましたら、 @katzchum宛 もしくはブクマコメで指摘して頂けると助かります。
感想など
- 課題・反省点
jsでAPIをごりごり呼び出ししているのでDeferredの嵐になってしまった。
もう少しスッキリかけると良いかな〜と。 - 得られたもの
Google SpreadSheetを使えたのは、色々学びがありました。
Google SpreadSheet自体でスクレイピングができたのは嬉しい発見でした。
今回は利用しませんでしたが、Spread Sheet APIでデータ更新を行えばバックエンドサービスとして色々広がるなと感じました。
又、Spreadsheetに入力していたデータをSQLで参照させるのはMicrosoft Accessっぽい感じがして面白かったです。
今回の様な簡単なシステムでサーバーを調達するまでもないものにはフィットするのでは？と感じました。 - LTをやってみた感想
ランキングも発表したのですが、Slackのonairチャネルで先読みされて面白かったです。 皆 AJITING大好き。橙を根城にしている90年台のネタが通じる素敵なおじ様猛者達がいることを再確認できました。
明日のエントリもお楽しみに！
