自分のはてなブログの投稿状況データをPowerBIで可視化する：CData XML ODBC Driver
先日公開した以下の記事で書いたのですが、自分のはてなBlogでどんな投稿をしているかPowerBIで可視化してみました。
最終的にできあがったのは以下のようなレポートです。
この記事ではこのようなレポートを作成するためのデータ取得方法について解説していきます。
その前にちょっとAPIの解説
はてなBlogにはいくつか外部からはてなBlogにアクセスするためのAPIが提供されています。
- Atom https://help.hatenablog.com/entry/feed
- RSS https://help.hatenablog.com/entry/feed
- AtomPub http://developer.hatena.ne.jp/ja/documents/blog/apis/atom
- oEmbed API http://developer.hatena.ne.jp/ja/documents/blog/apis/oembed
最初２つのAtomやRSSはおそらく使ったことがある方も多いと思いますが、Blogの更新状況を取得するためのAPIプロトコルです。
AtomPubはAtomのように外部からBlog記事を参照できる機能はもちろんですが、記事の登録や更新にも対応しているのがポイントとなるAPIです。
最後のoEmbed APIはこちらの解説記事がわかりやすいかと思いますが、名前の通り外部コンテンツへの埋め込みおよびその記事の参照用に特化したプロトコルです。
今回APIとして必要な要件は、過去の全記事に関する情報が参照でき、かつカテゴリ情報も取得する必要がありました。
そして、実はその要件を満たしたAPIは「AtomPub」一択です。
前２つのAtomとRSSは参照用としては優れているのですが、最新の記事参照のみに特化しているため、ページネーション、つまり過去記事の参照ができません。
oEmdedは参照するためにどの記事を参照したいか、といったURLが必要となるため、言わずもがな。
AtomPubはページネーションをサポートし、カテゴリも取得できるので、今回のようなユースケースにはうってつけのAPIとなっていました。
実装方法
それでは実際に実現する方法なのですが、この記事でははてなBlogデータをAtomPub経由で取得するためのコネクタを使うので、特にプログラミングの要素は出てきません。
なので必要なものは、以下の「CData XML ODBC Driver」と
XML ODBC Driver - ODBC | ODBC Drivers | ODBC Connection | ODBC API
XML ODBC Driverを通じてはてなBlog AtomPubに接続できるようにするための設定ファイルだけです。
あとは分析用に Microsoft Power BIを使いました。Tableauなどでも同じ用に実施できると思います。
API Keyと接続URLの取得方法
それでは実際にデータの取得を行っていきたいと思いますが、AtomPub APIは接続するために「接続用URL」と「API Key」が必要となります。
はてなBlogの管理画面から「設定」→「詳細設定」に移動
AtomPubにルートエンドポイントとAPIキーの情報があるので、これを利用します。
設定ファイルをダウンロード
次に接続用の設定ファイルをダウンロードします。通常設定ファイルは「CData XML Driver」用で都度カスタマイズする必要があるのですが、今回は私が作成してしまっているので、以下のURLからダウンロードして利用できます。
上記URLから「HatenaBlogFeeds.rsd」というファイルをダウンロードし、任意のフォルダに配置（例：C:\CData_REST\Hatena）します。
CData XML ODBC Driverのインストールとセットアップ
続いて「CData XML ODBC Driver」をインストールします。有償の製品ですが、30日間のトライアルバージョンがあるので、今回はそれを利用します。
以下のURLから評価版をダウンロードし
http://www.cdata.com/jp/drivers/xml/odbc/
ダウンロードしたexeファイルを実行して、セットアップを行います。
セットアップ完了後、DSN構成画面が表示されるので、以下のように必要な情報を入力します。
- URI：管理画面から入手したAtomPub用URLの末尾にentryを付与したものを入力（例：https://blog.hatena.ne.jp/sugimomoto/kageura.hatenadiary.jp/atom/entry）
- Auth Scheme：Basic
- Password：管理画面から入手したAtomPub用API Keyを入力
- User：はてなBlogのログインユーザーIDを指定
- Location：設定ファイルのフォルダパスを指定（例：C:\CData_REST\Hatena）
以上で下準備は完了です。
PowerBI からはてなBlog AtomPub APIに接続
それではPowerBIに はてなBlogのデータをロードしてみましょう。
PowerBIを立ち上げて、「データを取得」をクリックし
「ODBC」接続を選択します。
先程設定したDSN名を選択して「OK」をクリックすると
以下のようにはてなBlogのデータがフラット化されて取得できるテーブルが選択できるようになっています。これを読み込みます。
以下のような感じでデータが取得されました。設定ファイルが自動的にAtomPubのページネーションもするようになっているので、過去の全記事を取得してくるようになっています。
あとはこのデータを使って、PowerBIの機能でビジュアライズするだけです。
その他：カテゴリの取り方
ちなみに、デフォルト状態のカテゴリ情報は以下のようにカンマ区切りになっているのですが、
これだとリレーショナルなデータになっていないので、うまく可視化できない部分が出てきますよね？
これを以下のようにBlogIDとリレーションされた形式にする方法もあります。
方法は簡単で、まず元になっているクエリを複製し、適当に「Category」といった名前に変更、IdとCategoryTerm以外の列を削除します。
次に「列の分割」から「区切り記号による分割」を選択し
「コンマ」の区切りごとで列を分割します。
以下のようなデータができあがるので、この項目を選択して、「列のピボット解除」を実行します。
すると以下のようにIDに各カテゴリがリレーションされた形のデータができあがります。「属性」列は最終的に消してしまっても大丈夫です。
