CData Sync を使ってExcelファイルをMySQL HeatWave に同期してみました
はじめに
データの分析基盤として使用しているDBやDWHに対して、以下のようなデータを連携したい状況があると思います。
- オンプレミスもしくは、クラウド上に構築済みのシステムで生成された業務データ
- SalesforceやKintoneに代表されるクラウドサービス(Saas)内に格納されているデータ
各種API仕様を読み解き、分析担当者の要望や要求に合わせてデータ連携の仕組みを作り上げていくことは、もちろん可能です。
また、連携するデータをCSV形式ファイルで出力し、DB側で当該ファイルをロードするという運用もよく行われているものと思います。
今回は、CData Syncというツールを利用することで、より素早く簡単にDBへのデータ連携を実現する方法をご紹介したいと思います。
CData Syncは、CData Software社が開発、提供しているデータ連携用ツールとなります。
データの連携元がSaasの場合でも、一旦接続設定さえ確立できてしまえば、以降はその連携元のSaasを仮想的なRDBとみなすことが可能となります。その結果、データ抽出はSQLで簡単に実現できることが特徴のツールです。
システム構成イメージ
今回の検証環境は、OCI(Oracle Cloud Infrastracture)上に構築します。
システム構成イメージは、以下の図の通りです。
CData Syncが動作しているComputeインスタンスのOSは、Windows Server 2019を利用します。
MySQL Heatwave(MySQL HeatWave Database Service)は、トランザクション処理であるOLTP、データ分析処理であるOLAP、機械学習を意味するMLに対応可能なフルマネージド・データベース・サービスです。
なお、本環境のComputeインスタンスやMySQL HeatWaveインスタンスの構築手順については、本記事では割愛させていただきます。
連携シナリオ概要
- 連携元データ(データソース): サーバローカルに存在するExcelファイル
- データ同期先: MySQL HeatWaveのテーブル
今回の連携シナリオは、シンプルなものとなります。
サーバローカルのExcelファイルに含まれるデータをMySQL HeatWaveのテーブルに対して連携するシナリオを検証してみます。
利用するExcelファイル
検証で使用するExcelファイルは、総務省が以下のWebページで公開している「都道府県コード及び市区町村コード」を使用します。
https://www.soumu.go.jp/denshijiti/code.html
検証および説明の都合上、ファイル名を都道府県_市区町村.xlsxとし、2つあるシートのシート名を団体および政令指定都市とします。
団体シートには、ヘッダー行を除き、1,794件のデータ行が含まれています。
政令指定都市シートには、ヘッダー行を除き、195件のデータ行が含まれています。
また、ヘッダー行に含まれていたセル内改行は削除しています。
このExcelファイルをCData Syncが動作するWindows Serverの以下パスに配置するものとします。
C:\tmp\都道府県_市区町村.xlsx
MySQL HeatWaveの必要な情報
構築済みのMySQL HeatWaveインスタンスに関して、いくつかの情報が連携設定の際に必要となります。
まず、MySQL HeatWaveのエンドポイント情報が必要となります。
OCIでの確認方法は以下の通りです。
OCI Webコンソールで左上の三本線のメニュー → データベース → DBシステムの順にクリックします。
DBシステム一覧から対象のインスタンスを選択し、名前をクリックし、DBシステムの詳細画面でエンドポイント情報の内部FQDNを確認します。
また、連携データを格納するデータベース(スキーマ)、データベースユーザを事前に準備します。
その上で、データベース名およびそのデータベースを利用できるデータベースユーザ、パスワードを控えておきます
今回使用するHeatWave情報の例
| 情報名 | 情報値 |
|---|---|
| 内部FQDN | mds4cdata.….vcndata.oraclevcn.com |
| データベース(スキーマ) | test |
| データベースユーザ | admin |
CData Syncのインストールから起動まで
では、Computeインスタンス上にCData Syncをインストールしてみます。
CData Syncは、Windows Server用の.Net版、クロスプラットフォーム用のJava版の2種類が提供されています。
今回は前者の.Net版を選択します。
ダウンロードおよびインストール
- ブラウザで以下URLへ移動
https://www.cdata.com/jp/sync/download/ - 以下画面でWindows(.NET Edition) の
ダウンロードをクリック
- 入力必須項目を入力し、
ダウンロードをクリックし、ローカルにファイルを保存
- ダウンロードした
CDataSync.exeを起動 - 以下画面で
はいをクリック
- 以下画面以降では、
次へ(N)>、同意する(A)、インストールなどを適切にクリック
- 以下画面で
完了(F)をクリックし、インストールを完了
CData Syncの起動
インストールが完了したので、起動してみます。
- スタートメニューから
Start CData Sync Serverをクリック
- 表示された以下画面で、ユーザー:
adminのパスワードを設定しユーザーを作成をクリック
- ログインし、ライセンス情報を設定
インストール後、データ連携設定完了までのステップ
CData Syncの起動後、データ連携設定完了までは、以下の3ステップとなります。
- データソースを選択してコネクション設定
- 同期先を選択してコネクション設定
- 連携シナリオにもとづき、同期ジョブ(テーブル、抽出条件など)を設定
なお、ステップ1とステップ2については順不同です。
では各ステップを実行していきます。
ステップ1: データソースを選択してコネクション設定
CData Syncでは、データソース用(今回の場合Excel)のコネクタが必要となります。CData Syncのインストール直後には、Excel用のコネクタはないため、追加インストールする必要があります。
Excelコネクタのインストール
接続タブのデータソースタブをクリックし、Add Moreをクリック
コネクタをダウンロード画面でフィルタ欄にExcelと入力し、表示されたExcelをクリック
ダウンロード&インストールをクリック
はいをクリック
- インストールが完了すると、ログイン画面が再度表示されるので、ログイン
Excelコネクション設定
インストールしたExcelコネクタを利用して、コネクション設定を実施します。
CData SyncでExcelを仮想RDB化した場合、コネクションで設定したExcelファイルが仮想データベース(スキーマ)となります。
接続タブのデータソースタブをクリックし、Excelをクリック
設定タブで以下を設定
名前: 一意な名称 ※ここではConnect2Excelとします
Advancedタブで以下を設定
Excel File: 対象ファイルへのパス ※ここではC:\tmp\都道府県_市区町村.xlsx
設定タブで接続テストをクリックし、接続に成功することを確認変更を保存をクリック
ステップ2: 同期先を選択してコネクション設定
CData Syncでは、同期先として使用するDBやDWHのプロダクト(今回の場合MySQL HeatWave)に合わせてコネクタを選択し、コネクション設定を実施します。
MySQL HeatWaveですが、接続のためインターフェースは、通常のMySQLと同一となります。したがってCData Syncのコネクタとしては、MySQL用のコネクタをそのまま利用することが可能です。MySQLコネクタを利用して、コネクション設定を実施します。
接続タブの同期先タブをクリックし、MySQLをクリック
- 以下を設定
名前: 一意な名称 ※ここではConnect2MDSとします
Server: <MySQL HeatWaveのエンドポイントFQDN>mds4cdata.….vcndata.oraclevcn.com
Database: <作成したデータベース(スキーマ)>test
User: <データベースのユーザ>admin
Password: <データベースユーザのパスワード>
接続テストをクリックし、接続に成功することを確認変更を保存をクリック
ステップ3: 連携シナリオにもとづき、同期ジョブを設定
連携シナリオとしては、2つほど準備します。
シナリオ3-1:全てのシートの全てのデータを同期する
データソースとなるExcelファイルに含まれる全シートをMySQL HeatWaveのテーブルとしてデータ同期します。
CData SyncでExcelを仮想RDB化した場合、各シートが仮想テーブルとなります。
同期先MySQL HeatWaveのテーブル名は、デフォルトではExcel側のシート名となります。
ジョブタブのジョブを作成をクリック
- 新しいジョブを作成で以下を設定し、
作成をクリック
ジョブ名: (任意の名称)jobtest1
ソース:Connect2Excelを選択
同期先:Connect2MDSを選択
レプリケーションの種類:全テーブルを同期を選択
- 以下画面が表示されることを確認
シナリオ3-2:列の特定値を条件としてデータを抽出する
Excelファイルの団体シートに含まれるデータのうち、市区町村名（漢字）が空白の行のみをMySQL HeatWaveのテーブルとしてデータ同期します。
CData SyncでExcelを仮想RDB化した場合、各列が仮想カラムとなります。
本シナリオでの検索条件は、仮想カラム市区町村名（漢字）の値がNULL(is null)となります。
ちなみに、市区町村名（漢字）が空白の行は、都道府県を表す行となり、この結果、47都道府県の情報が含まれる団体テーブルとして同期されることになります。
ジョブタブのジョブを作成をクリック
- 新しいジョブを作成で以下を設定し、
作成をクリック
ジョブ名: (任意の名称)jobtest2
ソース:Connect2Excelを選択
同期先:Connect2MDSを選択
レプリケーションの種類:スタンダードを選択
- ジョブ設定の
+テーブルを追加をクリック
- テーブルを追加画面で
団体を選択し、追加をクリック
- ジョブ設定の
団体テーブルをクリック
- タスク設定画面の
カラムマッピングタブで一部の矢印部分をクリックし×に変更することで以下状態にする
- タスク設定画面の
Advancedタブのフィルタ欄の+Add ruleをクリックし、市区町村名（漢字）、is nullを選択し、OKをクリック
- ジョブ設定の
変更を保存をクリック
同期ジョブの実行
連携設定が完了しました。
では、設定した同期ジョブを実行し、結果を確認してみます。
同期ジョブの実行方法は複数ありますが、今回は最もシンプルな手動実行で行います。
シナリオ3-1の実行
ジョブタブをクリックjobtest1行の再生ボタンをクリックjobtest1行のステータス欄にRecords affected:1989と表示されたことを確認
シナリオ3-1の実行結果確認
MySQL Workbenchを使用して、MySQL HeatWave側で同期ジョブの実行結果を確認してみます。
政令指定都市テーブルのレコード数をカウントしてみたところ、195レコードという結果が得られました。
同じく団体テーブルのレコード数をカウントしてみたところ、1,794レコードという結果が得られました。
それぞれのレコード数は、Excelファイルの行数と一致するとともに、これを合わせるとCData Syncが同期したレコ―ド数と合致していることが確認できます。
シナリオ3-2の実行
ジョブタブをクリック
jobtest2行の再生ボタンをクリックjobtest2行のステータス欄にRecords affected:47と表示されることを確認
シナリオ3-2の実行結果確認
こちらのシナリオでも、MySQL Workbenchを使用して、MySQL HeatWave側で同期ジョブの実行結果を確認してみます。
団体テーブルのレコード数をカウントしてみたところ、想定通り、47レコードという結果が得られました。
まとめ
CData Syncは、コネクションを確立することでデータソースを仮想RDB化します。
今回は詳細に検証していませんが、この仮想RDBに対して、SQLを発行し、抽出したデータを、同期先のDBに対して連携するツールとなります。
したがって、SQLで実現可能な結合処理や変換処理などが可能です。
Excelをデータソースとした場合、RDBのコンテナ技術要素に対応するExcle側の要素についてまとめます。
| RDBの技術要素 | Excel側で対応する要素 | 今回の具体例 |
|---|---|---|
| データベース | Excelファイル | 都道府県_市区町村.xlsx |
| テーブル | ファイル内の各シート | 団体、政令指定都市 |
| カラム | シート内の列 | 団体シートのA～E列など |
| カラム名 | ヘッダー行 | 団体コード、都道府県(漢字)など |
| レコード | シート内の行 | 団体シートの2行目以降 |
今回は、CData Syncの基本機能の説明、検証となっています。
今回のシナリオのような単純なデータ同期であれば、SQLすら意識せずに実施することが可能でした。
今後、Excle以外の各種Saasをデータソース、MySQL Heatwaveを同期先としたデータ連携(データ取込み)を検証していく予定です。ご期待ください。
弊社は、CData SyncをはじめとするCData製品販売パートナーとなっています。興味を持たれましたら、お問い合わせいただければと思います。
