MySQLのデータをCDC形式で転送し、BigQueryに統合する

## 概要
MySQLからBigQueryにデータを転送する際、転送の設定が複雑で面倒に感じることがあるかもしれません。
そこで、今回は[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysqlcdc_to_bigquery)というETL & ワークフローサービスを使い、CDC形式で転送する方法をご紹介します。
CDC形式を取り入れることで、効率的で低レイテンシなデータ転送を実現することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/7b1399ae-59ed-6bd7-0ee3-16ea88a66433.png)


### 1-0. CDCとは？
今回はMySQLのbinlogをCDC(Change Data Capture = 変更データキャプチャ)形式で転送します。
これにより、初回のみ全件転送、それ以後は差分転送を実行し、転送量の削減・転送の高速化を図ることができます。

DBへの負荷を抑えつつバッチ処理でデータ同期を行いたいときには、CDC形式での転送がうってつけです。

全件転送との比較は以下の通りです。


|                                  | 全件転送                 | CDC                                  | 
| -------------------------------- | ------------------------ | ------------------------------------ | 
| trocco転送量(初回実行時)         | ソーステーブルの全データ | ソーステーブルの全データ             | 
| trocco転送量(2回目以降)          | ソーステーブルの全データ | 更新ログのみ(※1)                    | 
| スキーマ変更発生時<br>(列追加等) | 自動追従                 | 自動追従                             | 
| データ削除(DELETE)の挙動         | 物理削除                 | 論理削除                             | 
| データ更新(UPDATE)の挙動         | 物理更新                 | 物理更新                             | 
| BigQueryスキャン量               | なし                     | 現在のBigQuery上のテーブル全量+更新ログ    | 
| データソースDBへの負荷           | 高                       | 低（初回転送時のみ全量のため高負荷） | 

※1 INSERT,DELETE,DDL文については1行文、UPDATE文については更新前後の2行分

[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysqlcdc_to_bigquery)でCDC転送を行う際には
①[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysqlcdc_to_bigquery)がMySQLのbinlogをBigQuery上に転送
②binlogのデータから重複を排除し、それぞれのレコードの最新のレコードの集合を作成
③↑の集合を現在のBigQueryテーブルとマージし、マージ後のテーブルで置換する
という手順を踏んでいます。

CDC転送の詳細な手順については[こちらのドキュメント](https://docs.trocco.io/s/article/MySQL-to-BigQuery-%E8%BB%A2%E9%80%81-CDC)の「テクニカルオーバービュー」の項をご参照ください。

### 2-1. MySQLのパラメータ設定
[troccoのドキュメント](https://docs.trocco.io/s/article/MySQL-to-BigQuery-%E8%BB%A2%E9%80%81-CDC)を参照しつつ、MySQLのパラメータ設定を行います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/4c6147d7-1ea3-01f6-23a9-e2ef928559b8.png)

binlogの設定を反映させるためにはDBの再起動が必要です。
DBを再起動して次のステップに進みます。

### 2-2. MySQLの権限設定
[troccoのドキュメント](https://docs.trocco.io/s/article/MySQL-to-BigQuery-%E8%BB%A2%E9%80%81-CDC)を参照しつつ、MySQLの権限設定を行います。

ここではSELECTとREPLICATION CLIENTの２つの権限を設定します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/bd3d3602-4805-882e-6856-9db739b17676.png)

### 2-3. Primary Keyの設定
転送元のテーブルにはPrimary Keyが設定されている必要があります。
ここでは既に設定されているものとして、割愛します。

その他、[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysqlcdc_to_bigquery)で転送する際の必須条件などについては[こちらのドキュメント](https://docs.trocco.io/s/article/MySQL-to-BigQuery-%E8%BB%A2%E9%80%81-CDC)を参照してください。

これにてMySQL側での設定は完了です。
次に、[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysqlcdc_to_bigquery)の設定を行います。

### 3-0. [trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysqlcdc_to_bigquery)に登録
[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysqlcdc_to_bigquery)のアカウントが必要です。
無料トライアルも実施しているので、前もって申し込み・登録をしておいてください！
[https://trocco.io/lp/index.html](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysqlcdc_to_bigquery)
（申込時に、この記事を見た旨を記載して頂ければスムーズにご案内できます）

### 3-1. 転送元・転送先を決定
[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysqlcdc_to_bigquery)にアクセスし、ダッシュボードから「転送設定を作成」のボタンを押します。
![貼り付けた画像_2021_02_09_18_45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/6e04edcf-9a63-f89a-f95f-1cc5b37dc37e.png)

転送元にMySQL binlog、転送先にBigQueryを選択し、転送設定作成ボタンを押します。
![貼り付けた画像_2021_02_09_17_13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/34d06cf5-96d6-91df-747c-776d687d6c48.png)
すると、設定画面になるので、転送に必要な情報を入力していきます。

### 3-2. MySQLとの連携設定
転送設定の名前とメモを入力します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/0a664fcf-ff89-4542-581c-e08ab1d83c7f.png)

転送設定の名前を決めたら、「転送元の設定」内の「接続情報を追加」ボタンを押し、MySQLの接続情報の設定を行います。
![貼り付けた画像_2021_02_09_17_16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/64c08065-1283-39c7-5828-19eba9382e09.png)

接続設定の名前・ホスト・ポート・ユーザー名・パスワードを入力します。
![貼り付けた画像_2021_02_18_16_34.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/7d1d9f4b-d8d1-49c9-ef90-bd37487ec95d.png)

接続確認をしたのち、設定の保存をします。
![貼り付けた画像_2021_02_09_17_20.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/0e507e38-7272-c186-1eca-f6bcb3d9e2ce.png)

再度転送設定画面に戻り、「接続情報を読み込む」ボタンを押すと、作成した接続情報が選択できるかと思います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/6ff1ee25-6633-4cab-aa55-c5ff6ed6d9fc.png)

### 3-3. MySQLからのデータ抽出設定
これでMySQLとの連携は完了です。
次に、MySQLの取得データを設定します。  
まずは必須項目の「データベース名」「テーブル」をセレクトボックスの中から選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/4efeafad-5b18-f84e-c509-6f42d8ca5835.png)

次に、スキーマ追従の有効/無効を指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/044be366-97f9-30ab-859f-722caf7fddf2.png)

スキーマ追従が有効になっている場合、

   - クエリにRENAME COLUMNが含まれている場合、変更後の名前でデータ連携される
   - クエリにADD COLUMNが含まれている場合、追加したテーブルが自動で転送されるようになる

といった挙動になります。

最後に接続確認が通るか確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/ead45b85-d74a-d8b1-1b85-d92efdc13d8b.png)

大丈夫ですね。以上でMySQL側の設定は完了です。
次は転送先のBigQueryの設定を行っていきましょう。

### 3-4. 転送先BigQueryの設定
基本的には転送元と同じ要領です。
「接続情報を追加」ボタンからBigQueryの接続設定を行い、データベース・テーブル・データセットのロケーションを指定します。
![貼り付けた画像_2021_02_09_18_17.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/e0a3a925-e63f-2303-d223-b8e99cb78078.png)

最後に、接続確認が問題なく通るか確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/ad51b71a-2015-2a34-48a2-d0b10a9fafbb.png)

これで入力は完了です。「保存して自動データ設定・プレビューへ」をクリックして次に進みましょう。

### 3-5. カラム名・データ型の確認
「データプレビュー」の画面でカラム名・データ型の確認を行います。
MySQLのデータは自動的にBigQueryのデータ型に変換されます。

データ型については[こちらのドキュメント](https://docs.trocco.io/s/article/MySQL-to-BigQuery-%E8%BB%A2%E9%80%81-CDC)をご参照ください。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/fced3a28-b658-efae-1452-87f9751c102f.png)

問題なければ、転送設定の詳細画面に移りましょう。
![貼り付けた画像_2021_02_09_18_35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/740846ef-92c7-b1c0-833f-4ea8da654c17.png)


### 3-6. 初回転送
CDCは変更データのみを転送する方式ですが、初回だけは全件転送をする必要があります。
このステップでは、手動で全件転送を行います。

右上にある「実行」ボタンを押します。

![貼り付けた画像_2021_02_09_18_35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/ed10d3eb-c7d3-842c-2f9a-a4d5395bbf8b.png)

「全件転送を行う」にチェックマークを入れて、「ジョブを実行」を押します。
![貼り付けた画像_2021_02_09_18_37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/07d07f0a-44f8-6b68-aba3-a851bd49149a.png)

転送完了まで待ちます。
![貼り付けた画像_2021_02_09_18_39.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/a9aafe2d-9e83-2de6-f872-cfbde1dc4f79.png)

転送が成功しました。これで初回の全件転送は完了です。
![貼り付けた画像_2021_02_09_18_40.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/55fd3248-3e4e-5dd8-ceb9-7400e375d046.png)

次に、次回以降に行うCDC転送の設定をします。
転送設定の詳細画面に戻りましょう。

### 3-7. スケジュール設定
「スケジュール・トリガー設定」タブを開きます。
![貼り付けた画像_2021_02_09_18_42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/9022a511-b5c8-4f98-b165-8ea288e88667.png)

以下のように実行スケジュールを設定することで、転送を自動化することが出来ます。
![貼り付けた画像_2021_02_09_18_47.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/1b6a3316-5b76-79b1-2872-7d9598fd0af9.png)

### 3-8. 通知設定
必須の設定ではないですが、ジョブの実行ステータスに応じてEmailやSlackに通知を行うことが出来ます。
![貼り付けた画像_2021_02_09_18_48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/75769721-da50-4081-f00a-629f0176ecf8.png)

ここまでで全設定が完了しました。
これ以降は、設定したスケジュールに合わせてCDC転送が実行され、差分のデータがBigQueryに統合されるようになります。

### 4-1. 転送結果の確認
最後に、MySQLのデータがBigQueryに統合できているか確認してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/dc3a5d8d-0e59-2efa-fc85-84afd0ff6db2.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/7e33459b-a367-d52d-2632-2087786c0a68.png)

両方とも同じデータが入っていることが確認できますね。

ここで、元のテーブルにいくつか変更を加えて、BigQuery側でどのように表示されるのかを確認してみましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/865742cb-5c7b-dbea-e906-9565ce259d36.png)

id=1 のcommentをUPDATE
id=2 の行を削除
新たに「NewColumn」のカラムを追加
この３点が変更されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/673168/4e6e636a-f9fc-2898-a925-e07e46bd1f25.png)

このようにMySQLのテーブルに変更を加えたうえでCDC転送を行うと…

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/61228/15b49be9-b8d3-f2d2-54d9-160bed9ee056.png)

先ほどのクエリで変更した部分がBigQueryでも反映されていることが分かります。
また、削除したカラムについては`_trocco_deleted`カラムがtrueになっています。

今回は「スキーマ追従」の設定を有効にしているのでカラムの追加が反映されていますが、スキーマ追従が無効の場合には反映されないのでご注意ください。



## まとめ
いかがでしたでしょうか。[trocco](https://trocco.io/lp/index.html?utm_medium=social&utm_source=qiita&utm_campaign=mysqlcdc_to_bigquery)を使うと、簡単にMySQLのデータを取得し、DWH(BigQuery)に貯めることが出来ました。
ぜひデータ分析の際にご活用ください。

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

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