版本:
dinky: 0.7.4
flink: 17.1
mysql: 8.0.32
doris: 1.2.7
mysql
DROP TABLE IF EXISTS student;
CREATE TABLE IF NOT EXISTS student (
`sid` INT NOT NULL
,`name` STRING
,PRIMARY KEY ( `sid` ) NOT ENFORCED
) WITH (
'connector' = 'mysql-cdc'
,'hostname' = 'xx.xx.xx.xx'
,'port' = '3306'
,'username' = 'root'
,'password' = 'xxxx'
,'database-name' = 'test'
,'table-name' = 'student'
);
DROP TABLE IF EXISTS score;
CREATE TABLE IF NOT EXISTS score (
`cid` INT NOT NULL
,`sid` INT
,`cls` STRING
,`score` INT
,PRIMARY KEY ( `cid` ) NOT ENFORCED
) WITH (
'connector' = 'mysql-cdc'
,'hostname' = 'xx.xx.xx.xx'
,'port' = '3306'
,'username' = 'root'
,'password' = 'xxxx'
,'database-name' = 'test'
,'table-name' = 'score'
);
SET 'execution.checkpointing.interval' = '10s';
DROP TABLE IF EXISTS scoreinfo;
CREATE TABLE IF NOT EXISTS scoreinfo (
`cid` INT NOT NULL
,`sid` INT
,`name` STRING
,`cls` STRING
,`score` INT
,PRIMARY KEY ( `cid` ) NOT ENFORCED
) COMMENT 'OLAP'
WITH (
'connector' = 'doris'
,'fenodes' = 'xx.xx.xx.xx:18030'
,'table.identifier' = 'test.scoreinfo'
,'sink.label-prefix' = 'doris_label'
,'username' = 'root'
,'password' = 'xxxx'
);
insert into scoreinfo
select
a.cid,a.sid,b.name,a.cls,a.score
from score a
left join student b on a.sid = b.sid;
数据:
INSERT INTO `test`.`score` (`cid`, `sid`, `cls`, `score`) VALUES (1, 1, 'chinese', 90);
INSERT INTO `test`.`score` (`cid`, `sid`, `cls`, `score`) VALUES (2, 1, 'math', 95);
INSERT INTO `test`.`score` (`cid`, `sid`, `cls`, `score`) VALUES (3, 1, 'english', 93);
INSERT INTO `test`.`score` (`cid`, `sid`, `cls`, `score`) VALUES (4, 2, 'chinese', 92);
INSERT INTO `test`.`score` (`cid`, `sid`, `cls`, `score`) VALUES (5, 2, 'math', 75);
INSERT INTO `test`.`score` (`cid`, `sid`, `cls`, `score`) VALUES (6, 2, 'english', 80);
INSERT INTO `test`.`score` (`cid`, `sid`, `cls`, `score`) VALUES (7, 3, 'chinese', 100);
INSERT INTO `test`.`score` (`cid`, `sid`, `cls`, `score`) VALUES (8, 3, 'math', 60);
INSERT INTO `test`.`student` (`sid`, `name`) VALUES (1, '小红');
INSERT INTO `test`.`student` (`sid`, `name`) VALUES (2, '小黑');
INSERT INTO `test`.`student` (`sid`, `name`) VALUES (3, '小黄');
INSERT INTO `test`.`student` (`sid`, `name`) VALUES (4, '小白');
结果:
符合预期