debezium对表结构变更Schema evolution支持

绝地灬酷狼 2024-03-22 21:12 110阅读 0赞

debezium对表结构变更的支持  
debezium版本1.9（flinkcdc2.4.1使用此版本）

#### （1）Mysql ####

默认是支持的。  
![在这里插入图片描述][ff281b1b1138446f954f6660c5bee502.png]

#### （2）postgres ####

pg从9.4支持日志输出功能，但由于其限制 不支持捕捉表结构变更。

PostgreSQL’s logical decoding feature was introduced in version 9.4. It is a mechanism that allows the extraction of the changes that were committed to the transaction log and the processing of these changes in a user-friendly manner with the help of an output plug-in. The output plug-in enables clients to consume the changes.

![在这里插入图片描述][fe7780a3832c46028faa68992cf0c4c6.png]

#### （3）sqlserver ####

sqlserver是比较奇怪的方式，他会存储表结构。  
![在这里插入图片描述][8ece60c3e04b479e8e57e05322e7dcbf.png]

如果需要支持schema 变更则需要手动对数据库执行命令  
先通过指定新capture\_instance名称。创建新的capture对象，从而更新sqlserver cdc对表结构的描述。在停掉之前的capture\_instance。就能捕捉到新结构的数据。  
![在这里插入图片描述][ac9415e8456d4c6a9aacdc6177fe65eb.png]

#### （4）oracle ####

![在这里插入图片描述][798f05881c35484288dfab5dd2d008a8.png]  
![在这里插入图片描述][939152cd1c064b80b154777eeb257245.png]

当debezium解析redo logs 得到tableSchema。  
![在这里插入图片描述][94b7447ebcdd48d8960fafdcbe460a7f.png]

{
        
      "schema": {
        
      ...
      },
      "payload": {
        
        "source": {
        
          "version": "1.9.7.Final",
          "connector": "oracle",
          "name": "server1",
          "ts_ms": 1588252618953,
          "snapshot": "true",
          "db": "ORCLPDB1",
          "schema": "DEBEZIUM",
          "table": "CUSTOMERS",
          "txId" : null,
          "scn" : "1513734",
          "commit_scn": "1513754",
          "lcr_position" : null,
          "rs_id": "001234.00012345.0124",
          "ssn": 1,
          "redo_thread": 1
        },
        "databaseName": "ORCLPDB1", 
        "schemaName": "DEBEZIUM", //
        "ddl": "CREATE TABLE \"DEBEZIUM\".\"CUSTOMERS\" \n   (    \"ID\" NUMBER(9,0) NOT NULL ENABLE, \n    \"FIRST_NAME\" VARCHAR2(255), \n    \"LAST_NAME" VARCHAR2(255), \n    \"EMAIL\" VARCHAR2(255), \n     PRIMARY KEY (\"ID\") ENABLE, \n     SUPPLEMENTAL LOG DATA (ALL) COLUMNS\n   ) SEGMENT CREATION IMMEDIATE \n  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 \n NOCOMPRESS LOGGING\n  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1\n  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)\n  TABLESPACE \"USERS\" ", 
        "tableChanges": [ 
          {
        
            "type": "CREATE", 
            "id": "\"ORCLPDB1\".\"DEBEZIUM\".\"CUSTOMERS\"", 
            "table": {
         
              "defaultCharsetName": null,
              "primaryKeyColumnNames": [ 
                "ID"
              ],
              "columns": [ 
                {
        
                  "name": "ID",
                  "jdbcType": 2,
                  "nativeType": null,
                  "typeName": "NUMBER",
                  "typeExpression": "NUMBER",
                  "charsetName": null,
                  "length": 9,
                  "scale": 0,
                  "position": 1,
                  "optional": false,
                  "autoIncremented": false,
                  "generated": false
                },
                {
        
                  "name": "FIRST_NAME",
                  "jdbcType": 12,
                  "nativeType": null,
                  "typeName": "VARCHAR2",
                  "typeExpression": "VARCHAR2",
                  "charsetName": null,
                  "length": 255,
                  "scale": null,
                  "position": 2,
                  "optional": false,
                  "autoIncremented": false,
                  "generated": false
                },
                {
        
                  "name": "LAST_NAME",
                  "jdbcType": 12,
                  "nativeType": null,
                  "typeName": "VARCHAR2",
                  "typeExpression": "VARCHAR2",
                  "charsetName": null,
                  "length": 255,
                  "scale": null,
                  "position": 3,
                  "optional": false,
                  "autoIncremented": false,
                  "generated": false
                },
                {
        
                  "name": "EMAIL",
                  "jdbcType": 12,
                  "nativeType": null,
                  "typeName": "VARCHAR2",
                  "typeExpression": "VARCHAR2",
                  "charsetName": null,
                  "length": 255,
                  "scale": null,
                  "position": 4,
                  "optional": false,
                  "autoIncremented": false,
                  "generated": false
                }
              ]
            }
          }
        ]
      }
    }

[ff281b1b1138446f954f6660c5bee502.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/bf2fc8b2dc66410cbd836ae9813c9871.png
[fe7780a3832c46028faa68992cf0c4c6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/712ff21698ac48fb8e701a3f8c29724c.png
[8ece60c3e04b479e8e57e05322e7dcbf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/bf607e6d11974eae913f8a721f5fe112.png
[ac9415e8456d4c6a9aacdc6177fe65eb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/e1ba058c7a484329bfcb217dd5fcccaa.png
[798f05881c35484288dfab5dd2d008a8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/3e9551592d5548a583a77e945b641022.png
[939152cd1c064b80b154777eeb257245.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/c1982a9e7e34431f899050d0dbcb7e89.png
[94b7447ebcdd48d8960fafdcbe460a7f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/f62d5b5573c941bbb7cc9bb7f69e08e2.png