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

## 概要
* MySQLからBigQueryへのデータ転送を行いたい
    * 出来るだけ手軽に・簡単に・3分くらいで実現したい
    * データ転送専門のマネージド・サービス「[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysql_to_bigquery)」を使って実現してみる

## trocco(トロッコ)とは
[https://trocco.io](https://trocco.io/lp/embulk.html?utm_medium=social&utm_source=qiita&utm_campaign=mysql_to_bigquery)

* クラウド間・DB/DWH間のデータ転送処理をマネージド・サービス化したもの
    * 内部的には[Embulk](https://github.com/embulk/embulk)やAWS/GCP SDKなどを使用（※1）
* 簡単なフォーム入力のみ（＝開発・運用不要）でビッグデータ規模の転送が実現可能
* データエンジニアリングやビジネス活用に必要な機能が全て揃っている
    * データスキーマの自動推論機能や簡易ETL（フィルタ）
    * リトライ設定、スケジュール実行、Slack通知といった運用サポート機能

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

## 対象
* 分析基盤を持つ企業の…
    * エンジニア、データサイエンティスト
    * データへのアクセス権限があるディレクター・マーケター
* こんな用途で…
    * アプリケーションDB内にある会員データ・商品マスタをBigQueryに転送し、結合して分析したい
    * MySQLに貯めてたアクセスログを、そろそろDWH(BigQuery)に転送してちゃんと分析したい
    * Embulkのホスティング版として、もっと手軽にEmbulk使いたい

## 用意するもの
* MySQL接続情報（転送元テーブルへの読み取り権限が必要）
    * 接続先ホスト（例 `trocco.hogehoge.ap-northeast-1.rds.amazonaws.com`）
    * 接続先ポート（例 `3306`）
    * 接続ユーザー（例 `readonly_user`）
    * 接続パスワード（例 `hogehoge`）
* BigQuery接続情報（転送先データセット・テーブルへの書き込み権限が必要）
    * JSON Key（例 `{''private_key_id'': ''123456789'', ''private_key'': ''-----BEGIN PRIVATE KEY-----...'', ''client_email'': ''...''}`）
* [trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysql_to_bigquery)会員登録
    * [会員登録ページ](https://trocco.io/users/sign_up?utm_medium=social&utm_source=qiita&utm_campaign=mysql_to_bigquery)から会員登録をしておく
* MySQLのセキュリティ設定
    * [trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysql_to_bigquery)は`18.182.232.211`のIPアドレスでMySQLに接続します
    * ファイアウォールやセキュリティグループの設定でこのIPからのアクセスを許可する必要があります

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

### 1-1. 転送元・転送先を決定
[トップページ](https://trocco.io/?utm_medium=social&utm_source=qiita&utm_campaign=mysql_to_bigquery)より、転送元にMySQLを、転送先にBigQueryを選択し、転送設定作成ボタンを押す
![スクリーンショット 2018-12-11 10.55.47.png](https://qiita-image-store.s3.amazonaws.com/0/61228/5c327d8d-f63f-a131-a273-8868d35e696e.png)

### 1-2. 接続設定を作成
転送設定の名前とメモを適当に入力したら、「転送元の設定」内の「接続情報を追加」ボタンを押します。
![転送設定編集___trocco.png](https://qiita-image-store.s3.amazonaws.com/0/61228/0974dd8c-b138-5eb1-73e9-5841129ad0ef.png)

別タブで接続情報の新規作成画面が開きますので、予め準備した接続情報を入力して下さい。
![MySQLの接続情報作成___trocco.png](https://qiita-image-store.s3.amazonaws.com/0/61228/22a01968-b11a-e69e-c4ee-035bbf7950a7.png)
再度転送設定画面に戻り、MySQL接続情報の「再読込」ボタンを押すと、作成した接続情報が選択できるかと思います。
![転送設定編集___trocco2.png](https://qiita-image-store.s3.amazonaws.com/0/61228/e0ad4940-3c6f-f548-fc2d-7d2feebc9d06.png)
このステップが若干面倒ではありますが、一度保存した設定は再利用したり、チーム内で共有することが出来ます。

### 1-3. 転送元の設定
下記のように入力します。接続確認を押せば、入力した情報が正しいかを確認出来ます。
差分転送機能もサポートしているので、日次で増分データのみ転送したいみたいな用途にも使えます。
![転送設定作成___trocco.png](https://qiita-image-store.s3.amazonaws.com/0/61228/f3d7881f-5ffd-f799-7a0d-d470447b1507.png)

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

## 2. プレビューを確認
転送元のデータがプレビューされます。列名や、自動推論されたデータ型が正しく判定されているか確認して下さい。
<img width="955" alt="転送詳細設定___trocco.png" src="https://qiita-image-store.s3.amazonaws.com/0/61228/4ae96343-d944-9175-3dde-deaa73ede9cc.png">
もしタイムスタンプのフォーマット等が誤っていた場合、データ設定内のカラム定義で修正することが可能です。
<img width="927" alt="転送詳細設定___trocco.png" src="https://qiita-image-store.s3.amazonaws.com/0/61228/11491cdd-3a89-0569-c3af-c01c17622f5a.png">


## 3. 定期実行・Slack通知を設定
定期実行のスケジューリングや転送失敗時のSlack通知設定が可能です。
![転送スケジュール・通知設定___trocco4.png](https://qiita-image-store.s3.amazonaws.com/0/61228/3a7e24a7-0364-70fc-4d99-958e6401c789.png)
![転送スケジュール・通知設定___trocco5.png](https://qiita-image-store.s3.amazonaws.com/0/61228/52f8fc6f-b2da-2835-c77a-6dacec8ed2bb.png)

## 4. ジョブ実行
定期実行の他に、アドホックな実行も可能です。
転送設定の詳細ページで実行ボタンを押せば実行が始まります。
![転送設定___trocco5.png](https://qiita-image-store.s3.amazonaws.com/0/61228/d8800a7d-32eb-b6da-aa8c-8f8e4ccc0d01.png)

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


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

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

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