目录
- 数据库
- ETL工具
- ETL搭建数仓的操作步骤
- 1、ODS层
- 2、DWD层和ADS层
数据库
源系统为Oracle数据库。
中转服务器兼数仓也安装了Oracle数据库。
帆软的服务器上仅安装了内置的数据库,供存放帆软自己的数据。
服务器的操作系统都是windows server版
ETL工具
使用了比较稳定的Kettle 7.1版本。
实际企业级运行的时候,需要在默认安装后,进行性能调优,主要是把运行内存尽可能扩大,避免跑死程序。
同时,我是把日志记录到数据库中,以便后续查询日志。 这块不做详细介绍,能找到比较多的资料。
ETL搭建数仓的操作步骤
1、ODS层
建立转换的顺序和原则是:
先基础档案,再业务单据;
先主表,再子表;(实际上同时放一个转换也OK,毕竟新建的表,约束关系也没有了);
数据量小的表,先删除,后新增;删除用delete 效率低,可以直接truncate;
数据量大的表,进行新增/更新。
按照这个原则,在ODS层建立了如下图的job和转换。
这里设置了定时,每天在跑后面2层之前的时间,先跑这层。
这里是为了测试,数据量也相对比较小,层次间预留了足够的时间。
实际在生产环境,可以在3个job的上面,再做一个大的job,然后顺序是1、2、3、成功。这样可以避免某一层没有跑完导致后续层跑数据有错漏的情况。
2、DWD层和ADS层
job见下图:
每个转换为了区分清楚作用,这里写了具体实现的功能。
ODS1基础数据:把ODS的数据进行了更新。
ODS2门店更新:对门店信息进行更新
删除STORE_EACH:这里没有使用帆软的同比去年的公式,思路是直接用Oracle里面的函数进行处理。
然后为了取之前365行数据,就构建了门店+日期的全量表,方便Oracle现场的函数进行去年同比数据的抓取。实际上也可以用其他方法。
插入DWB_TRUNOVER_FULL和STORE:
调用了2个存储过程
DWB+TURNOVER_STORE_P的代码如下:
create or replace procedure DWB_TURNOVER_STORE_P
is
begin
delete from DWD_TURNOVER_STORE WHERE BILLDATE>=
(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss') FROM DUAL);
commit;
insert into DWD_TURNOVER_STORE
(BILLDATE,PK_ORG,PK_DEPTID,MONEY)
(
select billdate,pk_org,pk_deptid,sum(money) money
from
(
select a.billdate, a.money,
(select distinct(pk_deptid) from ar_recitem b where b.pk_recbill=a.pk_recbill) pk_deptid,
pk_org
from ar_recbill a
where a.pk_org in
(
select pk_org from org_orgs where enablestate=2 and isbusinessunit ='Y' and dr=0 and (pk_fatherorg='0001A110000000003G7C' or code='1601') and def1<>'~'
)
and a.billdate>=(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss') FROM DUAL)
and a.dr=0
)
group by (pk_deptid,billdate,pk_org)
);
COMMIT;
end DWB_TURNOVER_STORE_P;
DWB_TURNOVER_STORE_FULL_P的代码如下:
create or replace procedure DWB_TURNOVER_STORE_FULL_P
is
begin
delete from DWD_TURNOVER_STORE_FULL WHERE BILLDATE>=
(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss') FROM DUAL);
commit;
INSERT INTO DWD_TURNOVER_STORE_FULL (BILLDATE,PK_ORG,PK_DEPTID,MONEY)
(
select A.DAY BILLDATE,B.PK_ORG PK_ORG,A.PK_DEPT PK_DEPTID,B.MONEY from
DWB_STORE_EACHDAY A
LEFT join DWD_TURNOVER_STORE B
ON A.DAY=B.BILLDATE AND A.PK_DEPT=B.PK_DEPTID
where A.DAY>=(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss') FROM DUAL)
);
COMMIT;
end DWB_TURNOVER_STORE_FULL_P;
执行DWD_TURNOVER_STORE_FULL_AN_P的转换也非常简单:就是执行一个存储过程:DWD_TURNOVER_STORE_FULL_AN_P。
其代码如下:
create or replace procedure DWD_TURNOVER_STORE_FULL_AN_P
is
begin
delete from DWD_TURNOVER_STORE_FULL_AN WHERE BILLDATE>=
(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss') FROM DUAL);
commit;
insert into DWD_TURNOVER_STORE_FULL_AN
(BILLDATE,PK_DEPTID,MONEY,PK_ORG,SDLY,MTD,YTD)
(
SELECT * FROM
(
select BILLDATE,pk_deptid,money,pk_org,
LAG(money,365,0) OVER (PARTITION BY pk_deptid ORDER BY BILLDATE) AS SDLY,
sum(money) over (partition by to_char(to_date(BILLDATE,'yyyy-MM-dd hh24:mi:ss'),'YYYYMM') ,pk_deptid ORDER BY BILLDATE) AS MTD,
sum(money) over (partition by to_char(to_date(BILLDATE,'yyyy-MM-dd hh24:mi:ss'),'YYYY') ,pk_deptid ORDER BY BILLDATE) AS YTD
from
DWD_TURNOVER_STORE_FULL
)
where
billdate>=(SELECT to_char(TRUNC(SYSDATE-8),'yyyy-mm-dd hh24:mi:ss') FROM DUAL)
);
COMMIT;
end DWD_TURNOVER_STORE_FULL_AN_P;
至此,我们把这个简单的数仓做好了。
然后后面就是通过这个最终加工后的表,通过帆软来展视出来。
由于已经做过数据加工,即便我用了一台性能非常差的笔记本来充当帆软的服务器(All in One),且上面同时还运行了其他程序,报表展示出来的速度也能控制在5秒以内。