MySQL to BigQuery転送(CDC)

印刷
共有
PDF

記事の要約

この要約は役に立ちましたか?

ご意見ありがとうございます

概要

MySQLのbinlogからCDC（Change Data Capture）方式で、変更があったデータのみを識別してBigQueryへ転送できます。
それにより、転送量の削減、転送の高速化が得られます。
最初の実行時には、全件転送する必要があります。その後転送においては、差分だけをCDC方式で連携します。

必須条件

MySQL 対応表

	Master	Read Replica	サポートバージョン
Amazon Aurora MySQL RDS	○	x	5.6.2+
Amazon MySQL RDS	○	○	5.6.2+
Google CloudSQL MySQL	○	x	5.6.2+
MariaDB	○	○	5.6.2+
MySQL	○	○	5.6.2+

MySQLのパラメーター設定

[mysqld]
binlog_format=ROW
binlog_row_image=FULL
expire_logs_days=7
binlog_expire_logs_seconds=604800
log_bin=mysql-binlog
log_slave_updates=1

補足説明は以下となります。

binlogの設定を反映させるためにはDBの再起動が必要になる場合があります。
カラム：binlog_expire_logs_seconds
MySQL 8.0.1 以降は指定してください。
7日間を指定しておりますが、短くもできます。
毎回の転送する頻度よりも十分に長くなければいけません。
カラム：expire_logs_days
MySQL 8.0.0 以下の場合設定してください。
7日間を指定しておりますが、短くもできます。
毎回の転送する頻度よりも十分に長くなければいけません。
カラム：log_bin
自由に変更していただいて大丈夫です。
カラム：log_slave_updates
Read Replicaからbinlogを取得する際に設定してください。
RDS MySQL
expire_logs_daysはmysql.rds_set_configurationプロシージャを使い設定してください。
参考：https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html
Cloud SQL
point-in-time recoveryを有効にしてください。
参考：https://cloud.google.com/sql/docs/mysql/backup-recovery/backups?authuser=2#tips-pitr

接続ユーザーの権限

下記の3つ権限が必要です。

SELECT

データを取得
テーブルのスキーマ情報参照

例:

GRANT SELECT ON `<database_name>`.`<table_name>` to `<username>``@'%'`;

REPLICATION CLIENT、REPLICATION SLAVE

例:

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO `<username>`@'%';

転送元テーブルのPrimary Key

転送元のテーブルにはPrimary Keyの設定が必要です。
複数のPrimary Key(Composite Primary Key) も対応しております。

転送時の変換機能

MySQL to BigQueryのCDCの転送では下記をサポートしております。

カラムのドロップ
カラムのデータ変換
カラム名の変更

カラムのデータ変換は、BigQueryでCASTされます。なのでBigQueryのCASTのルールに従います。
詳しくは下記をご覧ください。
https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules?hl=ja

メタデータ

_trocco_fetched_atというカラムが付加されてDWHに連携されます。
本カラムは、TROCCOの転送ジョブによって転送が開始された時間を値として持ちます。
本カラムが追加される都合上、_trocco_fetched_atという文字列をカラム名に使用できません。

転送元MySQLのSQLの対応

INSERT
UPDATE
DELETE
- デリートされたデータはDWH側では論理削除となります。

制限があるSQL

ALTER TABLE ... RENAME COLUMN old_col_name TO new_col_name
スキーマ追従を有効にした場合、変更後の名前でデータ連携されます。
すでに連携されているカラムは、削除されず、値はNULLが入ります。
ALTER TABLE ... ADD col_name date_type
スキーマ追従を有効にした場合のみ、自動で転送されます。
カラム追加時は、管理画面で設定してからMySQLのカラムを追加してください。
カラム追加後すでに連携されている場合は、データの抜けが発生する場合があります。
ALTER TABLE ... DROP col_name
DWHに連携されたデータは削除されず、削除以降は、NULLが入ります。
ALTER TABLE ... MODIFY col_name column_definition
変更された型が型変更可能であれば、継続して転送できます。
エラーとなってしまった場合には全件転送を行う必要があります。

サポートされないSQL

下記のSQLはbinlogでキャッチできないため、データがDWHに正しく転送されません。

TRUNCATE
DROP
LOAD
RENAME
CASCADING DELETES
CASCADING UPDATES
ALTER TABLE ... SET DEFAULT
すでに連携されているデータでNULLの場合はNULLのままで更新されません。

MySQLとBigQueryのスキーマの型

データの型変換の機能において、変更が可能なデータ型においてはご指定した型に変換できます。
デフォルトでは下記の対応になっております。バイナリーデータは16進数に変換されて、STRINGとして扱われます。

MySQL データ型	BigQuery データ型
BINARY BLOB LONGBLOB MEDIUMBLOB TINYBLOB VARBINARY CHAR ENUM LONGTEXT MEDIUMTEXT SET TIME TINYTEXT VARCHAR	STRING
BIT TINYINT	BOOLEAN
BIGINT INT MEDIUMINT SMALLINT YEAR	INTEGER
DECIMAL NUMERIC DOUBLE FLOAT	FLOAT
DATE	DATE
TIMESTAMP DATETIME	TIMESTAMP
GEOMETRY GEOMETRYCOLLECTION JSON LINESTRING MULTILINESTRING MULTIPOINT MULTIPOLYGON POINT POLYGON	STRING（STRUCT は現在対応しておりません。）
CURVE MULTICURVE MULTISURFACE	SURFACE サポートしておりません。