FTP/FTPS/SFTPのデータをBigQueryに統合し、データポータル(旧DataStudio)で可視化する

FTP/FTPS/SFTPのデータをBigQueryに統合してユーザーの行動を可視化してサービスを改善

## 概要
運営しているサービスのユーザーの行動ログをFTP/FTPS/SFTPに溜めており、サービス改善のためにデータ分析を行おうと考える方もいらっしゃると思います。そこで今回はFTP/FTPS/SFTPのデータをBigQueryに自動転送し、データを可視化するまでを行ってみます。
<img width="1791" alt="qiita_20201006_1.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/ef97d4f2-fe6f-36d3-b4a8-7517229ce0f4.png">
## ゴール
↓こんなのを15分くらいで作り上げます（当然、作ったあとは自動で最新値が更新されるようにします）
<img width="1791" alt="qiita_20191210_1.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/5dcb8951-01c6-e983-c401-2c6004ec013d.png">
## こんな人におすすめ
* FTP/FTPS/SFTPにサービスの情報等を溜めている方
* FTP/FTPS/SFTPにあるデータをBigQueryで分析したい方
* 自前のコードを用いたデータ取り込みに疲れている方・・・

##1. DWHと同期する手段の選定<br>
###1-1. DWHの選定
まずはデータをどこに集約するか、DWH（データウェアハウス）を選定します。

* Google BigQuery
* Amazon Redshift
* MySQLやPostgreSQL

今回はBigQueryを利用することにします。

###1-2. FTP/FTPS/SFTPのデータをBigQueryに転送する4つの方法
BigQueryにデータを集約することが決まったので、次は転送するための手段を検討していきます。
　1. FTP/FTPS/SFTPのデータをダウンロードし、手動でBigQueryにアップロードする
　2. FTP/FTPS/SFTPとBigQueryを各APIやライブラリ等を用いてプログラムを書いて連携する
　3. Embulkを利用し、自分で環境を構築する
　4. [trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=ftp_to_bigquery)を利用し、画面上で設定する

1は単発の実行であればよいのですが、定期的な取り込み用途だと毎回同じ作業を繰り返すことになり、非効率な作業になりがちです
2はAPIのキャッチアップ工数+プログラムを書く工数+環境構築工数が発生する他、エラー対応などの運用工数も継続的に発生します
3も2と同じくEmbulkはある程度の専門知識が必要になり、自前で環境構築・運用を行う手間が発生します。加えてエラーの内容が少し専門的なので、↓のように困っている人もいたりします。

そこで今回はEmblukの課題も解決してくれて、プログラムを書かずに画面上の設定で作業が完結する、4の[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=ftp_to_bigquery)というSaaSを利用します。

その他にも広告やデータベースなど、様々な分析データをETL・転送した事例をまとめました。
[troccoの使い方まとめ（CRM・広告・データベース他）]
(https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)

## 2. troccoでFTP/FTPS/SFTP→BigQueryの転送自動化
### 2-0. 事前準備
[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=ftp_to_bigquery)のアカウントおよびFTP/FTPS/SFTPのホスト情報等が必要です。
[無料トライアル](https://trocco.io/inquiry_trial/new?utm_medium=social&utm_source=qiita&utm_campaign=ftp_to_bigquery)もやっているみたいなので、事前に申し込み・登録しておいてください！
（申込時に、この記事を見た旨を記載して頂ければご案内がスムーズです）
### 2-1. 転送元・転送先を決定
[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=ftp_to_bigquery)にアクセスし、ダッシュボードから転送設定を作成ボタンを押します。
<img width="1792" alt="qiita_20200827_2.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/098f74e5-a834-6b9f-0ea2-2b9c592a6169.png">
FTP/FTPSから転送したい場合は転送元にFTPを、SFTPから転送したい場合は転送元にSFTPを指定し、転送先にBigQueryを選択して転送設定作成ボタンを押します。（画像は転送元にFTPを選んだものです。）
<img width="1792" alt="qiita_20200827_3.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/c7ab5f3b-12aa-5f75-26a5-be5245f7d353.png">
すると、設定画面になるので、設定画面から必要な情報を入力していきます。
### 2-2. FTP/FTPS/SFTPとの連携設定
転送設定の名前とメモを入力したら、「転送元の設定」内の「接続情報を追加」ボタンを押します。
<img width="1792" alt="qiita_20200827_3.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/5edc9b88-fd43-98b3-57c2-5d7ed9533bf0.png">
<img width="1792" alt="qiita_20200827_4.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/f9b226aa-2865-9113-8ed2-9e8face8b663.png">
別タブで接続情報の新規作成画面が開きますので、FTP/FTPS/SFTPに関する情報を入力して、「保存」ボタンを押します。画像はFTPの接続設定作成画面であり、FTPSを利用する場合はSSL通信を有効にしてPEMファイルの内容を添付します。SFTPを利用する場合は画像の内容に加えて秘密鍵、秘密鍵のパスフレーズを入力します。
<img width="1792" alt="qiita_20200827_5.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/d2ec5360-00f3-88a2-bd00-cab6fb065989.png">
再度転送設定画面に戻り、接続情報の「再読込」ボタンを押すと、作成した接続情報が選択できるかと思います。
<img width="1101" alt="qiita_20200827_6.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/8813a8ee-b82a-cc92-b4cc-5ac20ae577fb.png">
### 2-3. FTP/FTPS/SFTPからのデータ抽出設定
これでFTP/FTPS/SFTPとの連携は完了です。次に、どのようなデータを取得するか設定していきましょう。
ここでは試しにFTP/FTPS/SFTPの中に溜めておいたあるサイトのログを取得します。
パスプレフィックスで取得したいデータのディレクトリを指定します。
入力内容はFTP/FTPS/SFTPのいずれでもほぼ変わりません。
<img width="1101" alt="qiita_20200827_7.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/3a761ec4-d67f-a3c4-6862-ea5312a3142a.png">
### 2-4. 転送先BigQueryの設定
転送元と同じ要領で設定していきます。
データセットとテーブルはお好きな名前を入力してください。自動生成オプションを有効にすれば、データセット・テーブルが自動作成されます。
また、カスタム変数を使うことにより、ジョブ実行時に指定の値に置き換えることができます。
BigQueryデータセットのロケーションを指定することができます。デフォルトはUSリージョンです。
<img width="988" alt="qiita_20200827_8.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/366ddfa8-b43f-6bf5-22af-4a97ac3ecd5d.png">
これで入力は完了です。「保存して自動データ設定・プレビューへ」をクリックし、確認作業に進みましょう。
### 2-5. データのプレビュー
転送元のデータがプレビューされます。ここではFTP/FTPS/SFTPから取り込んだデータが表示されています。
問題ないので、このまま「スケジュール・通知設定」に進みます。
<img width="1792" alt="qiita_20200827_9.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/cf124fd4-91b6-7551-4806-9e359749d709.png">
### 2-6. スケジュール・通知設定
以下のように実行スケジュールを設定することで、転送を自動化することが出来ます。
<img width="1792" alt="qiita_20200827_10.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/82934171-8ae9-114c-cff6-e92513c84d30.png">
### 2-7. データ転送ジョブの実行
設定は以上です。最後に、手動で転送ジョブを実行し、BigQueryにデータを送ってみましょう。
実行はジョブ詳細画面の「実行」ボタンを押すだけです。
<img width="1792" alt="qiita_20200827_11.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/bb3e10b7-924c-da81-2b7d-db5dc8e106e9.png">
## 3. BigQueryの設定
特に設定することありません。データが溜まっているので、今すぐに分析・可視化を行うことが出来ます。
念の為データをプレビューして確認してみます。
<img width="1792" alt="qiita_20200827_12.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/f2231574-4aa8-838c-6e8b-082a178f6929.png">
## 4. Googleデータポータル（旧データスタジオ）で可視化
BigQueryの画面から、「エクスポート > データポータル」を選択します。
<img width="240" alt="qiita_20200827_13.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/eb566777-527e-55a4-5ca1-96bd87e0bd3d.png">
すると、以下のようなData Portalの画面に遷移します。
試しに、日ごとのクリック数を可視化してみましょう。
図の指示のとおりに変更してみてください。
<img width="1792" alt="qiita_20200827_14.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/ea33a4d4-f3bf-1a8f-33ae-deb53051c04c.png">
すると、以下のようなグラフが出来上がります。
<img width="1792" alt="qiita_20200827_15.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/697569/c14e98aa-02ea-12a2-90be-e89946e669d1.png">
この画面はデータポータルの「エクスプローラ」という機能になります。
<img width="267" alt="qiita_20200827_16.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/03a167e2-d307-20af-a378-56712023708e.png">
データポータルのトップ画面から、「レポート」を作成し、上述の通りグラフ作成を行うと、サービス用ダッシュボードが出来上がります。
<img width="1791" alt="qiita_20200827_17.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/b225ec66-18b6-dc70-2361-bcf566745a54.png">
## まとめ
いかがでしたでしょうか。[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=ftp_to_bigquery)を使うと管理画面を触ることなく、簡単にデータを転送し、DWH(BigQuery)に貯めることが出来ました。
また、BigQueryにデータを貯めると、データポータルというGoogleの無料ダッシュボードですぐに可視化することが出来ます。
実際に弊社サービスの[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=ftp_to_bigquery)においても、マーケティングKPI等をこのような流れで収集・分析しています。

実際に試してみたい場合は、[無料トライアル](https://trocco.io/inquiry_trial/new?utm_medium=social&utm_source=qiita&utm_campaign=ftp_to_bigquery)を実施しているので、この機会にぜひ一度お試しください。
（申込時に、この記事を見た旨を記載して頂ければスムーズにご案内できます）

その他にも広告やデータベースなど、様々な分析データをETL・転送した事例をまとめました。
[troccoの使い方まとめ（CRM・広告・データベース他）]
(https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)