Kettle:使用觸發(fā)器和快照表進(jìn)行增量數(shù)據(jù)同步

本文介紹了使用快照表和觸發(fā)器進(jìn)行增量數(shù)據(jù)同步让歼。主庫(kù)為Oracle 11g數(shù)據(jù)庫(kù)梳猪，針對(duì)需要同步的表建立增量數(shù)據(jù)臨時(shí)表以及觸發(fā)器并通過(guò)kettle定時(shí)同步到PostgreSQL數(shù)據(jù)庫(kù)彤钟。
1、主庫(kù)創(chuàng)建快照表和觸發(fā)器
注意：快照表結(jié)構(gòu)和源表結(jié)構(gòu)必須一致！

--創(chuàng)建插入快照表
CREATE TABLE SPWUSER.WEB_CUSTOMER_INSERT_TMP
(
  ID        NUMBER                              NOT NULL,
  NAME      VARCHAR2(128 BYTE)                  NOT NULL,
  CREATED   TIMESTAMP(6) WITH LOCAL TIME ZONE   DEFAULT (current_timestamp)   NOT NULL,
  MODIFIED  TIMESTAMP(6) WITH LOCAL TIME ZONE   DEFAULT (current_timestamp)   NOT NULL
);
--創(chuàng)建更新快照表
CREATE TABLE SPWUSER.WEB_CUSTOMER_UPDATE_TMP
(
  ID        NUMBER                              NOT NULL,
  NAME      VARCHAR2(128 BYTE)                  NOT NULL,
  CREATED   TIMESTAMP(6) WITH LOCAL TIME ZONE   DEFAULT (current_timestamp)   NOT NULL,
  MODIFIED  TIMESTAMP(6) WITH LOCAL TIME ZONE   DEFAULT (current_timestamp)   NOT NULL
);
--創(chuàng)建刪除快照表
CREATE TABLE SPWUSER.WEB_CUSTOMER_DELETE_TMP
(
  ID        NUMBER                              NOT NULL,
  NAME      VARCHAR2(128 BYTE)                  NOT NULL,
  CREATED   TIMESTAMP(6) WITH LOCAL TIME ZONE   DEFAULT (current_timestamp)   NOT NULL,
  MODIFIED  TIMESTAMP(6) WITH LOCAL TIME ZONE   DEFAULT (current_timestamp)   NOT NULL
);
--創(chuàng)建插入觸發(fā)器
CREATE OR REPLACE TRIGGER SPWUSER.WEB_CUSTOMER_INSERT_TRI
    AFTER INSERT
    ON SPWUSER.WEB_CUSTOMER
    FOR EACH ROW
BEGIN

    INSERT INTO WEB_CUSTOMER_INSERT_TMP (ID,
                                         NAME,
                                         CREATED,
                                         MODIFIED)
         VALUES (:new.id,
                 :new.name,
                 :new.created,
                 :new.modified);
END;
/
--創(chuàng)建刪除觸發(fā)器
CREATE OR REPLACE TRIGGER SPWUSER.WEB_CUSTOMER_DELETE_TRI
    AFTER DELETE
    ON SPWUSER.WEB_CUSTOMER
    FOR EACH ROW
BEGIN
    INSERT INTO WEB_CUSTOMER_DELETE_TMP (ID,
                                         NAME,
                                         CREATED,
                                         MODIFIED)
         VALUES (:old.ID,
                 :old.NAME,
                 :old.created,
                 :old.modified);
END;
/
--創(chuàng)建更新觸發(fā)器
CREATE OR REPLACE TRIGGER SPWUSER.WEB_CUSTOMER_UPDATE_TRI
    AFTER UPDATE
    ON SPWUSER.WEB_CUSTOMER
    FOR EACH ROW
BEGIN
    INSERT INTO WEB_CUSTOMER_UPDATE_TMP (ID,
                                         NAME,
                                         CREATED,
                                         MODIFIED)
         VALUES (:old.ID,
                 :old.NAME,
                 :old.created,
                 :old.modified);
END;
/

2、創(chuàng)建一個(gè)針對(duì)表增刪改的轉(zhuǎn)換
從kettle菜單中依次選擇“文件”-->“新建”-->“轉(zhuǎn)換”或者按ctrl+N快捷鍵創(chuàng)建话浇。然后依次選擇“輸入”-->“表輸入”，“輸出”-->“插入/更新”和“輸出”-->“刪除”闹究，執(zhí)行三次幔崖，如下圖所示：