【现场问题】flink-cdc，Oracle2Mysql的坑，Oracle区分大小写导致-Toy模板网

这篇具有很好参考价值的文章主要介绍了【现场问题】flink-cdc，Oracle2Mysql的坑，Oracle区分大小写导致。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

错误的flink-cdc语句sql

CREATE TABLE t_wx_source_1 (
    id String,
    name String,
    age String
) WITH (
    'connector' = 'oracle-cdc',
    'hostname' = '192.168.1.135',
    'port' = '1521',
    'username' = 'flink',
    'password' = 'XXXXX',
    'database-name' = 'dbc',
    'schema-name' = 'FLINK',
    'table-name' = 't_wx_source_2'
);
CREATE TABLE t_wx_target (
    id String Not Null,
    name String,
    age String,
    PRIMARY KEY(id) NOT ENFORCED
) WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://192.168.1.91:3306/test',
    'table-name' = 't_wx_target_1',
    'username' = 'root',
    'password' = 'XXXXX',
    'driver' =  'com.mysql.cj.jdbc.Driver'
);
insert into t_wx_target select id,name,age from t_wx_source_1;

我们看一下oracle的数据库字段

【现场问题】flink-cdc，Oracle2Mysql的坑，Oracle区分大小写导致,flink,oracle,大数据

再看一下错误sql里面的内容

【现场问题】flink-cdc，Oracle2Mysql的坑，Oracle区分大小写导致,flink,oracle,大数据

flink报错内容

【现场问题】flink-cdc，Oracle2Mysql的坑，Oracle区分大小写导致,flink,oracle,大数据
Column ‘id’ is NOT NULL, however, a null value is being written into it. You can set job configuration ‘table.exec.sink.not-null-enforcer’=‘DROP’ to suppress this exception and drop such records silently
大致意思就是不能插入为空的数值。
为什么会报这个错误，我们来看DML的执行语句：
insert into t_wx_target select id,name,age from t_wx_source_1;
【现场问题】flink-cdc，Oracle2Mysql的坑，Oracle区分大小写导致,flink,oracle,大数据

每次数据都是null,然后我们的sink（t_wx_target ）表里面的字段id，是非空字段，所以就报错了
【现场问题】flink-cdc，Oracle2Mysql的坑，Oracle区分大小写导致,flink,oracle,大数据文章来源地址https://www.toymoban.com/news/detail-521224.html

正确的sql

CREATE TABLE t_wx_source_1 (
    id String NOT NULL,
    name String,
    age String
) WITH (
    'connector' = 'oracle-cdc',
    'hostname' = '192.168.1.135',
    'port' = '1521',
    'username' = 'flink',
    'password' = 'XXXX',
    'database-name' = 'dbc',
    'schema-name' = 'FLINK',
    'table-name' = 't_wx_source_2'
);
CREATE TABLE t_wx_target (
    id String,
    name String,
    age String,
    PRIMARY KEY(id) NOT ENFORCED
) WITH (
    'connector' = 'jdbc',
    'url' = 'jdbc:mysql://192.168.1.91:3306/test',
    'table-name' = 't_wx_target_1',
    'username' = 'root',
    'password' = 'XXXXXX',
    'driver' =  'com.mysql.cj.jdbc.Driver'
);
insert into t_wx_target select id,name,age from t_wx_source_1;