【Sisense Connector】Zoom APIを使ってSisenseからCData API Driverで接続してみた

シャローム！Sato-Gです。
テレワークになってからというもの、すっかり定着してしまった感のあるZoom。
もうこれ無しでは仕事にならない。

僕は3月にテレワークに入り、未だにテレワークなのだが、皆さんはテレワークをどう思っているのかな？
働いているところが見えないというのは気楽なようで全然気楽じゃない。
こっちが忙しくても、その空気を読んでもらえないから、Slackで「今、Zoomいいですか？」と来る。
「じゃあ、30分後で」と答える。
そしてどんどんZoomで予定が詰まっていき、仕事に集中する時間が減って来る。
こんな風にコミュニケーションツールは便利すぎても困ってしまうもので、例えばSlackに「既読」とかあったら絶対使いたくない！
と思うのは僕だけじゃないと思う。

...といいことばかりじゃないテレワークなのだが、今回はZoom APIを使って、CData API DriverでSisenseにZoomの利用データを取り込んでみるーという企画です！

1. Zoom APIについて

Zoomの大半のユーザはFreeプランだと思うが、有償版としてPro, Business, Education, Enterpriseなどのプランがある。
Zoom APIはプランに応じて制限があるのだが、なんとFreeプランでもAPIを利用できるらしい。
制限内容はここでは割愛するけど、ZoomのMTGが増加しているとはいえ、ビデオミーティングの発言をデータ化しない限り、データ量は限られているから、制限があったとしても実用に耐えうると思う。
今回は普段使っているProのアカウントを使って試してみる。

2. CData API Driverの接続環境設定

CData API Driverは個別のDriver製品と異なり、ドライバのコアの部分を汎用ドライバ化した上で、個別のデータソースの事前定義をプロファイルとして提供する製品群だ。
プロファイルの数はなんと100を超える！

これだけ繋がるんだね。よく作ったわ。この製品群見ても僕は半分も知らないもん。
で、このプロファイルの中にひっそりと"Zoom"があるのだ。

2.1 CData API Driverのセットアップ

CData API DriverとプロファイルはAPI Driver のダウンロードから行える。
まず、API Driverのダウンロード。

API Driverは2020年9月現在、ODBC, JDBC, ADO.NET, SSIS用, BizTalk用, Excel用, FireDAC用が準備されている。
今回はJDBC版を選択してダウンロードする。ダウンロード後にsetup.jarをダブルクリックしインストールした後、例によってファイルを次のように配置する。

セットアップ後のフォルダ（C:\Program Files\CData\CData JDBC Driver for API 2020J）のlibフォルダ下のファイルをまるごとSisenseのjdbcdriversフォルダにコピーしておく（CDataApiDriverフォルダは作成してね）。さらにCDataApiDriverの下にCDataAPIsフォルダも作成しておく。

次に先程と同じ API Driver のダウンロードからZoom用のプロファイルをダウンロードし、上で作成したAPIsフォルダ(C:\ProgramData\Sisense\DataConnectors\jdbcdrivers\CDataApiDriver\CDataAPIs)内に移動する。

以上でコネクター側は準備OK

2.2 Zoom APIの設定

今回はZoom API用のアプリをOAuth接続で作成する手順を利用する。
ここは意味を理解していないと結構つまずくと思う。僕はここ結構試行錯誤したんだよね。
まずは、Zoom App Marketplace を開き、サインアップしておこう。

サインインができたら、画面右上の[Develop]ドロップダウンから[Build App]を選択する。

アプリの接続タイプを指定する。ここでは「OAuth」を選択する。

アプリの名前とタイプを設定する。アプリの名前は任意、タイプはUser-managed appとする。尚、Marketplaceで公開はしないので、トグルスイッチはOFF。

Credentialの設定を行う。Client IDとClient Secretが自動設定される。この２つがOAuth認証で利用されることになる。
Redirect URLはhttp://localhost:33333を指定する（このURLはWhite Listにも登録しておく必要がある。

次にInfomationの設定。
Short Description, Long Descriptionを入力する。ここはこのアプリに関する説明だ。
あと、このページで必須なのは、Company Name, Name, Email Addressの３つなので指定しておこう。

Feartureの設定では、Event Subscriptionは不要なのでOFFにする。

Scopeの設定に移る。ここではどんなデータをどう使いたいかを設定する。僕は
・Meeting
・Webinar
・Users
の３つを指定し、右側ではそれぞれについて全ての機能にチェックを入れた。

Scopeが設定されると以下の画面となるので[Continue]をクリックして次に進む。

最後にActivationの画面となる。ここで[Install]したくなるのだが、ここでは行わない（押しても接続エラーとなる）。

2.3 CData API Driverから接続

C:\ProgramData\Sisense\DataConnectors\jdbcdrivers\CDataApiDriverにインストールしたcdata.jdbc.api.jarをクリックし、Driver接続ツールを立ち上げる。接続プロパティで指定する必要がある項目は以下のとおり
・CallbackURL 　http://localhost:33333
・InitiateOAuth 　GETANDREFRESH
・Client ID  　　　App Merketplaceで指定されたClient ID
・Client Secret　 App Merketplaceで指定されたClient Secret
・Profile　　　　C:\ProgramData\Sisense\DataConnectors\jdbcdrivers\CDataApiDriver\CDataAPIs
以上の設定が終わったら、[接続]をクリックする。

Zoomへの接続が行われ、以下の画面が表示されるので[許可]ボタンを押して次に進み「OAuth authorization successful!」の画面になったら接続は成功。

因みに成功すると、C:\Users\\AppData\Roaming\CData\API Data Providerに以下のようのOAuthSettings.txtという接続設定ファイルが生成される。
これができていないと接続はできないので必ず確認しておこう。

3. Sisenseから接続

3.1 データモデリング

CData API Driverでの接続が成功したので、Sisenseから接続してみる。
Generic JDBC Driverにて以下の接続設定を行う。

・Connection String
jdbc:api:Profile="C:\ProgramData\Sisense\DataConnectors\jdbcdrivers\CDataApiDriver\CDataAPIs\zoom.apip";Callback URL="http://localhost:33333";InitiateOAuth="GETANDREFRESH";OAuthClientId=xxxxxxxxxx;OAuthClientSecret=zzzzzzzzzz;OAuthSettingsLocation=C:\Users\Administrator\AppData\Roaming\CData\API Data Provider\OAuthSettings.txt;
・JDBC JARs Folder
CDataApiDriver
・Drivers Class name
cdata.jdbc.api.APIDriver

接続がうまくいくと下記のようにテーブルが表示されるので、試しにUsersテーブルを指定してみる。

さらにMeetingsテーブルも同様に追加してみた。
...と簡単に書くわけにはいかない。
実はZoom APIの制限事項として、各テーブルをそのままSELECTできるのはごく限られたテーブルだけなのである。
よって、Meetingsテーブルを指定する時は、SQLを編集してWHERE句で絞り込む必要があるのだ。
SQLを知っている人からすれば「そんなのIN述語で相関サブクエリでSELECTすればいいじゃん！」と思うのだが（僕もそうしてみた）、残念ながら"="で指定してあげないとエラーとなる。もちろんLIKEも使えない。
何か抜け道はないのかなあと思って、CDataさんに聞いてみたんだけど「API仕様なので」とのことだった。残念...

”APIの中にはWHERE句で明示的に指定しないと取得できないケースがある”

というか、「ある」というより「多い」ということなんだと思う。
そりゃあ、Zoom側からすれば、全世界からAPI使って重たいクエリ投げられるとか勘弁してほしいよね。
もともとAPIは外部アプリを作るために準備されているものなので、BI用途で全レコード持ってくることなど想定はしていないんだろう。
というわけで、Meetingsテーブルは

SELECT * FROM `zoom`.`Meetings`
WHERE UserId  = 'zzzzzzzzzz'

というカスタムSQLで取得した（zzzzzzzzzzはUsersテーブルのIdの値）。
Usersテーブルとのリレーションシップは
Users.id - Meetings.Userid
という設定だ。

3.2 ダッシュボードで確認

これをダッシュボード上でテーブル表示するとこんな感じ。

なんだかな、使えそうで使えないな。
SisenseはSELECTはできるけど、カーソルを使ったLOOP処理などはできない。変数も使えない。
サブクエリはたくさん書けるけど、あくまでSELECT一本なので...

4. まとめ

以上、Zoom APIを使って、CData API DriverでSisenseにZoomの利用データを取り込んでみた。
結構面倒くさいけど、MarketplaceでのAPIの設定さえうまくできれば、接続はうまくいくはず。
ただ、API制限でWHERE句で絞り込まないと取得できないテーブルが多いので、スクリプト環境のないSisenseには厳しい。
ETLツールを使用するか、スクラッチでアプリを作成するなど別のアプローチが必要となると思う。
つまり、ETLやアプリでLOOP処理してデータベースに一旦取り込み、Sisenseからはデータベースに接続して取得するという流れ...
うーーん、これはSisenseの弱点かな。他のBI製品ならスクリプトで対応できる製品もあるからね。
PL/SQLみたいな拡張言語があれば容易にLOOP処理できるのになあ。ここは全然コーディングしてもいいので！

久々ですが...

【Sisense様への要望】データモデリングの機能拡張
データモデリングの際、SELECTだけではなく、
LOOP処理できるような拡張機能を是非作って下さい

ただ、データ基盤というのは役割分担なので、DWHはなくていいとは僕は思っていません。
全体最適...です。

ではまた！