Kettle (二）实战案例 (1) 关系型数据库之间的数据同步

Kettle (二）实战案例 (1) 关系型数据库之间的数据同步 

操作前的准备：建库建表

auto_increment=1 这个是修饰主键primary key，自动增长1

--================================
-- 日志库
--================================
create database etl;
use etl;
alter table control_table auto_increment = 1;

drop table control_table;
create table control_table
(id int primary key auto_increment,
task_name varchar(255),
run_flag int,
created_at datetime,
updated_at datetime
)
;

drop table task_log;
create table task_log
(id int  primary key  auto_increment,
task_name varchar(50),
status varchar(50),
log_time datetime
)
;

--================================
-- 测试数据 
--================================

use test;
drop table order_detail;
create table order_detail
(
id bigint,
user_id bigint,
product_id bigint,
status  varchar(50),
amount  double,
created_at datetime,
updated_at datetime
)
;

insert into order_detail
select 1,
       123,
       10001,
       'new',
       100.2,
       now(),
       now();
commit;
  
--step2 
insert into order_detail
select 2,
       222,
       10001,
       'new',
       200,
       now(),
       now();

update order_detail
set status = 'success'
where id = 1;
commit;

select * from order_detail;

create database ods;

create table order_detail
(
id bigint,
user_id bigint,
product_id bigint,
status  varchar(50),
amount  double,
created_at datetime,
updated_at datetime
)
;

use etl;
insert into control_table(task_name,run_flag,created_at,updated_at)
select  'order_detail',1,now(),now()
from dual;

insert into control_table(task_name,run_flag,created_at,updated_at)
select  'customer',0,now(),now()
from dual;
commit;

select * from control_table;
commit;

select task_name
from control_table 
where run_flag = 1
;

--=====================================
-- mysql 表
--=====================================
use test;
drop table customer;
create table customer
(
user_id bigint,
user_name varchar(50),
created_at datetime,
updated_at datetime
)
;

drop table product;
create table product
(
id         bigint,
product_id bigint,
product_name varchar(50),
created_at datetime,
updated_at datetime
)
;
insert into product
select 1,1,'name1',now(),now();
commit;

drop table product_info;
create table product_info
(
product_id bigint,
product_name varchar(50),
created_at datetime,
updated_at datetime
)
;

--===================================
delete from etl.control_table where  task_name = 'customer';
insert into etl.control_table(task_name,run_flag,created_at,updated_at)
select  'customer',1,now(),now()
from dual;

delete from etl.control_table where  task_name = 'product';
insert into etl.control_table(task_name,run_flag,created_at,updated_at)
select  'product',1,now(),now()
from dual;

delete from etl.control_table where  task_name = 'product_info';
insert into etl.control_table(task_name,run_flag,created_at,updated_at)
select  'product_info',1,now(),now()
from dual;
commit ;

select * from etl.control_table;

关系型数据库间的同步

开始-->初始化变量-->写日志--加载数据到关系型数据库中（数据的转换和同步），

如果转换同步失败就会重试（先判断重试次数，然后等待15秒再继续尝试执行），

如果转换同步成功，就会写日志然后更新元数据信息，最后同步转换数据成功。

该过程实现的是SQL数据库表  test.order_detail  到SQL数据库表 ods.order_detail 之间的转换。

详细解释：

1、转换: INIT_PARAMETERS (初始化参数)                                           

查看 test.order_detail 表结构和原始数据:

(1) Table input

SQL语句:

TABLE_NAME 列值为'order_detail',  IN_STST_DATE列值为当前时间再减去400个小时

(-400是因为后面从test.order_detail表中抽取的时间需要满足:updated_at >= '${IN_STAT_DATE}',

   也就是说新获取的时间数据要小些)

这个时候还没用上MySQL的表. 仅是一个查询,

因为配置文件 D:\Kettle7.1\data-integration\simple-jndi\jdbc.properties 中默认连接的是test数据库：

　　MYSQL_RW/url=jdbc:mysql://192.168.212.50:3306/test

TABLE_NAME变量在这里被初始化值为: test.order_detail

IN_STAT_DATE变量也会被赋予初始值de

SELECT 'order_detail' AS TABLE_NAME,
              date_add(now(), interval -400 hour) AS IN_STAT_DATE
FROM DUAL

预览得到数据样例:

(2) Set Variables--设置环境变量

2、LogStart                                                                                              

按照原始表结构创建SQL脚本,目的是将新的数据插入到 etl.task_log 表中,用来记录转换过程

查看 原始表结构和表数据:

INSERT INTO etl.task_log
(TASK_NAME,
STATUS,
LOG_TIME
)
SELECT '${TABLE_NAME}',
              '${STATUS}',
              NOW()
FROM DUAL

设置命名参数:TABLE_NAME、STATUS

这里的TABLE_NAME的值就是之前参数初始化的值 test.order_detail 

3、执行数据的转换和同步 Load Increament Data To Mysql                        

(1) Table input

SELECT  *
FROM  ${TABLE_NAME}
WHERE updated_at >= '${IN_STAT_DATE}'

注意: 要选上下面框中的两项

(2) Table output

将数据同步到 ods.order_detail 表中.

这里的ods是目标数据库的名称

 4、Simple evaluation                                                                              

如果加载失败，检验变量－RETRY_TIMES(默认值为0), 判断值是否小于１.

如果是，就走左侧的等待15秒

然后再设置环境变量 RETRY_TIMES为1

DUMY：没有实际意义，就是为了排版好看

如果失败，就走右侧的结束日志-LogEnd2

它的SQL和LogStart相同,命名参数SUCCESS则变成Fail

最后终止作业(Abort job)

5、如果加载成功                                                                              

走DUMMY(没有实际的意义,只是排版美观考虑)、LogEnd(SQl和LogStart一样, 参数STATUS变为SUCCESS)、update metadata(更新元数据).

更新成功的话就返回success,失败的话就终止作业.

6、查看三个表变化后的数据