kettle 同步Oracle 与 Postgres

环境: PDI 8.2  ,windows, oracle 12C,  postgres 12

整个过程性能很好，10分钟+1分钟完成。接下是动态更新任务。

所使用的时间戳字段必须是按时间入库的字段，不能是业务数据的时间戳字段。比如入库时间是递增的，业务里面的某一个时间戳字段不一定就是递增的。

在spoon上，建立一个数据库连接postgres_150，并共享出来。
建议使用jndi的方式，下面更新后，需要重启spoon（这是它的缺点）
simple-jndi/jdbc.properties的设置如下：

postgres_150/type=javax.sql.DataSource
postgres_150/driver=org.postgresql.Driver
postgres_150/url=jdbc:postgresql://ip:ports/yourdatabase
postgres_150/user=username
postgres_150/password=****

1.建立时间中间表

create table D_BZDZ_MLP_TIMES
(
  id           NUMERIC not null,
  last_load    TIMESTAMP(6),
  current_load TIMESTAMP(6)
)

插入初始化的数据

INSERT INTO topology.d_bzdz_mlp_times(
	id, last_load, current_load)
	VALUES (1, to_timestamp('1971-01-01 01:01:01','YYYY-MM-DD HH24:MI:SS'),
to_timestamp('1971-02-02 01:01:01','YYYY-MM-DD HH24:MI:SS') );

# 或者
INSERT INTO topology.d_bzdz_mlp_times(
	id, last_load, current_load)
	VALUES (1, '1971-01-01 01:01:01'::timestamp,'1971-01-01 01:01:01'::timestamp);

2..先把数据一次性从Oralce导入postgres，采用表输入和表输出
在表输出中通过SQL建立表，添加gemo字段，注意Oracle与postgres数据类型的不一样。

如果目标表和源表的字段类型不一致，需要在select * ^语句中转换，比如to_number("string")把字符串转成数字

create table D_BZDZ_MLP_new
(
  systemid        VARCHAR(50),
  sssqcjwhdm      VARCHAR(13),
  ssjlxdm         VARCHAR(20),
  sspcsdm         VARCHAR(12),
  dzxxd           TEXT,
...........       ......
  zxjd            NUMERIC(30,20),
  zxwd            NUMERIC(30,20),
  geom            GEOMETRY(Point,4326),
  zxzt            TEXT,
  smzt            TEXT,
  sffw            TEXT,
  uuid            VARCHAR(50),
  cccjsj          TIMESTAMP(6),
  lastupdatedtime TIMESTAMP(6)
)

Postgres中对表建立索引
不建立索引的话，后面的插入/更新转换步骤会非常慢。
因为插入/更新都需要进行select操作（这里是select systemid ***），再决定是插入还是更新。

create index d_bzdz_mlp_idx_sysid on d_bzdz_mlp(systemid);

 

然后建立一个转换，从Oralce中输入，输出到Postgres，一次性批量输出数据。

具体步骤包括：获取系统时间、更新时间中间表、获取时间中间表数据、查询数据、表输出。
记下上面同步完毕的时间，对时间中间表进行更新：

update topology.D_MLP_TIMES set last_load = current_load where id=1;

3.建立四个转换，用一个作业把这四个转换运转起来。
3.1时间同步转换
从systemdate 获取当前时间，插入更新时间中间表的 当前时间
3.2 数据同步转换。
按照时间中间表，从oracle中查询变化的数据

SELECT
  LAST_LOAD last_load
, CURRENT_LOAD current_load
FROM topology.D_BZDZ_MLP_TIMES

last_load 和 current_load 作为参数传入下面的查询

SELECT
  systemid,
 .....
  zxjd,
  zxwd,
......
  cccjsj,
  lastupdatedtime
FROM LG.D_MLP 
WHERE (x > 112.916 and x <114.082 and y>22.526 and y>24.005) AND lastupdatedtime >= ? AND lastupdatedtime < ?

把上面表输入的数据，插入/更新到postgres
以systemid作为查询的关键字

3.3 时间中间表同步转换
这是一个SQL脚本转换

update topology.D_BZDZ_MLP_TIMES set last_load = current_load where id=1;

3.4 更新geom字段

update table set geom=ST_SetSRID(st_point(x,y),4326) where geom is null ;