kettle工作中常用的复杂实例-数据定时增量同步

需求

每两个小时源库数据到目标库，根据创建时间，更新时间进行增量同步

步骤

1.转换1，设置同步时间变量，查询同步日志表，查询上次同步成功的数据时间（表输入），将上次数据同步的时间和当前的系统时间作为系统环境变量（设置变量），方便后面的同步数据的查询
2.转换2，同步数据，查询创建时间或者更新时间大于环境变量中的上次数据同步时间的数据（表输入），插入或更新到目标表（插入/更新）
3.转换3，保存同步日志，插入一条，数据同步时间为第一步设置的本次同步时间，作为下次同步的数据时间条件。防止日志表过大，可以自定义保留条数（执行sql脚本）

建表脚本

DROP TABLE "DDW"."T_SOURCE";
CREATE TABLE "DDW"."T_SOURCE" (
  "ID" VARCHAR2(32 BYTE) NOT NULL,
  "NAME" NVARCHAR2(100),
  "BIRTH_DATE" DATE,
  "CREATOR" VARCHAR2(32 BYTE),
  "CREATED_TIME" DATE DEFAULT sysdate,
  "CREATED_UNIT" VARCHAR2(32 BYTE),
  "LAST_UPDATED_BY" VARCHAR2(32 BYTE),
  "LAST_UPDATED_TIME" DATE DEFAULT sysdate,
  "LAST_UPDATED_UNIT" VARCHAR2(32 BYTE),
  "IS_DEL" NUMBER(1,0) DEFAULT 0
)
TABLESPACE "SPRINGBOOT"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 8192 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "DDW"."T_SOURCE"."NAME" IS '名称';
COMMENT ON COLUMN "DDW"."T_SOURCE"."BIRTH_DATE" IS '出生日期';
COMMENT ON TABLE "DDW"."T_SOURCE" IS '源表';
ALTER TABLE "DDW"."T_SOURCE" ADD CONSTRAINT "PK_T_SOURCE" PRIMARY KEY ("ID");

DROP TABLE "DDW"."T_SOURCE_TARGET";
CREATE TABLE "DDW"."T_SOURCE_TARGET" (
  "ID" VARCHAR2(32 BYTE) NOT NULL,
  "NAME" NVARCHAR2(100),
  "BIRTH_DATE" DATE,
  "CREATOR" VARCHAR2(32 BYTE),
  "CREATED_TIME" DATE DEFAULT sysdate,
  "CREATED_UNIT" VARCHAR2(32 BYTE),
  "LAST_UPDATED_BY" VARCHAR2(32 BYTE),
  "LAST_UPDATED_TIME" DATE DEFAULT sysdate,
  "LAST_UPDATED_UNIT" VARCHAR2(32 BYTE),
  "IS_DEL" NUMBER(1,0) DEFAULT 0
)
TABLESPACE "SPRINGBOOT"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 8192 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "DDW"."T_SOURCE_TARGET"."NAME" IS '名称';
COMMENT ON COLUMN "DDW"."T_SOURCE_TARGET"."BIRTH_DATE" IS '出生日期';
COMMENT ON TABLE "DDW"."T_SOURCE_TARGET" IS '目标表';
ALTER TABLE "DDW"."T_SOURCE_TARGET" ADD CONSTRAINT "PK_T_SOURCE_TARGET" PRIMARY KEY ("ID");

--创建同步日志表的序列
create sequence SEQ_TBRZ
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20;

create table "DDW"."T_TBRZ"
(
  id     NUMBER,
  sjtbsj DATE,  
  zxsj DATE,
  bm     VARCHAR2(100),
  tbjg   CHAR(1)
)

TABLESPACE "SPRINGBOOT"
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 8192 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "DDW"."T_TBRZ"."id" IS '主键';
COMMENT ON COLUMN "DDW"."T_TBRZ"."sjtbsj" IS '数据同步时间';
COMMENT ON COLUMN "DDW"."T_TBRZ"."zxsj" IS '执行时间';
COMMENT ON COLUMN "DDW"."T_TBRZ"."bm" IS '同步的表名';
COMMENT ON COLUMN "DDW"."T_TBRZ"."tbjg" IS '同步结果：1-成功；2-未成功';
COMMENT ON TABLE "DDW"."T_TBRZ" IS '同步日志表';

数据同步作业

包含三个转换

设置同步时间变量

SELECT
nvl(max(SJTBSJ-1/12),sysdate-1/12) as V_SJTBSJ,sysdate as V_BCSJTBSJ
FROM DDW.T_TBRZ
where bm = 'T_SOURCE' and tbjg='1'

查询同步数据

SELECT
*
FROM DDW.T_SOURCE
where 
CREATED_TIME > TO_DATE(SUBSTR('${V_SJTBSJ}', 0, 19), 'yyyy-mm-dd hh24:mi:ss')
or
LAST_UPDATED_TIME > TO_DATE(SUBSTR('${V_SJTBSJ}', 0, 19), 'yyyy-mm-dd hh24:mi:ss')

保存同步日志

insert into DDW.T_TBRZ(ID,SJTBSJ,ZXSJ,BM,TBJG) VALUES(SEQ_TBRZ.NEXTVAL,
TO_DATE(SUBSTR('${V_BCSJTBSJ}', 0, 19), 'yyyy-mm-dd hh24:mi:ss'),
SYSDATE,
'T_SOURCE',
'1'
);


DELETE 
FROM
	DDW.T_TBRZ
WHERE
	TBJG = '1' 
	AND BM = 'T_SOURCE' 
	AND id IN (
	SELECT
		id 
	FROM
		( SELECT ID, ROWNUM RN FROM ( SELECT ID FROM DDW.T_TBRZ WHERE TBJG = '1' AND BM = 'T_SOURCE' ORDER BY ID DESC ) ) 
	WHERE
	rn > 10 
	);
commit;