草庐IT

clickhouse高级功能之MaterializeMySQL详解

Seattle小小瓜 2023-03-28 原文
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对应的表，已可以实时看到数据变化

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单线程写入能力可以达到每秒几十万，在一般业务体系下增量更新的模式是完全没有问题的。
MaterializeMySQL详解spanstylecolormysql数据ciNoSQL数据库技术人为什么要写博客？

有关clickhouse高级功能之MaterializeMySQL详解的更多相关文章

  1. ruby-on-rails - Cucumber 是否只是 rspec 的包装器以帮助将测试组织成功能？ - 2

    只是想确保我理解了事情。据我目前收集到的信息，Cucumber只是一个“包装器”，或者是一种通过将事物分类为功能和步骤来组织测试的好方法，其中实际的单元测试处于步骤阶段。它允许您根据事物的工作方式组织您的测试。对吗？ 最佳答案 有点。它是一种组织测试的方式，但不仅如此。它的行为就像最初的Rails集成测试一样，但更易于使用。这里最大的好处是您的session在整个Scenario中保持透明。关于Cucumber的另一件事是您(应该)从使用您的代码的浏览器或客户端的角度进行测试。如果您愿意，您可以使用步骤来构建对象和设置状态，但通常您

  2. ruby-on-rails - rails 功能测试 - 2

    在Rails自动生成的功能测试(test/functional/products_controller_test.rb)中，我看到以下代码:classProductsControllerTest我的问题是:方法调用products()在哪里/如何定义？products(:one)到底是什么意思？看代码，大概意思是“创建一个产品”，但是它是如何工作的呢？注意我是Ruby/Rails的新手，如果这些是微不足道的问题，我深表歉意。 最佳答案 如果您查看test/fixtures文件夹，您会看到一个products.yml文件。这是在您创建

  3. ruby-on-rails - 功能测试 Authlogic？ - 2

    在我的一些Controller中，我有一个before_filter检查用户是否登录？用于CRUD操作。application.rbdeflogged_in?unlesscurrent_userredirect_toroot_pathendendprivatedefcurrent_user_sessionreturn@current_user_sessionifdefined?(@current_user_session)@current_user_session=UserSession.findenddefcurrent_userreturn@current_userifdefine

  4. 物联网MQTT协议详解 - 2

    一、什么是MQTT协议MessageQueuingTelemetryTransport：消息队列遥测传输协议。是一种基于客户端-服务端的发布/订阅模式。与HTTP一样，基于TCP/IP协议之上的通讯协议，提供有序、无损、双向连接，由IBM（蓝色巨人）发布。原理：（1）MQTT协议身份和消息格式有三种身份：发布者（Publish）、代理（Broker）（服务器）、订阅者（Subscribe）。其中，消息的发布者和订阅者都是客户端，消息代理是服务器，消息发布者可以同时是订阅者。MQTT传输的消息分为：主题（Topic）和负载（payload）两部分Topic，可以理解为消息的类型，订阅者订阅（Su

  5. Tcl脚本入门笔记详解（一） - 2

    TCL脚本语言简介•TCL（ToolCommandLanguage）是一种解释执行的脚本语言（ScriptingLanguage）,它提供了通用的编程能力：支持变量、过程和控制结构；同时TCL还拥有一个功能强大的固有的核心命令集。TCL经常被用于快速原型开发，脚本编程，GUI和测试等方面。•实际上包含了两个部分：一个语言和一个库。首先，Tcl是一种简单的脚本语言，主要使用于发布命令给一些互交程序如文本编辑器、调试器和shell。由于TCL的解释器是用C\C++语言的过程库实现的，因此在某种意义上我们又可以把TCL看作C库，这个库中有丰富的用于扩展TCL命令的C\C++过程和函数，所以，Tcl是

  6. ruby - Ruby 中允许 "p *1..10"打印出数字 1-10 的功能是什么？ - 2

    require'pp'p*1..10这会打印出1-10。为什么这么简洁？您还可以用它做什么？ 最佳答案 它是“splat”运算符。它可用于分解数组和范围并在赋值期间收集值。这里收集赋值中的值:a,*b=1,2,3,4=>a=1b=[2,3,4]在此示例中，内部数组([3,4])中的值被分解并收集到包含数组中:a=[1,2,*[3,4]]=>a=[1,2,3,4]您可以定义将参数收集到数组中的函数:deffoo(*args)pargsendfoo(1,2,"three",4)=>[1,2,"three",4]

  7. ruby - 高级语言是否使用数据结构？ - 2

    我目前还在上学，正在上一门关于用C++实现数据结构的类(class)。在业余时间，我喜欢使用“高级”语言(主要是Ruby和一些c#)进行编程。既然这些高级语言为你管理内存，你会用数据结构做什么？我可以理解对队列和堆栈的需求，但是您需要在Ruby中使用二叉树吗？还是2-3-4树？为什么？谢谢。 最佳答案 Sosincethesehigherlevellanguagesmanagethememoryforyou,whatwouldyouusedatastructuresfor?使用数据结构的主要原因与垃圾收集无关。但它是以某种方式有效的

  8. ruby - 现代计算机的功能是否不足以处理字符串而无需使用符号(在 Ruby 中) - 2

    我读过的关于Ruby符号的每一篇文章都在谈论符号相对于字符串的效率。但是，这不是1970年代。我的电脑可以处理一些额外的垃圾收集。我错了吗？我拥有最新最好的奔腾双核处理器和4GBRAM。我认为这应该足以处理一些字符串。 最佳答案 您的计算机可能能够处理“一点点额外的垃圾收集”，但是当“一点点”发生在运行数百万次的内部循环中时呢？如果它在内存有限的嵌入式系统上运行呢？有很多地方你可以随意使用字符串，但在某些地方你不能。这完全取决于上下文。 关于ruby-现代计算机的功能是否不足以处理字符串

  9. ruby - 如何在 Cucumber 的功能名称中使用空格 - 2

    我正在使用Windows并尝试运行一个现有的功能包，该功能包最初是在MacOS上构建的，这允许他们通过使用带空格的"\"来解决问题。我正在使用Ruby2.2.3和Cucumber。功能名称包含空格，我无法更改它。我尝试使用""和''来绕过空白，但每次都有同样的问题。这是问题的一个例子。如果我运行:cucumberfeatures/'Namecontainingwhitespaces.feature'它工作正常。但是当我运行时:cucumber-pmy_profile和cucumber.yml包含:my_profile:features/'Namecontainingwhitespace

  10. ruby - 如何测试在运行之间提供功能的 Ruby 脚本？ - 2

    单元测试的好方法是测试脚本在执行之间保持正确数据的能力——在使用Ctrl-C终止脚本然后重新运行之后？是否有针对执行类似操作的现有模块或脚本的任何测试可以针对最佳实践进行审查？ 最佳答案 像http://avdi.org/devblog/2010/07/19/greenletters-painless-automation-and-testing-for-command-line-applications/一样使用库或者期望、运行、终止并重新运行您的程序，并检查它是否运行正确。好的做法是将程序设计为独立的模块，每个模块都经过良好测试

随机推荐