ETL Hive

关于数据统计中的年累计运算

Posted by hydra on 2019-11-13

开窗函数实现

数仓里有ads层，是需要我们建模后，从dwd层抽取需要的数据，组建应用宽表，以供后续使用。
以下简化说明。

有资金事实表ads.ads_fact_fund_d，
列city（城市），资金（happen_amt），交易日期（deal_dt）

需求：
输出宽表格式，按城市，按月，统计每月资金量，以及从年初到该月的年累计资金量。

1
2
3
4
5
6
7
8
9
10
11
12
13
with a as (
	select city,
	       SUM(happen_amt) as current_period_val,
           trunc(deal_dt,'MM') AS data_dt
	from ads.ads_fact_fund d 
	group by city,trunc(deal_dt,'MM')
)

select city,data_dt,current_period_val,
	   sum(current_period_val) OVER (PARTITION BY city ORDER BY data_dt ASC) AS year_tol
from a
where year(data_dt)=2019
order by city,data_dt

利用开窗函数可以轻松实现 城市-月-月资金-当月年累计资金 的宽表格式。
可以思考一下，进一步实现加上 上期值-去年同期资金-去年同期年累计资金 之后的需求。

ETL过程实现增量更新

在我们快速开发时，利用SQL将大量历史数据初始化进入宽表后，再接下来就要开发每月数据增量更新的ETL程序，我们暂时使用的工具是infomatica公司的BDM。这是将用来代替PowerCenter的新一代工具，支持大数据平台如Hive等与传统关系型数据库进行交互，能够在不同数据源及数据平台上开发ETL流程，开发完成后的ETL流程可以推送给spark引擎执行，功能强大。

现在BDM网上资料不多，很多材料也靠公司同事翻译，暂时用起来还比较跌跌撞撞，具体ETL流程不详述了，将实现逻辑用SQL表达：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT overwrite table ads.ads_value_tol
PARTITION(data_dt)
SELECT 
city,data_dt,current_period_val,year_tol,sysdate AS etl_tm
FROM (
	select city,
	sum(case when trunc(deal_dt,'MM')=trunc(sysdate,'MM') then happen_amt ELSE 0 END) as current_period_val, --当期值
	SUM(case when trunc(deal_dt,'MM')=trunc(add_months(sysdate,-1),'MM') THEN happen_amt ELSE 0 END) AS last_period_val, --上期值
	sum(case when trunc(deal_dt,'MM')=trunc(add_months(sysdate,-12),'MM') THEN happen_amt ELSE 0 END) AS period_of_time_val, --去年同期值
	sum(case when trunc(deal_dt,'YEAR')=trunc(sysdate,'YEAR') AND trunc(deal_dt,'MM')<date_add(trunc(sysdate,'MM'),1) THEN happen_amt ELSE 0 END) AS year_tol, --年累计
	sum(case when trunc(deal_dt,'YEAR')=trunc(add_months(sysdate,-12),'YEAR') AND trunc(deal_dt,'MM')<date_add(trunc(add_months(sysdate,-12),'MM'),1) THEN happen_amt ELSE 0 END) AS last_year_tol, --去年同期年累计
	trunc(sysdate,'MM') AS data_dt
	from ads.ads_fund_inout d 
	group by city
)t



Tags
ETL Hive
FRIENDS