首页 > 数据库 >clickhouse高级功能之MaterializeMySQL详解

clickhouse高级功能之MaterializeMySQL详解

时间:2023-03-06 12:32:50浏览次数:62  
标签:code MaterializeMySQL scene 详解 mysql id clickhouse

clickhouse 20.8将新增 MaterializeMySQL引擎 ,可通过binlog日志实时物化mysql数据,极大提升了数仓的查询性能和数据同步的时效性;原有mysql中承担的数据分析工作 可交由clickhouse去做,这么做可显著降低线上mysql的负载,从此OLTP与OLAP业务实现完美融合

​知识列表​

介绍

目前 MaterializeMySQL database engine 还不支持表级别的同步操作,需要将整个mysql database映射到clickhouse,映射过来的库表会自动创建为ReplacingMergeTree表engine。

MaterializeMySQL 支持全量和增量同步,首次创建数据库引擎时进行一次全量复制,之后通过监控binlog变化进行增量数据同步;该引擎支持mysql 5.6/5.7/8.0版本数据库,兼容insert,update,delete,alter,create,drop,truncate等大部分DDL操作。

演示

  • 修改my.cnf开启mysql binlog模式


log-bin=/data/logs/mysql/mysql-bin.log  # 指定binlog日志存储位置
binlog_format=ROW # 这里一定是row格式
server-id=1

如果clickhouse使用的是20.8 prestable之后发布的版本,那么还需要配置开启GTID模式


gtid-mode=on
enforce-gtid-consistency=1 # 设置为主从强一致性
log-slave-updates=1 # 记录日志
  • 首先在mysql中先创建scene表


CREATE TABLE `scene` (
`id` int NOT NULL AUTO_INCREMENT,
`code` int NOT NULL,
`title` text DEFAULT NULL,
`updatetime` datetime DEFAULT NULL,
PRIMARY KEY (`id`), ##主键要设置为not null,否则会报错
KEY `idx_code` (`code`) ##索引键也要设置为not null,否则会报错
) ENGINE=InnoDB default charset=Latin1;
  • 登陆clickhouse客户端,开启mysql物化引擎


SET allow_experimental_database_materialize_mysql = 1
# 因为该功能目前还处于实验阶段,在使用之前需要开启
  • 创建一个复制管道


CREATE DATABASE scene_mms
ENGINE = MaterializeMySQL('localhost:3306', 'db', 'root', 'xxx')

创建成果后可查看到clickhouse对应的表


VM_10_14_centos :) show tables

SHOW TABLES

┌─name───────────────────────┐
│ scene │
└────────────────────────────┘

25 rows in set. Elapsed: 0.002 sec.

表结构如下:


ATTACH TABLE scene
(
`id` Int32,
`code` Int32,
`title` Nullable(String),
`updatetime` Nullable(DateTime),
`_sign` Int8 MATERIALIZED 1,
`_version` UInt64 MATERIALIZED 1
)
ENGINE = ReplacingMergeTree(_version)
PARTITION BY intDiv(id, 4294967)
ORDER BY tuple(id,code)
SETTINGS index_granularity = 8192

其中partition根据id,按照长度为4294967进行分段分区

  • 向表中插入数据


INSERT INTO scene(code, title, updatetime) VALUES(1000,'邀请函',NOW());
INSERT INTO scene(code, title, updatetime) VALUES(1001,'gyc',NOW());
INSERT INTO scene(code, title, updatetime) VALUES(1002,'易企秀',NOW());

目前20.8 testing版本使用的监听事件方式为UpdateRowsEventV2 ,而20.8 prestable只后的版本使用的gtid的binlog监听方式,这种方式在mysql主从模式下可以确保数据同步的一致性,但使用过程中可能会有一些意向不到问题,建议大家先使用testing版本进行测试,等20.8稳定版出来后再测试gtid的同步模式。

查询clickhouse对应的表,已可以实时看到数据变化

clickhouse高级功能之MaterializeMySQL详解_数据

SELECT * FROM scene

┌─id─┬─code─┬─title─┬──────────updatetime─┐
│ 2 │ 1001 │ gyc │ 2020-09-03 10:00:02 │
└────┴──────┴───────┴─────────────────────┘
  • 更新mysql中id为2的数据


update scene set title="校园招聘" where id = 2

检查clickhouse中id为2的数据


select * from scene where id = 2
  • 尝试删除mysql中id为2的数据


DELETE FROM scene where id = 2

再次查询clickhouse中的数据已无数据

  • 在mysql执行删除表


drop table scene

此时在clickhouse处会同步删除对应表,如果查询会报错


DB::Exception: Table scene_mms.scene doesn't exist..
  • 同理 ,如果在mysql客户端新增一张表,在clickhouse处也可实时生成对应的数据表
  • 修改表名


mysql> alter table scene rename test

Query OK, 0 rows affected (0.02 sec)

mysql> show tables

+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)

修改表名称不会同步至clickhouse,且查询会报错


VM_10_14_centos :) show tables

SHOW TABLES

┌─name──┐
│ scene │
└───────┘


VM_10_14_centos :) select * from scene

Received exception from server (version 20.8.1):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Table test_mms.test doesn't exist..

# 通过报错信息可以看出 虽然查询的是scene表 ,但底层已重写为改名之后的test表,因为test表在clickhouse处没有执行成功,所以会报找不到对应表的错误;

同理, 在mysql处删除test表,clickhouse中的scene表依然存在,可见两边执行语句是根据表名进行对应的

  • 修改列名称也是不支持的,如果出现这种情况,删除通道重建就好了
  • 支持添加列与删除列,在mysql添加一列,随后再删除


mysql> alter table scene add column  title text;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table scene drop column title;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

观察clickhouse中前后表结构变化


DESCRIBE TABLE scene 

┌─name───────┬─type───────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int32 │ │ │ │ │ │
│ code │ Int32 │ │ │ │ │ │
│ name │ Nullable(String) │ │ │ │ │ │
│ updatetime │ Nullable(DateTime) │ │ │ │ │ │
│ title │ Nullable(String) │ │ │ │ │ │
│ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │
│ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │
└────────────┴────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

7 rows in set. Elapsed: 0.001 sec.

VM_10_14_centos :) desc scene

DESCRIBE TABLE scene

┌─name───────┬─type───────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id │ Int32 │ │ │ │ │ │
│ code │ Int32 │ │ │ │ │ │
│ name │ Nullable(String) │ │ │ │ │ │
│ updatetime │ Nullable(DateTime) │ │ │ │ │ │
│ _sign │ Int8 │ MATERIALIZED │ 1 │ │ │ │
│ _version │ UInt64 │ MATERIALIZED │ 1 │ │ │ │
└────────────┴────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

总结

  • 通过上面的测试我们发现clickhouse的删除动作也是实时同步的,原因在于我们创建的MaterializeMySQL engine会默认为每一张表生成ReplacingMergeTree engine,当clickhouse遇到删除的binlog操作时,会将这条数据的_sign字段设为-1;
    目前ReplacingMergeTree还只是标记性删除,并非物理上的实际删除,索引随着删除日志的增多,查询过滤会有一定的负担。
  • MaterializeMySQL DataBase中的ReplacingMergeTree Engine表查询不再需要额外添加final修饰符了:


select * from scene
##等同于
select * from scene final
  • 需要注意的是20.8版本目前还不是稳定版,如果mysql中没有设置主键字段时,会在创建MaterializeMySQL数据库时报错:


DB::Exception: The db.scene cannot be materialized, because there is no primary keys.

主键字段和索引字段不允许为NULL


Rewritten MySQL DDL Query ... wasn't finished successfully: Code: 44, e.displayText() = DB::Exception: Sorting key cannot contain nullable columns

不过该​​ISSUES​​目前已被重视,20.7版本在create table 时解决了这个问题,可以通过设置allow_nullable_key=1来解决,但因为MaterializeMySQL是自动创建的数据表,所以该问题还是存在的,相信不久的版本在创建MaterializeMySQL DataBase时 也会解决这个问题


CREATE TABLE nullable_key 
(k Nullable(int), v int) ENGINE MergeTree ORDER BY k SETTINGS allow_nullable_key = 1;
  • clickhouse单线程写入能力可以达到每秒几十万,在一般业务体系下增量更新的模式是完全没有问题的。

标签:code,MaterializeMySQL,scene,详解,mysql,id,clickhouse
From: https://blog.51cto.com/u_13146445/6102632

相关文章

  • 《数据万象带你玩转视图场景》第一期:avif图片压缩详解
    前言随着硬件的发展,不管是手机还是专业摄像设备拍出的图片随便可能就有几M,甚至几十M,并且现在我们处于随处可及的信息海洋里,海量的图片带来了存储问题、带宽问题、加载时延......
  • 美颜SDK人脸表情识别技术详解
    人脸识别,是美颜SDK大部分功能实现的先决条件,比如贴纸、化妆,这些都需要用到人脸关键点识别技术,其次人脸关键点识别、面部表情识别,可以帮助美颜工具实现更多细分的趣味拍摄功......
  • 详解一致性哈希
    在单机系统中,所有的数据都存储在同一个服务器下,当数据量越来越多的时候,超过了单机存储容量的上限,就需要使用分布式存储系统,在分布式存储系统重,数据会被拆分到不同的存储服......
  • Vue过滤器的使用详解(代码实现)
    过滤器的功能是对要显示的数据进行格式化后再显示,其并没有改变原本的数据,只是产生新的对应的数据 过滤器,其实不是必须要用的东西,它只是vue给我们提供的新的数据处理方式......
  • Java基础随笔(2)static静态详解
    1packagecom.chapter;23classBowl{4Bowl(intmarker){5System.out.println("Bowl+("+marker+")");6}78voidf1(int......
  • 07-Redis系列之-双写一致性,缓存详解和优化点
    双写一致性redis和mysql数据同步方案先更新数据库,再更新缓存(一般不用)先删缓存,再更新数据库(在存数据的时候,请求来了,缓存不是最新的,一般也不用)先更新数据库,再删缓存(请求......
  • Kubernetes(k8s)权限管理RBAC详解
    一、简介kubernetes集群相关所有的交互都通过apiserver来完成,对于这样集中式管理的系统来说,权限管理尤其重要,在1.5版的时候引入了RBAC(RoleBaseAccessControl)的权限......
  • 03-Redis系列之-高级用法详解
    慢查询生命周期我们配置一个时间,如果查询时间超过了我们设置的时间,我们就认为这是一个慢查询.慢查询发生在第三阶段客户端超时不一定慢查询,但慢查询是客户端超时的一个......
  • TextView(文本框)详解
    从本节开始我们来一个个讲解Android中的UI控件,本节给大家带来的UI控件是:TextView(文本框),用于显示文本的一个控件,另外声明一点,我不是翻译API文档,不会一个个属性的去扣,只学实......
  • 【基数排序算法详解】Java/Go/Python/JS/C不同语言实现
    说明基数排序(RadixSort)是一种非比较型整数排序算法,其原理是将整数按位数切割成不同的数字,然后按每个位数分别比较。由于整数也可以表达字符串(比如名字或日期)和特定格式的......