LoginSignup
1

More than 1 year has passed since last update.

posted at

updated at

Organization

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

概要

MySQLからBigQueryにデータを転送する際、転送の設定が複雑で面倒に感じることがあるかもしれません。
そこで、今回はtroccoというETL & ワークフローサービスを使い、CDC形式で転送する方法をご紹介します。
CDC形式を取り入れることで、効率的で低レイテンシなデータ転送を実現することができます。
image.png

1-0. CDCとは?

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

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

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

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

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

troccoでCDC転送を行う際には
troccoがMySQLのbinlogをBigQuery上に転送
②binlogのデータから重複を排除し、それぞれのレコードの最新のレコードの集合を作成
③↑の集合を現在のBigQueryテーブルとマージし、マージ後のテーブルで置換する
という手順を踏んでいます。

CDC転送の詳細な手順についてはこちらのドキュメントの「テクニカルオーバービュー」の項をご参照ください。

2-1. MySQLのパラメータ設定

troccoのドキュメントを参照しつつ、MySQLのパラメータ設定を行います。

image.png

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

2-2. MySQLの権限設定

troccoのドキュメントを参照しつつ、MySQLの権限設定を行います。

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

image.png

2-3. Primary Keyの設定

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

その他、troccoで転送する際の必須条件などについてはこちらのドキュメントを参照してください。

これにてMySQL側での設定は完了です。
次に、troccoの設定を行います。

3-0. troccoに登録

troccoのアカウントが必要です。
無料トライアルも実施しているので、前もって申し込み・登録をしておいてください!
https://trocco.io/lp/index.html
(申込時に、この記事を見た旨を記載して頂ければスムーズにご案内できます)

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

troccoにアクセスし、ダッシュボードから「転送設定を作成」のボタンを押します。
貼り付けた画像_2021_02_09_18_45.png

転送元にMySQL binlog、転送先にBigQueryを選択し、転送設定作成ボタンを押します。
貼り付けた画像_2021_02_09_17_13.png
すると、設定画面になるので、転送に必要な情報を入力していきます。

3-2. MySQLとの連携設定

転送設定の名前とメモを入力します。
image.png

転送設定の名前を決めたら、「転送元の設定」内の「接続情報を追加」ボタンを押し、MySQLの接続情報の設定を行います。
貼り付けた画像_2021_02_09_17_16.png

接続設定の名前・ホスト・ポート・ユーザー名・パスワードを入力します。
貼り付けた画像_2021_02_18_16_34.png

接続確認をしたのち、設定の保存をします。
貼り付けた画像_2021_02_09_17_20.png

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

3-3. MySQLからのデータ抽出設定

これでMySQLとの連携は完了です。
次に、MySQLの取得データを設定します。

まずは必須項目の「データベース名」「テーブル」をセレクトボックスの中から選択します。
image.png

次に、スキーマ追従の有効/無効を指定します。
image.png

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

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

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

最後に接続確認が通るか確認します。
image.png

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

3-4. 転送先BigQueryの設定

基本的には転送元と同じ要領です。
「接続情報を追加」ボタンからBigQueryの接続設定を行い、データベース・テーブル・データセットのロケーションを指定します。
貼り付けた画像_2021_02_09_18_17.png

最後に、接続確認が問題なく通るか確認します。
image.png

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

3-5. カラム名・データ型の確認

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

データ型についてはこちらのドキュメントをご参照ください。
image.png

問題なければ、転送設定の詳細画面に移りましょう。
貼り付けた画像_2021_02_09_18_35.png

3-6. 初回転送

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

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

貼り付けた画像_2021_02_09_18_35.png

「全件転送を行う」にチェックマークを入れて、「ジョブを実行」を押します。
貼り付けた画像_2021_02_09_18_37.png

転送完了まで待ちます。
貼り付けた画像_2021_02_09_18_39.png

転送が成功しました。これで初回の全件転送は完了です。
貼り付けた画像_2021_02_09_18_40.png

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

3-7. スケジュール設定

「スケジュール・トリガー設定」タブを開きます。
貼り付けた画像_2021_02_09_18_42.png

以下のように実行スケジュールを設定することで、転送を自動化することが出来ます。
貼り付けた画像_2021_02_09_18_47.png

3-8. 通知設定

必須の設定ではないですが、ジョブの実行ステータスに応じてEmailやSlackに通知を行うことが出来ます。
貼り付けた画像_2021_02_09_18_48.png

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

4-1. 転送結果の確認

最後に、MySQLのデータがBigQueryに統合できているか確認してみます。
image.png

image.png

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

ここで、元のテーブルにいくつか変更を加えて、BigQuery側でどのように表示されるのかを確認してみましょう。
image.png

id=1 のcommentをUPDATE
id=2 の行を削除
新たに「NewColumn」のカラムを追加
この3点が変更されています。
image.png

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

image.png

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

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

まとめ

いかがでしたでしょうか。troccoを使うと、簡単にMySQLのデータを取得し、DWH(BigQuery)に貯めることが出来ました。
ぜひデータ分析の際にご活用ください。

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

その他にも様々な分析データをETL・転送した事例をまとめています。ご活用ください。
troccoの使い方まとめ(CRM・広告・データベース他)

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
1