反馈已提交

网络繁忙

当前为10.0版本文档,只有最新版本的文档支持在线编辑修改,如果想创建/编辑文档,请移步至 最新版帮助文档

更新目标表部分字段

  • 文档创建者:Leo.Tsai
  • 编辑次数:1次
  • 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 作为参数,向后续节点传递。

    1636093390293016.png

    用到的 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 的数据


    附件列表


    主题: ETL作业
    已经是第一篇
    • 有帮助
    • 没帮助
    • 只是浏览

    售前咨询电话

    400-811-8890转1

    在线技术支持

    请前往「服务平台」,选择「在线支持」

    热线电话:400-811-8890转2

    总裁办24H投诉

    热线电话:173-1278-1526