1. 概述
1.1 应用场景
将来源表的数据更新到目标表时,将部分字段进行比对,只更新这些字段的数据。
1.2 实现思路
根据比对字段更新目标表指定字段的思路:
来源表有数据新增:目标表中插入新增数据
来源表有数据更新:根据选择的比对字段更新目标表指定字段的数据
来源表有数据删除:删除目标表中的对应的数据
2. 示例
2.1 准备数据
1)数据库 dwTest1 中新建一张表 User_base,作为来源表1,主键是 bid,进行比对的字段是 id,如下图所示:
建表语句如下:
CREATE TABLE `User_base` (
`bid` int(11) NOT NULL AUTO_INCREMENT,
`id` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`sex` varchar(255) DEFAULT NULL,
`age` varchar(255) DEFAULT NULL,
`degree` varchar(255) DEFAULT NULL,
PRIMARY KEY (`bid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `User_base` VALUES (null, '1001', '张飞云', 'male','25','博士');
INSERT INTO `User_base` VALUES (null, '1002', '李亮', 'male','22','学士');
INSERT INTO `User_base` VALUES (null, '1003', '王大林', 'female','24','硕士');
INSERT INTO `User_base` VALUES (null, '1004', '刘再清', 'male','21','学士');
INSERT INTO `User_base` VALUES (null, '1005', '马占云', 'female','20','硕士');
INSERT INTO `User_base` VALUES (null, '1006', '曹子云', 'male','23','博士');
INSERT INTO `User_base` VALUES (null, '1007', '刘飞', 'male','20','学士');
2)数据库 dwTest1 中新建一张表 User_test,作为来源表2,主键是 tid,进行比对的字段是 id,如下图所示:
建表语句如下:
CREATE TABLE `User_test` (
`tid` INT ( 11 ) NOT NULL AUTO_INCREMENT,
`id` VARCHAR ( 255 ) DEFAULT NULL,
`time` VARCHAR ( 255 ) DEFAULT NULL,
`place` VARCHAR ( 255 ) DEFAULT NULL,
`position` VARCHAR ( 255 ) DEFAULT NULL,
`level` VARCHAR ( 255 ) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `User_test` VALUES (null, '1001','2021-11','无锡','运营专家','3');
INSERT INTO `User_test` VALUES (null, '1002','2021-10','南京','运营专员','1');
INSERT INTO `User_test` VALUES (null, '1003','2020-11','无锡','运营经理','2');
INSERT INTO `User_test` VALUES (null, '1004','2021-10','南京','运营专员','1');
INSERT INTO `User_test` VALUES (null, '1005','2020-09','无锡','运营专家','3');
INSERT INTO `User_test` VALUES (null, '1006','2020-09','南京','运营专家','3');
INSERT INTO `User_test` VALUES (null, '1007','2020-11','无锡','运营经理','2');
3)数据库 dwTest1 中新建一张表 User_position,作为目标表,主键是 tid,进行比对的字段是 id,如下图所示:
建表语句如下:
CREATE TABLE `User_position` (
`pid` int(11) NOT NULL AUTO_INCREMENT,
`id` varchar(255) DEFAULT NULL,
`age` varchar(255) DEFAULT NULL,
`degree` varchar(255) DEFAULT NULL,
`position` varchar(255) DEFAULT NULL,
`level` varchar(255) DEFAULT NULL,
PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=89 DEFAULT CHARSET=utf8;
INSERT INTO `User_test` VALUES (null, '1001','25','博士','运营专家','3');
INSERT INTO `User_test` VALUES (null, '1002','22','学士','运营专员','1');
INSERT INTO `User_test` VALUES (null, '1003','24','硕士','运营经理','2');
INSERT INTO `User_test` VALUES (null, '1004','21','学士','运营专员','1');
INSERT INTO `User_test` VALUES (null, '1005','20','硕士','运营专家','3');
INSERT INTO `User_test` VALUES (null, '1006','23','博士','运营专家','3');
INSERT INTO `User_test` VALUES (null, '1007','20','学士','运营经理','2');
2.2 场景模拟
目标表中保留着来源表上一次更新的数据,而当前来源表已经发生了如下改变:
来源表1-User_base:新增了1008陈飞宇的数据
来源表1-User_base:删除了1004刘再清的数据
来源表2-User_test:新增了1008陈飞宇的数据
来源表2-User_base:删除了1004刘再清的数据
来源表2-User_test:1001、1006的 position 和 level 的数据发生变化
2.3 整体流程
实现上述场景的 ETL 整体流程如下:
2.4 实现步骤
首先按照下图把节点拖到设计界面,给这些节点重命名,并将节点连接起来。
2.4.1 来源表1数据删除
1)双击参数赋值节点「获取来源表1的比对字段ID」,如下设置,将 id 作为参数,向后续节点传递。
用到的 SQL 语句为:
-- 获取来源表1中ID的所有值
SELECT id FROM User_base
2))双击SQL脚本节点「根据来源表1删除目标表数据」,基于上游传递下来的参数,用 not in 语法,筛选出目标表中存在,但是来源表中不存在的 id ,用 delete 语法删除目标表中对应 id 的整行数据。如下图所示:
SQL 语句为:
-- 筛除出目标表中存在但是来源表不存在的ID,将该ID对应的数据行删除
delete from User_position
where id not in (${User_base_id})
2.4.2 来源表2数据删除
1)双击参数赋值节点「获取来源表2的比对字段ID」,如下设置,将 id 作为参数,向后续节点传递。
用到的 SQL 语句如下:
-- 获取来源表2中ID的所有值
SELECT id FROM User_test
2)双击SQL脚本节点「根据来源表2删除目标表数据」,基于上游传递下来的参数,用 not in 语法,筛选出目标表中存在,但是来源表中不存在的 id ,用 delete 语法删除目标表中对应 id 的整行数据。如下图所示:
SQL 语句为:
-- 筛除出目标表中存在但是来源表不存在的ID,将该ID对应的数据行删除
delete from User_position
where id not in (${User_test_id})
2.4.3 新增和更新数据
1)双击离线同步节点「更新目标表的年龄和学位字段」,获取来源表1中的全部数据,选择比对字段 id,使用“比对字段遇到相同值,覆盖目标表数据”的更新策略。
字段映射页面,使用编辑映射功能删除目标表中不存在的字段,仅保留计划进行更新的字段。
2)双击离线同步节点「更新目标表的职位和职级字段」,获取来源表2中的全部数据,选择比对字段 id,使用“比对字段遇到相同值,覆盖目标表数据”的更新策略。
字段映射页面,使用编辑映射功能删除目标表中不存在的字段,仅保留计划进行更新的字段。
2.5 效果查看
2.2 节里面的数据变化在来源表中发生后,执行该 ETL 任务,最终目标表的结果如下图所示:
可以看到 1001 和 1006 的 position 和 level 都更新了,1004 那行数据被删除,新增了一条 1008 的数据