Kettle实现跨数据库指标计算

背景说明

业务上需要每天统计业务指标的当日值,同时还需要计算出该指标的的昨日值,上月同期值,以及月累计值/年累计值.

考虑到表使用时方便,因此设计指标表每天产生一条指标记录,同时用字段来记录该指标对应昨日值及其他值.

同时减少不必要的统计数据量,在计算累计值时将从目标表中取出昨日数据,昨日累计值与当日值相加得到当日累计值.

因为当日指标值取数数据库与目标表不在同一数据库,因此不能使用单一SQL实现查询,需要跨数据库进行结果集关联.

因此开发此作业的难点就在于数据库之间的关联.

目标表结构

列名	列说明	备注
day_id	日期
area_code	区域
kpi_code	指标
kpi_value	当日指标
kpi_value_ld	昨日指标
kpi_value_lm	上月同期值
kpi_value_sm	当月累计值
kpi_value_sy	当年累计值

创建测试表及测试数据

源端数据表:

drop table if exists test_1;
create table test_1 as
(
select '001' as area_code ,to_date('20190906','yyyymmdd') as create_date ,100 as sum_amount union all
select '001' as area_code ,to_date('20190906','yyyymmdd') as create_date ,200 as sum_amount 
)

目标数据表:

drop table if exists test_target;
create table test_target as
(
select '20190806' as day_id ,'001' as area_code ,'001' as kpi_code,100 as kpi_value ,100 as kpi_value_ld,100 as kpi_value_lm ,100 as kpi_value_sm,100 as kpi_value_sy union all
select '20190905' as day_id ,'001' as area_code ,'001' as kpi_code,100 as kpi_value ,100 as kpi_value_ld,100 as kpi_value_lm ,100 as kpi_value_sm,200 as kpi_value_sy union all
select '20190906' as day_id ,'001' as area_code ,'001' as kpi_code,200 as kpi_value ,200 as kpi_value_ld,200 as kpi_value_lm ,200 as kpi_value_sm,200 as kpi_value_sy 
)

查询语句

指标查询的SQL语句并不复杂.只是查询中需要使用作业变量,这样方便进行调度按天跑批.
当日指标值:

select 
'${day_id}' as day_id
,area_code as area_code
,'001' as kpi_code
,sum(sum_amount) as kpi_value
from test_1
where create_date >= to_date('${day_id}','yyyymmdd') and create_date < to_date('${day_id}','yyyymmdd') + interval '1 day'
group by area_code

昨日指标值:

select 
area_code
,kpi_code
,kpi_value as kpi_value_ld
,case when extract(day from to_date('${day_id}','yyyymmdd')) = 1 then 0 else kpi_value_sm end as kpi_value_ldsm
,case when extract(doy from to_date('${day_id}','yyyymmdd')) = 1 then 0 else kpi_value_sy end as kpi_value_ldsy
from test_target 
where day_id = to_char(to_date('${day_id}','yyyymmdd') - interval '1 day','yyyymmdd')

上月同期值:

select 
area_code
,kpi_code
,kpi_value as kpi_value_lm
from test_target 
where day_id = to_char(to_date('${day_id}','yyyymmdd') - interval '1 month','yyyymmdd')

删除数据

为了让作业可以重复跑数据,而不会数据重复,需要在插入数据前先删除当日数据.

delete from test_target
where day_id = '${day_id}'

Kettle转换

转换作业整体视图:

删除数据

• 控件: 执行数据库脚本

重点说明:

1. 勾选变量替换

配置图:

当日值

• 控件: 表输入

重点说明:

1. 勾选变量替换
2. 数据源为source

配置图:

昨日值

• 控件: 表输入

重点说明:

1. 勾选变量替换
2. 数据源为target
3. SQL语句中三个累计字段的写法

配置图:

上月值

• 控件: 表输入

重点说明:

1. 勾选变量替换
2. 数据源为target

配置图:

排序

因接下来就要进行数据集关联连接了,因为关联连接前要求合并,因此需此步骤.

三个排序步骤为相似操作.

• 控件: 排序记录

重点说明:

1. 指定排序列为关联列
2. 注意字段顺序

配置图:

关联

• 控件: 记录集连接

重点说明:

1. 连接方式
2. 连接字段

配置图:

累计计算

之前的记录集连接操作只是把多个数据集之间进行了一次左关联操作,并没有计算出我们需要的当月累计和当年累计值.

• 控件: 计算器

重点说明:

1. 计算公式
2. 计算字段

在实际开发中发现:
添加第二行的计算公式,下拉选择字段A/字段B时出现的只有上一行的结果新字段.无法选择上一步骤中的其他字段.
因此直接写入字段名称,而非选择.
如果不想直接写入,可再增加一个计算器步骤.

配置图:

输出目标表

• 控件: 表输出

重点说明:

1. 数据库连接target
2. 目标表名
3. 可使用获取字段快速添加列
4. 删除多余字段
   

总结

通过以上的各项设置.就可以实现单个指标跨数据库关联产生指标分析记录.

执行结果:
可以看到之前的一条(20190906)当日的错误的记录已经被纠正了.
当然,作为日常调度作业可以有以下改进:

1. 增加Kettle作业(kjb)作为循环控制来调用此作业可实现日期范围内循环跑数.
2. 增加一层指标单日值表,也就是去掉各种指标分析字段(累计值,同期值)的表.这样就不用跨库进行数据集关联了.数据集之间的关联完全放到数据库SQL来操作.kettle的开发就会简单很多.本次不想增加表数量,同时减少调度任务数量,因此没有采用指标当日值表.