SS00004.EBpositions——|Hadoop&EbProjec电商实时数仓项目.v04|——|EbProject.v04|canal部署&kafka客户端测试|

yanqi_vip

已于 2022-04-18 14:38:40 修改

阅读量74

点赞数

分类专栏： developv002——EB实时数仓文章标签：数据库 mysql java sql 大数据

于 2022-04-17 15:47:00 首次发布

不予转载

本文链接：https://blog.csdn.net/yanqi_vip/article/details/124240541

版权

developv002——EB实时数仓专栏收录该内容

14 篇文章 0 订阅

订阅专栏

一、Kafka客户端测试

### --- 启动相关服务

~~~     # 启动zookeeper服务
[root@hadoop01 ~]# ./zk.sh start
~~~     # 启动kafka服务
[root@hadoop01 ~]# kafka-server-start.sh -daemon /opt/yanqi/servers/kafka_2.12/config/server.properties
~~~     # 启动canal服务
[root@hadoop02 ~]# sh /opt/yanqi/servers/canal/bin/startup.sh

### --- 创建kafka主题

~~~     # 创建主题
[root@hadoop02 ~]# kafka-topics.sh --zookeeper hadoop01:2181,hadoop02:2181,hadoop03:2181/myKafka --create --replication-factor 3 -partitions 1 --topic canal
~~~     # 查看主题
[root@hadoop02 ~]# kafka-topics.sh --zookeeper localhost:2181/myKafka --list
canal

### --- 启动kafka主题

~~~     # 启动kafka生产者
[root@hadoop02 ~]# kafka-console-producer.sh --broker-list hadoop02:9092 --topic canal
~~~     # 启动kafka消费者
[root@hadoop02 ~]# kafka-console-consumer.sh --zookeeper hadoop02:2181/myKafka --topic canal --from-beginning

二、在kafka验证canal采集MySQL生成binlog数据

### --- 验证kafka通过canal采集mysql生成的binlog数据
~~~      在mysql下yanqi_trade_orders表中对数据进行操作

~~~     # insert插入操作
mysql> INSERT INTO `yanqi_trade_orders` VALUES ('2', '23a0b124546', '121', '2', '0.12', '6331.00', '2', '0', '370203', '0', '0', '0', '1', '2020-06-28 16:55:02', '2020-06-28 16:55:02', '2020-10-21 22:54:32');
mysql> INSERT INTO `yanqi_trade_orders` VALUES ('3', '23a0b124546', '35', '2', '0.12', '1987.50', '4', '0', '370203', '0', '0', '0', '1', '2020-06-28 12:07:01', '2020-06-28 12:07:01', '2020-10-21 22:54:34');
~~~     # update更新操作
mysql> update `dwshow`.`yanqi_trade_orders` set `status` = '2' where `orderId` = '1';
~~~     # delete删除操作
mysql> DELETE FROM `dwshow`.`yanqi_trade_orders` WHERE `orderId` = '3';

### --- 在kafka消费者下查看采集到的数据

~~~     # 在kafka消费者主题下查看采集到的数据
[root@hadoop02 ~]# kafka-console-consumer.sh --zookeeper hadoop02:2181/myKafka --topic canal --from-beginning
~~~     # 在mysql下insert插入操作采集到数据
{"data":[{"orderId":"2","orderNo":"23a0b124546","userId":"121","status":"2","productMoney":"0.12","totalMoney":"6331.0","payMethod":"2","isPay":"0","areaId":"370203","tradeSrc":"0","tradeType":"0","isRefund":"0","dataFlag":"1","createTime":"2020-06-28 16:55:02","payTime":"2020-06-28 16:55:02","modifiedTime":"2020-10-21 22:54:32"}],"database":"dwshow","es":1638097808000,"id":3,"isDdl":false,"mysqlType":{"orderId":"bigint(11)","orderNo":"varchar(20)","userId":"bigint(11)","status":"tinyint(4)","productMoney":"decimal(11,2)","totalMoney":"decimal(11,2)","payMethod":"tinyint(4)","isPay":"tinyint(4)","areaId":"int(11)","tradeSrc":"tinyint(4)","tradeType":"int(11)","isRefund":"tinyint(4)","dataFlag":"tinyint(4)","createTime":"varchar(25)","payTime":"varchar(25)","modifiedTime":"timestamp"},"old":null,"pkNames":["orderId"],"sql":"","sqlType":{"orderId":-5,"orderNo":12,"userId":-5,"status":-6,"productMoney":3,"totalMoney":3,"payMethod":-6,"isPay":-6,"areaId":4,"tradeSrc":-6,"tradeType":4,"isRefund":-6,"dataFlag":-6,"createTime":12,"payTime":12,"modifiedTime":93},"table":"yanqi_trade_orders","ts":1638097808750,"type":"INSERT"}
{"data":[{"orderId":"3","orderNo":"23a0b124546","userId":"35","status":"2","productMoney":"0.12","totalMoney":"1987.50","payMethod":"4","isPay":"0","areaId":"370203","tradeSrc":"0","tradeType":"0","isRefund":"0","dataFlag":"1","createTime":"2020-06-28 12:07:01","payTime":"2020-06-28 12:07:01","modifiedTime":"2020-10-21 22:54:34"}],"database":"dwshow","es":1638098012000,"id":4,"isDdl":false,"mysqlType":{"orderId":"bigint(11)","orderNo":"varchar(20)","userId":"bigint(11)","status":"tinyint(4)","productMoney":"decimal(11,2)","totalMoney":"decimal(11,2)","payMethod":"tinyint(4)","isPay":"tinyint(4)","areaId":"int(11)","tradeSrc":"tinyint(4)","tradeType":"int(11)","isRefund":"tinyint(4)","dataFlag":"tinyint(4)","createTime":"varchar(25)","payTime":"varchar(25)","modifiedTime":"timestamp"},"old":null,"pkNames":["orderId"],"sql":"","sqlType":{"orderId":-5,"orderNo":12,"userId":-5,"status":-6,"productMoney":3,"totalMoney":3,"payMethod":-6,"isPay":-6,"areaId":4,"tradeSrc":-6,"tradeType":4,"isRefund":-6,"dataFlag":-6,"createTime":12,"payTime":12,"modifiedTime":93},"table":"yanqi_trade_orders","ts":1638098012708,"type":"INSERT"}
~~~     # 在mysql下update更新操作采集到数据
{"data":[{"orderId":"1","orderNo":"23a0b124546","userId":"98","status":"2","productMoney":"0.12","totalMoney":"10468.0","payMethod":"2","isPay":"0","areaId":"370203","tradeSrc":"0","tradeType":"0","isRefund":"1","dataFlag":"2","createTime":"2020-06-28 18:14:01","payTime":"2020-06-28 18:14:01","modifiedTime":"2021-11-28 19:20:52"}],"database":"dwshow","es":1638098452000,"id":6,"isDdl":false,"mysqlType":{"orderId":"bigint(11)","orderNo":"varchar(20)","userId":"bigint(11)","status":"tinyint(4)","productMoney":"decimal(11,2)","totalMoney":"decimal(11,2)","payMethod":"tinyint(4)","isPay":"tinyint(4)","areaId":"int(11)","tradeSrc":"tinyint(4)","tradeType":"int(11)","isRefund":"tinyint(4)","dataFlag":"tinyint(4)","createTime":"varchar(25)","payTime":"varchar(25)","modifiedTime":"timestamp"},"old":[{"status":"1","modifiedTime":"2021-11-28 19:19:53"}],"pkNames":["orderId"],"sql":"","sqlType":{"orderId":-5,"orderNo":12,"userId":-5,"status":-6,"productMoney":3,"totalMoney":3,"payMethod":-6,"isPay":-6,"areaId":4,"tradeSrc":-6,"tradeType":4,"isRefund":-6,"dataFlag":-6,"createTime":12,"payTime":12,"modifiedTime":93},"table":"yanqi_trade_orders","ts":1638098452275,"type":"UPDATE"}
~~~     # 在mysql下delete删除操作采集到数据
{"data":[{"orderId":"3","orderNo":"23a0b124546","userId":"35","status":"2","productMoney":"0.12","totalMoney":"1987.50","payMethod":"4","isPay":"0","areaId":"370203","tradeSrc":"0","tradeType":"0","isRefund":"0","dataFlag":"1","createTime":"2020-06-28 12:07:01","payTime":"2020-06-28 12:07:01","modifiedTime":"2020-10-21 22:54:34"}],"database":"dwshow","es":1638098556000,"id":7,"isDdl":false,"mysqlType":{"orderId":"bigint(11)","orderNo":"varchar(20)","userId":"bigint(11)","status":"tinyint(4)","productMoney":"decimal(11,2)","totalMoney":"decimal(11,2)","payMethod":"tinyint(4)","isPay":"tinyint(4)","areaId":"int(11)","tradeSrc":"tinyint(4)","tradeType":"int(11)","isRefund":"tinyint(4)","dataFlag":"tinyint(4)","createTime":"varchar(25)","payTime":"varchar(25)","modifiedTime":"timestamp"},"old":null,"pkNames":["orderId"],"sql":"","sqlType":{"orderId":-5,"orderNo":12,"userId":-5,"status":-6,"productMoney":3,"totalMoney":3,"payMethod":-6,"isPay":-6,"areaId":4,"tradeSrc":-6,"tradeType":4,"isRefund":-6,"dataFlag":-6,"createTime":12,"payTime":12,"modifiedTime":93},"table":"yanqi_trade_orders","ts":1638098557109,"type":"DELETE"}

附录一：此时MySQL数据表若有变化，会将row类型的log写进Kakfa，具体格式为json：

### --- 此时MySQL数据表若有变化，会将row类型的log写进Kakfa，具体格式为json：

data：       # --- 最新的数据，为json数组。 如果是插入则表示最新插入的数据； 如果是更新，则表示更新后的最新数据； 如果是删除，则表示被删除的数据
database：   # --- 数据库名称
es：         # --- 事件时间，13位的时间戳
id：         # --- 事件操作的序列号，1,2,3...
isDdl：      # --- 是否是DDL操作
mysqlType：  # --- 字段类型
old：        # --- 旧数据
pkNames：    # --- 主键名称
sql：        # --- SQL语句
sqlType：    # --- 是经过canal转换处理的，比如unsigned int会被转化为Long，unsigned long会被转换为BigDecimal
table：      # --- 表名
ts：         # --- 日志时间
type：       # --- 操作类型，比如DELETE，UPDATE，INSERT

附录二：insert操作

{
  "data": [
    {
      "orderId": "3",
      "orderNo": "23a0b124546",
      "userId": "35",
      "status": "2",
      "productMoney": "0.12",
      "totalMoney": "1987.50",
      "payMethod": "4",
      "isPay": "0",
      "areaId": "370203",
      "tradeSrc": "0",
      "tradeType": "0",
      "isRefund": "0",
      "dataFlag": "1",
      "createTime": "2020-06-28 12:07:01",
      "payTime": "2020-06-28 12:07:01",
      "modifiedTime": "2020-10-21 22:54:34"
    }
  ],
  "database": "dwshow",
  "es": 1638098012000,
  "id": 4,
  "isDdl": false,
  "mysqlType": {
    "orderId": "bigint(11)",
    "orderNo": "varchar(20)",
    "userId": "bigint(11)",
    "status": "tinyint(4)",
    "productMoney": "decimal(11,2)",
    "totalMoney": "decimal(11,2)",
    "payMethod": "tinyint(4)",
    "isPay": "tinyint(4)",
    "areaId": "int(11)",
    "tradeSrc": "tinyint(4)",
    "tradeType": "int(11)",
    "isRefund": "tinyint(4)",
    "dataFlag": "tinyint(4)",
    "createTime": "varchar(25)",
    "payTime": "varchar(25)",
    "modifiedTime": "timestamp"
  },
  "old": null,
  "pkNames": [
    "orderId"
  ],
  "sql": "",
  "sqlType": {
    "orderId": -5,
    "orderNo": 12,
    "userId": -5,
    "status": -6,
    "productMoney": 3,
    "totalMoney": 3,
    "payMethod": -6,
    "isPay": -6,
    "areaId": 4,
    "tradeSrc": -6,
    "tradeType": 4,
    "isRefund": -6,
    "dataFlag": -6,
    "createTime": 12,
    "payTime": 12,
    "modifiedTime": 93
  },
  "table": "yanqi_trade_orders",
  "ts": 1638098012708,
  "type": "INSERT"
}

附录三：update操作

{
  "data": [
    {
      "orderId": "1",
      "orderNo": "23a0b124546",
      "userId": "98",
      "status": "2",
      "productMoney": "0.12",
      "totalMoney": "10468.0",
      "payMethod": "2",
      "isPay": "0",
      "areaId": "370203",
      "tradeSrc": "0",
      "tradeType": "0",
      "isRefund": "1",
      "dataFlag": "2",
      "createTime": "2020-06-28 18:14:01",
      "payTime": "2020-06-28 18:14:01",
      "modifiedTime": "2021-11-28 19:20:52"
    }
  ],
  "database": "dwshow",
  "es": 1638098452000,
  "id": 6,
  "isDdl": false,
  "mysqlType": {
    "orderId": "bigint(11)",
    "orderNo": "varchar(20)",
    "userId": "bigint(11)",
    "status": "tinyint(4)",
    "productMoney": "decimal(11,2)",
    "totalMoney": "decimal(11,2)",
    "payMethod": "tinyint(4)",
    "isPay": "tinyint(4)",
    "areaId": "int(11)",
    "tradeSrc": "tinyint(4)",
    "tradeType": "int(11)",
    "isRefund": "tinyint(4)",
    "dataFlag": "tinyint(4)",
    "createTime": "varchar(25)",
    "payTime": "varchar(25)",
    "modifiedTime": "timestamp"
  },
  "old": [
    {
      "status": "1",
      "modifiedTime": "2021-11-28 19:19:53"
    }
  ],
  "pkNames": [
    "orderId"
  ],
  "sql": "",
  "sqlType": {
    "orderId": -5,
    "orderNo": 12,
    "userId": -5,
    "status": -6,
    "productMoney": 3,
    "totalMoney": 3,
    "payMethod": -6,
    "isPay": -6,
    "areaId": 4,
    "tradeSrc": -6,
    "tradeType": 4,
    "isRefund": -6,
    "dataFlag": -6,
    "createTime": 12,
    "payTime": 12,
    "modifiedTime": 93
  },
  "table": "yanqi_trade_orders",
  "ts": 1638098452275,
  "type": "UPDATE"
}

附录四：delete操作

{
  "data": [
    {
      "orderId": "3",
      "orderNo": "23a0b124546",
      "userId": "35",
      "status": "2",
      "productMoney": "0.12",
      "totalMoney": "1987.50",
      "payMethod": "4",
      "isPay": "0",
      "areaId": "370203",
      "tradeSrc": "0",
      "tradeType": "0",
      "isRefund": "0",
      "dataFlag": "1",
      "createTime": "2020-06-28 12:07:01",
      "payTime": "2020-06-28 12:07:01",
      "modifiedTime": "2020-10-21 22:54:34"
    }
  ],
  "database": "dwshow",
  "es": 1638098556000,
  "id": 7,
  "isDdl": false,
  "mysqlType": {
    "orderId": "bigint(11)",
    "orderNo": "varchar(20)",
    "userId": "bigint(11)",
    "status": "tinyint(4)",
    "productMoney": "decimal(11,2)",
    "totalMoney": "decimal(11,2)",
    "payMethod": "tinyint(4)",
    "isPay": "tinyint(4)",
    "areaId": "int(11)",
    "tradeSrc": "tinyint(4)",
    "tradeType": "int(11)",
    "isRefund": "tinyint(4)",
    "dataFlag": "tinyint(4)",
    "createTime": "varchar(25)",
    "payTime": "varchar(25)",
    "modifiedTime": "timestamp"
  },
  "old": null,
  "pkNames": [
    "orderId"
  ],
  "sql": "",
  "sqlType": {
    "orderId": -5,
    "orderNo": 12,
    "userId": -5,
    "status": -6,
    "productMoney": 3,
    "totalMoney": 3,
    "payMethod": -6,
    "isPay": -6,
    "areaId": 4,
    "tradeSrc": -6,
    "tradeType": 4,
    "isRefund": -6,
    "dataFlag": -6,
    "createTime": 12,
    "payTime": 12,
    "modifiedTime": 93
  },
  "table": "yanqi_trade_orders",
  "ts": 1638098557109,
  "type": "DELETE"
}

yanqi_vip

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
SS00004.EBpositions——|Hadoop&EbProjec电商实时数仓项目.v04|——|EbProject.v04|canal部署&kafka客户端测试|

一、Kafka客户端测试### --- 启动相关服务~~~ # 启动zookeeper服务[root@hadoop01 ~]# ./zk.sh start~~~ # 启动kafka服务[root@hadoop01 ~]# kafka-server-start.sh -daemon /opt/yanqi/servers/kafka_2.12/config/se...
复制链接

扫一扫