自学内容网 自学内容网
自学内容网 > 正文

Kettle的实战练习指南：从数据导入到ETL自动化

🕗 发布于 2024-09-23 14:36 etl  数据仓库  mysql  大数据  kettle  

        在数据集成和数据仓库建设中，Kettle作为一个强大的开源ETL工具，提供了灵活的数据抽取、转换和加载功能。本文将通过实战案例，详细介绍Kettle在数据导入、ETL流程设计、自动化任务调度等方面的应用。

一、数据导入

1. SQL语句导入

导入sql语句，支持拖拽

加入你导入sql失败了，将sql语句中的创建数据库的语句删除掉，手动的创建shop数据库，再导入sql即可。

创建一个新的数据库：shop_bi

2. 数据库区分

  • 业务数据库（shop）：支撑业务运行。
  • 分析型数据库（shop_bi）：数据分析工程师操作，减轻业务数据库负担。

二、Kettle实现ETL

1. 数据同步

ODS层：

贴源层，与原数据库结构相似。

将 shop数据库中的数据导⼊到 shop_bi 中。

image.png

表同步：

areas表同步

设置更新操作，确保字段映射正确。

设置 更新 操作：

设置另个一数据库的连接：

执行SQL语句，创建表ods_areas:

此时发现ods_areas表中，需要一个字段（dt）,该字段表示什么时候更新的这个数据。

goods_cats表同步

确保执行创建表SQL语句。

以上错误原因是没有执行创建表的SQL语句。

2. 定时任务

每周定时执行

设置Kettle作业，每周周一1点自动执行数据同步。

这样就可以达到每周周一1点进行数据同步的目的。

3. 日常数据抽取

获取特定日期数据：

如何获取一个orders表中某一天的数据

使用SQL语句筛选特定日期数据。

方式1：
select * from orders where createTime like  '2019-06-22%';

方式2：
select * from orders where createTime >=  '2019-06-22 00:00:00' and createTime <='2019-06-22 23:59:59';

方式3：
select * from orders where substr(createTime,1,10) = '2019-06-22';

select *,str_to_date('${dt}','%Y-%m-%d') dt from orders where substr(createTime,1,10) = '${dt}';
也可以使用下面这个：
SELECT *,str_to_date('${dt}','%Y-%m-%d') as dtime FROM shop.orders  where createTime like '${dt}%'
如何传递参数

传递参数，确保数据准确性。

必须保证预览有数据才可以：

插入\更新操作设置：

执行SQL语句建表的时候：出现错误

一般出现这个错误，就把数据类型，变为小写，再执行一遍。

4. 多表数据同步

goods、users、order_goods：

goods数据：

SELECT *,str_to_date(now(),'%Y-%m-%d %H:%i:%s') as dt
FROM goods
where subStr(createTime,1,10) = '${dt}'

后面的users 以及order_goods 操作是一样的。

三、自动化任务调度

1. 每天执行的任务

Job任务：设置Kettle作业，每天自动执行数据抽取和同步任务。

 每天执行的任务，做一个Job

image.png

image.png

以上步骤结束了，但是每天都需要修改一下dt 的参数。

image.png

目前还没有分析数据，没有分析任何的指标。分析的时候再shop_bi数据库上，减轻shop数据库的负担。

2. 获取前一天日期

方法一：使用正则表达式获取

使用正则表达式获取当前时间，传递给任务中的变量。

在sql语句中，可以获取前一天的日期：

select date_sub(str_to_date('2023-09-23','%Y-%m-%d'), INTERVAL 1 DAY);
INTERVAL 是间隔的意思

可以将以前的sql修改为：
select *,date_sub(str_to_date(?,'%Y-%m-%d'), INTERVAL 1 DAY) dt
from orders where substr(createTime,1,10) = date_sub(str_to_date(?,'%Y-%m-%
d'), INTERVAL 1 DAY);

获取前一天的数据另一个sql:
select adddate(str_to_date('2023-09-23','%Y-%m-%d'), -1);

新建转换：

通过这个工具可以获取当前时间。

在脚本中选择正则表达式脚本。为什么搞这个，因为我想从上一个步骤中获取年-月-日三个数据。

创建一个脚本，选择正则表达式，编写解析的正则表达式：

2023/09/25 10:20:07.986

(\d{4})/(\d{2})/(\d{2})\s\d{2}:\d{2}:\d{2}\.\d{3}
为什么在前面的年月日需要添加括号，因为括号括起来叫做组，可以通过别的方式获取组的数据，由于其他数据我不需要获取，所以不加括号

在脚本中创建正则表达式

新建作业--设置变量，使用线连接。

运行查看结果：

结果正确，保存，留着以后使用。

继续回归到项目中：

新建转换，选中 [ 作业 ]，获取变量。

接着输入选择表输入，使用如下sql语句：

select *,date_sub(str_to_date(?,'%Y-%m-%d'), INTERVAL 1 DAY) dt
from orders where substr(createTime,1,10) = date_sub(str_to_date(?,'%Y-%m-%d'), INTERVAL 1 DAY);

开始编辑：获取变量这个 job

可以点击预览一下。

创建表输出为--插入更新 操作

修改数据库中的一条数据（orders表）：

回顾：整个步骤只有两步，第一步设置变量

第二步：使用变量：

方法二：使用Java代码获取

使用Java代码获取前一天日期，并传递给任务中的变量。

1）编辑java代码
一个输入参数，一个输出参数，给定一个日期，获取这个日期的前一天的日期。

image.png

import java.util.Calendar;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.text.ParseException;
public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException {
    if (first) {
        first = false;
    }
    Object[] r = getRow();
    if (r == null) {
        setOutputDone();
        return false;
    }
    r = createOutputRow(r, data.outputRowMeta.size());
    String foobar = get(Fields.In, "dt").getString(r);
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    // 创建Calendar对象，并设置为传⼊的时间
    Calendar calendar = Calendar.getInstance();
    try {
        calendar.setTime(format.parse(foobar));
    } catch (ParseException e) {
        throw new RuntimeException(e);
    }
    // 将Calendar的⽇期减1，即为昨天的⽇期
    calendar.add(Calendar.DATE, -1);
    // 获取昨天的⽇期
    Date yesterdayDate = calendar.getTime();
    // System.out.println("昨天的时间：" + yesterdayDate);
    // 将昨天的⽇期格式化为指定格式
    String yesterdayStr = sdf.format(yesterdayDate);
    // 设置需要输出的字段
    get(Fields.Out, "yesterday").setValue(r, yesterdayStr);
    // Send the row on to the next step.
    putRow(data.outputRowMeta, r);
    return true;
}

image.png


2) 设置输入 为系统时间
操作步骤跟之前一样，数据名称必须是 dt， 值是 系统时间（可变）

image.png


测试一下，从java代码中点击【测试类】

image.png

image.png


确实可以获取到dt的数据。
创建一个作业--设置变量，操作跟之前一样。

image.png


运行一下查看结果是否正确：

image.png


接着可以在项目中使用：
在作业中，拉取【获取变量】，输入是表输入，输出是插入更新

image.png


点击获取变量，输入yesterday。

image.png


表输入中的sql语句如下：

select *,str_to_date(?,'%Y-%m-%d') dt
from order_goods where substr(createTime,1,10) = str_to_date(?,'%Y-%m-%d');

image.png


修改获取变量，因为我的sql语句中有两个 ？ ，表示需要两个参数。

image.png


表输出的设置：

image.png


记得先创建表，执行sql语句。

image.png


执行任务，查看结果。

image.png


 

四、总结

        Kettle不仅提供了强大的数据抽取和转换功能，还支持灵活的任务调度和自动化处理。通过本文的实战案例，读者可以更好地理解和应用Kettle，提高数据集成的效率和准确性。希望这篇文章能帮助你更好地掌握Kettle的实战应用。


原文地址：https://blog.csdn.net/weixin_64726356/article/details/142218429

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024