技术标签： Teradata 基础层 中间层 数据仓库
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基础层时，要求目标表为空，即基础层的表数据清空。从这点来说基础层又叫做接口层。
显示所有的binlogSHOW BINARY LOGS;显示所有binlog事件SHOW BINLOG EVENTS IN 'mysql-bin.004374'；使用binlog恢复误删数据mysqlbinlog /data/mysql/mysql-bin.004374 --database=tuling --start-datetime='2019-01-01:00:00' ...
为什么80%的码农都做不了架构师？>>> ...
Android中pendingIntent的深入理解 pendingIntent字面意义：等待的。未决定的Intent。要得到一个pendingIntent对象，用法类的静态方法getActivity(Context, int, Intent, int),getBroadcast(Context, int, Intent,...
文/温国兵 惰性人皆有之，也算是人的一大天性。几日之前便构思好此文，怎奈每日杂事繁多。今日才提起笔，作下此文。本文谈谈我与数据库的故事。 说起和数据库结缘。还得从大一说起。大一刚開始接触C语言，每日就沉浸在无止境的代码中。在网上查资料的过程中，看到别人用C语言写了一个小型的图书操作程序，数据库採用的是SQL Server，执...
1、mutations（修改状态）（1）template中直接使用$store.commit( )触发// template<button @click="$store.commit('ADD')">+</button>// src/vuex/store.jsconst mutations = { // 状态变更函数 ADD (state) { state.count++; }}（2）利用mapMutations引入触发&l
关于时序分析： 我们跟随时间的脚步，试图解释现在、理解过去、甚至预测未来........原文链接：http://blog.sciencenet.cn/home.php?mod=space&uid=34250&do=blog&id=287173简介： 时间序列是一种重要的高维数据类型，它是由客观...
这篇博客是由iOS个人开发者Soheil Moayedi Azarpour发布的。每个人都会在使用iOS或者Mac app，点击按钮或者输入文本时，有过让人沮丧的经历，突然间，用户交互界面停止了响应。你真幸运 – 你只能盯着沙漏或者旋转的风火轮一段时间直到能够再次和UI界面交互为止！挺讨厌的，不是吗？在一款移动端iOS程序中，用户期望你的app可以即时地响应他们的触摸操作，然而当它不响
今天把项目的Qt版本从Qt4.6.3升级到Qt4.8.4，重新编译项目代码的时候，特别关注了一下编译器的警告。于是找到《C++编程规范——101条规则、准则与最佳实践》翻了翻，重温了一下第1条 在高级别警告干净利落地进行编译。 如果编译器对某个构造发出警告，一般表明代码中存有潜在的问题。警告就好比代码的“肿瘤”，可能是良性的也可能是恶性的——作为代码医生的我们不能对其视而不见。必须“把它...
autoauto的用法，可以根据变量值，推出变量类型，其中比较特殊的就是cosnt引用用法int main(){ const int a=10; auto x=a;// x int auto& y=a; //auto就是const int & 类型} 错误用法：auto&y=a,u=a; //这个时候u就不能编译通过，必须是 auto& y=a,&u =a;c++真的需要对内存，cpu，缓存应...
如果当前是root用户2113，那么5261切换成普通用户test用以下命令：[email protected]:~#su-test[sudo]passwordfortest:[email protected]:~$如果要切换4102回root用户，那么用以下命令：su或su-用户名1653root可以省略不写。切换回root用户时要输入root密码。一般直接输入exit命令来切换回root用户，这样就不用...
本来想通过esc/p指令打印标签，但是无奈能找到的资料太少，而且打电话给客服，也没有sample可以提供参考，后来客服说使用template功能的话，打印很方便。于是下载手册学习了一下，先下载P-touch Editor软件，制作标签模板。制作的模板就是普通的标签，不需要连接数据库之类的，模板里有3个object，分别是文本1，文本2，条形码3。这个很关键，后面的代码传送数据，就是按这...