ETL算法--拉链表算法以及Kettle工具实现

最新推荐文章于 2025-06-27 19:30:44 发布

原创最新推荐文章于 2025-06-27 19:30:44 发布 · 2.9k 阅读

13 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一种利用Kettle工具实现数据拉链表的方法，包括增量数据的获取、历史记录的更新及新数据的插入等关键步骤，并提供了具体的SQL语句示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

用处：能够保存每日的余额，方便历史查询，对于数据条数较多，每日变化不多的情况非常实用。

看具体例子【转】

OD(在第一天就等于HIS)
用户标志      状态 开始时间   结束时间
1             1    200712   299901
2             2    200712   299901
3             3    200712   299901
4             4    200712   299901
5             5    200712   299901

ND
用户标志     状态  开始时间     结束时间
1             2    200801      299901
2             2    200801      299901
3             4    200801      299901
4             4    200801      299901
5             6    200801      299901

W_I=ND-OD  (  将W_I表的内容全部插入到历史表中，这些是新增记录 )
用户标志    状态   开始时间     结束时间
1             2   200801        299901
3             4   200801        299901
5             6   200801        299901

W_U=OD-ND ( 对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天 )
用户标志    状态  开始时间       结束时间
1            1   200712          299901
3            3   200712          299901
5            5   200712          299901

INSERT操作把I插入到HIS
用户标志   状态   开始时间        结束时间
1            1   200712           299901
2            2   200712           299901
3            3   200712           299901
4            4   200712           299901
5            5   200712           299901
1            2   200801           299901  --new
3            4   200801           299901  --new
5            6   200801           299901  --new
</span>
update操作按U更新HIS
用户标志       状态 开始时间 结束时间
1               1   200712  200801 --change
2               2   200712  299901  
3               3   200712  200801 --change
4               4   200712  299901
5               5   200712  200801 --change
1               2   200801  299901
3               4   200801  299901
5               6   200801  299901

下面为具体的KETTLE实现方式：

1、历史数据（第一次即为前一日的数据，如20180101）入库操作，将数据源导入oracle库，目标表为：2018_HIS

2、当日数据如（20180102）入库操作，目标表为：2018_NEW

3、获取当日增量数据（余额有更新或者新增的账号），存到临时表：2018_CHG

具体sql如下：

--查找状态发生变化的数据、增量数据

INSERT INTO 2018_CHG 
	SELECT
		T2.* 
	FROM
		2018_HIS T1
		LEFT JOIN 2018_NEW T2 ON T1.ID = T2.ID 
	WHERE
		T1.end_date = '299901' 
		AND T1.STATUS <> T2.STATUS
UNION ALL
	SELECT
		T2.* 
	FROM
		2018_NEW T2 
	WHERE
		NOT EXISTS ( SELECT 1 FROM 2018_HIS T1 WHERE T1.ID = T2.ID )

4、封链操作（即将历史表中状态有变化的结束时间更新为当前日期，称为失效记录）

具体sql如下

UPDATE 2018_HIS
SET 2018_HIS.END_DATE = 
(SELECT 2018_CHG.FSRQ FROM 2018_CHG WHERE 2018_HIS.ID = 2018_CHG.ID)
WHERE 2018_HIS.ID IN (SELECT ID FROM 2018_CHG) AND 
2018_HIS.END_DATE = '99991231'


【20190627更新为以下写法】
UPDATE 2018_HIS T1 
SET T1.end_date = IFNULL( ( SELECT T2.start_date FROM 2018_CHG T2 WHERE T2.ID = T1.ID ), '299901' ) -- 这里不明白为什么会出现NULL？？
WHERE
	T1.end_date = '299901'


-- 如果用(SELECT T2.start_date  FROM 2018_CHG T2 RIGHT JOIN 2018_HIS T1 ON T2.ID = T1.ID)就可以理解，但是会报错
-- You can't specify target table '2018_HIS' for update in FROM clause

5、插入操作（将变化表直接插入到历史表当中）

具体sql如下：

INSERT INTO
2018_HIS 
SELECT * FROM 2018_CHG

6、清理临时表和变化表，以备下次新数据继续使用

几点说明：

（一）具体的KETTLE例子和转的例子操作过程最后两步骤有点不同，可以参考KETTLE例子为准

（二）建表时候发生END_DATE默认都设置为‘99991218’（oracle中设置），FSRQ在导入数据时候新增文件日期列（KETTLE中入库可实现）

最后附上KETTLE的下载网址，免费开源软件：

https://sourceforge.net/projects/pentaho/files/Data%20Integration/7.0/pdi-ce-7.0.0.0-25.zip/download

使用过程中可能出现的问题解决方法

https://www.cnblogs.com/espooky/p/6007326.html 闪退解决方法
https://www.jianshu.com/p/8930ab9af827 闪退解决方法
http://blog.youkuaiyun.com/xyj0808xyj/article/details/46976113?locationNum=13 TXT文本导入
http://download.youkuaiyun.com/download/muyandong/207249

http://blog.sina.com.cn/s/blog_7e04e0d00101k53d.html 无法连接数据库

附上一张本人KETTLE拉链算法流程图：