主页:​小王叔叔的博客​​欢迎来访

支持:点赞Kettle Spoon应用_MySQL收藏Kettle Spoon应用_搜索引擎_02关注Kettle Spoon应用_搜索引擎_03

【搜索引擎】安装

一、效果,使用

Kettle Spoon应用_微服务_04Kettle Spoon应用_搜索引擎_05​         Kettle Spoon应用_搜索引擎_06Kettle Spoon应用_MySQL_07

二、Kettle Spoon 简介

kettle 是纯 java 开发,开源的 ETL工具,用于数据库间数据迁移,开源的ETL工具,ETL是Extract-Transform-Load的缩写,即数据抽取、转换、装载的过程.

kettle有四大核心组件: chef(工作设计工具), kitchen(工作执行器), spoon(转换设计工具), pan(转换执行器)

Kettle Spoon应用_MySQL_08Kettle Spoon应用_MySQL_09Kettle Spoon应用_搜索引擎_10

Kettle Spoon应用_搜索引擎_11

自己生成的案例:

Kettle Spoon应用_微服务_12

Kettle Spoon应用_MySQL_13

三、【官网下载】工具下载

官网 :​​http://kettle.pentaho.org/ ​

下载的最新版本的kettle是:​​pdi-ce-7.1.0.0-12​

官方入门文档 :​​Pentaho Community Wiki​

四、Kettle Spoon的安装

备注:我用的是公司领导分享的文件,逐步安装和学习使用

4.1) 解压下载的文件夹

4.2) Windows10本地配置开发必备Java环境变量【百度 Java环境变量即可】

4.3)以上准备好后,双加/spoon/spoon.bat启动,欢迎页如图:Kettle Spoon应用_搜索引擎_14

Kettle Spoon应用_搜索引擎_15Kettle Spoon应用_MySQL_16          Kettle Spoon应用_MySQL_17Kettle Spoon应用_大数据_18

由于Kettle中有两种脚本文件,transformation(交换)和job(作业):transformation完成针对数据的基础转换,job则完成整个工作流的控制

创建成功后,通过下图进行连接MySQL,如图:

Kettle Spoon应用_搜索引擎_19Kettle Spoon应用_MySQL_20

安装,关联成功后,进行“转换”,“加载”,“作业”的使用:

五、开始使用

5.1)转换

首先点击“和新对象 - 输入 - 表输入”,进行创建一个目标表的属性,如图:

Kettle Spoon应用_kettle_21Kettle Spoon应用_微服务_22   Kettle Spoon应用_搜索引擎_23

创建成功后,双击“表输入”,进行选择,如图:

Kettle Spoon应用_微服务_24Kettle Spoon应用_MySQL_25

选择完成之后,点击左侧“插入/更新”,进行将目标表的数据插入/更新到右侧区域,如图:

原则是:插入:目标表没有id   更新:目标表有id

摁住shift键不放,鼠标点击表输入,然后拖向旁边的插入/更新,这样就把两个动作连接起来了。

Kettle Spoon应用_微服务_26Kettle Spoon应用_微服务_27​     Kettle Spoon应用_大数据_28

Kettle Spoon应用_MySQL_29          Kettle Spoon应用_MySQL_30Kettle Spoon应用_大数据_31

创建完成之后点击“转换”按钮,进行转换,如图:

Kettle Spoon应用_kettle_32

Kettle Spoon应用_kettle_33

运行之后 ,显示日志,Metrics,Preview data,如图:

Kettle Spoon应用_kettle_34Kettle Spoon应用_kettle_35    Kettle Spoon应用_搜索引擎_36 Kettle Spoon应用_kettle_37Kettle Spoon应用_kettle_38

转换成功后,系统生成***.ktr文件。

5.2)作业

选择“文件- 新建-作业”,生成一个作业业务,如图:

Kettle Spoon应用_MySQL_39

创建成功后,分别通过以下功能进行“作业”,如图:

Kettle Spoon应用_kettle_40

首先,选择“Start”,进行创建“定时调度”,设置时间间隔,重复频率,如图:

我的设置为了看当前设置效果,业务中具体问题具体分析。

Kettle Spoon应用_kettle_41

设置完成之后,进行确定保存,然后 双击“转换”,进行选择生成的ktr文件,如图:

Kettle Spoon应用_MySQL_42

设置成功后,进行执行“作业”,如图:

Kettle Spoon应用_kettle_43

这样就是执行成功,如图,一定设置好ktr生成文件的关键步骤。

Kettle Spoon应用_微服务_44Kettle Spoon应用_微服务_45       Kettle Spoon应用_MySQL_46Kettle Spoon应用_大数据_47

以上就是Spoon 的初步使用,后期根据工作情况,适当更新。

后期将更新一个【​Kettle Spoon 抽取MySQL数据 保存到ES​】的博客,这个重点。

​spoon新手入门教程​

​spoon mysql教程_kettle 教程​

​kettle之MySQL表转换案例 ​


【Java】抽MySQL数据

一、实现的具体步骤及效果

1.1 首先通过es-head创建es的查询索引

1.2 mysql选择测试表,并进行增加相应测试数据

1.3 然后通过kettle spoon 进行转换,生成及作业设置

1.4 kettle spoon 成功连接 es 

1.5 kettle spoon 进行推送到es中,并在kibana中进行查看

二、背景理论

由于项目业务数据量背景,进行实时备份,跟中,分析数据的走势,数据量增大导致MySQL访问速度变慢,因此通过kkettle spoon 将MySQL中的数据定时抽取,并保存到es中。

要求:

本地分别安装kettle spoon9.0+    elk 6.8.13(es  es-head kibana),安装方法在本博客中均可以找到

spoon :​​​Windows10 数据库管理工具 Kettle Spoon的安装 ​

elk :​​​Elastic系列 → windows10 安装 EKL​

三、操作方法 

3.1 创建索引:

          参考博客:​​ElasticSearch 6.1.1--Head插件​

Kettle Spoon应用_微服务_48Kettle Spoon应用_微服务_49    Kettle Spoon应用_微服务_50

3.2 建立映射

解释:http://localhost:9200/search-to-es-log/search_to_log/   

search-to-es-log : 之前创建索引的名称

search_to_log : 给指定索引中创建的测试字段集合

注意:mapping properties之间层级与关系不能错

Kettle Spoon应用_大数据_51Kettle Spoon应用_MySQL_52     Kettle Spoon应用_微服务_53Kettle Spoon应用_搜索引擎_54

四、MySQL 抽数据,插入ES

4.1 kettle 连接 mysql 

基本配置见博客 : ​​​)Windows10 数据库管理工具 Kettle Spoon的安装 使用​

4.2 编写sql 

Kettle Spoon应用_kettle_55

4.3 kettle测试

Kettle Spoon应用_大数据_56Kettle Spoon应用_kettle_57

4.4 kettle 往es 中写  数据 

      业务解释:将抽取后的数据写入es中

  kettle 写入数据时提示错误:分别创建 写入的过程,并执行

Kettle Spoon应用_大数据_58

Kettle Spoon应用_搜索引擎_59

Kettle Spoon应用_MySQL_60

4.4.1 - 字段属性中有 【 Timestamp 】 情况时,kettle 设置:

4.4.1.1 设置数据库连接的属性: zeroDateTimeBehavior convertToNull

Kettle Spoon应用_微服务_61

4.4.1.2 - 设置 时间格式类型 

Kettle Spoon应用_大数据_62

4.4.2 - 抽取数据时,遇到存在数据 为 null  时,将 字段通过 “替换NULL值”格式化,

见地址:​​Kettle案例十五:替换NULL值​

Kettle Spoon应用_MySQL_63Kettle Spoon应用_kettle_64

4.4.3 - 写入完成后,自动多出一条全部为NULL的数据情况,

Kettle Spoon应用_微服务_65Kettle Spoon应用_大数据_66

Kettle Spoon应用_微服务_67Kettle Spoon应用_kettle_68

Kettle Spoon应用_kettle_69Kettle Spoon应用_kettle_70

Kettle Spoon应用_kettle_71

4.5 ES显示数据

Kettle Spoon应用_kettle_72


补充!Kettle Spoon  测试 连接 ES 

5.1)spoon中 选择“核心对象-生成记录”,在记录中设置测试的响应字段(当前字段和3.2中预设置字段统一)

Kettle Spoon应用_kettle_73

5.2)spoon中,在“核心对象-批量加载”中,找到 elasticsearch build insert功能,设置

问题:spoon  请求  localhost 本机es时,提示如下问题: Elasticsearch bulk insert.0 - ERROR (version 9.0.0.0-423, build 9.0.0.0-423 from 2020-01-31 04.53.04 by buildguy) : NoNodeAvailableException[None of the configured nodes are available: 

Kettle Spoon应用_kettle_74Kettle Spoon应用_MySQL_75

解决

       ​​NoNodeAvailableException[None of the configured nodes are available​

可能的原因

1、项目引用的 ES 版本和 Kettle Spoon 版本不一样;【本地是这种情况】:

由于本地kettle spoon版本是9.0+,es版本7.3+,由于7.3+版本问题多,并且提示版本不适配,因此这里选择降低es的基础版本,选择官网整套elk版本:6.8.13

ELK 6.8.13 : 官网下载地址:​​https://www.elastic.co/cn/downloads/past-releases#elasticsearch​​ 

2、端口不正确,tcp访问端口是9300;【问题很少出现】

3、cluster.name不正确,可以通过访问http://localhost:9200/查看ES服务下的cluster.name;【问题很少出现】

4、ES服务没启动;【问题很少出现&&超级低级错误】

根据上面的【1】,我这里重新修改版本,重新配置elk.

Kettle Spoon应用_MySQL_76         Kettle Spoon应用_大数据_77Kettle Spoon应用_微服务_78

Kettle Spoon应用_MySQL_79

版本修改成功后,kettle 连接 es 成功,如图:

Kettle Spoon应用_MySQL_80

问题解决后,设置“elasticeseartch bulk insert”的 general 、 servers 、 fields  、  settings,如图:

Kettle Spoon应用_MySQL_81Kettle Spoon应用_大数据_82  Kettle Spoon应用_MySQL_83 Kettle Spoon应用_MySQL_84


 五、效果

Kettle Spoon应用_微服务_85

六、操作

2.1)创建索引通过谷歌浏览器的es-head插件创建即可

下载地址:​​es-head谷歌浏览器插件20210420.zip-系统安全文档类资源​

2.1)es-head 配置成功后,创建索引:参考文章:​​通过es-head创建索引 ​

2.2)通过postman客户端工具es索引参数创建

Kettle Spoon应用_大数据_86Kettle Spoon应用_微服务_87

Kettle Spoon应用_微服务_88Kettle Spoon应用_搜索引擎_89

以上是自己整理的,并测试过,可以直接用

参考博客:

​使用spoon(kettle)工具抽取Elasticsearch的数据并入库​

​2.数据仓库-kettle抽取Mysql数据_吾我的博客​

​kettle详解(数据抽取、转换、装载)​


【ETL】定时变量+增量

一、作业、转换图

Kettle Spoon应用_大数据_90Kettle Spoon应用_大数据_91

二、实现步骤思路

1. 安装 kettle   elk 

2.在kettle中 新创建一个“转换”

3.根据“转换”创建“作业”

4.执行“作业”内容

Kettle Spoon应用_MySQL_92Kettle Spoon应用_MySQL_93

Kettle Spoon应用_kettle_94Kettle Spoon应用_微服务_95

三、转换图 实现

3.1 主-转换图 : 

  主要内容本博客中已经编写完毕 , 【增量】是在原本转换图基础上修改

Kettle Spoon应用_大数据_96

【3.1 转换图解析】:  主要

3.1.1   - 1.连接DB抽取对应学生数据

SELECT  * , DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%s') as create_time  
from course_issue_detail
where delete_flag='0' and issue_type='1' and create_time > '${createTime}'
-- limit 201,210;

3.1.2  -  2.替换null值

Kettle Spoon应用_搜索引擎_97Kettle Spoon应用_微服务_98

3.1.3 -  3.ESInsert_写入数据 

【具体配置 见博客:​​​Windows10 Kettle Spoon 抽取MySQL数据 保存到 本地ES​

Kettle Spoon应用_大数据_99Kettle Spoon应用_kettle_100

3.2 辅助-转换图

Kettle Spoon应用_MySQL_101Kettle Spoon应用_搜索引擎_102

3.2.1 - 修改更新时间

Kettle Spoon应用_kettle_103

select DATE_FORMAT(max(createTime,'%Y-%m-%d %H:%i:%s')) as createTime from etl_last_timestamp WHERE index_name = 'es_student_sub_test'

update etl_last_timestamp set createTime = '?' where index_name = 'es_student_sub_test'

四、作业图实现

Kettle Spoon应用_搜索引擎_104

4.1 创建作业图: 主要

4.2.1 - 创建更新表 

    1.检查表 1.1创建表:

           首先要 创建 中间转换时间戳的表 【etl_last_timestamp】,用来保存最新更新替换更新的时间

       先检查是否存在,然后创建 

Kettle Spoon应用_微服务_105

CREATE TABLE `etl_last_timestamp` ( 
`createTime` DATETIME(3) NULL DEFAULT NULL COMMENT '抽取的最后的时间戳'
)
COMMENT='记录抽取的最大的时间戳'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

4.2.2 - 2.变量赋值 

var prevRow=previous_result.getRows();
if (prevRow == null && (prevRow.size() =0))
{

false;

}else{
parent_job.setVariable("createTime",prevRow);
parent_job.setVariable("size",prevRow.size());
parent_job.setVariable("i",0);
parent_job.setVariable("createTime",prevRow.get(0).getString("createTime",""));
true;
}

4.2.3 -  3.调用变量转换业务

【见本博客 3.2 辅助-转换图】

4.2.4 -  4.循环控制

   Kettle Spoon应用_微服务_106            Kettle Spoon应用_大数据_107

4.2.5

var list_Tables = parent_job.getVariable("createTime").replace("[","").replace("]","").split(",");
var size = new Number(parent_job.getVariable("size"));
var i = new Number(parent_job.getVariable("createTime")+1);
if( i<size ){
parent_job.setVariable("createTime",list_Tables[i]);
}
parent_job.setVariable("i",i);
true;

Kettle Spoon应用_大数据_108

遗留问题: 抽取成功后,成功导出到Excel文件中,后期逐步修改整理。。。

五、完成效果

Kettle Spoon应用_微服务_109

Kettle Spoon应用_大数据_110

本博客内容整理成资源以上传到【本资源】中:​​20210511_kettle抽取mysql增量到ES中.zip-项目管理文档类资源​

 六、效果

Kettle Spoon应用_MySQL_111

七、连接

  基本配置kettle连接mysql

          见 :​​​Windows10 数据库管理工具 Kettle Spoon的安装 使用​

八、配置

Kettle Spoon应用_微服务_112Kettle Spoon应用_MySQL_113

九、操作

Kettle Spoon应用_大数据_114Kettle Spoon应用_kettle_115

 说明:

     配置数据源连接池大小设置,一般都是默认设置,如有特殊需求可以手动修改。


转载声明:本文为博主原创文章,未经博主允许不得转载

⚠️注意 ~

💯本期内容就结束了,如果内容有误,麻烦大家评论区指出

如有疑问❓可以在评论区💬或私信💬,尽我最大能力🏃‍♀️帮大家解决👨‍🏫!

如果我的文章有帮助,欢迎点赞+关注✔️鼓励博主🏃,您的鼓励是我分享的动力🏃🏃🏃~