全表对比增量抽取

全表对比增量抽取

1 概述

在做数据仓库系统时，增量数据抽取是必不可少的环节，目前实现增量数据的方式有很多，如：时间戳、触发器，CDC，归档日志，全表删除后全量抽取以及全表对比增量抽取等，本次案例只对全表对比增量抽取讲解。

2 创建源表和目标表

-- 新建源表

create table increasea

(

  ID            VARCHAR2(30) NOT NULL,

  DATEID        VARCHAR2(50),

  DEPARTMENTID  VARCHAR2(50),

  EMPID         VARCHAR2(50),

  EMPNAME       VARCHAR2(100),

  SALARY        NUMBER(18,2),

  STATUS        VARCHAR2(50)

);

ALTER TABLE increasea ADD CONSTRAINT PKINCREASE PRIMARY KEY (ID);

 

-- 新建目标表

create table increaseb

(

  ID            VARCHAR2(30) NOT NULL,

  DATEID        VARCHAR2(50),

  DEPARTMENTID  VARCHAR2(50),

  EMPID         VARCHAR2(50),

  EMPNAME       VARCHAR2(100),

  SALARY        NUMBER(18,2),

  STATUS        VARCHAR2(50)

);

ALTER TABLE increaseb ADD CONSTRAINT PKINCREASEB PRIMARY KEY (ID);

 

3 监控源表中新增、修改、删除的数据

-- 本条SQL可以监控源表中的新增和修改的数据

SELECT * FROM INCREASEA MINUS SELECT * FROM INCREASEB;

-- 本条SQL可以监控源表中删除的数据

SELECT * FROM INCREASEB MINUS SELECT * FROM INCREASEA;

 

4 存储过程实现增量抽取

CREATE OR REPLACE PROCEDURE INCREASEETL AS

BEGIN

  -- 找出所有新增、修改的数据，然后根据ID主键删除目标表中未修改的数据，之后插入所有新增、修改的数据

  DELETE FROM INCREASEB

   WHERE ID IN

         (SELECT ID

            FROM (SELECT * FROM INCREASEA MINUS SELECT * FROM INCERASEB) a);

  COMMIT;

  INSERT INTO INCREASEB

    SELECT *

      FROM (SELECT * FROM INCREASEA MINUS SELECT * FROM INCREASEB) b;

  COMMIT;

 

  -- 找出所有源表中删除的数据，删除目标表中相关删除的数据

  DELETE FROM INCREASEB

   WHERE ID IN

         (SELECT ID

            FROM (SELECT * FROM INCREASEB MINUS SELECT * FROM INCREASEA) c);

  COMMIT;

END;

 

5 测试

1）源表新增数据

INSERT INTO INCREASEA(ID,EMPNAME) VALUES(3,'wangwu');

commit;

调用一次存储过程INCREASEETL

begin

  -- Call the procedure

  increaseetl;

end;

查看目标表

SELECT * FROM INCREASEB;

 

 

2）源表修改数据

UPDATE INCREASEA SET EMPNAME = 'ZHAOLIU' WHERE ID = '3';

COMMIT;

调用一次存储过程INCREASEETL

begin

  -- Call the procedure

  increaseetl;

end;

查看目标表

SELECT * FROM INCREASEB;

 

 

 

3）源表删除数据

DELETE FROM INCREASEA WHERE ID = '3';

COMMIT;

调用一次存储过程INCREASEETL

begin

  -- Call the procedure

  increaseetl;

end;

查看目标表

SELECT * FROM INCREASEB;

 

 

6 备注

在测试过程中，每次执行存储过程的原因是：此存储过程用于每天调度执行增量抽取，实现ETL每天定时抽取数据。

本案例适用于数据量较小的业务。

转载于:https://www.cnblogs.com/nbjin/p/10884396.html