博客

1、ETL及其常用工具

ETL：Extract-Transform-Load的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。ETL是将业务系统的数据经过抽取、清洗转换后加载到数据仓库的过程。



ETL详细介绍：https://www.cnblogs.com/yjd_hycf_space/p/7772722.html

ETL常用工具：


1、informatica、Datastage


information专业的ETL工具，收费，价格比Datastage便宜一点，适合大规模的ETL项目，Datastage是IBM公司的ETL工具，也要收费，适合大规模的ETL项目，这两个都是分客户端和服务端的，因为收费速度比较快，比较稳定

2、Talend


java编写的，分为开源版和企业版，按照以后也可以界面化操作，不过只能job，没有transform，说实话没用过，只是看了下，感觉适合大型项目，操作有点复杂

3、Kettle


​ kettle是基于Java的ETL工具，只需要jvm环境就可以使用，并且是免费开源的，可跨平台，扩展性比较好，提供界面化操作，操作还比较简单

2、kettle的下载安装

2.1 kettle简介

kettle

家族目前包括4个产品：Spoon Pan Chef Kitchen


Spoon

：kettle的界面化操作，可以通过图形界面来操作ETL的转换过程


Pan

：允许批量运行由Spoon设计的ETL转换，Pan是一个后台执行的程序，没有图形界面


Chef

：允许创建任务(job)，任务通过允许每个转换，任务，脚本等等，更有利于自动化更新数据仓库的复杂工作。任务通过允许每个转换，任务，脚本等等，任务将会被检查，看是否正确的执行。


Kitchen

：允许批量使用由Chef设计的任务，Kitchen是一个后台运行的程序

2.2 kettle下载安装

下载地址：

https://sourceforge.net/projects/pentaho/files/Data Integration/


kettle绿色安装，下载kettle的zip包，解压即可使用，下面就是解压以后的安装目录，其中lib文件夹中放的都是jar包，如果kettle要链接mysql配置，需要将数据库连接驱动包放到这里，要引用自定义java代码jar包也放到这里


Windows系统直接运行Spoon.bat文件，就可以打开运行spoon可视化界面，通过这个图形界面可以进行你想做的ETL操作。界面如下：


kettle的控件有2种，分为：作业（job）和转换（Transform)

转换：主要是针对数据的各种处理，一个转换可以包含多个步骤（Step)，我目前的操作都是根据转换完成的。

作业：更加关心更为宏观的数据处理，比如文件和目录操作等等。一个作业可以包含多个作业项（Job Entry）。作业和转换均可作为一个作业项，也就是说，一个作业可以包含多个子作业和转换。一个作业中的作业项之间是顺序执行的。对于一个作业项来说，只有当该作业项之前的所有作业项执行完毕后，才会执行该作业项。

3、kettle的使用

3.1 kettle之转换的基本概念

新建了一个转换后会有主对象树和核心对象这两个

主对象树：

• DB连接是数据库的连接，这里我新建了一个mysql的连接命名为user
• Steps(步骤)：是这个转换的步骤，我在这里创建了三个步骤，表输入 / java代码 / 插入更新
• Hops(节点连接)：是每个步骤之间的连接，创建连接以后是一个串行的结构，执行了表输入拿到数据 —-> 在执行java代码，对数据进行处理 —–> 处理完以后执行更新数据步骤

这张表中展示的是所有的核心对象中的控件，上面的步骤都是一个个控件，表输入是输入控件中的，java代码是脚本控件，插入/更新是输出控件。

3.2 输入控件的使用

上面是所有的输入控件中包含的步骤，可以进行csv文件和Excel文件的输入等等，功能还是很强大

简单案例：输入一个csv文件

选择文件 ——> 新建 ——–> 转换 ——-> ctrl+s保存重命名

选择 ——-> 核心对象 ——-> 输入 ——–> 双击csv文件输入控件，在右边就会出现csv文件输入的步骤

• 1、选择csv文件输入右键选中编辑步骤，弹出CSV文件输入弹窗
• 2、浏览选择要输入的文件
• 3、选择文件后获取字段会自动识别文件中的字段填充到上图的表中
• 4、点击预览，预览文件中指定行数的数据
• 5、点击确定保存数据

3.3 输出控件的使用

以上是输出控件中包含的，可以输出excel文件，输出表，插入/更新表，输出配置文件都可以

输出步骤必须在输入步骤后面，因为一定要有输入流以后才能输出数据，否则不能输出数据

简单案例：输入一个csv文件后，输出Excel文件 ，输入步骤参考上面

• 点击输出 ——> 双击Excel输出 ——> 右边出现Excel输出步骤后
• 按住shift键，鼠标右键建立csv文件输入和Excel输出的节点连接，选择主输出步骤
• 右键Excel输出，选择编辑步骤 ——–> 浏览指定输出文件目录 ——-> 点击确认保存
• 点击运行会执行这个转换，如果报错会在日志中输出报错信息

3.4 脚本控件的使用

脚本控件可以写java代码sql脚本还有JavaScript代码，我常用的就是java脚本

简单案例：添加java脚本对数据进行转换，这里也是需要先使用输入控件获取数据，然后java脚本对数据进行处理，我直接讲java脚本处理，就省略掉前面输入的步骤

• 点击脚本 ——> 双击java代码 ——-> 选中java代码右键选择编辑步骤
• 点击展开Code Snippits ——> 点击展开Common use ——> 点击Main，出现右边的代码块
• 这是一个processRow()方法，在这里面根据模板添加自己的java代码，处理数据，上面的框框就是我自定义的java代码，通过code字段是否为空，给newcode字段赋值
• 下面是在数据流中新建了一个字段newcode，String类型，上面的代码就是给newcode的赋值
• 完成代码后点击测试类，如果弹出上面的弹窗说明代码没问题，可以选择预览数据，如果报错会弹出报错信息

3.5 案例1

根据我们时间的因为需求，将tb_product_cash_sale_sku新增三个未税价格字段，并且根据不同的公司现在是含税价还是不含税价，及其税率，计算出另外三个价格

思路：1、在表中新建三个不含税字段，创建输入表控件，将数据输入到流中

​ 2、创建java脚本，在数据流中新建含税价和未税价共6个字段，然后用java代码根据公司重新计算每一个价格并赋值给数据流中新建的价格字段

​ 3、创建插入/更新步骤，将数据库中的价格字段重新对应数据流中新建的价格字段进行更新操作

步骤：

• 1、新建转换以后， 创建数据库连接

现在对表操作，新建完转换以后要创建数据库连接，选择mysql连接，主机名称是mysql的主机IP，数据库名称是要连接的数据库的名称，下面就是mysql的端口，用户名和密码，连接方式使用的是jdbc连接池，这里需要注意的是连接mysql之前要在kettle的lib文件夹下放入mysql连接驱动jar包，点击测试会告知是否连接成功，以及连接失败的报错原因

选择选项可以配置其他数据库连接参数，这些参数可以影响读取和写入表的速度

• 2、创建输入表步骤，在编辑步骤中获取tb_product_cash_sale_sku数据

点击获取SQL查询语句选择要输入的表，这个sql是自动填充的，如果要连表查询，这里可以写自定义sql获取数据，框框中的三个字段是新增的未税价格，后期存储的就是未税的价格

• 3、创建java脚本，并且创建输入表和java脚本直接的节点连接

  

  在数据流中新建6个字段，重新保存价格，上面的java代码是根据公司不同计算了每个公司的6种价格，并赋值给数据流中新创建的这几个字段，上面是未税价格，下面是对应的含税价

• 4、创建插入/更新步骤

  

• 目标表：选择要修改的表

• 提交记录数据：是每次修改提交的数据，默认值是1000，因为这张表数据有4万多，我设置5万，一次性提交

• 用来查询的关键字：是根据那个字段锁定数据并更新数据，这里选定的是id

• 流字段：是输入流中的字段，也包含java代码中新建的字段都是数据流中的字段

• 表字段：是目标表中的字段

我只修改了价格字段，所以上面其他字段都没变，值是将6个价格字段重新赋值更新为，我在java代码那里新建的6个数据流字段