CData Software Blog

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

Cacco のデータベーススキーマ機能を使ってCData Driverからモデルを生成してみる:CData Dynamics 365 Sales ODBC Driver

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

「Cacco」というサービスはご存知でしょうか?

Caccoとは、フローチャートからワイヤーフレーム、プレゼン資料まで作れる「オンライン作図ツール」です。

cacoo.com

f:id:sugimomoto:20200502201337p:plain

作図ツールとしてもすごく使いやすいのですが、CaccoはAWSの構成図を自動生成できたりと、なかなか意欲的な機能が多いサービスです。

そんなCaccoで最近、以下の新機能を発表したというプレスリリースを見ました!

cacoo.com

ざっくり言うと、以下のようなデータベーススキーマを自動的に生成してくれる機能が追加されたというものです!

f:id:sugimomoto:20200502201324p:plain

使い方は簡単で、画面からCaccoデータベーススキーマ生成用のSQLを使用して、CSV保存したものを、アップロードするだけです。

基本的にはMySQLPostgreSQLが対象と書かれているのですが、以下のBlogでBigQueryから生成したりしているのも発見!

k1low.hatenablog.com

おおお!最終的にCSV出力すればいい、というわかりやすさから、汎用性が高い感じでいいですね!

とすると・・・

「CData Driverを使えば、Dynamics365やSalesforce、kintoneなど、200種類近くあるデータソースラインナップからも同様に生成できるのではないか?」

f:id:sugimomoto:20200502202325p:plain

と思いたち、早速試してみました!

www.cdata.com

f:id:sugimomoto:20200502201345p:plain

アプローチ

現在Coccoで使われているSQLは以下のような感じです。これをそのまま使えればいいのですが、これはMySQLPostgreSQLでサポートされている構文のため、そのままではCData DriverのODBCJDBCでは実行できません。

SELECT 
  'mysql' dbms,
  t.TABLE_SCHEMA,
  t.TABLE_NAME,
  c.COLUMN_NAME,
  c.ORDINAL_POSITION,
  c.DATA_TYPE,
  c.CHARACTER_MAXIMUM_LENGTH,
  n.CONSTRAINT_TYPE,
  k.REFERENCED_TABLE_SCHEMA,
  k.REFERENCED_TABLE_NAME,
  k.REFERENCED_COLUMN_NAME 
FROM INFORMATION_SCHEMA.TABLES t 
  LEFT JOIN INFORMATION_SCHEMA.COLUMNS c 
    ON t.TABLE_SCHEMA=c.TABLE_SCHEMA 
    AND t.TABLE_NAME=c.TABLE_NAME 
  LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 
    ON c.TABLE_SCHEMA=k.TABLE_SCHEMA 
    AND c.TABLE_NAME=k.TABLE_NAME 
    AND c.COLUMN_NAME=k.COLUMN_NAME 
  LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS n 
    ON k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA 
    AND k.CONSTRAINT_NAME=n.CONSTRAINT_NAME 
    AND k.TABLE_SCHEMA=n.TABLE_SCHEMA 
    AND k.TABLE_NAME=n.TABLE_NAME 
WHERE 
  t.TABLE_TYPE='BASE TABLE' 
  AND t.TABLE_SCHEMA=database()

なので、この構文をベースにして、CDataが提供する汎用的なシステムテーブルでクエリできる形式に変換してみました。

CData Driver ではシステムテーブル郡が以下のように定義されているので、これを置き換えていくイメージです。

cdn.cdata.com

最終的に出来上がったのは、以下のようなクエリです。

SELECT 
  'mysql' dbms,
  t.SchemaName TABLE_SCHEMA,
  t.TableName TABLE_NAME,
  c.ColumnName COLUMN_NAME,
  c.Ordinal ORDINAL_POSITION,
  c.DataTypeName DATA_TYPE,
  c.Length CHARACTER_MAXIMUM_LENGTH,
  CASE
    WHEN k.IsKey THEN 'PRIMARY KEY'
    WHEN k.IsForeignKey THEN 'FOREIGN KEY'
    ELSE null
  END CONSTRAINT_TYPE,
  k.ReferencedSchemaName REFERENCED_TABLE_SCHEMA,
  k.ReferencedTableName REFERENCED_TABLE_NAME,
  k.ReferencedColumnName REFERENCED_COLUMN_NAME
  
  FROM sys_tables t 
  
  LEFT JOIN sys_tablecolumns c ON t.SchemaName=c.SchemaName AND t.TableName=c.TableName 
  LEFT JOIN sys_keycolumns k  ON c.SchemaName=k.SchemaName AND c.TableName=k.TableName AND c.ColumnName=k.ColumnName

実際にDynamics365からテーブルスキーマを取得する

それでは実際に上記SQLを用いて、Caccoの取り込みCSVを作成してみます。

データソースはなんでもかまいませんが、今回はリレーションがわかりやすく、私自身も案件でこのような構成図の作成に度々苦労した経験がある Dynamics 365 Salesを選んでみました。ちなみに、PowerApps Common Data Service でもいけます。

利用するテクノロジーODBCを選んでいますが、JDBCでも同様に実行可能です。

https://www.cdata.com/jp/drivers/d365sales/odbc/

f:id:sugimomoto:20200502201355p:plain

このODBCドライバーをインストールし、DSNの接続構成を行います。Dynamics 365 の場合は、以下のように接続先となるOrganization Urlを入力して、接続のテストを実施してください。OAuthが働いて、ブラウザが立ち上がるので、ログインすればOKです。

f:id:sugimomoto:20200502201402p:plain

次にこのODBC DSNを実行してCSVを取得しますが、今回は手軽に再現しやすいようにPowerShellスクリプトを書きました。

以下のGithubで公開しています。

github.com

[void][System.Reflection.Assembly]::LoadWithPartialName("System.Data")

# 任意のDSNを指定
$connectionsString = "DSN=CData D365Sales Source"
$odbcCon = New-Object System.Data.Odbc.OdbcConnection($connectionsString)
$odbcCon.Open();

$odbcCmd = New-Object System.Data.Odbc.OdbcCommand
$odbcCmd.Connection = $odbcCon

# コマンド実行(SELECT)
$odbcCmd.CommandText = "
SELECT 
  'mysql' dbms,
  t.SchemaName TABLE_SCHEMA,
  t.TableName TABLE_NAME,
  c.ColumnName COLUMN_NAME,
  c.Ordinal ORDINAL_POSITION,
  c.DataTypeName DATA_TYPE,
  c.Length CHARACTER_MAXIMUM_LENGTH,
  CASE
    WHEN k.IsKey THEN 'PRIMARY KEY'
    WHEN k.IsForeignKey THEN 'FOREIGN KEY'
    ELSE null
  END CONSTRAINT_TYPE,
  k.ReferencedSchemaName REFERENCED_TABLE_SCHEMA,
  k.ReferencedTableName REFERENCED_TABLE_NAME,
  k.ReferencedColumnName REFERENCED_COLUMN_NAME
  
  FROM sys_tables t 
  
  LEFT JOIN sys_tablecolumns c ON t.SchemaName=c.SchemaName AND t.TableName=c.TableName 
  LEFT JOIN sys_keycolumns k  ON c.SchemaName=k.SchemaName AND c.TableName=k.TableName AND c.ColumnName=k.ColumnName
WHERE t.TableName IN ('accounts','contacts','opportunities');
"


#SQLを実行し結果をDataSetやDataTableに格納
$odbcDataAdapter = New-Object -TypeName System.Data.Odbc.OdbcDataAdapter($odbcCmd)
$dataSet = New-Object -TypeName System.Data.DataSet
$odbcDataAdapter.Fill($dataSet) > $null      # 実行結果を破棄する
 
#データセットのデータテーブルをCSV出力する
$dataSet.Tables[0] | export-csv export_csvFile.csv -notypeinformation -Encoding Default

# コマンドオブジェクト破棄
$odbcCmd.Dispose()

# DB切断
$odbcCon.Close()
$odbcCon.Dispose()

DSN名は各ドライバーに合わせて変更してください。

また、標準のDynamics 365 はエンティティが大量に存在するので、全部取り込もうとすると、かなりわかりづらくなります。そのため、以下のWhere句の部分で対象とするスキーマを限定するのがおすすめです。

WHERE t.TableName IN ('accounts','contacts','opportunities');

実行すると、以下のようなCSVファイルが得られます。

f:id:sugimomoto:20200502201410p:plain

CSVファイルを生成したら、後はCaccoに取り込むだけです。

作図のメニューから「データベーススキーマ」を選択し

f:id:sugimomoto:20200502201418p:plain

CSVファイルはすでにあるので、「次へ」をクリック

f:id:sugimomoto:20200502201423p:plain

生成したCSVファイルを読み込んで「OK」をクリックすれば、取り込まれます。

f:id:sugimomoto:20200502201428p:plain

暫くすると以下のような形でデータベーススキーマが表示されました!ちなみに対象テーブルを絞り込んだせいで、リレーション先のテーブルが足りないとエラーメッセージが色々と出ますが、とりあえず正常に読み込めている感じです。

f:id:sugimomoto:20200502201435p:plain

なお、型や桁数はCData Driverを介したものが表示されるので、クラウドサービス側の方とは厳密には異なりますので要注意です。

各ドライバーは30日間のトライアルも提供しているので、ぜひお好みのデータソースで試してみてください。