Datastream for BigQuery 解説 ~Cloud SQL から BigQuery へ直接レプリケーションする~

次世代システム研究室の Y.I です。 Google Cloud Platform (GCP) の BigQueryについてまとめます。最近プレビュー公開された Datastream for BigQuery を用いて Cloud SQL(MySQL) から BigQuery へレプリケーションしてみます。現在 BigQuery への直接データ複製はプレビュー版なのですが、どの程度のことができるのか調査した内容をご紹介します。

Datastream for BigQuery とは
結論
接続構成
Datastream確認
最後に

Datastream for BigQuery とは

GCPにて発表されたリレーショナルデータベースから BigQuery へのシームレスなレプリケーションを可能とする機能です。対象RDBMSは MySQL, Oracle, PostgreSQL で GCP 上の Cloud SQL やセルフホスト型や AWS RDS/Aurora からも直接レプリケーションできます。今までは BigQuery で分析を行うためには Cloud Dataflow などでデータを BigQuery へ取り込む ETL 処理が必要でしたが、 Datastream for BigQuery を使うと、対象のリレーショナルデータベースから直接データを取り込むことが可能となります。

結論

Datastream for BigQuery による Cloud SQL(MySQL) から BigQuery へのレプリケーションは動作する

レコードのINSERT/UPDATE/DELETEは問題なく動作する

テーブル作成やテーブルカラム追加は動作するがレコードが登録されるまでレプリケーションされなかった

DROP TABLE や DROP COLUMN などの DDL DROP は動作しない

他DBで集計を行っていて処理速度に問題がある場合など BigQuery へ容易にデータ同期できるため検討価値がある

接続構成

元DB: Cloud SQL(MySQL)

中継: GCE (cloud_sql_proxy)

宛先: BigQuery

今回はこちらの接続構成で試します。 Datastream for BigQuery が Cloud SQLへ接続するには cloud_sql_proxy が必要なため、VMを立ててcloud_sql_proxy経由で接続しています。

Datastream確認

既存テーブル

[結果] Datastream開始前に用意したテーブルがDatastream開始後にBigQueryへレプリケーションされることを確認できた

・レプリケーション対象

CREATE TABLE db01.table01 (id int not null auto_increment, name varchar(100), created_at datetime, primary key(id));
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(100) | YES  |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

insert into table01 (id, name, created_at) values (1, 'datastream 対象', now());
+----+-------------------+---------------------+
| id | name              | created_at          |
+----+-------------------+---------------------+
|  1 | datastream 対象   | 2022-10-10 09:43:14 |
+----+-------------------+---------------------+

・BigQueryレプリケーション結果

bigint, json, text カラムテーブル

[結果] bigint, json, text カラムを持ったテーブルがBigQueryへレプリケーションされることを確認できた

・レプリケーション対象

CREATE TABLE db01.table20 (id bigint not null auto_increment, name text, j json, created_at datetime, primary key(id));
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | bigint   | NO   | PRI | NULL    | auto_increment |
| name       | text     | YES  |     | NULL    |                |
| j          | json     | YES  |     | NULL    |                |
| created_at | datetime | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+

insert into table20 (id, name, j, created_at) values (99999999999, 'datastream 対象 bigint/json', '{"key1": "val1", "key2": "val2"}', '9999-12-31 23:59:59');
+-------------+-------------------------------+----------------------------------+---------------------+
| id          | name                          | j                                | created_at          |
+-------------+-------------------------------+----------------------------------+---------------------+
| 99999999999 | datastream 対象 bigint/json   | {"key1": "val1", "key2": "val2"} | 9999-12-31 23:59:59 |
+-------------+-------------------------------+----------------------------------+---------------------+

・BigQueryレプリケーション結果

Datastream for BigQuery jsonやtextカラムのレプリケーション結果

除外テーブル

[結果] レプリケーション対象からの除外も確認できた

・レプリケーション対象

CREATE TABLE db01.table03 (id int not null auto_increment, name varchar(100), primary key(id));
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

insert into table03 (id, name) values (1, 'datastream 除外されるテーブル');
+----+----------------------------------------+
| id | name                                   |
+----+----------------------------------------+
|  1 | datastream 除外されるテーブル          |
+----+----------------------------------------+

・Datastream設定でMySQL db01.table03 を除外オブジェクトに指定

・BigQueryレプリケーション結果

テーブル作成(CREATE TABLE)

[結果] CREATE TABLEのみだとレプリケーションされなかったが、レコードを登録したらレプリケーションされることを確認できた

・レプリケーション対象

CREATE TABLE db01.new_table04 (id int, name varchar(100), created_at datetime, primary key(id));
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | NO   | PRI | NULL    |       |
| name       | varchar(100) | YES  |     | NULL    |       |
| created_at | datetime     | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

insert into new_table04 (id, name, created_at) values (1, 'datastream 対象 新規テーブル', now());
+----+--------------------------------------+---------------------+
| id | name                                 | created_at          |
+----+--------------------------------------+---------------------+
|  1 | datastream 対象 新規テーブル         | 2022-10-10 11:27:03 |
+----+--------------------------------------+---------------------+

・BigQueryレプリケーション結果

テーブル変更(ALTER TABLE)

[結果] Add Columnは反映されたが、 Drop Columnは反映されない

・レプリケーション対象

ALTER TABLE db01.table02 add column add_col1 int , drop column created_at;
+----------+--------+------+-----+---------+----------------+
| Field    | Type   | Null | Key | Default | Extra          |
+----------+--------+------+-----+---------+----------------+
| id       | bigint | NO   | PRI | NULL    | auto_increment |
| name     | text   | YES  |     | NULL    |                |
| j        | json   | YES  |     | NULL    |                |
| add_col1 | int    | YES  |     | NULL    |                |
+----------+--------+------+-----+---------+----------------+

insert into table02 (id, name, j, add_col1) values (111, 'datastream 対象 add/drop column', '{"key1": "val1", "key2": "val2"}', 999);
+-------------+-----------------------------------+----------------------------------+----------+
| id          | name                              | j                                | add_col1 |
+-------------+-----------------------------------+----------------------------------+----------+
|         111 | datastream 対象 add/drop column   | {"key1": "val1", "key2": "val2"} |      999 |
| 99999999999 | datastream 対象 bigint/json       | {"key1": "val1", "key2": "val2"} |     NULL |
+-------------+-----------------------------------+----------------------------------+----------+

・BigQueryレプリケーション結果

add_col1カラムは追加されたが、dropしたcreated_atカラムはBigQueryに残り続けている

テーブル削除(DROP TABLE)

[結果] DROP TABLE　は反映されない. レプリケーション済みのテーブルレコードが残り続けている

・レプリケーション対象

CREATE TABLE db01.drop_table05 (id int, name varchar(100), created_at datetime, primary key(id));
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int          | NO   | PRI | NULL    |       |
| name       | varchar(100) | YES  |     | NULL    |       |
| created_at | datetime     | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

insert into drop_table05 (id, name, created_at) values (1, 'datastream 対象 削除テーブル', now());
+----+--------------------------------------+---------------------+
| id | name                                 | created_at          |
+----+--------------------------------------+---------------------+
|  1 | datastream 対象 削除テーブル         | 2022-10-10 11:27:03 |
+----+--------------------------------------+---------------------+

DROP TABLE if exists drop_table05;

・BigQueryレプリケーション結果

Datastream for BigQuery DROP TABLEは反映されない

レコードレプリケーション（INSERT/UPDATE/DELETE）

[結果] INSERT UPDATE DELETE 共にレプリケーションされることを確認できた

・レプリケーション対象

CREATE TABLE db01.table01 (id int not null auto_increment, name varchar(100), created_at datetime, primary key(id));
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(100) | YES  |     | NULL    |                |
| created_at | datetime     | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

insert into table01 (id, name, created_at) values (2, 'C->U', now());
insert into table01 (id, name, created_at) values (3, 'C->D', now());
update table01 set name = 'U' where id = 2;
delete from table01 where id = 3;
+----+-------------------+---------------------+
| id | name              | created_at          |
+----+-------------------+---------------------+
|  1 | datastream 対象   | 2022-10-10 09:43:14 |
|  2 | U                 | 2022-10-10 11:37:14 |
+----+-------------------+---------------------+

・BigQueryレプリケーション結果

Datastream for BigQuery Insert/update/delete結果

最後に

グループ研究開発本部では、グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ募集職種一覧からご応募をお願いします。

Datastream for BigQuery 解説 ~Cloud SQL から BigQuery へ直接レプリケーションする~

Datastream for BigQuery とは

結論

接続構成

Datastream確認

既存テーブル

bigint, json, text カラムテーブル

除外テーブル

テーブル作成(CREATE TABLE)

テーブル変更(ALTER TABLE)

テーブル削除(DROP TABLE)

レコードレプリケーション（INSERT/UPDATE/DELETE）

最後に

関連記事

MariaDB Multi-Source Replicationを使ってサービス無停止でDBサーバーを統合する

BigQuery解説 ~上手にコスト削減しながらBigQueryを活用する~

BigQuery解説 ~データ登録方法いろいろ~

MySQLの冗長化を試す！～Percona XtraDB Cluster & ProxySQL & Replication～