kettle庖丁解牛第33篇之从上游抽取最近6个月的数据

引言

在上一篇文章中，我们主要讲解的是：我工作中遇到的一个实际案例，我们要周期性的从上游数据库中抽取数据到本地库，每次抽取的是最近180天的数据。如果上游最近180天的数据量有增加变多了，先把本地表中最近180天的数据删除，然后把上游最近180天的数据抽取到本地库表中。最后把本地库表中所有记录，进行去重复操作。如果上游最近180天的数据量没有化或变少了，本地表不做任何操作。

在本篇文章中，介绍的是：依然我工作中遇到的一个实际案例，我们要周期性的从上游mysql数据库中抽取数据到本地hive库中，每次抽取的是最近6个月的数据。hive中的目标表是按月做的分区，把最近6个月的数据分6次循环插入覆盖到对应的分区中。

数据库表准备

此处演示需要：1台mysql数据库、1台hive数据库，所以演示起来会显得乱一些，为了大家看着清楚，我在命名的时候进行明确的标识。

远端mysql库操作(127.0.0.1)

a、登录mysql

b、创建库和表

创建库

mysql> create database test;

使用指定库

mysql> use test;

查看最近6个月份的时间

mysql>

select date_format(now(),'%Y-%m') as business_time

union all

select date_format(DATE_SUB(now(), INTERVAL 1 MONTH),'%Y-%m') as business_time

union all

select date_format(DATE_SUB(now(), INTERVAL 2 MONTH),'%Y-%m') as business_time

union all

select date_format(DATE_SUB(now(), INTERVAL 3 MONTH),'%Y-%m') as business_time

union all

select date_format(DATE_SUB(now(), INTERVAL 4 MONTH),'%Y-%m') as business_time

union all

select date_format(DATE_SUB(now(), INTERVAL 5 MONTH),'%Y-%m') as business_time;

创建表

mysql> create table t_table(id int,name char(10),ttime char(10));

往表中插入数据

mysql> insert into t_table values(1,'a1','2022-05');

mysql> insert into t_table values(2,'a2','2022-04');

mysql> insert into t_table values(3,'a3','2022-03');

mysql> insert into t_table values(4,'a4','2022-02');

mysql> insert into t_table values(5,'a5','2022-01');

mysql> insert into t_table values(6,'a6','2021-12');

mysql> insert into t_table values(7,'a7','2021-11');

mysql> insert into t_table values(8,'a8','2021-10');

mysql> insert into t_table values(9,'a9','2021-09');

mysql> insert into t_table values(10,'a10','2021-08');

mysql> insert into t_table values(11,'a11','2021-07');

mysql> insert into t_table values(12,'a12','2021-06');

mysql> insert into t_table values(13,'a13','2021-05');

mysql> insert into t_table values(14,'a14','2021-04');

mysql> insert into t_table values(15,'a15','2021-03');

mysql> insert into t_table values(16,'a16','2021-02');

mysql> insert into t_table values(17,'a17','2021-01');

查看表中最终的数据

mysql> select * from t_table ;

c、操作截图

本地hive库操作(10.1.66.70)

a、登录hive

b、创建库和表

创建库

create database test01;

创建表

CREATE EXTERNAL TABLE test01.t_table_temp(

 id BIGINT comment '编号',

 name VARCHAR(20) comment '姓名',

 ttime VARCHAR(10) comment '业务时间',

 increment_type VARCHAR(20) comment '数据增长类型：1.增量,2.全量',

 load_time VARCHAR(20) comment '加载数据的时间戳')

PARTITIONED BY(btime VARCHAR(10) comment '按业务时间所在月份分区')

ROW FORMAT DELIMITED  

FIELDS TERMINATED BY '|'

STORED AS TEXTFILE;

c、操作截图

DB连接

a、远端Mysql DB连接

b、本地Hive DB连接

实战演示

转换和作业的简介

此次操作有2个作业和2个转换，说明如下：

(1)、to_scheduling.kjb   作业：整个ETL操作的总开关，调度此作业即可完成整个ETL数据抽取所有操作内容。

(2)、step1times.ktr   转换：读取上游库表中最近6个月的时间，将数据量保存到变量中。

(3)、step2hive_load.kjb   作业：调用step3sql_load.ktr转换，将结果txt通过SFTP传到hive服务器的磁盘目录上，然后通过命令把txt的数据加载到Hive的分区表中。

(4)、step3sql_load.ktr  转换：读取上游最近6个月的数据，将数据保存到本地的txt文件中。

step1times.ktr

a、获取最近6个月时间

b、复制记录到结果

step2hive_load.kjb

a、读取data转换成文本文件

b、SFTP 上传

c、装载数据到Hive

step3sql_load.ktr

a、根据不同时间查询数据

b、文本文件输出

to_scheduling.kjb

a、获取最近6个月时间

b、6个月时间保存到数组并设置变量

c、循环控制器

d、等待10秒

e、输出日志

f、抽取远端数据到本地Hive

g、循环变量累加

h、循环结束

运行总调度作业

a、运行作业

b、作业控制台输出

作业控制台输出内容比较多(分多张图展示)

验证本地库表数据

a、执行命令

select * from t_table_temp

b、操作截图

结束语

在本篇文章中，介绍的是：依然我工作中遇到的一个实际案例，我们要周期性的从上游mysql数据库中抽取数据到本地hive库中，每次抽取的是最近6个月的数据。hive中的目标表是按月做的分区，把最近6个月的数据分6次循环插入覆盖到对应的分区中。

兄弟们，其实想和作是有一段距离的，你想着想着就没有了，可是你做着做着，它就落地了。

啥都别说了，兄弟们后面跟着我干就完了，我们依然掰开揉碎的方式去说。后续的内容更精彩，敬请期待，感谢兄弟们的关注！！！