CData Software Blog

クラウド連携のCData Software の技術ブログです。

Power BI から Cosmos DB Table API にSQLクエリを発行する:CData Power BI Cosmos DB Connector

こんにちは。CData Software Japan リードエンジニアの杉本です。

最近個人的なプロジェクトとして、テスラのデータをAPI経由で収集するのにハマってます。

kageura.hatenadiary.jp

kageura.hatenadiary.jp

目下の目標は1日中テスラの状態をAPI経由で監視して自分のテスラ利用実績を可視化することです。

そんな目標へのアプローチが最近ようやく定まってきて、以下のようなアーキテクチャで実装を考えていました。Azure のローコード・iPaaSであるLogicAppsを利用して定期的にTeslaのデータを取得し、Cosmos DB Table APIへ格納。最終的にPower BIで可視化する、という方法です。

f:id:sugimomoto:20201229120445p:plain

ただ、このアプローチで一点不満がありました。

Power BI は Microsoft製のBIツールなので、もちろんCosmos DBへの接続をサポートしています。

https://docs.microsoft.com/ja-jp/azure/cosmos-db/powerbi-visualize

f:id:sugimomoto:20201229120455p:plain

しかしながら、デフォルトですとデータセットをそのままプリロードし

f:id:sugimomoto:20201229120500p:plain

型の調整やカラムの取捨選択を一つ一つ実施しなければいけません。(Power Queryをガリゴリ書きなさいという話なのかもですが)

f:id:sugimomoto:20201229120507p:plain

というか、そもそも、Cosmos DB Table APISQLクエリをサポートしているので、

https://docs.microsoft.com/ja-jp/azure/cosmos-db/sql-query-getting-started

f:id:sugimomoto:20201229120514p:plain

以下のようにCosmos DB データエクスプローラー上で実行できるSQLクエリのように、リクエストさせてほしいという思いもありました。

f:id:sugimomoto:20201229120522p:plain

そこで、手前味噌ですが、CData Cosmos DB Power BI Connectorを使ってこのSQLによるPower BIからのリクエスト方法を試したいと思います。

https://www.cdata.com/jp/drivers/cosmosdb/powerbi/

f:id:sugimomoto:20201229120528p:plain

使い方

まず、以下のURLからトライアル版をダウンロードして、対象のマシンにセットアップします。

https://www.cdata.com/jp/drivers/cosmosdb/powerbi/

f:id:sugimomoto:20201229120536p:plain

セットアップ完了後、以下のように接続画面が立ち上がるので必要なプロパティを設定します。

プロパティ名 備考
Account Endpoint https://XXXXX.documents.azure.com:443/ Cosmos DB のURIを指定します。
Account Key XXXXXXXXXX== Cosmos DBのキーを入力します。

f:id:sugimomoto:20201229120542p:plain

これで接続のテストが通れば設定は完了です。

f:id:sugimomoto:20201229120547p:plain

Power BI から Cosmos DB に接続する

それでは、Power BI から CData Connector 経由で Cosmos DBに接続してみたいと思います。

通常通り、Power BIを立ち上げて、「データを取得」をクリックし

f:id:sugimomoto:20201229120551p:plain

一覧から、「CData Cosmos DB」を選択します。

f:id:sugimomoto:20201229120555p:plain

先程のData Source Nameを入力すれば、これで接続が可能です。

とりあえず素直に「OK」をクリックすると

f:id:sugimomoto:20201229120609p:plain

以下のようにテーブルデータにアクセスできます。

f:id:sugimomoto:20201229120615p:plain

SQLを使ってアクセスする

次にSQLを使って、Cosmos DBにアクセスしてみます。

Cosmos DBのデータエクスプローラー上では以下のようなクエリで、時間ごとのステータス状態を取得しています。

SELECT c.datetime, c.state FROM VehicleData as c

f:id:sugimomoto:20201229120621p:plain

CData Connector では、以下のAdvanced Optionの項目にSQLを記述することで、Cosmos DBへSQLを使ってリクエストを行うことができます。

f:id:sugimomoto:20201229120629p:plain

以下のようにうまく取得できていますね。

f:id:sugimomoto:20201229120635p:plain

せっかくなので集計処理も試してみましょう。対象の日付の範囲で、バッテリーレベルのMAX・MINを取得するクエリを書いてみました。

SELECT 
Max(c.charge_state_battery_level) as maxbatterylevel,
Min(c.charge_state_battery_level) as minbatterylevel  
FROM VehicleData as c WHERE c.date = '2020-12-28'

f:id:sugimomoto:20201229120640p:plain

同じ用にSQLを貼り付けるだけです。

f:id:sugimomoto:20201229120645p:plain

集計結果が取得できました!

f:id:sugimomoto:20201229120651p:plain

補足

ちなみに、ここまで同じSQLでリクエストを実行してきましたが、厳密にはCosmos DBのSQLとCData Power BI ConnectorのSQLは規格が少し異なります。

CData Power BI Connector では以下の構文をサポートしているので、参考にしてみてください。

cdn.cdata.com