S3→BigQueryのデータ転送を、出来るだけラクにやる方法

## 概要
* S3からBigQueryへデータを転送し、分析に使いたい
    * 出来るだけ手軽に・簡単に・3分くらいで実現したい
    * 分析基盤向けデータ統合サービス「[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=s3_to_bigquery)」を使って実現してみる

※MySQL→BigQueryのデータ統合は、[こちら](https://qiita.com/hiro_koba_jp/items/8839bfb4fd3726f61f5a)の記事にあります
[troccoの使い方まとめ（CRM・広告・データベース他）]
(https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)

# troccoとは
[https://trocco.io/lp/index.html](https://trocco.io/lp/index.html)

* 分析基盤向けのデータ統合サービス
* 分析に必要な様々なデータ(DB/ストレージ/SaaS/API)を、DWHに統合可能
* DWH自体の管理機能や、運用保守サポート機能も豊富

## 用意するもの
* S3接続情報（転送元テーブルへの読み取り権限が必要）
    * IAM UserのAWSクレデンシャル（AccessKeyとSecretKey）
* BigQuery接続情報（転送先データセット・テーブルへの書き込み権限が必要）
    * JSON Key（例 `{''private_key_id'': ''123456789'', ''private_key'': ''-----BEGIN PRIVATE KEY-----...'', ''client_email'': ''...''}`）
* S3のセキュリティ設定
    * [trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=s3_to_bigquery)は`18.182.232.211` `13.231.52.164` `3.113.216.138`の3つのIPアドレスでS3に接続します
    * ポリシー等でIP制限等を行っている場合は、こちらのIPを許可しておいてください。
* [trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=s3_to_bigquery)の登録
    * [無料トライアル](https://trocco.io/inquiry_trial/new?utm_medium=social&utm_source=qiita&utm_campaign=s3_to_bigquery)もやっているみたいなので、事前に申し込み・登録しておいてください！
    * 申込時に、この記事を見た旨を記載して頂ければご案内がスムーズです

## 1. 転送設定を作成する

### 1-1. 転送元・転送先を決定
[トップページ](https://trocco.io/)より、転送元にS3を、転送先にBigQueryを選択し、転送設定作成ボタンを押す
<img width="1792" alt="qiita_20191205_1.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/98810693-011c-ec9f-ea32-056857140964.png">

すると、以下のような画面になるかと思いますが、これが設定画面です。
わからないことは右下のチャットで、直接聞いたりSlackのサポートから問い合わせることが出来ます。
<img width="1792" alt="qiita_20191205_2.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/a6f02886-fa16-e2a5-33ca-cfb3842fd910.png">

### 1-2. 接続設定を作成
転送設定の名前とメモを適当に入力したら、「転送元の設定」内の「接続情報を追加」ボタンを押します。
<img width="1107" alt="qiita_20191205_3.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/97ea9284-cf2b-3c9f-851d-7b1b95c445c4.png">

別タブで接続情報の新規作成画面が開きますので、予め準備した接続情報を入力して下さい。
<img width="915" alt="qiita_20191205_4.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/d8c5a271-c28a-be1c-d563-4bc1adbaf576.png">

再度転送設定画面に戻り、S3接続情報の「再読込」ボタンを押すと、作成した接続情報が選択できるかと思います。
<img width="1113" alt="qiita_20191205_5.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/2d4a9843-5cc7-3b2d-033e-d20966630071.png">

このステップが若干面倒ではありますが、一度保存した設定は再利用したり、チーム内で共有することが出来ます。

### 1-3. 転送元の設定
下記のように入力していきます。
入力ファイル形式はJSON Lines/JSONPath/CSVの他、オプションでAvroも対応しているみたいです。
圧縮形式はGzipとBzip2に対応しており、自動で判定されます。Zipも対応中だとか。tarとかも要望すれば対応してくれるそうです。

また、差分転送機能もサポートしているので、例えばあるディレクトリ（パスプレフィックス）配下に、日次でファイルが増えていくような場合、増分のファイルだけを転送するようなユースケースでもお使いいただけます。
（その他の項目の詳しい内容は[ドキュメントページ](https://trocco.zendesk.com/hc/ja/articles/360019972173-%E8%BB%A2%E9%80%81%E5%85%83-S3)を参照してください）

今回はパス配下には1ファイルしか無いので、全件転送で次に進みます。
<img width="879" alt="qiita_20191205_6.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/ab97ceb6-9b88-28a4-04f1-108641b6ad0c.png">

### 1-4. 転送先の設定
転送元と同じ要領で設定していきます。
データセットとテーブルは既存のものを使っても良いですし、新しい名前で入力した上で自動生成オプションを有効にすれば、自動で作成されます。
転送モードは`append(追記)`や`replace(テーブル作り替え)`などが選択可能で、いずれもトランザクション内で処理されるため、途中で処理が失敗したらロールバックされます。
少量のデータを定期的に転送する場合は`replace`を、大量データで増分のみを転送する場合は`append`を選択すると良いかと思います。
<img width="897" alt="qiita_07.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/50baad1f-955e-5331-1792-da7c43cc994e.png">

また、入力した情報に誤りがないか、疎通確認や権限確認をすることが出来ます。
（転送元も同様に確認が出来ます）
<img width="1047" alt="qiita_08.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/77fa5c51-b81a-ef60-8f97-b5e01cc5f9d6.png">
この段階で疎通や権限が正しいことを確認できると、安心感がありますね。

## 2. プレビューを確認
転送元のデータがプレビューされます。列名や、自動推論されたデータ型が正しく判定されているか確認して下さい。
<img width="1792" alt="qiita_20191205_7.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/648370a9-af43-a69a-cb12-cc0320127c19.png">

自動推論機能により、CSVの区切り文字（デリミタ）や文字エンコーディング等も自動で判定されます。
万が一誤っていたら、入力オプションから変更してください。
<img width="1458" alt="qiita_20191205_8.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/4bc981dd-367e-c500-cc43-e1bfc4d21887.png">

データのスキーマは自動で推論されますので、間違っていたら「データ設定」タブから修正することも出来ます。
また、BigQueryの分割テーブルなど、詳細な設定も「入力設定」や「出力設定」から行うことが出来ます。
<img width="1419" alt="qiita_10.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/95de8891-ba76-dbea-6f1c-fcea86893262.png">

また、ここでメールアドレスのマスキングや、Ruby ETLなども組めます。詳しくは[こちらの記事](https://qiita.com/hiro_koba_jp/items/e1877be8ed31697f9a0d)で解説していますのでご参照ください。

## 3. 定期実行・Slack通知を設定
最後は定期実行のスケジューリングや、通知設定の入力画面です。
<img width="1792" alt="qiita_20191205_9.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/ee6be98a-15da-9a31-8396-a51969f5536c.png">

日次バッチだったら、以下のようなUIで、数クリックで設定可能です。
（最短１分間隔で転送可能）
<img width="499" alt="qiita_12.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/06167554-05ef-9f7b-182f-6a0cb6d1669f.png">

地味に嬉しいのが、この通知設定。
当然ジョブが失敗したらSlack通知とかはできるんですが、よくある「データが0件でジョブが正常完了してしまった」みたいなのまで検知できるんです。
<img width="1792" alt="qiita_20191205_10.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/4cf05217-9f75-8cd8-a310-c1c982d79cd1.png">

## 4. ジョブ実行
定期実行の他に、アドホックな実行も可能です。
下図の実行ボタンを押せば実行が始まります。
<img width="1792" alt="qiita_20191205_11.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/6edd4805-cbd7-5fbf-9c37-48e216fe2672.png">

実行画面こんな感じです。ちなみにジョブ実行のたびに専用コンテナが立ち上がり、かつCPUやメモリの割当を動的に変更できるので、パフォーマンス要件が厳しい場合も大丈夫らしいです。
<img width="1792" alt="qiita_20191205_12.png" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/b4b17725-1ce7-3668-6cd5-b0a415ebcd0a.png">

## まとめ
* [trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=s3_to_bigquery)を使うことで、約3分ほどでデータの転送設定を作ることが出来ました
* 昨今機会が急激に増えつつあるデータ転送を、より手軽に実現できました
* 複雑な作業はほぼないので、エンジニアだけでなく、マーケター・ディレクターの方まで幅広く利用出来るかと思います

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

[troccoの使い方まとめ（CRM・広告・データベース他）]
(https://qiita.com/hiro_koba_jp/items/2b2caa040804e402bda7)

※1 今回は[embulk-input-s3](https://github.com/embulk/embulk-input-s3)と[embulk-output-bigquery](https://github.com/embulk/embulk-output-bigquery)のホスティング環境を利用