一、kettle介绍

  Kettle 是一款国外开源的 ETL 工具,纯 Java 编写,绿色无需安装,数据抽取高效稳定(数据迁移工具)。Kettle 中有两种脚本文件,transformation 和 job,transformation 完成针对数据的基础转换,job 则完成整个工作流的控制。

二、ETL介绍

  ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程,目的是将企业中的分散、零乱、标准不统一的数据整合到一起,为企业的决策提供分析依据, ETL是BI(商业智能)项目重要的一个环节。

三、ETL实现细说

  其实实现ETL功能的工具很多,我熟悉并使用过的:Informatica PowerCenter、kettle、sql、PLSQL编程、python等等。

分别简单聊聊这些实现ETL的手段:

(1)、Informatica PowerCenter

  此软件是商用的,网上成熟的中文资料比较少,而且版本较旧。英文资料居多,给很多初学者带来了困扰。国内最出名的大神就是杨晓东,国内的中文资料几乎全是杨晓东分享的,资料对应的版本还停留在7.6和8.5。(我当年学的时候,自己买了书,在淘宝上淘的视频,还在杨晓东的群里打酱油,经过6个月的努力,才能上手正常工作)

(2)、kettle

  此软件是开源的,纯java编写,网上文档和视频资料特别多,有很多人在博客分享自己的案例。(从第一次接触Kettle,到灵活使用,我一共花了2天时间。这主要受益于我的技术沉淀:java编程、sql、Informatica PowerCenter的使用)

(3)、sql

  一提到sql,各位感觉只要是个干IT的人就会。你以为就是select\insert\update\delete,你就实现etl了?醒醒吧铁子,你如果这样想,你永远吃不上4个菜!!

此处提到的sql,要与你工作中的业务相结合,你得吃透业务,然后编写出来的sql。你操作完sql后,你还要验证一下,结果和你想的对不对?时效如何?如果性能不好,你还要sql调优。一说调优,有些人会说百度上找啊,文章特别多。你会发现很文章,好像是近亲,天下学文一大抄啊!而且你不懂业务,你要调优纯扯淡。

举例说明:

小明饭量很大,他一顿饭吃了一只烤鸭,小明说我刚刚吃饱了,吃饱了太幸福了。

你的饭量很小,你平时每顿就吃半个馒头的。你听了小明的说法,你也吃一只烤鸭,你肚皮都撑爆了,一张嘴烤鸭都快从嘴中吐出来了。

上面的小例子说明,不同的业务规则\数据量\访问量,对应的调优手段也不同。其实也是有一些通用的调优方法,就从sql的写法上实现的。可以来参考我的博文:点击 oracle调优笔记(揭开传言的面纱)

(4)、PLSQL编程

  PLSQL是oracle的高级编程,如果想使用PLSQL编程实现ETL,基本要求和上面第3点sql的要求很相似。另外你要熟悉PLSQL语法,并能排除PLSQL运行时产生的Exception。(我做的是ORALCE DBA,对PLSQL编程非常熟悉,所以此处提到PLSQL。大部分数据都有自己编程,只要你熟悉都可以实现ETL)。如果你对plsql编程也感兴趣,可以参考我的博文:点击 史上最简单的数据抽取

(5)、Python

  Python是一种编程语言,它里面集成了很多数学函数、工具类,都可以帮我们更加方便的实现ETL操作。如果你要操作的数据源是excel和csv,你就可以使用pandas,太方便了。

四、kettle基本概念

  (1)ktr转换:将一个或多个数据源组装成一条数据流水线,根据业务要求,利用Kettle内部的组件,进行数据处理,最后输出到某一个地方(文件或数据库)。

image.png





  (2)kjb作业:可以调度设计好的一个或多个转换,也可以执行一些文件处理(比较\删除等),还可以往ftp上传和下载文件,发送邮箱,执行shell命令等等。

image.png     

 



举例说明:

  ktr转换:ktr是根据业务编写的,它们分别实现不的操作,ktr会有多个。ktr就是建筑工地上干活的工人们,每个工人的技能不同(小工、瓦匠、钢筋工、木工、塔吊司机)

  kjb作业:kjb管理多个ktr。kjb就是包工头,包工头管理多个工人。当有任务的时候,直接找包工头说,包工头就会根据任务的要求,找不同技术的工人,来完成工作。

五、业务说明

  我们公司的业务是多源的,有一条业务线是服务于国家医保局,根据全国各地名提供的,医院HIS\PACK\LIS\财务数据,及医保局的结算数据,进行数据分析和筛查,配合业务专家进行飞行检查,找出违规收费等问题。对医院进行管控,实现就医的合理收费,医患和睦,使用最少的医保费用,干更多的事,为老百姓谋幸福。

六、工作的流程

  根据业务专家的经验,将医保中出现的各种违规收费规则,写成kettle的转换,每一个转换就是一个检查规则,这样的检查规则有好几百个,部分如下图:

 image.png






这么多的转换,不可能一个一个去运行,我把每10个转换交给1个作业来管理调用,部分截图如下:

 image.png













这样的话就方便多了,我只需要调用这几十个作业,就可以对间接的调用这几百个转换了。

七、工作的困难

  最开始只有一个地区的10家医院,我们根据地区创建数据库,把本地区的所有医院的数据保存在对应库中hive的分区表里,使用医院的医疗机构编码作为分区Key。每家医院有自己的筛查转换文件,因为转换文件中的条件会使用到各医院的医疗机构编码不同,每家医院筛查的结果保存的路径也不同。每家医院对应500个转换和50个作业文件,10家医院我共修改5000个作文件和500个作业文件,当然了是使用工具快速查找替换的。

  但是现在有多个地区,医院数也由最初的10家变成了100多家。 我还是手工去替换修改的话,我当场就去世了。现在还要修改数据库连接信息,因为多个地区,对应多个数据库。后期全国的数据都上来了,要修改的转换和作业文件,太多了。我也不用活了,直接就脑梗死了。

八、解决困难

  前面提到了每个医院都有自己的几百个筛查转换,所有医院的数据结构是不变的。能不能只保留一套通用的筛查转换文件,因为筛查规则是一样的,就是各医院的医疗机构编码、结果文件输出路径、数据库连接信息是不同的。于是想到了可以使用java调用kettle,调用kettle时传入不同的参数,在kettle中是可以使用命名参数来接收,这样就实现了程序的复用性、灵活性。

  数据库相关替换的参数有:数据库名、数据库IP、端口号、用户名、密码。

  任务相关替换的参数有:医院的医疗机构编码、作业存放的结对路径、筛查结果文件保存路径。

附上 作业和转换的 命名参数 案例,如下截图:

  (1)kjb作业 全局的命名参数

image.png

  








(2)kjb作业中调用的转换 命名参数

image.png














(3)ktr转换 全局的命名参数

image.png










(4)ktr转换 DB连接的参数

 image.png












(5)结果文件输出路径 参数

image.png










  好多年不干java开发了,现在又要动代码,想想就头疼。但工作还要继续,日子还要过。上百度查查吧,还真查到了SpringBoot调用kettle的文章。剩下的就是动手干吧,再次化身程序员。

九、项目代码结构

image.png















十、项目代码运行

(1)、启动springboot

  InsuranceETLApplication类中,右键------>Run As----->Java Application

(2)、在浏览器上输入

http://localhost:9090/kettle/task

  (3)、控制台输出打印

 image.png










从控制台输出的信息中,可以看到现在正在调用的load_violation_data_to_hive_01作业,并输出了该作业中当前转换执行的sql语句详细信息。

就写这么多吧,如果你也在搞ETL自动化,咱们可以多交流。我在开发代码时,遇到了太多的坑,一起学习一起进步!


学习累了让自己笑一下吧,告诉自己未来的生活是美好的!!!

image.png