1、Teradata Trainee Technical Booklets Course 5ETL开发与维护,By：Li Erniu,议题,ETL 介绍ETL Automation说明ETL 开发ETL 管理维护ETL人员职责与技能要求,议题,ETL 介绍ETL Automation说明ETL 开发ETL 管理维护ETL人员职责与技能要求,ETL 定义ETL/ELT/ETLT-总结ETL 流程概述ETL 技术架构,ETL 定义,ETL抽取(Extract)、转换 (Transform)和加载(Load)Teradata use E-L-T framework抽取(Extract)从数据库、磁带或其
2、它各种介质抽取数据或由数据提供方主动提供数据加载(Load)将数据源文件加载至数据仓库(Teradata) 使用fastload/multiload/tpump/TPTinmod/axsmod转换(Transform)将源表数据根据业务需要转换成目标表数据使用bteqSQL （SELECT/DELETE/INSERT/UPDATE）,ETL vs. EtLT vs. ELT 总结,Teradata ETL 流程简要说明,ETL流程是数据触发的、全自动的数据流过程；ETL流程是将数据抽取、转换和装载到数据仓库的一个数据流的过程，它定义了对众多数据处理的顺序性、依赖性及触发性等关系；使用ETL A
3、UTOMATION进行统一的管理、调度；使用ETL监控服务进行流程监控并处理异常情况；一般情况下，ETL流程包括数据源准备、加载、转换、数据导出和数据备份等几部分。,CSDCC ETL流程图,Teradata ETL技术架构,议题,ETL 介绍ETL Automation说明ETL 开发ETL 管理维护ETL人员职责与技能要求,ETL Automation简介Perl对数据库的访问ETL Automation目录架构ETL Automation服务ETL Automation处理流程ETL管理及组件ETL管理示意图,ETL Automation简介,Teradata公司开发的ETL流程调度产品
4、（product）；使用Perl程序开发，运行环境需要安装Perl软件及DBI、DBD插件；C/S服务器架构ETL Automaiton由以下几部分组成：ETL Automation服务ETL Automation目录架构ETL Automation知识库ETL Automation环境变量ETL Administrator管理工具（GUI）ETL monitor监控工具（GUI）ETL Automation任务调度：定义了目录架构及知识库目录架构下不同的目录用于不同的目的知识库中不同的表用于不同的目的定义了目录架构下部分目录与知识库中相应表的对应关系规则目录中的任务名称与知识库任务表中相应任
5、务名称必须相同目录中的日志文件名称与知识库日志表中相应日志名称必须相同定义了控制文件的命名规则控制文件作为Automation调度任务的必需条件之一ETL Automation服务使用环境变量、控制文件和知识库对ETL任务进行调度,ETL Automation简介,ETL Automation功能说明ETL任务的自动化调度ETL任务管理一般情况下使用图形界面工具（GUI）ETL Administrator和ETL Monitor对ETL任务进行管理、监控添加/删除/修改ETL server（可选）、子系统（可选）、任务定义任务之间的关系、任务组、日历等任务定时ETL日志清理日志文件清理日志记录
6、清理ETL任务讯息通知邮件通知手机短信通知ETL远程访问管理,Perl对数据库的访问,ETL Automation目录架构,/ETL （Automation 主目录）|-/APP 存放ETL任务脚本。在此目录下先建子系统目录，然后建ETL任务目录|-/DATA|-/complete存放已成功执行过的数据. 以系统名称以及日期来建立子目录|-/fail|-/bypass存放不须执行的档案.以系统名称以及日期来建立子目录 |-/corrupt存放大小不符合的档案. 以系统名称以及日期来建立子目录|-/duplicate存放重复接收的档案. 以系统名称以及日期来建立子目录|-/error存放执行作业
7、过程中产生错误的档案. 以系统名称以及日期来建立子目录 |-/unknown存放未定义在 ETL Automation 机制中的档案. 以日期来建立子目录 |-/message存放要发送讯息通知的控制文件 |-/process存放正在执行中的作业所使用的数据文件及控制档 |-/queue存放准备要执行的作业所使用的数据文件及控制档 |-/receive用来接收各来源系统所传送来的数据文件及控制文件 |-/LOG存放 ETL Automation 系统程序及各作业在执行时所产生的记录档案 |-/bin存放 ETL Automation 系统程序的执行文件 |-/etc存放 ETL Automat
8、ion 机制的一些设定档案 |-/lock存放 ETL Automation 系统程序及各作业在执行时所产生的 lock 档案 |-/tmp临时缓冲目录，存放临时文件,ETL Automation服务,ETL Automation处理流程,ETL 管理及组件,ETL 管理自动流程管理：附以开发的ETL程序及ETL Automation产品进行自动的ETL流程管理Automation是调度工具，不能自动捕获源数据文件建议开发的ETL程序包括（1）、源文件处理程序，负责生成控制文件，传递给Automation进行任务提交；（2）、ETL错误处理及监控程序，对ETL任务进行监控及对异常的任务进行自动
9、处理、不能处理的任务告警手工维护：维护人员通过ETL Administrator/Monitor或直接访问ETL Repository/ETL 服务器进行管理、维护ETL 组件ETL AutomationETL ServiceETL 目录架构ETL RepositoryETL EnvironmentETL Administrator工具（GUI）ETL Monitor工具（GUI）ETL任务脚本（ETL Job Scripts）用户自定义ETL程序（User Defined ETL Program）ETL Server/Client服务器,ETL 管理示意图,议题,ETL 介绍ETL Auto
10、mation说明ETL 开发ETL 管理维护ETL人员职责与技能要求,开发前的必要准备脚本生成器ETL任务分类异常监控及错误处理开发任务开发示例-CSDCC ETL任务开发说明,开发前的必要准备,安装Perl软件及DBI、DBI-ODBC插件安装Teradata TTU工具配置Teradata ODBC数据源配置hosts文件创建ETL Automation目录设置ETL Automation环境变量拷贝相关的ETL程序到$auto_homebin目录配置ETL 服务（可选）,必须完成以下操作：,Scripts Generators（脚本生成器）,尽可能的情况下，使用任务脚本生成器进行任务开发
11、ETL开发人员要逐步了解并能够独立开发脚本生成器脚本生成器分为：加载任务脚本生成器生成加载（Load）任务脚本转换任务脚本生成器生成转换（Transform）任务脚本备份任务脚本生成器生成备份（Backup）任务脚本脚本生成器高效的生成Perl代码脚本并存放在Automation相应目录下生成的Perl代码脚本格式统一，有助于维护并增强安全性对生成的Perl代码脚本可以自定义修改，一般情况下加载任务脚本不需要修改,ETL任务分类,ETL加载任务将源数据文件加载到Teradata数据仓库调用Fastload/Multiload/Tpum/TPT等工具如果数据源比较复杂，需要调用Inmod/Axs
12、mod等API接口ETL转换任务将源表（Source tables）数据根据业务需求进行转换加载到目标表（Target tables）中调用Bteq工具如果转换比较复杂，可以考虑使用UDFETL导出任务将数据仓库表数据导出以供接收方使用调用Fastexport工具如果对导出要求较高,需要调用Outmod/Preprocessor等接口ETL备份任务备份ETL数据及数据仓库表到磁带库磁带调用Netvault工具,异常监控及错误处理开发,异常监控程序开发监控源数据传输状态监控ETL任务状态异常监控页面开发监控ETL流程加载情况进行错误报警ETL错误处理程序开发自动处理ETL加载失败的任务,任务开发
13、示例-CSDCC ETL任务开发说明,ETL任务开发ETL任务开发流程ETL源数据加载任务开发ETL转换任务开发标准加载算法历史表加载算法任务映射文档sdm excel工具模板ETL任务测试ETL开发、测试总结ETL程序开发ETL开发成果归档,ETL任务开发,ETL加载任务开发二进制文件加载任务以dds文件为核心的开发模式使用加载任务脚本生成器生成加载任务脚本其他文件加载任务使用加载任务脚本链接生成器直接生成加载任务脚本ETL转换任务开发使用sdm excel集成工具进行开发便捷的生成源表、目标表的字段映射关系，生成ETL任务的sdm文档便捷的将映射关系插入到ETL映射表中，生成ETL映射元数
14、据便捷的根据ETL映射元数据生成ETL转换任务脚本，自动存放在ETL相应目录下对较复杂的ETL任务脚本根据业务需求做少许的修改,ETL任务开发流程,ETL源数据加载任务开发,根据源数据结构生成dds文件dds文件格式ASCII文件dds文件内容包含表名称、字段名称、字段长度、数值型字段长度、数值型小数位数、字段中文名称等7列列之间使用Tab键分隔HCGDATV1R0_CGBJJLPF.dds文件示例：,ETL加载任务开发,使用脚本生成器生成加载任务脚本Usage: scriptmap.pl SYS JOBDIR DBNAME TABLE DDSFILESYS：子系统名称JOBDIR：任务路径名
15、称DBNAME：source table 所在库名TABLE：source table nameDDSFILE: dds file name示例： scriptmap.pl SHH HCGDATV1R0_CGBJJLPF dvs0ddl HCGDATV1R0_CGBJJLPF HCGDATV1R0_CGBJJLPF.dds生成源文件结构生成三个加载任务脚本hcgdatv1r0_cgbjjlpf0090.pl、hcgdatv1r0_cgbjjlpf0100.pl和hcgdatv1r0_cgbjjlpf0200.pl，脚本自动存放在$AUTO_HOMEAPPSHHHCGDATV1R0_CGBJJL
16、PFbin目录下生成一个源数据结构文件hcgdatv1r0_cgbjjlpf.map，自动存放在$AUTO_HOMEAPPSHHHCGDATV1R0_CGBJJLPFddl目录下,Fastload语法,Fastload SyntaxSet record ;Tenacity ;Sleep ;Session ;Logon ;Begin loading ERRORFILES ;Define INMOD(FILE)=;Insert values ();End loading;Logoff;,Fastload示例,Fasload ExampleLOGON educ2/user14,ziplock;BEG
17、IN LOADING AccountsERRORFILES Accts_Err1, Accts_Err2 ;DEFINE in_Acctno(CHAR(9) , in_Trnsdate(CHAR(10) , in_Balcurr(CHAR(7) , in_Balfwd(INTEGER) , in_Status(CHAR(10) FILE = INFILE;INSERT INTO Accounts (Account_Number,Account_Status,Trans_Date,Balance_Forward ,Balance_Current)VALUES ( :in_Acctno,:in_S
18、tatus,:in_Trnsdate (Format YYYY-MM-DD),:in_Balfwd,:in_Balcurr);END LOADING;LOGOFF;,ETL转换任务开发,标准加载算法说明转换任务脚本生成器说明genhis200.exe -生成历史表加载脚本gentbl200.exe -生成除历史表外的加载脚本使用sdm excel集成工具进行任务开发任务映射文档说明源表、目标表字段映射关系执行” 生成脚本”语句生成任务脚本,标准加载算法,算法代码算法名称 算法说明0其它加载方式 手工书写sql 1全表覆盖 delete/insert2直接追加 insert where 3历史表
19、加载 历史表加载算法4主表、历史表加载 主表、历史表加载算法5主表加载 delete where 主键 in()/insert6初始、比对加载 初始加载：insert 每日加载：insert minus ,历史表加载算法,2个重要概念开始日期：数据开始存在的日期结束日期：数据消失（或失效）的日期记录了数据的变动轨迹初始加载Insert开始日期为加载日期或最小日期（mindate)19800101结束日期为最大日期（maxdate）30001231日常加载新增数据直接插入目标表s_date=加载日期，e_date=最大日期消失或失效数据更新目标表结束日期字段值e_date=加载日期,历史表加载算
20、法示例,历史表脚本生成器,genhis200.exe -历史表脚本生成器Usage: genhis200.exe SUBSYS JOBDIR DBNAME HISTABLE LOADMODE MASTABLE# Function: 拉链历史表数据转换脚本生成# Usage : 使用参数# SUBSYS-ETL自动流程所定义的子系统名，格式XXX# JOBDIR-历史表数据转换任务名# DBNAME-历史表所在数据库名# HISTABLE-历史表名# LOADMODE-目标表加载方式# 1:全表覆盖 2:直接追加 3:插入历史表 4:插入历史表和主表0:其它# MASTABLE-主表名(该参数若
21、省略，则无主表)生成网站历史表加载任务clp_pty_mgr_dtl_src_his脚本示例：genhis200.exe CLP clp_pty_mgr_dtl_src_his dvpdata clp_pty_mgr_dtl_src_his 3 脚本名称clp_pty_mgr_dtl_src_his0200.pl脚本自动存放在$AUTO_HOMEAPPCLP clp_pty_mgr_dtl_src_hisbin目录,除历史表外的加载脚本生成器,gentbl200.exe -除历史表外的加载脚本生成器Usage: gen200.pl SUBSYS JOBDIR DBNAME HISTABLE L
22、OADMODE_D DATEFLD# Function: 目标表数据转换脚本生成# Usage : 使用参数# SUBSYS-ETL自动流程所定义的子系统名，格式XXX# JOBDIR-目标表数据转换任务名# DBNAME-目标表所在数据库名# HISTABLE-目标表名# LOADMODE_D-目标表日常加载方式# 【1:全表覆盖 2:直接追加 5:插入主表 6:初始比对 0:其它】# DATEFLD-加载方式为2、6时的“日期修改字段”生成深圳数据表act_s_acct_mdf_src示例（加载算法为直接追加）：gentbl200.exe ACT act_s_acct_mdf_src dv
23、pdata act_s_acct_mdf_src 2 upd_date脚本名称act_s_acct_mdf_src 0200.pl脚本自动存放在$AUTO_HOMEAPPCLP act_s_acct_mdf_src bin目录,任务映射文档,文档名称:DEV_datamapping-文档建立日期.xls记录了目标表、源表之间的表级映射关系记录了目标表的业务主题、加载算法及加载说明记录了目标表的加载频率包含了众多的转换任务,sdm excel工具模板（一）,为Excel文件包含3个excel sheet制作DataMap生成源表、目标表字段的映射关系DataMap任务存放从”任务映射文档”中拷贝
24、过来的表级映射关系制作脚本存放“生成脚本”语句包含定制VB代码宏”宏:加载导入mapping表”数据库登录框,sdm excel工具模板（二）,Mapping（映射）框4个文本框目标库(pdata)：目标表所在的库源库(s0ddl)：源表所在的库“选择任务：（输入rowid)”：”DataMap任务”sheet下任务映射所在的行号”目标表名:”:目标表名称，即从映射关系表中获取此目标表的字段映射8个命令按钮退出获取/维护Mapping信息：获取“目标库(pdata)”、”源库(s0ddl)“下的目标表、源表结构并显示在”制作DataMap”sheet；目标表结构显示在左上方，源表结构显示在右下
25、方“插入mapping表数据到数据库：”：将”制作DataMap”sheet中的字段映射插入到数据库映射关系表中“清除相同的mapping表：”：将数据库映射关系表中目标表的字段映射记录删除Get mapping from DB:从数据库映射关系表中获取”目标表名:”文本框中的目标表记录，并显示在”制作DataMap”sheet生成script脚本：在”制作脚本”sheet下产生“生成脚本”语句命令执行脚本生成：执行”制作脚本”sheet下“生成脚本”语句，生成转换任务脚本执行结果日志：查看脚本生成过程日志,“Mapping（映射）框”图,源表目标表字段映射关系evt_h_nontran_a_
26、src示例,将sdm excel工具模板etl_datamapping_module.xls另存为act_s_acct_std_rtn_src.xls双击act_s_acct_std_rtn_src.xls，点击启用宏从Dev_Datamapping-20060801.xls（任务映射关系）文档中拷贝act_s_acct_std_rtn_src表映射到” DataMap任务“sheet下第2行,点击”宏：加载导入Mapping表”，登录数据库，进入mapping框界面，填写文本框：目标库(pdata)=dvpdata、源库(s0ddl)=dvs0ddl、“选择任务：（输入rowid)”=2，出
27、现”目标表： act_s_acct_std_rtn_src”,点击”获取/维护Mapping信息“，在”制作DataMap“sheet中生成目标表、源表的字段信息；对目标表和源表的字段进行映射映射完成后，点击“插入mapping表数据到数据库：”将字段映射关系插入到数据库映射关系表中,产生” 生成脚本”语句,点击”生成scripts脚本”按钮，在“制作脚本”sheet中生成“生成脚本语句”,转换任务脚本生成及日志查看,点击”执行脚本生成”按钮，ETL脚本生成器读取数据库映射表数据自动生成转换任务脚本点击”执行结果日志”按钮，查看任务脚本生成情况,ETL任务测试,源数据加载任务测试语法测试数据检
28、查转换任务测试语法测试业务规则检查数据检查备份任务测试语法测试测试结果ETL流程测试针对于批量上线的任务使用ETL AUTOMATION调度单个任务不需要流程测试,源数据加载任务测试,语法测试执行命令：”脚本所在绝对路径”+脚本名称 dir+”.”+脚本任务名称+数据日期示例：D:dwETLAPPSHZSAGDTALIB_ZHGFHBLbinsagdtalib_zhgfhbl0100.pl dir.sagdtalib_zhgfhbl20080228数据检查表与源文件记录数是否一致是否有乱码重点检查字段日期字段数值型字段中文字段,转换任务测试,语法测试执行命令：”脚本所在绝对路径”+脚本名称 d
29、ir+”.”+脚本任务名称+数据日期示例：D:dwETLAPPACTACT_S_ACCT_STD_RTN_SRCbinact_s_acct_std_rtn_src0200.pl dir. act_s_acct_std_rtn_src20080228业务规则检查与设计文档核对数据检查目标表数据与源表数据在某一时间（内）业务规则上的记录数是否一致是否有乱码重点检查字段日期字段数值型字段中文字段源表与目标表字段类型不一致的字段,测试文档,源数据加载任务测试文档转换任务测试文档,ETL开发、测试总结,基于ETL脚本生成器的高效、准确的开发模式高度集成的ETL转换任务开发架构便捷的生成sdm便捷的生成任
30、务脚本分批次的ETL测试开发人员测试开发人员交叉测试ETL负责人员测试客户上线测试,ETL程序开发,INMOD程序开发Fastload调用INMOD程序将数据源文件直接加载至数据仓库使用C语言开发EMBEDDED-C程序开发将数据仓库表数据导出为dbf文件使用C语言开发业务报表程序开发执行配置sql文件，将数据仓库表数据生成word报表使用VB程序开发ETL服务程序开发根据ETL流程需要及业务需求开发程序使用perl语言开发,已开发的ETL程序,Visual SourceSafe（VSS）介绍,VSS是微软公司发布的版本管理器产品简单易用、方便高效并与Windows操作系统及微软开发工具高度集
31、成 完善的版本、安全保护和跟踪检查功能保存文件的多个版本，包括文件版本之间每一处微小的变动 签出（check out）文档，避免多人同时修改文档，保证文档的安全性 对源代码和其他文件进行存储和早期版本的追踪 只支持Windows平台需要快速大量的信息交换，因此仅适用于快速本地网络，而无法实现基于Web的快速操作,ETL开发成果归档,CSDCC项目使用VSS做为项目版本管理器，项目文档存放在VSS服务器上ETL开发、测试完成后，必须将所有开发成果归档到VSSdds文件sdm文件ETL任务脚本加载任务脚本转换任务脚本备份任务脚本ETL程序ETL相关的测试文档,议题,ETL 介绍ETL Automa
32、tion说明ETL 开发ETL 管理维护ETL人员职责与技能要求,ETL 任务管理ETL 加载监控/错误处理ETL 加载服务器管理主索引调整、分区及二级索引创建SQL优化（脚本优化）,ETL任务管理,使用ETL Administrator或执行SQL语句对ETL任务进行管理、维护增/删/改ETL Server信息，可选择增/删/改ETL Sys，可选择增/删/改ETL job定义增/删/改ETL job source定义增/删/改ETL dependency，可选择增/删/改ETL stream，可选择增/删/改ETL related，可选择增/删/改ETL 任务calendar，可选择增/删
33、/改ETL任务组，可选择增/删/改ETL 任务至任务组，可选择,ETL 加载监控/错误处理,数据仓库系统ETL监控Web页面ETL人员开发的加载流程监控界面包含了所有数据源加载流程的各个阶段记录了流程各个阶段的完成时间、运行状态(完成、未完成、错误)等使用不同的颜色标识各个阶段的各个运行状态绿色：流程正常完成黄色：流程未完成红色：流程出现错误监控数据库空间使用情况点击各个阶段的链接，可以查看任务或数据库空间的详细信息错误报警显示数据源文件错误的详细信息显示出错任务的详细信息显示数据库空间不足错误显示联系人员信息使用ETL Administrator/ ETL Monitor进行加载监控、错误处
34、理,数据仓库系统ETL流程监控图,ETL加载服务器管理,ETL加载服务器分类生产系统数据ETL加载服务器生产系统数据ETL备份服务器其他用途的ETL加载服务器管理加载服务器空间监控定时对磁盘空间监控数据备份、清理监控ETL服务监控ETL失败任务处理,主索引调整、分区及二级索引创建,数据仓库数据量随每日ETL加载而增大，为节约数据库空间及保证查询、加载的效率，需要考虑对一些表的主索引进行调整以及创建分区主索引或二级索引主索引的选择标准经常访问的字段取值唯一的字段值稳定的字段综合以上三点考虑，选择主索引分区主索引对表的部分数据进行扫描，为了提高查询效率通常选择日期或地区做为分区主索引二级主索引为了
35、提高查询效率通常选择经常访问的字段做为二级主索引示例:上海交易过户表采用”股东帐户+交易日期”做为主索引(保证取值唯一性)、使用交易日期做为分区主索引(保证查询、关联效率)、使用股东帐户做为二级主索引(保证查询、关联效率)，这样既节省了实际可用空间，又提高了查询效率,SQL优化（脚本优化）,保证加载、查询效率、降低数据库(SPOOL)空间使用经常采用的优化方法：收集（临时）表统计信息拆分冗长的SQL语句，创建临时表（注意临时表的PI选择），多步实现去除不必要（使用）的字段，只选择有用字段对于汇总（sum）关联语句，一般情况下，先进行汇总，然后关联表的PPI字段，尽可能的使用常量，不使用关联。常
36、见的情况：将一条记录放入临时表中，使用此临时表字段与其他表的PPI字段关联对PPI字段的表进行全表扫描，执行效率极差将此条记录取出，然后做为常量和表的PPI字段连接对PPI字段的表部分记录扫描，执行效率极高,数据库用户管理,角色(role)&profile角色是一系列权限的集合为不同功能用户创建不同的角色Profile是用户参数的集合Account id(s)Default databaseSpool space allocationTemporary space allocationPassword attributes (expiration, etc.)角色和profile简化、方便用户
37、的访问权限及参数管理，提高数据库的运行效率创建、修改、清空及删除用户用户的权限管理,角色(role)管理创建、删除角色语法: CREATE ROLE role_name;语法: DROP ROLE role_name;为角色赋权或取消权限语法: GRANT access_rights TO role_name;语法: REVOKE access_rights TO role_name;将角色赋权或取消给用户或角色语法: GRANT role_name TO user_namerole_name;语法: REVOKE role_name TOFROM user_namerole_name;Pro
38、file创建、修改、删除profile语法: CREATEMODIFY PROFILE profile_name AS ;语法: DROP PROFILE profile_name;将profile赋给（关联）相关用户语法: CREATEMODIFY USER user_name AS , PROFILE = profile_name; 角色(role)&profile的管理也可以通过Terdata Administrator图形界面操作实现,角色(role)&profile,议题,ETL 介绍ETL Automation说明ETL 开发ETL 管理维护ETL人员职责与技能要求,ETL人员职责技能要求,ETL人员职责领域,能够捕获并把握ETL需求能够设计、开发以及测试从旧版本系统或生产系统抽取数据的程序 能够设计、开发以及测试在数据加载入库前对数据验证和加工处理的程序能够设计、开发以及测试加载初始数据和日常数据的程序 能够为数据的及时性和传输频率设计符合SLA的流程 能够决定哪些数据是历史数据以及这些历史数据的保存周期 能够决定数据的增长趋势和业务峰值周期,ETL人员技能要求,精通Teradata数据库精通ETL tools,THANK YOU!Q&A,
