CData Software Blog

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

kintoneのデータをS3に連携してAthena経由でQuickSightで可視化する:CDataSync

こんにちは、エンジニアの宮本です。
今回は、「kintone」 から 「Amazon S3」 へ CData Sync を使ってCSVファイルを連携し、その連携データを 「Amazon Athena」 を通してマネージドBIツール 「Quick Sight」で可視化する内容をご紹介します。

構成

f:id:sennanvolar44:20200420210737p:plain シナリオとしては、kintone にある案件情報を日次で連携し、担当者ごとの受注確度による受注金額を可視化していきます。

構成の大まかな流れは以下の通りです。

  • kintone から S3 へ定期的にデータ連携するジョブを、CDataSycn を使って行います。ジョブ実行時に、CDataSync から連携内容の DDL ステートメントを出力します。今回は kintone ですが別のデータソースでも構いません。対象データソースはこちらをご参照ください。

https://www.cdata.com/jp/sync/connections/

  • 生成したDDLステートメントAmazon Athena で実行して、連携データに対応するテーブルを作成します。

  • Quick Sight から Athena を参照して可視化

色々書いてますが、特段コーディング等は不要です。全てツールの力を用いて実現できます。

そもそも CData Sync とは?

f:id:sennanvolar44:20200420213536p:plain 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 版を使用して進めていきます。

詳しいインストール手順は、以下の記事をご参考ください。

www.cdatablog.jp

kintoneとS3の接続設定

まずはデータソース側の kintone への接続設定を行います。
CDataSync の接続設定で kintone を選択後、以下の画面で必要な接続情報を入力します。
f:id:sennanvolar44:20200420230807p:plain

次に、S3 への接続設定を行います。手順は kintone と全く同じです。

f:id:sennanvolar44:20200420231128p:plain

Athenaで使う DDL 生成のオプションをオンにする

S3 の接続設定画面上部にある「Advanced」タブをクリックします。画面が表示されたら、下の方にスクロールすると「Generate Hive DDL」という項目が表示されます。この項目にパスを設定すると、レプリケート時にDDL ステートメントを指定のパスに出力してくれるようになります。
今回は CDataSync を起動している Windowsマシンの C:\work フォルダに出力するよう指定しました。
f:id:sennanvolar44:20200420231424p:plain

ジョブを作成

CData Sync のトップ画面から新規ジョブの追加でジョブ作成画面を開きます。
今回は以下のクエリをセットします。

replicate deal_kintone select 部署名,受注確度,ご担当者名,案件名,更新日時,受注金額,顧客名,作成日時,受注予定日 from deal where ご担当者名 is not null

上記の「replicate deal_kintone 」は、deal_kintone という名前でレプリケート先にテーブルを作成するという指定ですが、今回はその名前でS3 にフォルダが作成されていきます。

f:id:sennanvolar44:20200420232526p:plain

スケジューリングを作成

ジョブ設定画面の中段にある「スケジュール」タブより、ジョブのスケジューリングを行います。今回は毎日夜中の2時に実行されるようにしています。(本記事ではスケジューリングの実行確認はしませんので設定までとなります)
f:id:sennanvolar44:20200420233049p:plain

ジョブを実行

チェックを入れた後に実行ボタンを押すことで、スケジューリングに関係なく実行することができます。実行後は、右側に更新時間とレプリケート件数が表示されます。
f:id:sennanvolar44:20200420233659p:plain

では、S3 を確認するとcsv ファイルが作成されているのがわかります。
赤枠を確認すると、replicate~ で指定した名前でフォルダが作成され、その中にファイルが作成されています。
f:id:sennanvolar44:20200420235409p:plain

続いて、生成されたDDL の確認です。
先ほど指定したパスを確認すると、DDL ステートメントが作成されているのがわかります。
f:id:sennanvolar44:20200420234254p:plain

中身はコチラ

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 ステートメントをペーストしてそのまま実行するだけで大丈夫です。
f:id:sennanvolar44:20200421000117p:plain

正常に実行されると「Query successful」が表示され、左側に「deal_kintone」というテーブルが作成されます。
f:id:sennanvolar44:20200421000326p:plain

select * from deal_kintone を入力して実行すると、ちゃんとデータが表示されました。
f:id:sennanvolar44:20200421001030p:plain

Quick Sight で可視化

これまでは、kintone → S3 → Athena と、キモとなるデータ連携部分の内容でした。最後に連携されたkintone の案件情報を可視化していきましょう。

まずは Quick Sight のアカウント作成です。スタンダード版で進めます。
f:id:sennanvolar44:20200421001747p:plain

アカウント名や対象のS3 を設定後、右下の完了ボタンをクリックします。
f:id:sennanvolar44:20200421002457p:plain

Quick Sight のトップ画面で「新しい分析」をクリックします。
f:id:sennanvolar44:20200421002725p:plain

「新しいデータセット」をクリックします。
f:id:sennanvolar44:20200421002926p:plain

「Athena」をクリックします。
f:id:sennanvolar44:20200421003201p:plain

適当なデータソース名を入力し、データソースを作成ボタンをクリックします。
f:id:sennanvolar44:20200421003527p:plain

Athena に作成したテーブルを選択します。
f:id:sennanvolar44:20200421005912p:plain

「データクエリを直接実行」を選択して「Visualize」をクリックします。
f:id:sennanvolar44:20200421010218p:plain

グラフを作成できるところまできましたが、受注金額のデータ型がStringだったので、Decimal に変更していきます。
f:id:sennanvolar44:20200421011321p:plain

「Edit」をクリックします。
f:id:sennanvolar44:20200421011557p:plain

「受注金額」のところにある String をクリック後 int を選択し、上部にある保存をクリックします。
f:id:sennanvolar44:20200421093459p:plain

これで準備ができましたので、以下のように可視化することができました。
f:id:sennanvolar44:20200421012822p:plain

あとは、CData Sync にて日々ジョブを運用していくことで、データの準備は不要となり、ユーザはQuickSight でデータを確認するだけになります。

最後に

いかがでしたでしょうか。 kintone → S3 → Athena → QuickSight といったデータの流れを、一部SQLはありましたが、ほぼほぼノーコードで実現できました。
この方法で、kintone 以外にもCData で提供しているドライバーであれば他のデータソースに変更することもできますので、是非皆様のケースにあったデータソースを使って「XXX → S3 → Athena → QuickSight」でデータ可視化を試してみていただければと思います。

CData Sync は30日間無料でご利用いただけますので、是非お試しください!

www.cdata.com