こんにちは、エンジニアの宮本です。
今回は、「kintone」 から 「Amazon S3」 へ CData Sync を使ってCSVファイルを連携し、その連携データを 「Amazon Athena」 を通してマネージドBIツール 「Quick Sight」で可視化する内容をご紹介します。
構成
シナリオとしては、kintone にある案件情報を日次で連携し、担当者ごとの受注確度による受注金額を可視化していきます。
構成の大まかな流れは以下の通りです。
- kintone から S3 へ定期的にデータ連携するジョブを、CDataSycn を使って行います。ジョブ実行時に、CDataSync から連携内容の DDL ステートメントを出力します。今回は kintone ですが別のデータソースでも構いません。対象データソースはこちらをご参照ください。
https://www.cdata.com/jp/sync/connections/
色々書いてますが、特段コーディング等は不要です。全てツールの力を用いて実現できます。
そもそも CData Sync とは？
Salesforce や Marketo、kintone などのクラウド（SaaS）データ を DB およびデータウェアハウスに同期するレプリケーションツールです。複雑な設定等は特になく、高度な同期ジョブを作成、運用することができます。また SQL を設定することができるので、柔軟にデータを抽出してデータベースへ連携することができます。
https://www.cdata.com/jp/sync/
必要なもの
- CDataSync のみ！（30日間無料です）
※kintone と AWS にアクセスできること
実施手順
では、ここから実際に CData Sync をインストールし、実際にジョブの作成～実行を行っていきます。
kintoneからS3 へのレプリケート
CData Sync のインストール
まずは、CDataSync のインストールから行います。
インストールは非常に簡単です。windows 版、Java 版と用意していますが、どちらもインストーラーを実行していただければすぐに使うことが出来ます。今回はWindows 版を使用して進めていきます。
詳しいインストール手順は、以下の記事をご参考ください。
https://www.cdata.com/jp/blog/2019-12-02-160226
kintoneとS3の接続設定
まずはデータソース側の kintone への接続設定を行います。
CDataSync の接続設定で kintone を選択後、以下の画面で必要な接続情報を入力します。
次に、S3 への接続設定を行います。手順は kintone と全く同じです。
Athenaで使う DDL 生成のオプションをオンにする
S3 の接続設定画面上部にある「Advanced」タブをクリックします。画面が表示されたら、下の方にスクロールすると「Generate Hive DDL」という項目が表示されます。この項目にパスを設定すると、レプリケート時にDDL ステートメントを指定のパスに出力してくれるようになります。
今回は CDataSync を起動している Windowsマシンの C:\work フォルダに出力するよう指定しました。
ジョブを作成
CData Sync のトップ画面から新規ジョブの追加でジョブ作成画面を開きます。
今回は以下のクエリをセットします。
replicate deal_kintone select 部署名,受注確度,ご担当者名,案件名,更新日時,受注金額,顧客名,作成日時,受注予定日 from deal where ご担当者名 is not null
上記の「replicate deal_kintone 」は、deal_kintone という名前でレプリケート先にテーブルを作成するという指定ですが、今回はその名前でS3 にフォルダが作成されていきます。
スケジューリングを作成
ジョブ設定画面の中段にある「スケジュール」タブより、ジョブのスケジューリングを行います。今回は毎日夜中の2時に実行されるようにしています。（本記事ではスケジューリングの実行確認はしませんので設定までとなります）
ジョブを実行
チェックを入れた後に実行ボタンを押すことで、スケジューリングに関係なく実行することができます。実行後は、右側に更新時間とレプリケート件数が表示されます。
では、S3 を確認するとcsv ファイルが作成されているのがわかります。
赤枠を確認すると、replicate～ で指定した名前でフォルダが作成され、その中にファイルが作成されています。
続いて、生成されたDDL の確認です。
先ほど指定したパスを確認すると、DDL ステートメントが作成されているのがわかります。
中身はコチラ
CREATE EXTERNAL TABLE IF NOT EXISTS `deal_kintone` (
`部署名` string,
`受注確度` string,
`ご担当者名` string,
`案件名` string,
`更新日時` string,
`受注金額` string,
`顧客名` string,
`作成日時` string,
`受注予定日` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = "\""
)
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://cdatajwrk/deal_kintone/'
TBLPROPERTIES (
'has_encrypted_data'='false'
)
Amazon Athena にテーブル作成
Athena を開き、クエリエディタに先ほど出力されたDDL ステートメントをペーストしてそのまま実行するだけで大丈夫です。
正常に実行されると「Query successful」が表示され、左側に「deal_kintone」というテーブルが作成されます。
select * from deal_kintone を入力して実行すると、ちゃんとデータが表示されました。
Quick Sight で可視化
これまでは、kintone → S3 → Athena と、キモとなるデータ連携部分の内容でした。最後に連携されたkintone の案件情報を可視化していきましょう。
まずは Quick Sight のアカウント作成です。スタンダード版で進めます。
アカウント名や対象のS3 を設定後、右下の完了ボタンをクリックします。
Quick Sight のトップ画面で「新しい分析」をクリックします。
「新しいデータセット」をクリックします。
「Athena」をクリックします。
適当なデータソース名を入力し、データソースを作成ボタンをクリックします。
Athena に作成したテーブルを選択します。
「データクエリを直接実行」を選択して「Visualize」をクリックします。
グラフを作成できるところまできましたが、受注金額のデータ型がStringだったので、Decimal に変更していきます。
「Edit」をクリックします。
「受注金額」のところにある String をクリック後 int を選択し、上部にある保存をクリックします。
これで準備ができましたので、以下のように可視化することができました。
あとは、CData Sync にて日々ジョブを運用していくことで、データの準備は不要となり、ユーザはQuickSight でデータを確認するだけになります。
最後に
いかがでしたでしょうか。
kintone → S3 → Athena → QuickSight といったデータの流れを、一部SQLはありましたが、ほぼほぼノーコードで実現できました。
この方法で、kintone 以外にもCData で提供しているドライバーであれば他のデータソースに変更することもできますので、是非皆様のケースにあったデータソースを使って「XXX → S3 → Athena → QuickSight」でデータ可視化を試してみていただければと思います。
CData Sync は30日間無料でご利用いただけますので、是非お試しください！
www.cdata.com
関連コンテンツ
