canal and dbz ddl and dml data structure

canal and dbz ddl and dml data structures

canal

dml

{<!-- -->
    "canalId":"canal-server-86d87f6cc7-b9x58:11111",
    "comment":{<!-- -->
        "traceId":"ce27f63948934680b6010e2d22eea76b.34.16976519950082421|4ad7d32221f36288",
        "userId":"00000000-0000-0000-0000-000000000000",
        "spring.profiles.active":"prodawsbjgray"
    },
    "data":[
        {<!-- -->
            "id":"22a88c4a-edbe-4a7f-a55d-3b7436704795",
            "time_create":"1697472177437",
            "is_finished":"0"
        },
        {<!-- -->
            "id":"238bfa16-0c5e-48b1-a2a5-b9eb82f69969",
            "time_create":"1697472123796",
            "is_finished":"0"
        }
    ],
    "database":"main",
    "es":1697652000000,
    "id":41127228,
    "isDdl":false,
    "mysqlType":{<!-- -->
        "id":"char(36)",
        "time_create":"bigint(20)",
        "is_finished":"tinyint(1)"
    },
    "old":null,
    "pkNames":null,
    "sql":"",
    "sqlType":{<!-- -->
        "id":1,
        "time_create":-5,
        "is_finished":-7
    },
    "table":"orderitemreceiveuniquerecord",
    "traceId":"ce27f63948934680b6010e2d22eea76b.34.16976519950082421|4ad7d32221f36288",
    "transactionId":"eb682fa3-4bb9-11ec-9342-02373ee17996:1-8122380830,5f5ff1bb-f4c9-11ed-9a25-02eca4b43118:1-5131967243,af89e972-6428-11ed-85b9-022 ddc42965e:1-2435875905,b8f92b4b-642a -11ed-80b0-02675a943cb0:1-1978753157,e3d1c91e-ed86-11ed-acf9-06ad59a4123c:1-334308525,bff3f1c5-da30-11ea-b6f6-0203edd271c0:1-15490823 332",
    "ts":1697652000103,
    "type":"DELETE", // INSERT,UPDATE
    "userId":"00000000-0000-0000-0000-000000000000"
}

ddl

{<!-- -->
    "canalId":"pupu-main-bigdata-kafka-canal-server-86d87f6cc7-b9x58:11111",
    "comment":null,
    "data":null,
    "database":"pupu_main",
    "es":1697644863000,
    "id":41074661,
    "isDdl":true,
    "mysqlType":null,
    "old":null,
    "pkNames":null,
    "sql":"alter table orderextends drop partition p20231012",
    "sqlType":null,
    "table":"orderextends",
    "traceId":null,
    "transactionId":"eb682fa3-4bb9-11ec-9342-02373ee17996:1-8122380830,5f5ff1bb-f4c9-11ed-9a25-02eca4b43118:1-5131888396,af89e972-6428-11ed-85b9-022 ddc42965e:1-2435875905,b8f92b4b-642a -11ed-80b0-02675a943cb0:1-1978753157,e3d1c91e-ed86-11ed-acf9-06ad59a4123c:1-334277763,bff3f1c5-da30-11ea-b6f6-0203edd271c0:1-15490823 332",
    "ts":1697644863502,
    "type":"ALTER",
    "userId":null
}

dbz

ddl

{<!-- -->
    "schema": {<!-- -->
      "type": "struct",
      "name": "io.debezium.connector.mysql.SchemaChangeValue",
      "optional": false,
      "fields": [
        {<!-- -->
          "field": "databaseName",
          "type": "string",
          "optional": false
        },
        {<!-- -->
          "field": "ddl",
          "type": "string",
          "optional": false
        },
        {<!-- -->
          "field": "source",
          "type": "struct",
          "name": "io.debezium.connector.mysql.Source",
          "optional": false,
          "fields": [
            {<!-- -->
              "type": "string",
              "optional": true,
              "field": "version"
            },
            {<!-- -->
              "type": "string",
              "optional": false,
              "field": "name"
            },
            {<!-- -->
              "type": "int64",
              "optional": false,
              "field": "server_id"
            },
            {<!-- -->
              "type": "int64",
              "optional": false,
              "field": "ts_sec"
            },
            {<!-- -->
              "type": "string",
              "optional": true,
              "field": "gtid"
            },
            {<!-- -->
              "type": "string",
              "optional": false,
              "field": "file"
            },
            {<!-- -->
              "type": "int64",
              "optional": false,
              "field": "pos"
            },
            {<!-- -->
              "type": "int32",
              "optional": false,
              "field": "row"
            },
            {<!-- -->
              "type": "boolean",
              "optional": true,
              "default": false,
              "field": "snapshot"
            },
            {<!-- -->
              "type": "int64",
              "optional": true,
              "field": "thread"
            },
            {<!-- -->
              "type": "string",
              "optional": true,
              "field": "db"
            },
            {<!-- -->
              "type": "string",
              "optional": true,
              "field": "table"
            },
            {<!-- -->
              "type": "string",
              "optional": true,
              "field": "query"
            }
          ]
        }
      ]
    },
    "payload": {<!-- -->
      "databaseName": "inventory",
      "ddl": "CREATE TABLE products ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, description VARCHAR(512), weight FLOAT ); ALTER TABLE products AUTO_INCREMENT = 101;",
      "source" : {<!-- -->
        "version": "0.10.0.Beta4",
        "name": "mysql-server-1",
        "server_id": 0,
        "ts_sec": 0,
        "gtid": null,
        "file": "mysql-bin.000003",
        "pos": 154,
        "row": 0,
        "snapshot": true,
        "thread": null,
        "db": null,
        "table": null,
        "query": null
      }
    }

dml

 {<!-- -->
    "schema": {<!-- --> ... },
    "payload": {<!-- -->
      "before": {<!-- -->
        "id": 1004,
        "first_name": "Anne Marie",
        "last_name": "Kretchmar",
        "email": "[email protected]"
      },
      "after": null,
      "source": {<!-- -->
        "version": "1.3.1.Final",
        "connector": "mysql",
        "name": "mysql-server-1",
        "ts_sec": 1465581,
        "snapshot": false,
        "db": "inventory",
        "table": "customers",
        "server_id": 223344,
        "gtid": null,
        "file": "mysql-bin.000003",
        "pos": 805,
        "row": 0,
        "thread": 7,
        "query": "DELETE FROM customers WHERE id=1004"
      },
      "op": "d", /c, r, u, d
      "ts_ms": 1465581902461
    }
  }