Canal数据格式及写入Kafka数据样例

1. kafka中格式

• 示例

{"data":[{"id":"27","key_value":null,"double_value":null,"long_value":"100","store_type":"NUMBER","store_url":null,"time":"1588748169","item_id":"600","create_time":"2020-05-06 00:00:00","modify_time":null}],"database":"bigdata","es":1588748884000,"id":24,"isDdl":false,"mysqlType":{"id":"bigint(20)","key_value":"mediumtext","double_value":"double","long_value":"bigint(20)","store_type":"varchar(255)","store_url":"varchar(255)","time":"bigint(20)","item_id":"bigint(20)","create_time":"timestamp","modify_time":"timestamp"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":-5,"key_value":-4,"double_value":8,"long_value":-5,"store_type":12,"store_url":12,"time":-5,"item_id":-5,"create_time":93,"modify_time":93},"table":"job_result","ts":1588748884545,"type":"INSERT"}

{"data":[{"id":"27","key_value":null,"double_value":null,"long_value":"1001","store_type":"NUMBER","store_url":null,"time":"1588748169","item_id":"600","create_time":"2020-05-06 15:09:56","modify_time":null}],"database":"bigdata","es":1588748996000,"id":30,"isDdl":false,"mysqlType":{"id":"bigint(20)","key_value":"mediumtext","double_value":"double","long_value":"bigint(20)","store_type":"varchar(255)","store_url":"varchar(255)","time":"bigint(20)","item_id":"bigint(20)","create_time":"timestamp","modify_time":"timestamp"},"old":[{"long_value":"100","create_time":"2020-05-06 00:00:00"}],"pkNames":["id"],"sql":"","sqlType":{"id":-5,"key_value":-4,"double_value":8,"long_value":-5,"store_type":12,"store_url":12,"time":-5,"item_id":-5,"create_time":93,"modify_time":93},"table":"job_result","ts":1588748996121,"type":"UPDATE"}

{"data":[{"id":"27","key_value":null,"double_value":null,"long_value":"1001","store_type":"NUMBER","store_url":null,"time":"1588748169","item_id":"600","create_time":"2020-05-06 15:09:56","modify_time":null}],"database":"bigdata","es":1588764940000,"id":780,"isDdl":false,"mysqlType":{"id":"bigint(20)","key_value":"mediumtext","double_value":"double","long_value":"bigint(20)","store_type":"varchar(255)","store_url":"varchar(255)","time":"bigint(20)","item_id":"bigint(20)","create_time":"timestamp","modify_time":"timestamp"},"old":null,"pkNames":["id"],"sql":"","sqlType":{"id":-5,"key_value":-4,"double_value":8,"long_value":-5,"store_type":12,"store_url":12,"time":-5,"item_id":-5,"create_time":93,"modify_time":93},"table":"job_result","ts":1588764940116,"type":"DELETE"}

{"data":null,"database":"","es":1588830163000,"id":2,"isDdl":false,"mysqlType":null,"old":null,"pkNames":null,"sql":"CREATE DATABASE /*!32312 IF NOT EXISTS*/ `canal_manager` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */","sqlType":null,"table":"","ts":1588830732675,"type":"QUERY"}

2. 格式分析

1. 一个操作为一个JsonObject
2. 格式详解:

{
  "data": [
    {
      "id": "27",
      "key_value": null,
      "double_value": null,
      "long_value": "1001",
      "store_type": "NUMBER",
      "store_url": null,
      "time": "1588748169",
      "item_id": "600",
      "create_time": "2020-05-06 15:09:56",
      "modify_time": null
    }
  ],
  "database": "bigdata",
  "es": 1588748996000,
  "id": 30,
  "isDdl": false,
  "mysqlType": {
    "id": "bigint(20)",
    "key_value": "mediumtext",
    "double_value": "double",
    "long_value": "bigint(20)",
    "store_type": "varchar(255)",
    "store_url": "varchar(255)",
    "time": "bigint(20)",
    "item_id": "bigint(20)",
    "create_time": "timestamp",
    "modify_time": "timestamp"
  },
  "old": [
    {
      "long_value": "100",
      "create_time": "2020-05-06 00:00:00"
    }
  ],
  "pkNames": [
    "id"
  ],
  "sql": "",
  "sqlType": {
    "id": -5,
    "key_value": -4,
    "double_value": 8,
    "long_value": -5,
    "store_type": 12,
    "store_url": 12,
    "time": -5,
    "item_id": -5,
    "create_time": 93,
    "modify_time": 93
  },
  "table": "job_result",
  "ts": 1588748996121,
  "type": "UPDATE"
}

1. data(JSONArray):

具体insert/update/delete的变更数据，可为多条，1个binlog event事件可对应多条变更，比如批处理

• 注意: 如果为type为delete操作,data为删除前的数据,需要特殊处理.

2. database:

数据库名

3. es:

mysql binlog里原始的时间戳,也就是数据原始变更的时间

4. id
5. isDdl:

是否是ddl变更操作，比如create table/drop table

6. mysqlType(JSONObject):

data中每个字段对应的格式类型

7. old(JSONArray):

修改前的字段数据(id变更前的值)

8. pkNames(JSONArray):

对应Mysql中PriMary Key

9. sql:

具体的ddl sql

10. sqlType(JSONObject):

jdbc type

11. table:

表名

12. ts:

时间戳(13位),canal收到这个binlog,构造自己协议对象的时间.

13. type:

操作类型(INSERT,UPDATE,DELETE,QUERY,TRUNCATE)

特例
当truncate表后,canal的数据为:

{
"data": null,
"database": "basicstat",
"es": 1608280674000,
"id": 30,
"isDdl": true,
"mysqlType": null,
"old": null,
"pkNames": null,
"sql": "truncate test_canal_truncate",
"sqlType": null,
"table": "test_canal_truncate",
"ts": 1608280674856,
"type": "TRUNCATE"
}