概要
Webサイトを運営していればユーザーの行動ログをFTP/FTPS/SFTPに溜めており、サービスの改善のためにそれらのデータ分析を行おうと考えている方もいると思います。ただしこのようなWeb行動ログをデータベースとして活用するには別途分析機能を持ったサービスと連携する必要があります。特に行動ログのデータはデータ量が膨大で、EXCELのような表計算ソフトでは処理が困難です。このように膨大なデータを分析するには、別途データの高速処理に長けたDWH（データウェアハウス）へとデータを統合し、連携したBIツールでデータを可視化するというデータ分析基盤の構築が有効です。
そこで今回はFTP/FPTS/SFTPのデータをtrocco®というデータ分析基盤構築サービスを利用してGoogle BigQueryへ統合し、Looker Studio（旧：Googleデータポータル）を利用してデータを可視化するまでをやってみます。
ゴール
↓このようなグラフを15分程度で作成します。作成後はtrocco®の機能を利用して自動でデータが最新値に更新されるように設定します。
こんな人におすすめ
- FTP/FTPS/SFTPにサービスログを溜めている
- FTP/FTPS/SFTPにあるデータをGoogle BigQueryへ統合したい
- 手作業のデータ取り込みが面倒
1. DWHと同期する手段の決定
1-1. DWHの選定
まずはデータを集約するDWHを決めます。
- Google BigQuery
- Amazon Redshift
- MySQLやPostgreSQL
trocco®はいずれのサービスにも対応していますが、今回はGoogle BigQueryを利用することにします。
1-2. FTP/FTPS/SFTPのデータをGoogle BigQueryに転送する4つの方法
Google BigQueryにデータを集約することが決まったので、次は転送するための手段を検討していきます。
1. FTP/FTPS/SFTPのデータをダウンロードし、手動でGoogle BigQueryにアップロード
2. FTP/FTPS/SFTPとGoogle BigQueryを各APIやライブラリ等を用いてプログラムを書いて連携
3. Embulkを利用して自分で環境を構築
4. trocco®を利用して画面上の設定のみで転送
1の方法では、単発の実行であればよいのですが、データを定期的に取り込んで分析することを考えると同じ作業を繰り返すのは非効率です。
2の方法では、まずAPIに関するキャッチアップが必要になります。その後自分でプログラムを書く工数、環境構築の手間が発生するほか、分析基盤のメンテナンス、エラー対処の必要が生じます。
3の方法についてもEmbulkについてある程度専門的な知識が必要になり、API連携と同じく自分で環境構築・運用を行う手間が発生します。エラーの内容もより専門的になるので、対処に困ってしまうケースも考えられます。
今回はEmblukの課題も解決してくれて、プログラムを書かずに画面上の設定で作業が完結する、4のtrocco®というデータ分析基盤構築サービスを利用します。
2. trocco®でFTP/FTPS/SFTP → BigQueryの転送自動化
2-0. 事前準備
事前準備としてtrocco®のアカウントおよびFTP/FTPS/SFTPのホスト情報等が必要になります。
trocco®はフリープランを実施しているため、ぜひ事前に申し込みをしておきましょう。
https://trocco.io/lp/inquiry_free.html
2-1. 転送元・転送先を決定
trocco®にアクセスし、ダッシュボード画面から「転送設定を作成」ボタンを押します。
FTP/FTPSから転送したい場合は転送元にFTPを、SFTPから転送したい場合は転送元にSFTPを指定します。転送先にはGoogle BigQueryを選択して「この内容で作成」ボタンを押します。
（画像では転送元にFTPを選択しています。）
設定画面になるので、必要な情報を入力していきます。
2-2. FTP/FTPS/SFTPとの連携設定
trocco®には社内のユーザー間でチームを作成し、チームで転送設定を共有するチーム機能があります。チームの他メンバーにも転送設定の内容がわかるよう転送設定の名前とメモを入力したら次に進み、「転送元の設定」内の「接続情報を追加」ボタンを押します。
別のタブで接続情報の新規作成画面が開きます。
FTP/FTPS/SFTPに関する情報を入力して、「保存」ボタンを押します。画像はFTPの接続設定作成画面であり、FTPSを利用する場合はSSL通信を有効にしてPEMファイルの内容を添付します。SFTPを利用する場合は画像の内容に加えて秘密鍵、秘密鍵のパスフレーズの入力が必要です。
再度転送設定画面に戻り、「接続情報を読み込む」ボタンを押すと、作成した接続情報が選択できるようになります。
2-3. FTP/FTPS/SFTPからのデータ抽出設定
これでFTP/FTPS/SFTPとの連携は完了です。次に取得するデータについての設定を入力していきます。
ここでは試しにFTP/FTPS/SFTPの中に溜めておいたあるサイトのログを取得します。
パスプレフィックスで取得したいデータのディレクトリを指定します。
入力内容はFTP/FTPS/SFTPのいずれでもほぼ変わりません。
2-4. 転送先Google BigQueryの設定
転送元と同じ要領で設定していきます。
データセットとテーブルはお好きな名前を入力してください。自動生成オプションを有効にすれば、データセット・テーブルが自動作成されます。
またカスタム変数を使うことにより、ジョブ実行時に指定の値に置き換えることができます。
Google BigQueryデータセットのロケーションを指定できます。なおデフォルトの設定ではUSリージョンです。
これで入力は完了です。「保存して自動データ設定・プレビューへ」をクリックし、確認作業に進みましょう。
2-5. データのプレビュー
FTP/FTPS/SFTPからどのようにデータが取得されるかプレビューが作成されます。
取得したいデータがプレビューに表示されていなければ再度設定画面に戻って転送設定をやり直します。
2-6. スケジュール・通知設定
設定を完了したら転送設定一覧からいま作成した転送設定を選択し、「スケジュール設定」のタブを開きます。画像のようにモーダルが出るので、実行スケジュールを設定することで、転送を自動化することが出来ます。
2-7. データ転送ジョブの実行
設定は以上です。以後はスケジュール設定に従って自動で転送が実行されますが、今回は手動で転送ジョブを実行し、Google BigQueryにデータを送ってみましょう。
実行はジョブ詳細画面の「実行」ボタンを押すだけです。
3. Google BigQueryの設定
Google BigQuery側で設定することは特にありませんが、コンソール画面から確認するとデータが転送されていることが確認出来ます。
4. Looker Studio（旧：Googleデータポータル）で可視化
最後にLooker Studioを用いたデータの可視化を行います。Google BigQueryの画面から、「エクスポート > データポータル」を選択します。
以下のようなLooker Studioの画面に遷移します。
試しに日ごとのクリック数を可視化してみましょう。
すると、以下のようなグラフが出来上がります。
この画面はLooker Studioの「エクスプローラ」という機能になります。
Looker Studioのトップ画面から、「レポート」を作成し、同様にいくつかグラフを作成するとサービス用ダッシュボードが出来上がります。
まとめ
いかがでしたでしょうか。trocco®を使うと画面上の設定のみでGoogle BigQueryへデータを統合することが出来ました。また連携するLooker Studioでデータの可視化も容易に行なえます。
trocco®では、クレジットカード不要のフリープランをご案内しています。ご興味がある方はぜひこの機会に一度お試しください。
