首页 > 其他分享 >Kettle的实战练习指南：从数据导入到ETL自动化

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

时间：2024-09-19 12:50:34浏览次数：11  
标签：导入到 -% Kettle 获取 date dt 数据 语句 ETL

        在数据集成和数据仓库建设中，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的实战应用。

标签：导入到,-%,Kettle,获取,date,dt,数据,语句,ETL
From： https://blog.csdn.net/weixin_64726356/article/details/142218429

相关文章

  • ETLCloud：新一代ETL数据抽取工具的定义与革新
    数据集成、数据治理已经成为推动企业数字化转型的核心动力，现在的企业比任何时候都需要一个更为强大的新一代数据集成工具来处理、整合并转化多种数据源。而ETL（数据提取、转换、加载）作为数据管理的关键步骤，已在企业数据架构中扮演重要角色。然而，随着数据量的爆炸性增长、数据复杂......
  • 试用完几十款ETL工具后的经验总结，ETL工具用这三款就足够了
    1.ETL选型前言市面上ETL工具国内外加起来估计得有30种之多，其中近20款工具都花时间试用过，现在把试用后总结出来的经验分享一下，目前很多企业在选择ETL工具时不知道怎么选择适合自己的工具也不可能一款一款的去试用，试用成本非常高，而且不同企业数据量，对数据质量的要求，技术开发人员的......
  • Kettle的安装与基本使用
    什么是Kettle？        Kettle最早是一个开源的ETL（Extract-Transform-Load的缩写）工具，全称为KDEExtraction,Transportation,TransformationandLoadingEnvironment。是一个功能丰富的ETL工具，它允许用户轻松地进行数据抽取、转换和加载。后来Kettle重命名为PentahoD......
  • 六种主流ETL工具的比较与Kettle的实践练习指南--MySQL、hive、hdfs等之间的数据迁移
            在数据集成和数据仓库建设中，ETL（Extract,Transform,Load）工具扮演着至关重要的角色。本文将对六种主流ETL工具进行比较，并深入探讨Kettle的实践应用。一、六种主流ETL工具比较1.DataPipeline设计及架构：专为超大数据量、高度复杂的数据链路设计的灵活、可扩......
  • kettle从入门到精通 第八十五课 ETL之kettle kettle中javascript步骤调用外部javascri
     场景：交流学习群里面有小伙伴咨询kettle中的javascript代码步骤如何调用外部js文件中的函数，觉得有点意思的，于是就抽时间整理了一下。 1、外部js文件为test.js，代码如下：functiontest(param){return"接收到了参数"+param;}2、当时没有过多考虑，在本地简单写了个demo测......
  • mysql数据怎么导入到帝国cms
    将MySQL数据导入到帝国CMS中通常有两种情况：一种是从现有的MySQL数据库导入数据到帝国CMS的新建数据库中，另一种是从帝国CMS的备份文件恢复数据到现有的帝国CMS数据库中。以下是针对这两种情况的具体步骤：从现有MySQL数据库导入数据到帝国CMS方法一：手动迁移数据导出现有数据库：......
  • ETL数据集成丨建设BI的关键前提是ETL数据集成?
    背景很多企业都购买了商业智能（BI）来加速数字化转型，但是发现仅仅依赖BI效果往往不太好。虽然通过BI，企业能够快速分析和可视化数据，然而，BI并不是一个万能工具，它虽然能帮助企业解读数据，但其有效性高度依赖于数据的质量和一致性，很多企业直接用BI连接生产系统的数据进行分析，极大影响了生产......
  • ETL数据集成丨建设BI的关键前提是ETL数据集成?
    背景很多企业都购买了商业智能（BI）来加速数字化转型，但是发现仅仅依赖BI效果往往不太好。虽然通过BI，企业能够快速分析和可视化数据，然而，BI并不是一个万能工具，它虽然能帮助企业解读数据，但其有效性高度依赖于数据的质量和一致性，很多企业直接用BI连接生产系统的数据进行分析，极大影响了生......
  • Kettle
    一、Kettle简介Kettle（又名：PentahoDataIntegration）是一款国外开源的ETL工具。Kettle中文名称叫水壶，该项目的主程序员MATT希望把各种数据放到一个壶里，然后以一种指定的格式流出。kettle是纯java开发，开源的ETL工具，用于数据库间的数据迁移。可以在Linux、Windows、Unix中运......
  • 实现 Excel 文件导入到向量数据库（Milvus），并支持 先查询知识库（Milvus），然后再查询大模型（Ol
    为了实现Excel文件导入到向量数据库（Milvus），并支持先查询知识库（Milvus），然后再查询大模型（Ollama）的功能，以下是具体的实现步骤：1.导入Excel数据到向量数据库（Milvus）首先，您需要将Excel文件中的数据向量化，并将这些向量导入到Milvus数据库中。可以使用pandas读取Excel文件，使用......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99