More than 3 years have passed since last update.

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

Last updated at 2020-12-01Posted at 2018-12-12

概要

MySQLからBigQueryへのデータ転送を行いたい
- 出来るだけ手軽に・簡単に・3分くらいで実現したい
- データ転送専門のマネージド・サービス「trocco」を使って実現してみる

trocco(トロッコ)とは

クラウド間・DB/DWH間のデータ転送処理をマネージド・サービス化したもの
- 内部的には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会員登録
- 会員登録ページから会員登録をしておく
MySQLのセキュリティ設定
- troccoは18.182.232.211のIPアドレスでMySQLに接続します
- ファイアウォールやセキュリティグループの設定でこのIPからのアクセスを許可する必要があります

1. 転送設定を作成する

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

トップページより、転送元にMySQLを、転送先にBigQueryを選択し、転送設定作成ボタンを押す

1-2. 接続設定を作成

転送設定の名前とメモを適当に入力したら、「転送元の設定」内の「接続情報を追加」ボタンを押します。

別タブで接続情報の新規作成画面が開きますので、予め準備した接続情報を入力して下さい。

再度転送設定画面に戻り、MySQL接続情報の「再読込」ボタンを押すと、作成した接続情報が選択できるかと思います。

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

1-3. 転送元の設定

下記のように入力します。接続確認を押せば、入力した情報が正しいかを確認出来ます。
差分転送機能もサポートしているので、日次で増分データのみ転送したいみたいな用途にも使えます。

1-4. 転送先の設定

転送元と同じ要領で設定していきます。
データセットとテーブルは既存のものを使っても良いですし、新しい名前で入力した上で自動生成オプションを有効にすれば、自動で作成されます。
転送モードはappend(追記)やreplace(テーブル作り替え)などが選択可能で、いずれもトランザクション内で処理されるため、途中で処理が失敗したらロールバックされます。
少量のデータを定期的に転送する場合はreplaceを、大量データで増分のみを転送する場合はappendを選択すると良いかと思います。