Help us understand the problem. What is going on with this article?

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

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

概要

運営しているサービスのユーザーの行動ログをFTP/FTPS/SFTPに溜めており、サービス改善のためにデータ分析を行おうと考える方もいらっしゃると思います。そこで今回はFTP/FTPS/SFTPのデータをBigQueryに自動転送し、データを可視化するまでを行ってみます。
qiita_20201006_1.png

ゴール

↓こんなのを15分くらいで作り上げます(当然、作ったあとは自動で最新値が更新されるようにします)
qiita_20191210_1.png

こんな人におすすめ

  • FTP/FTPS/SFTPにサービスの情報等を溜めている方
  • FTP/FTPS/SFTPにあるデータをBigQueryで分析したい方
  • 自前のコードを用いたデータ取り込みに疲れている方・・・

1. DWHと同期する手段の選定

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を利用し、画面上で設定する

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

そこで今回はEmblukの課題も解決してくれて、プログラムを書かずに画面上の設定で作業が完結する、4のtroccoというSaaSを利用します。

その他にも広告やデータベースなど、様々な分析データをETL・転送した事例をまとめました。
troccoの使い方まとめ(CRM・広告・データベース他)

2. troccoでFTP/FTPS/SFTP→BigQueryの転送自動化

2-0. 事前準備

troccoのアカウントおよびFTP/FTPS/SFTPのホスト情報等が必要です。
無料トライアルもやっているみたいなので、事前に申し込み・登録しておいてください!
(申込時に、この記事を見た旨を記載して頂ければご案内がスムーズです)

2-1. 転送元・転送先を決定

troccoにアクセスし、ダッシュボードから転送設定を作成ボタンを押します。
qiita_20200827_2.png
FTP/FTPSから転送したい場合は転送元にFTPを、SFTPから転送したい場合は転送元にSFTPを指定し、転送先にBigQueryを選択して転送設定作成ボタンを押します。(画像は転送元にFTPを選んだものです。)
qiita_20200827_3.png
すると、設定画面になるので、設定画面から必要な情報を入力していきます。

2-2. FTP/FTPS/SFTPとの連携設定

転送設定の名前とメモを入力したら、「転送元の設定」内の「接続情報を追加」ボタンを押します。
qiita_20200827_3.png
qiita_20200827_4.png
別タブで接続情報の新規作成画面が開きますので、FTP/FTPS/SFTPに関する情報を入力して、「保存」ボタンを押します。画像はFTPの接続設定作成画面であり、FTPSを利用する場合はSSL通信を有効にしてPEMファイルの内容を添付します。SFTPを利用する場合は画像の内容に加えて秘密鍵、秘密鍵のパスフレーズを入力します。
qiita_20200827_5.png
再度転送設定画面に戻り、接続情報の「再読込」ボタンを押すと、作成した接続情報が選択できるかと思います。
qiita_20200827_6.png

2-3. FTP/FTPS/SFTPからのデータ抽出設定

これでFTP/FTPS/SFTPとの連携は完了です。次に、どのようなデータを取得するか設定していきましょう。
ここでは試しにFTP/FTPS/SFTPの中に溜めておいたあるサイトのログを取得します。
パスプレフィックスで取得したいデータのディレクトリを指定します。
入力内容はFTP/FTPS/SFTPのいずれでもほぼ変わりません。
qiita_20200827_7.png

2-4. 転送先BigQueryの設定

転送元と同じ要領で設定していきます。
データセットとテーブルはお好きな名前を入力してください。自動生成オプションを有効にすれば、データセット・テーブルが自動作成されます。
また、カスタム変数を使うことにより、ジョブ実行時に指定の値に置き換えることができます。
BigQueryデータセットのロケーションを指定することができます。デフォルトはUSリージョンです。
qiita_20200827_8.png
これで入力は完了です。「保存して自動データ設定・プレビューへ」をクリックし、確認作業に進みましょう。

2-5. データのプレビュー

転送元のデータがプレビューされます。ここではFTP/FTPS/SFTPから取り込んだデータが表示されています。
問題ないので、このまま「スケジュール・通知設定」に進みます。
qiita_20200827_9.png

2-6. スケジュール・通知設定

以下のように実行スケジュールを設定することで、転送を自動化することが出来ます。
qiita_20200827_10.png

2-7. データ転送ジョブの実行

設定は以上です。最後に、手動で転送ジョブを実行し、BigQueryにデータを送ってみましょう。
実行はジョブ詳細画面の「実行」ボタンを押すだけです。
qiita_20200827_11.png

3. BigQueryの設定

特に設定することありません。データが溜まっているので、今すぐに分析・可視化を行うことが出来ます。
念の為データをプレビューして確認してみます。
qiita_20200827_12.png

4. Googleデータポータル(旧データスタジオ)で可視化

BigQueryの画面から、「エクスポート > データポータル」を選択します。
qiita_20200827_13.png
すると、以下のようなData Portalの画面に遷移します。
試しに、日ごとのクリック数を可視化してみましょう。
図の指示のとおりに変更してみてください。
qiita_20200827_14.png
すると、以下のようなグラフが出来上がります。
qiita_20200827_15.png
この画面はデータポータルの「エクスプローラ」という機能になります。
qiita_20200827_16.png
データポータルのトップ画面から、「レポート」を作成し、上述の通りグラフ作成を行うと、サービス用ダッシュボードが出来上がります。
qiita_20200827_17.png

まとめ

いかがでしたでしょうか。troccoを使うと管理画面を触ることなく、簡単にデータを転送し、DWH(BigQuery)に貯めることが出来ました。
また、BigQueryにデータを貯めると、データポータルというGoogleの無料ダッシュボードですぐに可視化することが出来ます。
実際に弊社サービスのtroccoにおいても、マーケティングKPI等をこのような流れで収集・分析しています。

実際に試してみたい場合は、無料トライアルを実施しているので、この機会にぜひ一度お試しください。
(申込時に、この記事を見た旨を記載して頂ければスムーズにご案内できます)

その他にも広告やデータベースなど、様々な分析データをETL・転送した事例をまとめました。
troccoの使い方まとめ(CRM・広告・データベース他)

hiro_koba_jp
ゴルフエンジニア
primenumber_inc
ビッグデータ分析基盤向けデータ統合自動化サービス「 trocco」などを提供する、データエンジニアリングスタートアップ
https://primenumber.co.jp
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away