SQL Server にkintone リンクサーバーを作成、PowerBIやTableau からデータを参照

はじめに

SQL Server のリンクサーバー機能って便利ですよね。
SQL Server のインターフェースで他のDB とかODBC データソースを叩きにいけます。

この記事ではkintone のデータをSQL Server のリンクサーバーとして設定します。
CData SQL Broker というサーバーツールを使って、ODBC for kintone DSN をSQL Server のリンクサーバーとして設定できます。

利用ケース：
・SQL Server に接続しているアプリケーションでkintone データを利用したいがデータ接続を増やしたくない
・kintone データをSQL Server のデータや他のリンクサーバーのデータをJOIN などして使いたい
・利用したいミドルウェア・BI ツールなどにODBC やJDBC の汎用のインターフェースがないが、SQL Server には接続できる

環境・利用アプリ

・Windows 10
・SQL Server 2017 + SQL Server Management Studio
・kintone （評価版あり）kintone評価版アカウント取得、および、サンプルアプリケーションの作成方法
・CData ODBC Driver for kintone（評価版あり）
・CData SQL Broker

ODBC Driver for kintone のインストールとkintone 接続

ODBC Driver for kintone のインストール

https://www.cdata.com/jp/drivers/kintone/odbc/ からODBC Driver をダウンロードします。30日の無償版があります。

.exe ファイルをクリックして、インストーラーを起動し、インストールを行います。

kintone Driver のDSN 設定

インストールを完了するとkintone Driver のDSN 構成画面が開きます。

URL、User、Password はkintone に接続するためのプロパティです。

プロパティ	内容
Url	kintone のURL
User	kintone のユーザー名
Password	kintone のパスワード

SQL Broker にkintone ODBC DSN を設定

CData ODBC Drivers にSQL Broker が内包されています。
「スタート」画面から「SQL Broker」を起動します。
起動すると右下のインジケータにSQL Broker のロゴが表示されます。

アプリケーションコンソールが開かない場合には、「Open Application」をクリックしてください。

ODBC データソース設定

アプリケーションコンソールの「Service」タブを開いて、「Add」をクリックして、新しいODBC データソースを設定します。

kintone ODBC の設定を行います。

項目	内容
Service Name	任意
リモーティングDB 選択	SQL Server を選択
Data Source	ドロップダウンで選択
Port	使用していないポートを選択

「OK」を押して、サービス設定します。その後「Save Changes」で設定を保存します。

ユーザー登録

次に「Users」タブ→「Add」からユーザーを登録します。
ユーザー毎にFull アクセスか、Readonly か、None の権限を選択できます。
複数のODBC データソースがある場合には、データソース毎に権限の設定が可能です。

「OK」を押して、ユーザー登録を完了します。
その後「Save Changes」で設定を保存します。

SQL Broker の「Service」タブで「Start」ボタンを押して、サービスを起動します。サービスの左側の〇が緑色になれば、サービスは起動中です。

SQL Server でのリンクサーバー設定

SQL Server Management Studio (SSMS) を開きます。
オブジェクトエクスプローラーの「サーバーオブジェクト」で「リンクサーバー」を右クリックして、「新しいリンクサーバー」をクリックします。

新しいリンクサーバーの設定画面の「全般」では、以下を入力します。

項目	内容
サーバー種類	その他データソース
プロバイダー	SQL Server Native _Client 11.0
データソース	この例の場合、localhost, 1433 を入力
カタログ	ODBC DSN 名を入力

また、「セキュリティ」では、「このセキュリティコンテキスト」を選択して、リモートログインにSQL Broker にユーザー設定したUser 名とPassword を入力します。

これで、リンクサーバーの設定は終わりですので、「OK」を押して設定を保存します。

SSMS でデータを見てみる

SSMS のオブジェクトエクスプローラーのリンクサーバー下に「KINTONE」が作成され、「テーブル」下にkintone のデータがアプリ単位でテーブルが生成されます。

新しいクエリを選択し、kintone データを取得してみます。

SELECT * from [KINTONE（リンクサーバー名）].[CData Kintone Source Sys（ODBC DSN 名）].[Kintone].[テーブル名]

このように、API のリクエストではなく通常のSQL 構文でデータを扱うことが可能になっています。

Visual Studio からkintone リンクサーバーを使う

Visual Studio のサーバーエクスプローラーの「データ接続」を右クリックし、「接続の追加」をクリックして、データソースの選択画面を開きます。データソースの選択画面でデータソースを「Microsoft SQL Server 」に選択して、「続行」を押します。

「接続の追加」画面で、以下を入力します。

項目	内容
サーバー名	SQL Server のサーバー名
認証	任意の認証方式
データベース名の選択または入力	※master など

※リンクサーバーを直接データベース名として入力することはできないようです。なので上位のデータベース名で設定しておきます。

「テスト接続」をして大丈夫であれば、「OK」を押して設定を完了させます。

「新しいクエリ」で、SQL でSELECT 文を書いてデータを取得してみます。

SELECT * from [KINTONE（リンクサーバー名）].[CData Kintone Source Sys（ODBC DSN 名）].[Kintone].[テーブル名]


このように、通常のSQL Server のリンクサーバーと同じようにVisual Studio 内でkintone データを扱うことが可能になります。他のIDE でもSQL Server にアクセスする方法でkintone データにSQL でアクセスが可能になります。

複数データソースのJOIN などを行う場合には大変便利です。

Power BI からkintone リンクサーバーを使う

データビジュアライゼーションツールのPower BI からSQL Server インターフェースでkintone リンクサーバーのデータを取得することが可能です。

「データを取得」から「SQL Server 」を選択します。

項目	内容
データベース	master など（リンクサーバーは設定できない）
詳細設定オプション	展開してSQL 記述

SELECT * from [KINTONE（リンクサーバー名）].[CData Kintone Source Sys（ODBC DSN 名）].[Kintone].[テーブル名]

kintone データがプレビューで確認できます。「読み込み」を押します。

あとは、通常のDB のテーブルデータと同じようにビジュアライズを作ります。

Tableau からkintone リンクサーバーを使う

同様に人気のあるデータビジュアライゼーションツールのTableau からもkintone リンクサーバーデータを使ってみましょう。

接続から「詳細」→「Microsoft SQL Server」を選択して、リンクサーバーを含むDB に設定します。「サインイン」で設定を完了します。

項目	内容
サーバー	SQL Server のサーバー名
データベース	入力不要
認証	任意の認証方式

データソース画面で、「データベース」からドロップダウンでmaster を選択。
リンクテーブルはテーブルとして表示されないようなので、直接「新しいカスタムSQL」でSQL を書きます。

SELECT * from [KINTONE（リンクサーバー名）].[CData Kintone Source Sys（ODBC DSN 名）].[Kintone].[テーブル名]

クエリを実行すると、データが取得されます。

あとは、通常のSQL Server のテーブルデータと同じように、ビジュアライズが可能です。

以上、CData SQL Broker を使った、kintone リンクサーバーの設定方法と使い方でした。