MaterializedMySQL是一个用于从MySQL数据库中获取数据的表引擎。它的工作原理是:
- MaterializedMySQL会定期从MySQL拉取数据,同步到云数据库ClickHouse本地。
- 数据同步后,会存储在本地云数据库ClickHouse表中,表引擎设置为合适的引擎(如MergeTree)。
- 查询时,云数据库ClickHouse不会访问MySQL,而是直接从本地已同步的表中读取数据。
- 同步的时间间隔可以通过
mysql_materialized_tables_ttl参数设置,默认10分钟同步一次。
MaterializedMySQL的主要优点是:
- 减少了跨网络访问MySQL的开销,查询性能更好。
- 可以对同步的数据进行云数据库ClickHouse的查询优化,实现更快的分析。
- 支持增量同步,只同步新增或修改的数据,而不是整表同步。
- 支持只同步指定的列,以减少不需要的数据量。
- 本地表引擎可自定义,使存储方式更灵活。
创建数据库
语法
创建使用MaterializedMySQL引擎的表的语法:
CREATE TABLE [IF NOT EXISTS] table_name
ENGINE = MaterializedMySQL(`mysql_source_host`, `mysql_source_database`, `mysql_source_table`, `clickhouse_sink_database`, `clickhouse_sink_table`, `clickhouse_engine`)
- mysql_source_host:MySQL的源主机地址
- mysql_source_database:MySQL源数据库名
- mysql_source_table:MySQL源表名
- clickhouse_sink_database:云数据库ClickHouse目标数据库名
- clickhouse_sink_table:云数据库ClickHouse目标表名
- clickhouse_engine:云数据库ClickHouse目标表的引擎
示例
-- 从MySQL sync_test库的data表同步到ClickHouse的sync数据库sync_data表
CREATE TABLE sync.sync_data
ENGINE = MaterializedMySQL('localhost', 'sync_test', 'data', 'sync', 'sync_data', 'MergeTree() order by id');
这个表会定期从MySQL localhost上的sync_test库的data表同步数据到云数据库ClickHouse的sync库的sync_data表,目标表的引擎是MergeTree。
MaterializedMySQL提供了从MySQL增量同步数据的便捷途径,可以让云数据库ClickHouse对MySQL数据进行更优化的存储和查询。
MySQL服务器端配置
为了 MaterializedMySQL的正确工作，有一些必须设置的 MySQL端配置设置:
default_authentication_plugin = mysql_native_password，因为MaterializedMySQL只能授权使用该方法。gtid_mode = on，因为基于GTID的日志记录是提供正确的MaterializedMySQL复制的强制要求。
说明当打开 gtid_mode 时需要指定 enforce_gtid_consistency = on。
支持的数据类型
| MySQL | 云数据库ClickHouse |
|---|---|
| TINY | Int8 |
| SHORT | Int16 |
| INT24 | Int32 |
| LONG | UInt32 |
| LONGLONG | UInt64 |
| FLOAT | Float32 |
| DOUBLE | Float64 |
| DECIMAL, NEWDECIMAL | Decimal |
| DATE, NEWDATE | Date |
| DATETIME, TIMESTAMP | DateTime |
| DATETIME2, TIMESTAMP2 | DateTime64 |
| YEAR | UInt16 |
| TIME | Int64 |
| ENUM | Enum |
| STRING | String |
| VARCHAR, VAR_STRING | String |
| BLOB | String |
| GEOMETRY | String |
| BINARY | FixedString |
| BIT | UInt64 |
| SET | UInt64 |
MySQL中的Time 类型，会被云数据库ClickHouse转换成微秒来存储。如果MySQL表包含此类类型的列，云数据库ClickHouse抛出异常"Unhandled data type"并停止复制。
规范和推荐用法
兼容性限制
除了数据类型的限制之外，还有一些限制与 MySQL数据库相比有所不同，这应该在复制之前解决:
MySQL中的每个表都应该包含PRIMARY KEY。- 对于表的复制，那些包含
ENUM字段值超出范围的行(在ENUM签名中指定)将不起作用。
DDL Queries
MySQL DDL 语句会被转换成对应的云数据库ClickHouse DDL 语句，比如： (ALTER, CREATE, DROP, RENAME). 如果云数据库ClickHouse 无法解析某些语句DDL 操作，则会跳过。
数据复制
MaterializedMySQL不支持直接的 INSERT， DELETE 和 UPDATE 查询。然而，它们在数据复制方面得到了支持:
- MySQL
INSERT查询被转换为_sign=1的INSERT查询。 - MySQL
DELETE查询被转换为INSERT，并且_sign=-1。 - 如果主键被修改了，MySQL的
UPDATE查询将被转换为INSERT带_sign=1和INSERT 带有_sign=-1;如果主键没有被修改，则转换为INSERT和_sign=1。
MaterializedMySQL 数据表查询
SELECT 查询从 MaterializedMySQL表有一些细节:
- 如果在SELECT查询中没有指定
_version，则FINAL修饰符被使用，所以只有带有MAX(_version)的行会返回每个主键值。 - 如果在SELECT查询中没有指定
_sign，则默认使用WHERE _sign=1。所以被删除的行不是 包含在结果集中。 - 结果包括列注释，以防MySQL数据库表中存在这些列注释。
索引转换
在云数据库ClickHouse表中，MySQL的 PRIMARY KEY 和 INDEX 子句被转换为 ORDER BY 元组。
云数据库ClickHouse只有一个物理排序，由 order by 条件决定。要创建一个新的物理排序，请使用materialized views。
说明
_sign=-1 的行不会被物理地从表中删除。
级联 UPDATE/DELETE 查询不支持 MaterializedMySQL 引擎。
MaterializedMySQL 受 optimize_on_insert 设置的影响。当MySQL服务器中的一个表发生变化时，数据会合并到 MaterializedMySQL 数据库中相应的表中。
使用示例
MySQL 查询语句:
mysql>CREATE DATABASE db;
mysql>CREATE TABLE db.test (a INTPRIMARYKEY, b INT);
mysql>INSERT INTO db.test VALUES (1,11),(2,22);
mysql>DELETE FROM db.test WHERE a=1;
mysql>ALTER TABLE db.test ADDCOLUMN c VARCHAR(16);
mysql>UPDATE db.test SET c='Wow!', b=222;
mysql>SELECT * FROM test;
┌─a─┬───b─┬─c────┐
│ 2 │ 222 │ Wow! │
└───┴─────┴──────┘
云数据库ClickHouse中的数据库，与MySQL服务器交换数据:
创建的数据库和表:
CREATE DATABASE mysql ENGINE = MaterializedMySQL('localhost:3306','db','user','***');
SHOW TABLES FROM mysql;
┌─name─┐
│ test │
└──────┘
数据插入之后：
SELECT * FROM mysql.test;
┌─a─┬──b─┐
│ 1 │ 11 │
│ 2 │ 22 │
└───┴────┘
删除数据后，添加列并更新:
SELECT * FROM mysql.test;
┌─a─┬───b─┬─c────┐
│ 2 │ 222 │ Wow! │
└───┴─────┴──────┘
完整同步示例
CREATE TABLE mysql_sync.full_table
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'sales', 'products', 'sync', 'products_mv', 'MergeTree() order by id');
这会完整同步MySQL server，其ip地址为 A.A.A.A 上的sales库中products表到云数据库ClickHouse中的sync库的products_mv表。
增量同步示例
CREATE TABLE mysql_sync.updated_data
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'sales', 'products', 'sync', 'product_updates', 'MergeTree() order by id', 1);
该表仅同步products表中新增或修改过的数据。第6个参数 1表示启用增量同步。
只同步部分列示例
CREATE TABLE mysql_sync.product_names
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'sales', 'products', 'sync', 'product_names', 'MergeTree() order by id', 0, ['name', 'description']);
这里只同步products表的name和description两列。第7个参数定义同步的列。
设置同步周期示例
CREATE TABLE mysql_sync.orders
ENGINE = MaterializedMySQL('A.A.A.A:3306', 'store', 'orders', 'sync', 'orders_mv', 'MergeTree() order by id', 0, [], 3600);
这里通过第8个参数将同步周期设置为3600秒,即每小时同步一次。
