kettle 增量式更新方案 (敬请指正)
本文利用kettle来实现oracle数据库的有删除,有增加,有更新的增量式同步
一、工作原理
分成2个独立的transformation ,一个负责 insert/update,另一个只负责delete。然后在一个job里,将二者结合。
insert/update 增量同步原理:采用的是“时间戳”的方式,来从源表中过滤出需要增量更新的数据,然后利用kettle的”insert/update” 本身的功能自动判断是“insert” 还是 ”update”不需要人工介入;另外,对于“update“操作,需要建立个”before update” 触发器,来更新时间戳字段,来表明未被同步的update操作。
delete操作的增量同步原理:使用一个中间表来记录删除的源表”主键“,“主键”数据通过在源表建立“after delete” 触发器写入。该表作为“目标表”删除数据的判断依据,当目标表删除完成后,将中间表数据清空;
二、实施
0.准备工作:
在源和目的端分别创建测试表ggtest1.t1_1
create table ggtest1.t1_1
(pid number primary key ,
name varchar2(10 char),
age number)
//注意,源表必须限定主键不能被更新(如果源表无主键,应该增加代理主键)
1.insert/update 同步方案:
(1) 在源表和目的表均增加时间戳字段。
alter table ggtest1.t1_1 add (moddate timestamp(6) default current_timestamp);
并在该字段上创建索引:
create Index IDX_t1_1_moddate on ggtest1.t1_1(moddate);
(2)在源表上创建before update触发器,用于update时候对时间戳进行更新
create or replace trigger ggtest1.tr_ggtest1_t1_1_moddate
before update on ggtest1.t1_1
referencing old as old new as new
for each row
begin
:new.moddate := current_timestamp;
end;
;
/
(3)在Kette中配置Transformation
效果如下:
配置如下:
(3.1)
新建一个“Transformation“;
创建一个“Table input” 命名为-获得目标表最大修改时间(作为源表数据过滤条件)
解释:
select
nvl(max(moddate),to_date('1000/01/01','yyyy/mm/dd'))
from ggtest1.t1_1
如果目的表是空表,就需要nvl函数,将null转换为一个很早的时间,可以保证源表所有数据一次初始化到目标表,这样兼容目表表是空表的情况。
(3.2) 创建第二个“Table input” 命名为—源表输入:
解释:
SQL: SELECT *
FROM GGTEST1.T1_1 where
MODDATE > ?
“?” : 用于替换“获得目标表最大修改时间”中sql返回的值。
: 开启第一步的sql返回值替换“?”
: 如果第一步返回多个值,就多次替代。(这里就一个值)
(3.3) 创建一个“Insert/update” 步骤:
解释:
PID = PID . 前者是源表PID,后者是目的表PID。用于源表和目的表的行对应。
Update fields: 是说更新目的表时候,哪些列更新,哪些不更新。 主键PID 不要更新(选 N)。
该“Insert/Update”功能,会自动判断是”insert“还是”update”。如果找不到对应的PID,就插入,
如果找到了,就比较各列,如果值不同就更新。
------------------------------------------------------------------------------------------------------------------
2. Delete 操作同步配置:
效果:
配置步骤:
(2.1)在源表所在数据库中创建一个存放源表主键的中间表
create table ggtest1.t1_1_del(pid number);
(2.2)在源表上创建delete 触发器(每当delete时候,填充上面的表)
CREATE OR REPLACE TRIGGER ggtest1.TR_GGTEST1_T1_1_del
after delete
ON GGTEST1.T1_1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
insert into ggtest1.t1_1_del (pid) values(:OLD.pid);
end;
;
/
(2.3) Kettle中
新建一个“Transformation“;
创建一个”Table input” 步骤命名为:中间表输入:
(2.4) 创建“delete”步骤
本步骤,利用(2.3)获得的PID值,删除目的表数据;
-------------------------------------------------------------------------------------------------------------------------
3. 最后组合两个Transformation为一个JOB
创建一个JOB:
解释:
“删除已经删除的”: 关联配置的delete Transformation。
“删除中间表数据”:是一个Scripting->SQL类型的Kettle步骤,配置如下:
“插入/更新”:关联“Insert/Update” 配置的Transformation。
--------------------------------------------------------------------------------------------------------------
4.同步的时候如何做:
(1) 清空目标表数据。
(2) 执行Kettle JOB 。
-完-