首单分析（二）
1、数据采集——maxwell 实现
1.1、maxwell 介绍
Maxwell 是由美国zendesk开源，用java编写的Mysql实时抓取软件，其抓取的原理也是基于binlog。
1.2、maxwell 与 canal 对比
- Maxwell没有canal那种server+client模式，只有一个server把数据发送到消息队列或redis。如果需要多个实例，通过指定不同配置文件启动多个进程。
- Maxwell有一个亮点功能，就是canal只能抓取最新数据，对已存在的历史数据没有办法处理。而Maxwell有一个bootstrap功能，可以直接引导出完整的历史数据用于初始化，非常好用。
- Maxwell不能直接支持HA，但是它支持断点还原，即错误解决后重启继续上次点儿读取数据。
- Maxwell只支持json格式，而Canal如果用Server+client模式的话，可以自定义格式。
- Maxwell比Canal更加轻量级。
1.3、安装 maxwell
[hui@hadoop201 software]$ tar -zxvf maxwell-1.25.0.tar.gz -C /opt/module/
[hui@hadoop201 module]$ mv maxwell-1.25.0 maxwell1.25
1.4、使用maxwell前准备工作
#在数据库中建立一个maxwell库用于存储Maxwell的元数据 [atguigu@hadoop202 module]$ mysql -uroot -p123465 CREATE DATABASE maxwell; #分配一个账号可以操作该数据库 GRANT ALL ON maxwell.* TO 'maxwell'@'%' IDENTIFIED BY '123456'; #分配这个账号可以监控其他数据库的权限 GRANT SELECT ,REPLICATION SLAVE , REPLICATION CLIENT ON *.* TO maxwell@'%';
另外需要开启mysql binlog 功能
[hui@hadoop201 ~]$ locate my.cnf /etc/my.cnf /etc/my.cnf.d [hui@hadoop201 ~]$ less /etc/my.cnf # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html [mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid max_allowed_packet=1024M #数据库id server-id = 1 ##启动binlog，该参数的值会作为binlog的文件名 log-bin=mysql-bin ##binlog类型，maxwell要求为row类型 binlog_format=row ##启用binlog的数据库，需根据实际情况作出修改,如需配置监听多个数据库，可以多写几行进行配置 binlog-do-db=gmall0423 #解除下面一行配置，可以多监听一个数据库 #binlog-do-db=gmall
1.5、编辑maxwell配置文件
[hui@hadoop201 maxwell1.25]$ vim config.properties producer=kafka kafka.bootstrap.servers=hadoop201:9092,hadoop202:9092,hadoop203:9092 # mysql login info host=hadoop201 user=maxwell password=maxwell #需要添加 kafka_topic=gmall0423_db_maxwell #需要添加 后续初始化会用 client_id=maxwell_1
producer_partition_by=primary_key
注意：默认还是输出到指定Kafka主题的一个kafka分区，因为多个分区并行可能会打乱binlog的顺序如果要提高并行度，首先设置kafka的分区数>1,然后设置producer_partition_by属性
可选值producer_partition_by=database|table|primary_key|random| column
maxwell 启动脚本
[hui@hadoop201 bin]$ less maxwell.sh
/opt/module/maxwell-1.25.0/bin/maxwell --config /opt/module/maxwell-1.25.0/config.properties >/dev/null 2>&1 &
[hui@hadoop201 ~]$ jps
2675 Maxwell
启动模拟数据程序，观察效果
[hui@hadoop201 kafka]$ bin/kafka-console-consumer.sh --bootstrap-server hadoop201:9092 --topic gmall0423_db_maxwell
数据格式
{ "database":"gmall0423", "table":"comment_info", "type":"insert", "ts":1651873950, "xid":2229, "xoffset":1392, "data":{ "id":1522695828560359428, "user_id":23, "sku_id":12, "spu_id":10, "order_id":3515, "appraise":"1204", "comment_txt":"评论内容：23985229577423662931538863626755831166131361537121", "create_time":"2022-04-23 05:52:30", "operate_time":null } }
1.6、Maxwell 版本的 ods 层处理
1.6.1、不同操作下 canal 和 maxwell 数据格式对比
Insert
INSERT INTO z_user_info VALUES(30,'zhang3','13810001010'),(31,'li4','1389999999');
|
canal |
maxwell |
|
{"data":[{"id":"30","user_name":"zhang3","tel":"13810001010"},{"id":"31","user_name":"li4","tel":"1389999999"}],"database":"gmall-2020-04","es":1589385314000,"id":2,"isDdl":false,"mysqlType":{"id":"bigint(20)","user_name":"varchar(20)","tel":"varchar(20)"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":-5,"user_name":12,"tel":12},"table":"z_user_info","ts":1589385314116,"type":"INSERT"} |
{"database":"gmall-2020-04","table":"z_user_info","type":"insert","ts":1589385314,"xid":82982,"xoffset":0,"data":{"id":30,"user_name":"zhang3","tel":"13810001010"}}
{"database":"gmall-2020-04","table":"z_user_info","type":"insert","ts":1589385314,"xid":82982,"commit":true,"data":{"id":31,"user_name":"li4","tel":"1389999999"}} |
update
UPDATE z_user_info SET user_name='wang55' WHERE id IN(30,31)
|
canal |
maxwell |
|
{"data":[{"id":"30","user_name":"wang55","tel":"13810001010"},{"id":"31","user_name":"wang55","tel":"1389999999"}],"database":"gmall-2020-04","es":1589385508000,"id":3,"isDdl":false,"mysqlType":{"id":"bigint(20)","user_name":"varchar(20)","tel":"varchar(20)"},"old":[{"user_name":"zhang3"},{"user_name":"li4"}],"pkNames":["id"],"sql":"","sqlType":{"id":-5,"user_name":12,"tel":12},"table":"z_user_info","ts":1589385508676,"type":"UPDATE"} |
{"database":"gmall-2020-04","table":"z_user_info","type":"update","ts":1589385508,"xid":83206,"xoffset":0,"data":{"id":30,"user_name":"wang55","tel":"13810001010"},"old":{"user_name":"zhang3"}}
{"database":"gmall-2020-04","table":"z_user_info","type":"update","ts":1589385508,"xid":83206,"commit":true,"data":{"id":31,"user_name":"wang55","tel":"1389999999"},"old":{"user_name":"li4"}} |
delete
DELETE FROM z_user_info WHERE id IN(30,31)
|
canal |
maxwell |
|
{"data":[{"id":"30","user_name":"wang55","tel":"13810001010"},{"id":"31","user_name":"wang55","tel":"1389999999"}],"database":"gmall-2020-04","es":1589385644000,"id":4,"isDdl":false,"mysqlType":{"id":"bigint(20)","user_name":"varchar(20)","tel":"varchar(20)"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":-5,"user_name":12,"tel":12},"table":"z_user_info","ts":1589385644829,"type":"DELETE"} |
{"database":"gmall-2020-04","table":"z_user_info","type":"delete","ts":1589385644,"xid":83367,"xoffset":0,"data":{"id":30,"user_name":"wang55","tel":"13810001010"}}
{"database":"gmall-2020-04","table":"z_user_info","type":"delete","ts":1589385644,"xid":83367,"commit":true,"data":{"id":31,"user_name":"wang55","tel":"1389999999"}} |
1.6.2、数据格式对比总结
- 日志结构:canal 每一条SQL会产生一条日志，如果该条Sql影响了多行数据，则已经会通过集合的方式归集在这条日志中。（即使是一条数据也会是数组结构）maxwell 以影响的数据为单位产生日志，即每影响一条数据就会产生一条日志。如果想知道这些日志是否是通过某一条sql产生的可以通过xid进行判断，相同的xid的日志来自同一sql。
- 数字类型:当原始数据是数字类型时,maxwell会尊重原始数据的类型不增加双引，变为字符串。canal一律转换为字符串。
- 带原始数据字段定义:canal数据中会带入表结构。maxwell更简洁。
1.6.3、SparkStreaming 分流业务逻辑修改-——maxwell
import com.alibaba.fastjson.{JSON, JSONObject}
import org.apache.kafka.clients.consumer.ConsumerRecord
import org.apache.kafka.common.TopicPartition
import org.apache.spark.SparkConf
import org.apache.spark.streaming.dstream.{DStream, InputDStream}
import org.apache.spark.streaming.kafka010.{HasOffsetRanges, OffsetRange}
import org.apache.spark.streaming.{Seconds, StreamingContext}
import org.wdh01.gmall.realtime.util.{MyKafkaSink, MyKafkaUtil, OffsetManagerUtil}
/**
* 使用 maxwell 采集数据 分流
*/
object BaseDBMaxwellApp {
def main(args: Array[String]): Unit = {
val conf: SparkConf = new SparkConf().setAppName("BaseDBMaxwellApp").setMaster("local[4]")
val ssc: StreamingContext = new StreamingContext(conf, Seconds(5))
var topic: String = "gmall0426_db_maxwell"
var groupid: String = "base_db_db_maxwell_group"
var recordDStream: InputDStream[ConsumerRecord[String, String]] = null
//从 redis 获取偏移量
val offsetMap: Map[TopicPartition, Long] = OffsetManagerUtil.getOffset(topic, groupid)
if (offsetMap != null && offsetMap.size > 0) {
//从指定位置读取数据
recordDStream = MyKafkaUtil.getKafkaStream(topic, ssc, offsetMap, groupid)
} else {
//从默认位置读取数据
recordDStream = MyKafkaUtil.getKafkaStream(topic, ssc, groupid)
}
//获取当前采集周琼的偏移量
var ranges: Array[OffsetRange] = Array.empty[OffsetRange]
val offsetDstream: DStream[ConsumerRecord[String, String]] = recordDStream.transform {
rdd => {
ranges = rdd.asInstanceOf[HasOffsetRanges].offsetRanges
}
rdd
}
//对读取的数据进行结构转换 ConsumerRecord<K,V>==>V(sonStr)==>V(jsonObj )
val jsonObjDStream: DStream[JSONObject] = offsetDstream.map {
record => {
val jsonStr: String = record.value()
//将jsonStr 转换为 jsonObj
val jsonObj: JSONObject = JSON.parseObject(jsonStr)
jsonObj
}
}
jsonObjDStream.foreachRDD {
rdd => {
rdd.foreach {
jsonObj => {
//获取操作数据
val dataJsonObj: JSONObject = jsonObj.getJSONObject("data")
//获取操作类型
val opType: String = jsonObj.getString("type")
if (dataJsonObj != null && !dataJsonObj.isEmpty && "insert".equals(opType)) {
//获取表名
val tabName: String = jsonObj.getString("table")
var sendTopic = "ods_" + tabName
MyKafkaSink.send(sendTopic, dataJsonObj.toString())
}
}
}
//提交偏移量
OffsetManagerUtil.saveOffset(topic, groupid, ranges)
}
}
}
}
