【ClickHouse連載】ApsaraDB RDS for MySQLからApsaraDB RDS for ClickHouseへデータ連携してみる(MaterializeMySQLを使用)

Hi, データエンジニアの大原です。
今回はAlibaba Cloudの国際サイトで提供している ClickHouse に対し、ApsaraDB RDS for MySQLからデータ連携する方法をご紹介します。

f:id:sbc_ohara:20210714233458p:plain

ClickHouseとは

ClickHouseは非集計データを含む大量のデータを安定的かつ継続しながら集計といったリアルタイム分析を支える列指向の分散型データベースサービスです。 トラフィック分析、広告およびマーケティング分析、行動分析、リアルタイム監視などのビジネスシナリオで幅広く 使用されています。
ApsaraDB for ClickHouseの概要として、詳しいことはこの記事にてまとめています。

www.sbcloud.co.jp

clickhouse.tech

少し前になりますが、LogServiceについての資料をSlideShareへアップロードしていますので、こちらも参考になればと思います。

www.slideshare.net

ApsaraDB RDS for MySQL とは

Alibaba Cloudのフルマネージド型リレーショナルデータベースサービスです。

ApsaraDB RDS for MySQL | Alibaba Cloud (アリババクラウド)

※ApsaraDBは、Alibaba Cloudがクラウドコンピューティングの上で稼働するデータベースとして名付けたもので、Apsaraはインド神話にある水の妖精で、「雲の海に生きるもの」を意味するものです。だからクラウドコンピューティングのデータベースとしてApsaraDB(アプサラス)ですね。

アプサラス

ja.wikipedia.org

1. ClickHouseClientの準備

1-1.ClickHouseインスタンスを準備します

この手法は過去の記事でも記載していますが、再掲として記載します。

www.sbcloud.co.jp

1)まずはApsaraDB for ClickHouseインスタンスを作成します。
①VPCを作成

f:id:sbc_ohara:20210716155036p:plain

f:id:sbc_ohara:20210716155113p:plain

②ClickHouseインスタンスを作成
著者は以下のインスタンススペックでインスタンスを作成しています。

ClickHouse version:20.8.7.15
Edition:Single-replica Edition

f:id:sbc_ohara:20210716155212p:plain

f:id:sbc_ohara:20210716155220p:plain

f:id:sbc_ohara:20210716155231p:plain

2)ClickHouseの登録アカウントを作成 インスタンスをクリックし、左側にアカウント管理画面で、アカウントを作成します

f:id:sbc_ohara:20210716155256p:plain

3)ClickHouseクラスターにDMSで接続 ①ClickHouseのインスタンスをクリックし、トップメニューの「Log On to Database」をクリックします

f:id:sbc_ohara:20210716155315p:plain

② DBアカウントとパスワードを入力し、ClickHouseへログイン

f:id:sbc_ohara:20210716155334p:plain

③DMS画面でClickHouseのインスタンスが表示されます

f:id:sbc_ohara:20210716155401p:plain

2. ApsaraDB RDS for MySQLおよびデータの準備

2-1.MySQLインスタンスを作成

1)MySQLインスタンスを作成します

①AlibabaCloudのサイトをログインし、コンソール画面に遷移します

f:id:sbc_ohara:20210816223812p:plain

②RDSをクリックし、RDSコンソール画面に遷移します

f:id:sbc_ohara:20210816223830p:plain

③インスタンス作成をクリックし、RDSを作成します。
注意として、MaterializeMySQLによるデータ連携の場合 GTID 機能を使う必要があり、ApsaraDB for RDS EnterpriseではGTIDがOFFであるため、BasicとHAのタイプを選択しなければならないです。

f:id:sbc_ohara:20210816223958p:plain

④VPCを設定します

f:id:sbc_ohara:20210816224024p:plain

⑤Mysqlインスタンス情報を確認します

f:id:sbc_ohara:20210816224038p:plain

④Mysqlインスタンスが作成されました
f:id:sbc_ohara:20210816224053p:plain

2)MySQLのデータベースを作成します
①データベース画面にデータベース作成ボタンをクリックします

f:id:sbc_ohara:20210816224126p:plain

②データベースを設定します

f:id:sbc_ohara:20210816224151p:plain

③データベースが作成されます

f:id:sbc_ohara:20210816224212p:plain

3)MySQLのアカウントを作成
①アカウント画面にアカウント作成ボタンをクリックします

f:id:sbc_ohara:20210816224232p:plain

②Privileged Accountアカウントを設定します
※通常のアカウント(Nomal Account)の場合は、MySQLライブラリのRELOAD、REPLICATION SLAVE、REPLICATION CLIENT、およびSELECTPRIVILEGE権限をアタッチする必要があります

f:id:sbc_ohara:20210816224300p:plain

③アカウントが作成されます

f:id:sbc_ohara:20210817000856p:plain

2-2.DMSでDBをログイン

1)DMSでDBをログイン
①データベースログインをクリックします

f:id:sbc_ohara:20210817000918p:plain

②データベースアカウントとパスワードを入力し、接続テストボタンをクリックする

f:id:sbc_ohara:20210817000931p:plain

③「OK」をクリックします

f:id:sbc_ohara:20210817001001p:plain

④「Login」をクリックします

f:id:sbc_ohara:20210817001017p:plain

⑤DMSでデータベース接続が成功したことを確認します

f:id:sbc_ohara:20210817001056p:plain

2-3.DMSでMySQLのテーブルデータの準備

1)下記shoppingテーブル作成

①userテーブルを作成します

create table user(
    user_id bigint not null auto_increment comment 'user_id ID',
    user_name varchar(30) comment 'user name',
    phone_num varchar(20) comment 'phonenum',
    email varchar(100) comment 'email',
    acct decimal(18,2) comment 'account',
    primary key (user_id),
    key I1 (user_name)
);

f:id:sbc_ohara:20210817001209p:plain

②inventoryテーブルを作成します

create table inventory(
    inventory_id bigint not null auto_increment comment 'inventory_id',
    inventory_name varchar(30) comment 'inventory name',
    price_unit decimal(18,2) comment 'price unit',
    inventory_num bigint not null default 0 comment 'inventory num',
    primary key(inventory_id)
);

f:id:sbc_ohara:20210817001236p:plain

③ordersテーブルを作成します

create table orders(
    order_id bigint not null auto_increment comment 'order id',
    user_id bigint not null comment 'user id',
    inventory_id bigint not null comment 'inventory id',
    price_unit decimal(18,2) comment 'price unit',
    order_num bigint not null default 0 comment 'order num',
    create_time datetime not null default current_timestamp,
    update_time datetime not null default current_timestamp on update current_timestamp, 
    primary key(order_id),
    key I1(user_id),
    key I2(inventory_id)
);

f:id:sbc_ohara:20210817001305p:plain

③テーブルを確認します

f:id:sbc_ohara:20210817001325p:plain

2)モックアップデータを作成します
①userテーブルを選択し、右クリックメニューから「データプラン」 - 「テストデータ作成」をクリックします

f:id:sbc_ohara:20210817001354p:plain

②10万件テストデータを設定します

f:id:sbc_ohara:20210817001407p:plain

③作成タスクを確認します

f:id:sbc_ohara:20210817001420p:plain

④データを確認します

SELECT count(*) FROM `user` ;

f:id:sbc_ohara:20210817001456p:plain

SELECT * FROM `user` 
LIMIT 20;

f:id:sbc_ohara:20210817001517p:plain

⑤同じ方法でinventoryデータを作成します

SELECT count(*) FROM `inventory` ;

f:id:sbc_ohara:20210817001541p:plain

SELECT * FROM `inventory`
LIMIT 20;

f:id:sbc_ohara:20210817001558p:plain

⑥同じ方法でordersデータを作成します

SELECT count(*) FROM `orders` ;

f:id:sbc_ohara:20210817001619p:plain

SELECT * FROM `orders`
LIMIT 20;

f:id:sbc_ohara:20210817001635p:plain

3)ordersデータを更新
①下記コマンドでordersを更新します

update orders set update_time = create_time;

f:id:sbc_ohara:20210817001659p:plain

②update_timeが create_timeと同じであることを確認します

SELECT * FROM `orders` 
LIMIT 20;

f:id:sbc_ohara:20210817001720p:plain

3.MaterializeエンジンでMySQLデータをClickHouseに同期します

ここから本題です。
ApsaraDB for ClickHouseはデータベースエンジンを MaterializeMySQL と設定することで、ApsaraRDS for MySQLのテーブルをApsaraDB for ClickHouseへリアルタイムでデータを同期することができます。
そのため、ApsaraDB for ClickHouseサービスはMySQLのコピーとして、Binlogを読み取り、DDLおよびDMLリクエストを実行することで、MySQL Binlogメカニズムに基づくデータベースのリアルタイム同期機能を実現することができます。      

前提条件:
・データソースとなるApsaraRDS for MySQLクラスターと、ターゲットのApsaraDB for ClickHouseクラスターは同じVPCネットワークに配置している必要があります
・ClickHouseクラスターのアドレスを ApsaraRDS for MySQLのホワイトリストに追加する必要があります
・MaterializeMySQLテーブルエンジンのユーザーは、MySQLライブラリのRELOAD、REPLICATION SLAVE、REPLICATION CLIENT、およびSELECT PRIVILEGE権限を持っている必要があります
・MySQL側でGTIDが対応していること、およびON状態になっている必要があります

3-1.DMSでClickHouseデータを確認します

1)DMSでClickHouseデータベースを作成します

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializeMySQL('host:port', ['database' | database], 'user', 'password') 
SETTINGS  
include_tables ='a,b,c...';

パーシングルール:
・*:/含む空の文字列を除く任意の文字を置き換えます
・?:任意の1文字を置き換えます
・{N..M} :NとMを含む、NからMの範囲の任意の数を置き換えます

①下記コマンドを実行し、MaterializeMySQLデータベースを作成します

※RDSのホワイトリストを設定し、Mysqlインスタンスにアクセス権限をあることを前提とします。

CREATE DATABASE mysql_clickhouse ENGINE = MaterializeMySQL('rm-0iw928qvgwn4c8ue8.mysql.japan.rds.aliyuncs.com:3306', 'sbdb', 'sbtest', 'Test1234')
SETTINGS  
include_tables ='*';

・Internal Endpointを確認します

f:id:sbc_ohara:20210817002419p:plain

f:id:sbc_ohara:20210817002430p:plain

SHOW TABLES FROM mysql_clickhouse;

f:id:sbc_ohara:20210817002446p:plain

2)DMSでテーブルを検索します

SELECT * FROM mysql_clickhouse.user;

f:id:sbc_ohara:20210817002500p:plain

SELECT * FROM mysql_clickhouse.inventory;

f:id:sbc_ohara:20210817002512p:plain

SELECT * FROM mysql_clickhouse.orders;

f:id:sbc_ohara:20210817002526p:plain

3)Mysqlでuserテーブルにデータを挿入
①userテーブルにデータを挿入します

INSERT INTO sbdb.user VALUES (100001,'test','ts','des','46.88');

f:id:sbc_ohara:20210817002543p:plain

②Clickhouseからデータを同期します

SELECT * FROM mysql_clickhouse.user WHERE user_id = 100001;

f:id:sbc_ohara:20210817002605p:plain

4)Mysqlでuserテーブルにデータを削除します

DELETE FROM sbdb.user WHERE user_id = 100001;

f:id:sbc_ohara:20210817002622p:plain

②Clickhouseからデータを同期します

SELECT * FROM mysql_clickhouse.user WHERE user_id = 100001;

f:id:sbc_ohara:20210817002639p:plain

5)Mysqlでuserテーブルにデータを更新
①userテーブルにデータを更新します

UPDATE sbdb.user SET user_name='test' where user_id =100000;

f:id:sbc_ohara:20210817002702p:plain

f:id:sbc_ohara:20210817002713p:plain

②Clickhouseからデータ同期します

SELECT * FROM mysql_clickhouse.user WHERE user_id = 100000;

f:id:sbc_ohara:20210817002727p:plain


最後に

ここまで、ApsaraDB for ClickHouseはデータベースエンジンを MaterializeMySQL と設定することで、ApsaraRDS for MySQLのテーブルをApsaraDB for ClickHouseへリアルタイムでデータを同期する方法を紹介しました。
ApsaraDB for ClickHouseはMySQLのテーブルとスムーズに連携できるので、例えば、RDS for MySQLでWebアプリケーション運用のち、MaterializeMySQLを使ってClickHouseへリアルタイム同期しながら、ClickHouseでリアルタイム可視化、といったソリューションとして仕上げることもできます。

Special Thanks, Nancy