引言

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

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

数据库表准备

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

远端mysql库操作(127.0.0.1)

a、登录mysql

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

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、操作截图

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

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

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

本地hive库操作(10.103.66.70)

a、登录hive

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

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、操作截图

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

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

DB连接

a、远端Mysql DB连接

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

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

b、本地Hive DB连接

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

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

实战演示

转换和作业的简介

此次操作有2个作业和2个转换,说明如下:

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

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

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

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

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

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

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

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

step1times.ktr

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

a、获取最近6个月时间

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

b、复制记录到结果

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

step2hive_load.kjb

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

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

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

b、SFTP 上传

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

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

c、装载数据到Hive

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

step3sql_load.ktr

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

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

b、文本文件输出

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

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

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

to_scheduling.kjb

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

a、获取最近6个月时间

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

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

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

c、循环控制器

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

d、等待10秒

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

e、输出日志

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

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

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

g、循环变量累加

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

h、循环结束

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

运行总调度作业

a、运行作业

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

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

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

b、作业控制台输出

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

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

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

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

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

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

验证本地库表数据

a、执行命令

select * from t_table_temp

b、操作截图

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

结束语

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

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

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