大量の JSON データからスキーマを解析して、Azure SQL Database にレプリケートする：CData Sync
先日公開した「500万件を超えるTwitter のリツイート データを取得・分析する方法」で取得したJSONデータをAzure SQL DataBaseにレプリケートして分析するアプローチを紹介しました。
ただ、元記事ではアプローチの紹介が中心で、実際の手順や機能に関する紹介ができなかったので、本記事で改めてこのCData Syncの使い方・およびJSONデータのスキーマを解析して Azure SQL DataBaseにレプリケートする方法を紹介しようと思います。
CData Sync とは？
https://www.cdata.com/jp/sync/
CData Data Sync シリーズは、Salesforce、Dynamics CRM、SharePoint などのクラウド上の業務アプリケーションデータのレプリケーション（複製）をローカル DB およびクラウド DB に簡単に作成するサーバー製品です。
最近のバージョンアップで、CDataが提供している100＋のドライバーを元にした Connector をリリースしたので、今回のユースケースのように JSONデータをAzure SQL DataBaseにレプリケートするというアプローチも可能になりました。
対象データ
対象となるデータは、前回のBlogで紹介したTwitter のリツイートデータです。
中身は以下のような構造体になっていて、ネスト構造がなかなか深い、解析しづらいJSONではないかなと思います。
元の記事では、このJSONデータにどのようにスキーマ構造を与えて、Power BIなどのツールから接続しやすくするのか？ といったお話も交えていました。
もちろん技術的には様々な解説ポイントがあるのですが、CData Sync ＋ JSON Connector を使うと、以下のように自動的にスキーマ構造を読み取り、テーブル化してくれるので、この記事ではこれといって特に難しい操作や設定は要求されません。
最終的には以下のようにAzure SQL Database に格納された状態になります。
必要なもの
・CData Sync 2019
・CData Sync JSON Connector
・Azure アカウント
CData Sync や JSON Connector はトライアル版が提供されています。
Azure アカウントは12ヶ月無料のトライアルアカウントがあります。
https://azure.microsoft.com/ja-jp/free/
レプリケート手順
それでは実際にレプリケートを行っていきます。
Azure SQL の構成
まず、レプリケート先となるAzure SQL Database を構成します。
Azure Portal にログインし、「リソースの作成」→「SQL Database」を選択
任意の構成でデータベースを作成します。
ちなみに価格レベル次第でレプリケート速度が変わるので、データ量や同期頻度を考慮しながら設定するとよいでしょう。
Azure SQL Database を構成したら、サーバー側のファイアウォール設定で接続元のIPアドレスを許可します。
今回はローカルマシンにCData Syncを入れるので、そのマシンから接続する際のIPアドレスを許可します。
対象のデータベース管理画面から「サーバーファイアウォール設定」をクリックし
「＋クライアントIPの追加」で、現在接続しているIPアドレスを自動的に登録し、「保存」を押します。
以上でAzure SQL Database側の設定は完了です。
CData Sync のインストール
続いて、CData Syncをインストールします。
以下のURLにアクセスし「ダウンロード」をクリック
https://www.cdata.com/jp/sync/
CData Sync は Windows 版と Linux などの Cross-Platform 版があるので、任意のプラットフォームを選択して、ダウンロードを行います。
今回はWindows 版を選択しました。
ダウンロードできたインストーラーを立ち上げると、セットアップウィザードが表示されるので、画面に従ってセットアップを実施します。
対象のデータソースコネクタをインストール（JSON Service）
CData Sync のインストールが完了したら、次に対象のデータソースコネクタをインストールします。
今回は JSON コネクタが必要なので、以下のURLにアクセスし、JSON serviceのところからダウンロードをクリック
https://www.cdata.com/sync/download/#sources
同じようにセットアップを実施します。
CData Syncの起動
CData Sync インストールが完了すると、以下のようにログイン画面が表示されるので、設定したID・PWでログインを実施します。
この画面から各種設定を実施していきます。
データソースの設定（JSON）
それでは最初にデータソース側の設定を行います。
ナビゲーションメニューから「接続」→「データソース」を選択し、表示されているデータソース一覧の中から「JSON」のデータソースを選択します。
次の画面で、JSON に接続するための各種設定を実施します。
今回必要な設定は以下のようになります。
［設定］ User admin（任意の文字列） Password admin（任意の文字列）
［Advanced］ URI JSONが格納されたフォルダパス：例：C:\Work\ZozoTweets\test_01\ Data Model Relational
ポイントは「Advanced」のURIとDataModelです。
URIではJSONが格納されたフォルダパスを指定します。こうすることで、複数のJSONファイルを読み取ることが可能になります。
また、Data Modelは「Document」「Relational」「FlattenedDocuments」の3種類から選択できますが、今回はリレーショナルな形でデータを取り出したいと考えているので、「Relational」を選択します。
各モデルの詳しい機能は以下のQiita記事にまとめているので、もし詳細を知りたい方はこちらを参照してみてください。
あとは、「接続」ボタンをクリックして、接続検証を実施し、「Success」が表示されればOKです。
「変更を保存」してデータソースの設定は完了になります。
同期先の設定（Azure SQL DataBase）
次に同期先の設定を行います。
同じく接続の画面から「同期先」を選択し、一覧の中から対象の同期先「Azure SQL Databae」を選択します。
先程のデータソースの接続先と同じように接続情報を入力します。
［設定］ Server Azure SQL DatabaseのServerアドレス 例：cdatasyncsugimototest.database.windows.net Database 接続先となるデータベース名 User SAユーザーのID Password SAユーザーのPW
同じように接続テストを実施して、接続が確立できれば設定完了です。
「変更を保存」して設定を終了します。
ジョブの設定
それでは最後にデータソースと同期先の設定を元に、どのテーブルをどのように同期するのか？といった設定となるジョブを追加します。
ナビゲーションメニューから「ジョブ」を選択し「ジョブを追加」をクリックします。
データソースと同期先の構成
ジョブの作成画面に移動後、まずソースと同期先の設定を確認します。
複数ソース、複数同期先を登録している場合は、ここで対象となるものをそれぞれ指定します。
テーブルの追加
続いてデータソースから同期したいテーブルを選択します。
「＋テーブルを追加」をクリックし
テーブルの一覧が表示されるので、ここから任意のテーブルを選択します。
今回データソースとしているのはJSON データですが、すでにここでもととなるJSONデータからスキーマが自動的に識別され、テーブル構造として提供されているのがわかるかと思います。
Twitter Premium Search API で取得したJSON データは大本のオブジェクトが「results」になっているので、今回はこの「results」テーブルを選択して、同期します。
カラムマッピングの構成
次に同期するカラムの構成を確認します。
追加したテーブル設定の横に「変換」のボタンが表示されるので、このボタンをクリックします。
この画面からレプリケートするカラムの取捨選択や、レプリケートするデータのフィルタリング設定を行うことが可能です。
今回は特に設定を変更しませんが、不要なカラムはここで除外しておくと、後々BIツールなどで分析する際に楽になるのではないかと思います。
スケジュールの構成
今回は一度きりのレプリケートですが、CData Sync ではスケジュールを構成して同期することも可能です。
1時間に1回や1日1回といった構成が可能になっています。
詳細設定
詳細設定では、レプリケートでよく発生するエラーハンドリングやアプローチの設定を変更することができます。
特に文字列の自動切り捨てなどはJSONのオブジェクトが想定以上に長い場合などに、エラーで終了せず切り捨て登録などをしてくれるものになるので、対象のデータベースの設定次第ではチェックを入れていいと思います。
実行と結果確認
すべての設定が完了したら、実行してみましょう。
一度「変更を保存」をクリックします。
今回は手動で実行するため、対象となるテーブルを選択し「実行」ボタンをクリックするだけです。
実行すると、以下のようにステータスが「Runninng」となり
成功すれば、以下のように処理された件数が表示されます。
SQL Server Management Stadio を起動し、対象のAzure SQL databaseにアクセスしてみると、以下のようにデータが取り込まれていることが確認できます。
終わりに
いかがでしたでしょうか。CData Sync を使うことで、特に難しい設定もなく、JSONデータがRDBに取り込めます。
今回は Azure SQL Database でしたが、Google BigQuery や MongoDBにもレプリケートすることが可能なので、是非いろいろと試してみてください。
