kintoneデータをCData ODBC Driverを使用して主要BIツールから接続するときのTips集

はじめに

本記事では、kintoneのアプリ内データを、CData ODBC Drivers製品を使用して主要なBIツールから利用する場合にサポートセンターに多くお問い合わせ頂く内容を纏めました。CData ODBC Drivers製品のインストールおよび接続設定、および、各BIツールからの基本的な接続手順は以下のリンクからご覧いただけますでご確認ください。

• kintone データをTableau でビジュアライズ by CData ODBC Drivers
• kintone内のデータをODBCで繋いてQlikViewでレポーティング
• CData を使ってkintone アプリのデータをMicrosoft Power BI でビジュアライズ
• kintoneを多様なツールでつなぐには？

Tips集

どういう仕組みで動作しているの？

主要なBIツールには、RDBMSへ接続してSQLでクエリーするODBC/JDBCといったデータソースへの接続インタフェースを持っています。CData kintone ODBC Driverは、ODBC規格でkintoneにアクセス出来るデータ接続ライブラリです。BIツールからアドホックに本ODBCドライバを呼び出すことでkintone内の最新のデータをBIツールで表示することが出来るようになります。BIツールからSQL(SELECT文)で呼ばれると、 CData kintone ODBC Driverは、SQLをkintoneのREST APIにパース（変換）して、kintoneのREST APIの以下のエンドポイントに対してGetメソッドでHTTPリクエストしています。レスポンスはJSONデータで戻ってきますので、JSONをBIツールで二次元表として扱えるようにRecordsetとして変換してBIツールに渡します。

• メターデータ（アプリ、および、データ項目の一覧および属性）の取得
• kintone developer network - アプリ情報の取得
• データの取得
• kintone developer network - レコードの取得（GET）

レコード取得時の制限は？

kintoneのREST APIは、一度に取得出来るレコード数は500までという制約があります。 CData kintone Driverでは、本API制約をユーザが意識しなくてもよいように、500件以上のデータの取得リクエストがあった場合には、内部的に500件ずつFetchする仕組みが組み込まれていますので一度のユーザリクエスト操作で500件以上のレコードを取得することが出来ます。

認証は？

kintone REST APIのパスワード認証を利用しています。

kintone developer network - kintone REST APIの共通仕様

APIトークン認証、および、セッション認証は対応していません。kintoneのAPIトークンは、アプリ毎に発行されるので、一つのAPIトークンで複数のアプリにアクセスすることは出来ません。もし、ご利用されたい方がいらっしゃいましたらこちらのフォームから機能要求リクエストください。

セキュアアクセスは？

Basic認証、および、クライアント証明書に対応しています。

Basic認証

ODBCのDSNの「Authentication」内の以下の項目を設定ください。

• Basic Auth User : Basic認証のユーザID
• Basic Auth Password : Basic認証のパスワード

クライアント証明書

ODBCのDSNの「SSL」Authentication内の以下の項目を設定ください。

• SSL Client Cert : .pfxファイルのパスを指定
• SSL Client Cert　Password : .pfxファイルのパスワード
• SSL Client Cert　　Subject : 「*」
• SSL Client Cert　　Type : 「PFXFILE」

社外ネットワークにアクセスするのにプロキシサーバ経由が必要

デフォルトではWindowsOS内（ブラウザ設定から設定可能で値はレジストリに保持されている）のプロキシ設定値を利用する設定となっています。マニュアルで変更したい場合は、ODBCのDSNの「Proxy」内の「Proxy Auto Ditect」を「False」に変更して、「Proxy Server」および認証が必要であれば関連項目を設定ください。

CData製品マニュアル - Proxy Auto Detect

サブテーブルってどうなるの？

「アプリ名_サブテーブル名」として別テーブルとして扱えます。以下、Tableauでのテーブル一覧の例です。

親テーブルとは、１対多のカーディナリティとなりますので、次節のように親テーブルとのテーブル（アプリ本体）とのテーブル結合も可能です。

アプリ（テーブル）間の結合

結構驚かれることが多いのですが全てのアプリ（テーブル）間のJOINが可能です。内部結合、外部結合に加えてUNIONも利用出来ます。

Tableau Desctopでのテーブル結合の定義

生成されたSQL

SELECT "案件情報 (Kintone.案件情報)"."AppId" AS "AppId",
  "案件情報_商談履歴 (Kintone.案件情報_商談履歴)"."AppId" AS "AppId__X_________",
  "案件情報_商談履歴 (Kintone.案件情報_商談履歴)"."Id" AS "Id",
  "案件情報 (Kintone.案件情報)"."RecordId" AS "RecordId",
  "案件情報 (Kintone.案件情報)"."Revision" AS "Revision",
  "案件情報_商談履歴 (Kintone.案件情報_商談履歴)"."Revision" AS "Revision__X_________",
  "案件情報 (Kintone.案件情報)"."詳細" AS "X_",
  "案件情報_商談履歴 (Kintone.案件情報_商談履歴)"."資料Aggregate" AS "X_Aggregate",
  "案件情報 (Kintone.案件情報)"."顧客名" AS "X__",
  "案件情報 (Kintone.案件情報)"."部署名" AS "X__1",
  "案件情報 (Kintone.案件情報)"."案件名" AS "X__2",
  "案件情報_商談履歴 (Kintone.案件情報_商談履歴)"."商談日" AS "X__3",
  "案件情報 (Kintone.案件情報)"."受注確度" AS "X___",
  "案件情報 (Kintone.案件情報)"."受注金額" AS "X___1",
  "案件情報_商談履歴 (Kintone.案件情報_商談履歴)"."商談内容" AS "X___2",
  "案件情報_商談履歴 (Kintone.案件情報_商談履歴)"."案件情報Id" AS "X___Id",
  "案件情報 (Kintone.案件情報)"."ご担当者名" AS "X____",
  "案件情報 (Kintone.案件情報)"."受注予定日" AS "X____1",
  "案件情報 (Kintone.案件情報)"."営業担当者Aggregate" AS "X____Aggregate"
FROM "Kintone"."案件情報" "案件情報 (Kintone.案件情報)"
  LEFT JOIN "Kintone"."案件情報_商談履歴" "案件情報_商談履歴 (Kintone.案件情報_商談履歴)" ON ("案件情報 (Kintone.案件情報)"."RecordId" = "案件情報_商談履歴 (Kintone.案件情報_商談履歴)"."案件情報Id")

どんなSQLが書けるの？

ANSIの規格SQL-92レベルをサポートしています。JOINに加えてWHERE句によるレコードの絞り込み、ORDER BYのソート、GROUP BYによる集約といった基本的な集合理論の操作に加えて、文字列・数値・日時関数が揃っています。詳しい構文規約は下記の製品マニュアルをご覧ください。

CData ODBC Driver for kintone 2017J 製品マニュアル - SELECT ステートメント

レコードの添付ファイルは？

CData ODBC Driver for kintone自体は、ストアドプロシージャで添付ファイルのアップロード、および、ダウンロードが可能です。

• CData製品マニュアル - ファイルダウンロード
• CData製品マニュアル - ファイルアップロード

kintoneのWebAPI的には下記のAPIを利用しています。

• kintone developer network - ファイルアップロード
• kintone developer network - ファイルダウンロードで必須となる2つの手順

一般的なBIツールの標準機能だと上記のストアドプロシージャを呼び出すのは難しいです。 別途、ストアドプロシージャを呼び出せるツールやプログラムが必要で連携する必要があります。

Mac版は？

上記でご紹介しているBIツールの中ではTableauDesktopはMacOS版があります。2017/9にリリースされたTableauDesktop10.4では、Mac版にODBC接続が追加されました。

Tableau Desctop Help - Other Databases (ODBC)

CData Software（US本社）では、2017/10に90を超えるデータソース向けのMac版ODBCDriverをリリースしました。

CData Software Blog - ODBC Drivers for Mac and Linux Released

現在(2017/12)、CData Software Japanにて、kintone版含む日本語版のリリースの準備をしています。準備が出来ましたら、別途Qiita記事にまとめます。

まとめ

kintoneは、業務ユーザでもアプリをノンプログラミングで作成してデータを一元管理できる便利なサービスです。このkintoneに入っているデータは企業にとって宝の山ですのでBIツールなどからkintone内のデータを分析するニーズは弊社サポートにも多くお問い合わせいただくことも多いです。CData ODBC Driver for kintoneは３０日間の無償評価版もございますので是非こちらのサイトからダウンロードしてお試しください。