FlinkCDC同步Sqlserver、Oracle数据到iceberg

• F’lin’k’CDC

CDC的核心思想是：监测并捕获数据库的变动（包括数据 或 数据表的插入INSERT、更新UPDATE、删除DELETE等），将这些变更按发生的顺序完整记录下来，写入到消息中间件中以供其他服务进行订阅及消费

环境约束：

• Flink1.14.5
• SqlServer和Oracle均开启CDC

一、开启Flink服务

start-cluster.sh

二、开启Flink客户端

 sql-client.sh

三、创建source表（注意：先不要执行"use catalog"）

#数据源为SqlServer：
CREATE TABLE T_CP_SUB_FACTORY(
    SUB_FACTORY_ID STRING ,
    SUB_FACTORY_CODE STRING,
    SUB_FACTORY_NAME STRING,
    COMPANY_ID STRING,
    REMARK STRING,
    ENABLED INT,
    IS_DELETED INT,
    SORT_NUM INT,
    CRT_USER_ID STRING,
    CRT_USER_NAME STRING,
    MNT_USER_ID STRING,
    MNT_USER_NAME STRING,
    CRT_DATE STRING,
    MNT_DATE STRING,
    COMPANY_CODE STRING,
    PRIMARY KEY (SUB_FACTORY_ID) NOT ENFORCED
)WITH (
'connector' = 'sqlserver-cdc',
'hostname' = 'xx.xxx.xxx.xxx',
'port' = '1433',
'username' = '用户名',
'password' = '密码',
'debezium.snapshot.mode'='initial',
'database-name' = 'IOPP_SYSM',
'schema-name'= 'dbo',
'table-name' = 'T_CP_SUB_FACTORY'
);

#数据源为Oracle
CREATE TABLE T_CP_SUB_FACTORY(
    SUB_FACTORY_ID STRING ,
    SUB_FACTORY_CODE STRING,
    SUB_FACTORY_NAME STRING,
    COMPANY_ID STRING,
    REMARK STRING,
    ENABLED INT,
    IS_DELETED INT,
    SORT_NUM INT,
    CRT_USER_ID STRING,
    CRT_USER_NAME STRING,
    MNT_USER_ID STRING,
    MNT_USER_NAME STRING,
    CRT_DATE STRING,
    MNT_DATE STRING,
    COMPANY_CODE STRING,
    PRIMARY KEY (SUB_FACTORY_ID) NOT ENFORCED
)WITH (
'connector' = 'oracle-cdc',
'hostname' = 'xx.xxx.xxx.xxx',
'port' = '1521',
'username' = '用户名',
'password' = '密码',
'database-name' = 'IOPP_SYSM',
'schema-name'= 'DISP_HGSJPT',
'table-name' = 'T_CP_SUB_FACTORY',
'debezium.database.tablename.case.insensitive'='false',
'scan.startup.mode' = 'initial'
);

四、验证数据

select * from T_CP_SUB_FACTORY;

五、创建iceberg表

CREATE TABLE ODS_T_CP_SUB_FACTORY(
    SUB_FACTORY_ID STRING ,
    SUB_FACTORY_CODE STRING,
    SUB_FACTORY_NAME STRING,
    COMPANY_ID STRING,
    REMARK STRING,
    ENABLED INT,
    IS_DELETED INT,
    SORT_NUM INT,
    CRT_USER_ID STRING,
    CRT_USER_NAME STRING,
    MNT_USER_ID STRING,
    MNT_USER_NAME STRING,
    CRT_DATE STRING,
    MNT_DATE STRING,
    COMPANY_CODE STRING,
    PRIMARY KEY (SUB_FACTORY_ID) NOT ENFORCED
)WITH (
  'connector'='iceberg',
 'catalog-name'='hive_catalog',  
 'catalog-type'='hive', 
 'catalog-database'='ODS_TEST',
 'warehouse'='hdfs://xx.xxx.xxx.xxx:8020/user/hive/warehouse/hive_catalog', 
 'format-version'='2' 
);

六、同步数据

insert into ODS_T_CP_SUB_FACTORY select * from T_CP_SUB_FACTORY;