【CData Sync ハンズオン】Google BigQuery を軸にしたデータ分析基盤をCDataSyncで構築してみよう！

by 宮本航太 | 2020年10月02日

はじめに

本資料は、CData Software Japanのハンズオン向けの資料です。本ドキュメントをもとに実機にCData Sync製品をインストールして手順に沿って操作する事で、Salesforce やkintone などのクラウドサービスのデータをBigQuery やRedShift をはじめ、MySQL やSQLServer などの主要データベースにデータを同期する方法を理解して頂くことを目的としています。

• はじめに
• ハンズオンの内容
• CData Sync製品のインストール&アクティベート
• Salesforce へのコネクションを作成
• BigQuery へのコネクションを作成
• Salesforce の取引先データをBigQuery にSync
  • ジョブの作成
  • ジョブの実行
  • BigQuery コンソールより確認
• 日本語カラムのkintone のデータをBigQuery にSync
  • kintone のコネクタを追加
  • kintone のコネクション設定
  • kintone からBigQueryへのレプリケートジョブを作成
  • ジョブの実行
  • BigQuery コンソールよりkintone データの確認
• BigQuery からローカルDB（MySQL）にSync
  • MySQL のコネクション設定
  • BigQueryからローカルのMySQLへのレプリケートジョブを作成
  • ジョブの実行
  • MySQL WorkBench を使用してレプリケートデータを確認
• その他機能の紹介
  • 差分更新
  • スケジューリングして実行
  • データソース側で削除されたデータを同期先にも反映
  • 既存テーブルへのマッピング
• おわりに

ハンズオンの内容

本ハンズオンでは以下の内容を行います。

• データソースの接続設定
• 同期先の接続設定
• Salesforce → BigQuery へのレプリケート
• kintone → BigQuery へのレプリケート
• BigQuery → MySQL へのレプリケート
• その他機能の紹介

CData Sync製品のインストール&アクティベート

今回はCDataSyncのインストールは行いません。
必要であれば以下の記事を参考にインストールを行ってください。

https://www.cdata.com/jp/blog/2019-12-02-160226

Salesforce へのコネクションを作成

ヘッダーで「接続」をクリックし、以下の画面で Salesforce のアイコンをクリックします。

Salesforce へのコネクションの設定画面が表示されるので、名前には任意の名称（例：Salesforce）に変更して、User、Password、SecurityTokenを設定し、「接続のテスト」ボタンをクリックします。
上段に「接続に成功しました。」のメッセージが出たら成功です。「変更を保存」ボタンをクリックして作成したコネクション情報を保存してください。
当ハンズオンではデスクトップにある各種接続情報ファイルを参照して以下の項目に設定してください。

• 名前：Salesforce
• フォーマット設定：プロパティリスト
• Auth Scheme：Basic
• User：Salesforce のアカウント
• Password：Salesforceのパスワード
• Security Token：Salesforceより発行されたセキュリティトークン

BigQuery へのコネクションを作成

Sync では、レプリケートジョブを実行時に同期先DBに対してテーブルを自動で作成するようになりますが、その際にBigQueryの情報として必要なのがプロジェクトIDとデータセットIDになりますので、この情報をBigQueryのコネクション設定画面で入力します。

ヘッダーで「接続」をクリックして以下の画面を表示後、「同期先」→「BigQuery」の順でクリックしていきます。

今回、BigQuery への接続にはサービスアカウントを使用します。接続情報ファイルの内容をコピーして以下項目に設定お願いします。

【設定タブ】

• 名前：BigQuery
• フォーマット設定：プロパティリスト
• Auth Scheme：OAuth
• Dataset Id：利用するBigQueryのデータセットID（RDBでいうスキーマ）
• Project Id：GCP のプロジェクトID

【Advancedタブ】

• OAuth JWT Cert： p12ファイルのパス
• OAuth JWT Cert Password：パスワード
• OAuth JWT Cert Subject： *
• OAuth JWT Cert Type：PFXFILE
• OAuth JWT Issuer： サービスアカウント名

入力完了後、設定タブに戻って右下の接続ボタン押下をクリック

接続テストが正常に完了した場合は、画面に緑色の帯でSuccessなどと表示されますので、右上の「変更を保存」で保存を行います。

これでBigQueryのコネクション設定が完了です。

Salesforce の取引先データをBigQuery にSync

それでは実際にSalesforceからBigQueryへのレプリケートを行います。

ジョブの作成

ヘッダーの「ジョブ」を選択後、右側にあります「ジョブを追加」をクリックします。

以下の画面が表示されますので、ジョブ名、ソース、同期先を入力したら「作成」をクリックします。

• ジョブ名：SalesforceToBigQuery
• ソース：Salesforceを選択
• 同期先：BigQueryを選択

次に、Salesforce内のテーブル（オブジェクト）を選択するために「＋テーブルを追加」をクリックします。

Salesforce内のテーブル（オブジェクト）リストが表示されるので、今回は、取引先であるAccountを選択して、「+選択したテーブルを追加」をクリックします。

テーブルとクエリに自動的にAccountテーブルを同期する内容が設定されました。ここでいう"テーブル" と書いてある箇所は同期先に作成するテーブル名（ファイル名）となります。"クエリ" はどういう内容で Salesforce からデータを抽出するかという意味になります。
REPLICATE とは Account テーブル内容をそのまま同期することを意味しています。ジョブ内容の確認が終わりましたら、右上にある「変更を保存」をクリックし、ジョブを保存します。以上でジョブの作成が完了です。

なお、以下は今回レプリケートするSalesforceの取引先データの一部になります。合計で47件あります。

ジョブの実行

それでは作成したジョブを実行してみます。ジョブのチェックボックスをオンにし、実行ボタンをクリックします。しばらくたつと、上部に緑のメッセージボックスで「ジョブ[ジョブ名] は正常に完了しました。Records affected: 13 ・・・」といったメッセージが表示されれば成功です。

BigQuery コンソールより確認

では、BigQueryのコンソール画面にて、Salesforceのデータがレプリケートされているか確認してみます。
左のサイドメニューに表示されている「opendx01」というのがデータセットになりますが、その配下にAccountというテーブルが作成されているのが確認できます。
またSelect * from Account でAccountテーブルのレコードを参照すると、このようにSalesforceのレコードがレプリケートされていることがわかります。

日本語カラムのkintone のデータをBigQuery にSync

それでは、Salesforce → BigQuery のジョブ作成時と同じ要領で、kintone → BigQuery のレプリケートジョブを作成していきます。

kintone のコネクタを追加

デフォルトではkintoneコネクタが含まれてないので、手動で追加してあげる必要があります。
「接続」→ 「＋Add More」をクリックします。

kintone と検索エリアに入力し、kintone アイコンをクリックします。

「ダウンロード&インストール」をクリックします。

kintone コネクタの追加が完了するとこのようにメッセージが表示されます。

OKボタン押下後、CDataSyncが再起動されます。その後ログイン画面よりログインを行い、また接続設定画面を表示してみると、このようにkintone アイコンが一覧に表示されるようになります。

kintone のコネクション設定

表示されたkintone アイコンをクリックして、他と同じようにコネクション設定を行います。
接続情報ファイルの内容をコピーして以下項目に設定お願いします。

• 名前：kintone
• フォーマット設定：プロパティリスト
• URL：kintone のURL
• User：kintoneユーザー名
• Password：パスワード
• Check For Subtables In：* （サブテーブルを検索するかどうか）
• Subtable Separator Character：_ （サブテーブル区切り文字）

入力完了後、右下の接続ボタン押下をクリック

kintone からBigQueryへのレプリケートジョブを作成

ヘッダーの「ジョブ」から「ジョブを追加」をクリックし、以下のようにソースに「kintone」、同期先に「BigQuery」を設定して作成する。

「テーブルを追加」からkintone のアプリ一覧を表示させ、今回は「顧客管理（営業支援パック）」を選択します。

ちなみに以下はkintoneにある「顧客管理（営業支援パック）」アプリの中身になります。これをBigQuery にレプリケートしていきます。

kintone は上記キャプチャにもあるように項目名は日本語となっていますが、BigQueryでは日本語を含むマルチバイトの項目名を作成することができません。
なので事前に項目名は英字に変更しておく必要があります。

では、赤枠部分のクエリをクリックし以下の画面表示させます。

カラムマッピングタブをクリックすると、各項目同士の紐づけ状態を確認できるようになっています。ここで日本語項目のものを英字に直してあげます。
また真ん中の矢印にマウスホバーすると、右矢印かバツが表示されますので、連携不要な項目などはここでバツを設定してレプリケート対象外にしてみます。

BigQuery に設定するテーブル名も今は日本語のままなので、英字に変更しておきます。変更箇所は直接クエリを修正するか、一般タブのテーブル名で変更が可能です。

ジョブの実行

それでは作成したジョブを実行してみましょう。

ジョブのチェックボックスをオンにして実行ボタンをクリックします。
正常に終了すれば緑色の帯にメッセージが表示されます。

BigQuery コンソールよりkintone データの確認

では、BigQueryのコンソール画面にて、kintone のデータがレプリケートされているか確認してみます。 データセットの「opendx01」配下に「CustomerList_kintone」というテーブルが作成されているのが確認できます。

またSelect * from CustomerList_kintone で顧客管理（営業支援パック）テーブルのレコードを参照すると、このようにkintone のレコードがレプリケートされていることがわかります。

BigQuery からローカルDB（MySQL）にSync

今まではBigQuery にデータをレプリケートする内容をご紹介してきましたが、今度はBigQuery のデータをローカルにあるMySQL にレプリケートしてみます。
ちなみに、レプリケート先には主要RDBをはじめ、RedShiftやsnowflakeなどのクラウドデータウェアハウスやNoSQL系、Access、S3、CSVファイルなどへレプリケートすることが可能となっています。

MySQL のコネクション設定

接続設定画面のにて、「同期先」→「MySQLのアイコン」の順にクリックします。

接続情報ファイルの内容をコピーして以下項目に設定お願いします。

• 名前：MySQL
• フォーマット設定：プロパティリスト
• Server：MySQL起動中のサーバ（localであればlocalhostの指定でOK）
• Port：MySQL のポート
• Database：MySQLのデータベース名
• User：MySQLのユーザー
• Password：MySQLユーザーのパスワード

BigQueryからローカルのMySQLへのレプリケートジョブを作成

ここからはジョブを作成していきますが、手順はこれまでの内容と同じです。

「テーブルを追加」から今回はSalesforceからレプリケートされたAccountテーブルをMySQLに連携させたいと思いますので、Account にチェックを入れ選択します。

作成されたクエリをクリックし、レプリケート対象の項目を絞ってみます。

赤枠の下矢印から「新規テーブルを作成」をクリックします。
※この画面で赤帯でエラーメッセージが表示されている場合がありますが、そのまま進めて頂いて構いません。

テーブル名は任意の名前で設定します。

次にレプリケート対象の項目を画面上から設定します。カラムマッピングタブをクリックして、以下の画面を表示させます。
矢印があるところにカーソルを当てると×印が表示されるので、それをクリックしますとレプリケート対象項目から除外されます。

対象項目を設定しましたら、画面下部にあるOKボタンをクリックします。
もし画面解像度などで下のOKボタンが見えない場合は他のタブに移動してからOKボタンをクリックでも大丈夫です。

ジョブの実行

それではBigQueryのデータをローカルのMySQLにレプリケートしてみます。作成したジョブの左にあるチェックボックスをオンにして実行ボタンよりレプリケートジョブを実行します。

正常に処理が完了しましたら、実際にMySQL WorkBench を使ってMySQLの中を見てみましょう。

MySQL WorkBench を使用してレプリケートデータを確認

画面下段にあるタスクバーよりMySQL WorkBench をクリックし、Localインスタンスへ接続する赤枠の接続先をクリックします。

handson というデータベースを選択してTablesをクリックすると、accountという先ほどCDataSyncで指定したテーブル名でテーブルが作成されているのが確認できました。 また、select文を実行すると、このようにBigQueryに格納されていたデータがMySQLにレプリケートされていることが確認できます。

その他機能の紹介

いろいろとあるCDataSync の機能について、いくつかピックアップしてご紹介します。

差分更新

CDataSync では一部のデータソースに対して差分更新を行うことができます。そのため差分更新モードがオンの場合、２回目からのレプリケート時は前回からの変更分のみが転送されるようになります。

差分更新モードのオンオフの判断は、以下ジョブオプションの「テーブルを削除」か「テーブルデータを削除」のどちらもチェックがついていない場合にオンとなります。

どのデータソースが差分更新対象なのかは以下WEBサイトのデータソース一覧にて、アイコンの右上に★マークがあるものが対象となります。
※★マークがないものについても手動で差分更新を行えるものもありますので、気になるデータソースがあればCData サポートまでご連絡下さい。
https://www.cdata.com/jp/sync/connections/

スケジューリングして実行

スケジューリング機能はジョブ設定の「スケジュール」タブを選択し、「スケジューラーを有効にする」にチェックを入れますと設定することができます。設定内容は、分～月や、cron式での柔軟な設定を行うことができます。

設定すると、ジョブ画面に次の実行予定日時が表示されるようになります。

データソース側で削除されたデータを同期先にも反映

CHECKCACHE コマンドというものを使用して、データソース側とレプリケート先で連携データの突合を行うことができます。
例えば、データソース側で削除されてしまったレコードは差分更新モードの場合、レプリケート先には基本的に反映されませんが、CHECKCACHE を実行することでデータソースを正にしてレプリケート先を修復してくれます。
https://www.cdata.com/jp/blog/2020-03-09-100726

既存テーブルへのマッピング

既存システムなどのテーブルへもデータを連携させることができます。
また、１つのテーブルを作成し、そこにいくつかのデータソースのデータを集めるということもできるようになっています。
https://www.cdata.com/jp/blog/2020-03-09-100751

おわりに

以上でハンズオンは終了となります。長時間お疲れ様でした。

CDataSync は30日間無料でお試しいただけるようになっています。
当ハンズオンは一時的なVMになりますので別な環境で使用したい場合は、以下WebサイトよりCDataSync をダウンロードしてご利用ください。

関連コンテンツ