Teradata数据仓库基础层和中间层
来Teradata实习2个月了，今天我分享一下之前在学习中的困惑，内容都很基础，但对刚来实习的同道们理清思路，应该会有所帮助吧，文中如有错误，请指正。
本文主要讲事实表和维度表从源系统到中间层的初始加载和增量加载的过程，列举了一张事实表和一张维度表，里面的代码为ETL开发的源代码。对其中的一些概念，做了自己的理解。
以我所在的一汽丰田NDMS_KPI项目举例。所用到的ETL 工具为Automation 和 Dsql，数据库用的是Teradata14.0 express
源系统用的是oracle数据库，我们的目的是将源系统的数据导入到TD数据库中，TD数据库分3层，基础层，中间层，集市层。3层之间的关系是数据从基础层流向集市层。这个从源系统到集市层的过程，我们叫做ETL（Extraction-Transformation-Loading ）。中文意思是数据提取、转换和加载 。TD的Automation与数据库绑定在了一起，从某种意义上来说，数据从源系统到到集市层，是个ELT过程。
在说明ETL过程之前，先解释一下维度表和事实表，初始加载和增量加载。
维度表和事实表：举个例子就很容易理解了。比如销售订单是一张事实表，记录了经销店代码，车的品种，购买人，购买日期等。而经销店的信息是维度表，它记录了经销店的代码，名字，位置等。一般来说，维度表不常变化，而事实表每天都会有变化，事实表参考了维度表。
初始加载和增量加载：在一个数据仓库搭好之后，是没有数据的。所以进行初始加载，将数据从源系统有条件地抽取过来。增量加载是在数据仓库正常运行之后，每天进行从数据库中抽取当日的数据到数据仓库中。
下面就来举例说明维度表和事实表的ETL过程。在抽取之前，基础层，中间层的表结构都是建好了的。
一、维度表的初始加载
1.源系统到基础层：
以BIN_INSAGENCY_M.sql为例子
SELECT
INSAGID
,INSAGCODE
,INSAGNAME
,INSAGTYPE
,MEMO
,UPDATECOUNT
,CREDATE
,CREUSER
,UPDATETIME
,UPDATEUSER
,DELETEFLAG
FROMwatool.BIN_INSAGENCY_M
假设这个脚本位置放于C:\TRY\EXTRACT\下，在基础层作业中指定参数路径为它。其fastload脚本中包含了与源系统连接的参数，如源系统的ip，端口号，用户名，密码。以及所要加载的数据文件BIN_INSAGENCY_M.sql。
注：执行的sql脚本是有TD，还是oracle来执行的（应该是oracle执行，这里有困惑）
通过Automation的基础层作业，将源系统的BIN_INSAGENCY_M放到了TD数据库的基础层中。这里可见做的逻辑处理是很少的。
2.基础层到中间层转化脚本
DELETE FROM $PVIEW.PTY_INSAGENCY ALL;
--先清空中间层表的数据，这是为了防止有脏数据。
.IF ERRORCODE<>0 THEN .QUIT 12;
INSERT INTO $PVIEW.PTY_INSAGENCY(
IC_Cd
,Insagency_Code
,Insagency_Name
,Insagency_Type
,Memo
,Delete_Ind
,Create_Dt
,Update_Dt
)
SELECT
COALESCE(trim(INSAGID),'')
,COALESCE(INSAGCODE,'')
,COALESCE(INSAGNAME,'')
,COALESCE(INSAGTYPE,'')
,COALESCE(MEMO,'')
,COALESCE(DELETEFLAG,'')
,COALESCE(CAST(CREDATE AS DATE FORMAT'YYYYMMDD'),CAST('00010101' AS DATE FORMAT 'YYYYMMDD'))
,COALESCE(CAST(UPDATETIME AS DATE FORMAT'YYYYMMDD'),CAST('00010101' AS DATE FORMAT 'YYYYMMDD'))
FROM $T_DATA.BIN_INSAGENCY_M;
.IF ERRORCODE<>0 THEN .QUIT 12;
.QUIT 0;
我们可以看到中间层的字段名改了，但区别不大，去掉了一些字段。
通过在Automation上执行该表对应的作业，即执行该脚本，便将基础层的数据做了些转化加载到中间层去了。
二、维度表的增量加载
1.源系统到基础层
--完全新增数据：
select
INSAGID
,INSAGCODE
,INSAGNAME
,INSAGTYPE
,MEMO
,UPDATECOUNT
,CREDATE
,CREUSER
,UPDATETIME
,UPDATEUSER
,DELETEFLAG
from watool.BIN_INSAGENCY_M
where to_char(CREDATE, 'yyyymmdd') =to_char(sysdate-1,'yyyymmdd')
and UPDATETIME is null
and instr(nvl(DELETEFLAG,'0'),'1')=0
union all
--更新数据：
select
INSAGID
,INSAGCODE
,INSAGNAME
,INSAGTYPE
,MEMO
,UPDATECOUNT
,CREDATE
,CREUSER
,UPDATETIME
,UPDATEUSER
,DELETEFLAG
from watool.BIN_INSAGENCY_M
where to_char(UPDATETIME, 'yyyymmdd') =to_char(sysdate-1,'yyyymmdd')
and instr(nvl(DELETEFLAG,'0'),'1')=0
union all
--逻辑删除数据：
select
INSAGID
,INSAGCODE
,INSAGNAME
,INSAGTYPE
,MEMO
,UPDATECOUNT
,CREDATE
,CREUSER
,UPDATETIME
,UPDATEUSER
,DELETEFLAG
from watool.BIN_INSAGENCY_M
where to_char(UPDATETIME, 'yyyymmdd') =to_char(sysdate-1,'yyyymmdd')
and DELETEFLAG = '1'
or (to_char(CREDATE, 'yyyymmdd') = to_char(sysdate-1,'yyyymmdd')
and UPDATETIME is null
and DELETEFLAG = '1')
取得数据是创建时间或更新时间为当日-1的数据。这里增量加载为每日加载一次。
2.基础层到中间层脚本
DELETE FROM $PVIEW.PTY_INSAGENCY ALL;
.IF ERRORCODE<>0 THEN .QUIT 12;
INSERT INTO $PVIEW.PTY_INSAGENCY(
IC_Cd
,Insagency_Code
,Insagency_Name
,Insagency_Type
,Memo
,Delete_Ind
,Create_Dt
,Update_Dt
)
SELECT
COALESCE(trim(INSAGID),'')
,COALESCE(INSAGCODE,'')
,COALESCE(INSAGNAME,'')
,COALESCE(INSAGTYPE,'')
,COALESCE(MEMO,'')
,COALESCE(DELETEFLAG,'')
,COALESCE(CAST(CREDATE AS DATE FORMAT'YYYYMMDD'),CAST('00010101' AS DATE FORMAT 'YYYYMMDD'))
,COALESCE(CAST(UPDATETIME AS DATE FORMAT'YYYYMMDD'),CAST('00010101' AS DATE FORMAT 'YYYYMMDD'))
FROM $T_DATA.BIN_INSAGENCY_M;
.IF ERRORCODE<>0 THEN .QUIT 12;
.QUIT 0;
与维度表的初始加载一样。
三、事实表的初始加载
1.源系统到基础层：
SELECT
MSIYEAR
,MSIMONTH
,DEALERCODE
,MSISEQUENCE
,MSINAME
,MSIVALUE
,MSIINPUT
,UPDATECOUNT
,DELETEFLAG
,UPDATEUSER
,UPDATETIME
,CREUSER
,CREDATE
FROMwatool.SRV_MSIDETAIL_F
与上类似。
2.基础层到中间层:
DELETE FROM $PVIEW.SLS_MSIDETAIL ALL;
.IF ERRORCODE<>0 THEN .QUIT 12;
/*将数据插入到目标表*/
INSERT INTO $PVIEW.SLS_MSIDETAIL
(
DLR_Cd
,MSI_Year
,MSI_Month
,MSI_Sequence
,MSI_Name
,MSI_Value
,MSI_Input_Value
,Delete_Ind
,Create_Dt
,Data_Start_Dt
,Data_End_Dt
)
SELECT
COALESCE(DEALERCODE,'')
,ZEROIFNULL(MSIYEAR)
,ZEROIFNULL(MSIMONTH)
,ZEROIFNULL(MSISEQUENCE)
,COALESCE(MSINAME,'')
,ZEROIFNULL(MSIVALUE)
,ZEROIFNULL(MSIINPUT)
,COALESCE(DELETEFLAG,'')
,COALESCE(CAST(CREDATE AS DATE FORMAT'YYYYMMDD'),CAST('00010101' AS DATE FORMAT 'YYYYMMDD'))
,$TX_DATE
,CASE WHEN DELETEFLAG='1' THEN $TX_DATEELSE CAST($MAXDATE AS DATE FORMAT 'YYYYMMDD') END
FROM $T_DATA.SRV_MSIDETAIL_F;
.IF ERRORCODE<>0 THEN .QUIT 12;
/*数据处理结束*/
.QUIT 0;
对于事实表，我们增设了两个字段Data_Start_Dt，Data_End_Dt。取消了字段Update_Dt。
Data_Start_Dt = CREDATE 很显然，而Data_End_Dt 则取决于删除标志，若标志为删除，则Data_End_Dt = $TX_DATE，$TX_DATE为当前日期，这样表示这条记录的结束时间为当日。如果标志位不删除，则令Data_End_Dt= MAXDATE。
四．事实表的增量加载：
1.源系统到基础层
--完全新增数据：
select
MSIYEAR
,MSIMONTH
,DEALERCODE
,MSISEQUENCE
,MSINAME
,MSIVALUE
,MSIINPUT
,UPDATECOUNT
,DELETEFLAG
,UPDATEUSER
,UPDATETIME
,CREUSER
,CREDATE
from watool.Srv_MsiDetail_F
where to_char(credate,'yyyymmdd') =to_char(sysdate-1,'yyyymmdd')
and (instr(nvl(deleteflag,'0'),'1')=0)
and updatetime is null
union all
--更新数据：
select
MSIYEAR
,MSIMONTH
,DEALERCODE
,MSISEQUENCE
,MSINAME
,MSIVALUE
,MSIINPUT
,UPDATECOUNT
,DELETEFLAG
,UPDATEUSER
,UPDATETIME
,CREUSER
,CREDATE
from watool.Srv_MsiDetail_F
where to_char(updatetime,'yyyymmdd') =to_char(sysdate-1,'yyyymmdd')
and (instr(nvl(deleteflag,'0'),'1')=0)
union all
--删除数据：
select
MSIYEAR
,MSIMONTH
,DEALERCODE
,MSISEQUENCE
,MSINAME
,MSIVALUE
,MSIINPUT
,UPDATECOUNT
,DELETEFLAG
,UPDATEUSER
,UPDATETIME
,CREUSER
,CREDATE
from watool.Srv_MsiDetail_F
where to_char(updatetime,'yyyymmdd')=to_char(sysdate-1,'yyyymmdd')
and trim(deleteflag) = '1'
or (to_char(credate,'yyyymmdd') =to_char(sysdate-1,'yyyymmdd')
andupdatetime is null
andtrim(deleteflag) = '1'
)
如上类似
2.基础层到中间层
--数据处理区
/*
逻辑处理：
1、判断是否数据是否完全新增数据；
2、如果不是完全新增数据，关闭旧数据，再把新增数据插入目标表；
3、如果是完全新增数据，直接插入数据库表
*/
--判断是否完全新增数据,并关闭旧数据
UPDATE a
FROM $PVIEW.SLS_MSIDETAILa
,$T_DATA.SRV_MSIDETAIL_F b
SET Data_End_Dt=$TX_DATE //Delete_Ind 也该设置为1
WHERE a.DLR_Cd =b.DEALERCODE
AND a.MSI_Year =b.MsiYear
AND a.MSI_Month =b.MsiMonth
AND a.MSI_Sequence =b.MsiSequence
AND a.Data_End_dt =$MAXDATE
;
.IF ERRORCODE<>0 THEN .QUIT 12;
/*回退处理区*/
DELETE FROM $PVIEW.SLS_MSIDETAIL WHEREData_Start_Dt>=CAST($TX_DATE AS DATE FORMAT 'YYYYMMDD');
.IF ERRORCODE<>0 THEN .QUIT 12;
/*将数据插入到目标表*/
INSERT INTO $PVIEW.SLS_MSIDETAIL
(
DLR_Cd
,MSI_Year
,MSI_Month
,MSI_Sequence
,MSI_Name
,MSI_Value
,MSI_Input_Value
,Delete_Ind
,Create_Dt
,Data_Start_Dt
,Data_End_Dt
)
SELECT
COALESCE(DEALERCODE,'')
,ZEROIFNULL(MSIYEAR)
,ZEROIFNULL(MSIMONTH)
,ZEROIFNULL(MSISEQUENCE)
,COALESCE(MSINAME,'')
,ZEROIFNULL(MSIVALUE)
,ZEROIFNULL(MSIINPUT)
,COALESCE(DELETEFLAG,'')
,COALESCE(CAST(CREDATE AS DATE FORMAT'YYYYMMDD'),CAST('00010101' AS DATE FORMAT 'YYYYMMDD'))
,$TX_DATE
,$MAXDATE
FROM $T_DATA.SRV_MSIDETAIL_F;
.IF ERRORCODE<>0 THEN .QUIT 12;
/*数据处理结束*/
.QUIT 0;
红色区域的代码很有意思，通过时间来关闭旧数据。举个例子来说，前天你买了一辆车，车的颜色为红色，今天你跟售车者谈了，把它改成了黑色，那么原先记录你购车的记录就成了旧数据，关闭的方法就是将Data_End_Dt = 当日。旧数据仅仅在逻辑上删了，但在物理上仍保留着。这里不同于以前所讲的数据库的主键，因为记录是可以改的，而且新旧数据同时存在于数据库中。
注：增量数据中，中间层的数据是不断会增加的，而基础层是不会的，因为在用fastload导入oracle数据到TD基础层时，要求目标表为空，即基础层的表数据清空。从这点来说基础层又叫做接口层。
扫一扫
222
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
