TD员工入职技术培训之ETL开发与维护.ppt

TeradataTraineeTechnicalBookletsCourse5ETL开发与维护,By：LiErniu,议题,ETL介绍ETLAutomation说明ETL开发ETL管理维护ETL人员职责与技能要求,议题,ETL介绍ETLAutomation说明ETL开发ETL管理维护ETL人员职责与技能要求,ETL定义ETL/ELT/ETLT-总结ETL流程概述ETL技术架构,ETL定义,ETL抽取(Extract)、转换(Transform)和加载(Load)TeradatauseE-L-Tframework抽取(Extract)从数据库、磁带或其它各种介质抽取数据或由数据提供方主动提供数据加载(Load)将数据源文件加载至数据仓库(Teradata)使用fastload/multiload/tpump/TPTinmod/axsmod转换(Transform)将源表数据根据业务需要转换成目标表数据使用bteqSQL（SELECT/DELETE/INSERT/UPDATE）,ETLvs.EtLTvs.ELT总结,TeradataETL流程简要说明,ETL流程是数据触发的、全自动的数据流过程；ETL流程是将数据抽取、转换和装载到数据仓库的一个数据流的过程，它定义了对众多数据处理的顺序性、依赖性及触发性等关系；使用ETLAUTOMATION进行统一的管理、调度；使用ETL监控服务进行流程监控并处理异常情况；一般情况下，ETL流程包括数据源准备、加载、转换、数据导出和数据备份等几部分。,CSDCCETL流程图,TeradataETL技术架构,议题,ETL介绍ETLAutomation说明ETL开发ETL管理维护ETL人员职责与技能要求,ETLAutomation简介Perl对数据库的访问ETLAutomation目录架构ETLAutomation服务ETLAutomation处理流程ETL管理及组件ETL管理示意图,ETLAutomation简介,Teradata公司开发的ETL流程调度产品（product）；使用Perl程序开发，运行环境需要安装Perl软件及DBI、DBD插件；C/S服务器架构ETLAutomaiton由以下几部分组成：ETLAutomation服务ETLAutomation目录架构ETLAutomation知识库ETLAutomation环境变量ETLAdministrator管理工具（GUI）ETLmonitor监控工具（GUI）ETLAutomation任务调度：定义了目录架构及知识库目录架构下不同的目录用于不同的目的知识库中不同的表用于不同的目的定义了目录架构下部分目录与知识库中相应表的对应关系规则目录中的任务名称与知识库任务表中相应任务名称必须相同目录中的日志文件名称与知识库日志表中相应日志名称必须相同定义了控制文件的命名规则控制文件作为Automation调度任务的必需条件之一ETLAutomation服务使用环境变量、控制文件和知识库对ETL任务进行调度,ETLAutomation简介,ETLAutomation功能说明ETL任务的自动化调度ETL任务管理一般情况下使用图形界面工具（GUI）ETLAdministrator和ETLMonitor对ETL任务进行管理、监控添加/删除/修改ETLserver（可选）、子系统（可选）、任务定义任务之间的关系、任务组、日历等任务定时ETL日志清理日志文件清理日志记录清理ETL任务讯息通知邮件通知手机短信通知ETL远程访问管理,Perl对数据库的访问,ETLAutomation目录架构,/ETL（Automation主目录）|-/APP存放ETL任务脚本。在此目录下先建子系统目录，然后建ETL任务目录|-/DATA|-/complete存放已成功执行过的数据.以系统名称以及日期来建立子目录|-/fail|-/bypass存放不须执行的档案.以系统名称以及日期来建立子目录|-/corrupt存放大小不符合的档案.以系统名称以及日期来建立子目录|-/duplicate存放重复接收的档案.以系统名称以及日期来建立子目录|-/error存放执行作业过程中产生错误的档案.以系统名称以及日期来建立子目录|-/unknown存放未定义在ETLAutomation机制中的档案.以日期来建立子目录|-/message存放要发送讯息通知的控制文件|-/process存放正在执行中的作业所使用的数据文件及控制档|-/queue存放准备要执行的作业所使用的数据文件及控制档|-/receive用来接收各来源系统所传送来的数据文件及控制文件|-/LOG存放ETLAutomation系统程序及各作业在执行时所产生的记录档案|-/bin存放ETLAutomation系统程序的执行文件|-/etc存放ETLAutomation机制的一些设定档案|-/lock存放ETLAutomation系统程序及各作业在执行时所产生的lock档案|-/tmp临时缓冲目录，存放临时文件,ETLAutomation服务,ETLAutomation处理流程,ETL管理及组件,ETL管理自动流程管理：附以开发的ETL程序及ETLAutomation产品进行自动的ETL流程管理Automation是调度工具，不能自动捕获源数据文件建议开发的ETL程序包括（1）、源文件处理程序，负责生成控制文件，传递给Automation进行任务提交；（2）、ETL错误处理及监控程序，对ETL任务进行监控及对异常的任务进行自动处理、不能处理的任务告警手工维护：维护人员通过ETLAdministrator/Monitor或直接访问ETLRepository/ETL服务器进行管理、维护ETL组件ETLAutomationETLServiceETL目录架构ETLRepositoryETLEnvironmentETLAdministrator工具（GUI）ETLMonitor工具（GUI）ETL任务脚本（ETLJobScripts）用户自定义ETL程序（UserDefinedETLProgram）ETLServer/Client服务器,ETL管理示意图,议题,ETL介绍ETLAutomation说明ETL开发ETL管理维护ETL人员职责与技能要求,开发前的必要准备脚本生成器ETL任务分类异常监控及错误处理开发任务开发示例-CSDCCETL任务开发说明,开发前的必要准备,安装Perl软件及DBI、DBI-ODBC插件安装TeradataTTU工具配置TeradataODBC数据源配置hosts文件创建ETLAutomation目录设置ETLAutomation环境变量拷贝相关的ETL程序到$auto_homebin目录配置ETL服务（可选）,必须完成以下操作：,ScriptsGenerators（脚本生成器）,尽可能的情况下，使用任务脚本生成器进行任务开发ETL开发人员要逐步了解并能够独立开发脚本生成器脚本生成器分为：加载任务脚本生成器生成加载（Load）任务脚本转换任务脚本生成器生成转换（Transform）任务脚本备份任务脚本生成器生成备份（Backup）任务脚本脚本生成器高效的生成Perl代码脚本并存放在Automation相应目录下生成的Perl代码脚本格式统一，有助于维护并增强安全性对生成的Perl代码脚本可以自定义修改，一般情况下加载任务脚本不需要修改,ETL任务分类,ETL加载任务将源数据文件加载到Teradata数据仓库调用Fastload/Multiload/Tpum/TPT等工具如果数据源比较复杂，需要调用Inmod/Axsmod等API接口ETL转换任务将源表（Sourcetables）数据根据业务需求进行转换加载到目标表（Targettables）中调用Bteq工具如果转换比较复杂，可以考虑使用UDFETL导出任务将数据仓库表数据导出以供接收方使用调用Fastexport工具如果对导出要求较高,需要调用Outmod/Preprocessor等接口ETL备份任务备份ETL数据及数据仓库表到磁带库磁带调用Netvault工具,异常监控及错误处理开发,异常监控程序开发监控源数据传输状态监控ETL任务状态异常监控页面开发监控ETL流程加载情况进行错误报警ETL错误处理程序开发自动处理ETL加载失败的任务,任务开发示例-CSDCCETL任务开发说明,ETL任务开发ETL任务开发流程ETL源数据加载任务开发ETL转换任务开发标准加载算法历史表加载算法任务映射文档sdmexcel工具模板ETL任务测试ETL开发、测试总结ETL程序开发ETL开发成果归档,ETL任务开发,ETL加载任务开发二进制文件加载任务以dds文件为核心的开发模式使用加载任务脚本生成器生成加载任务脚本其他文件加载任务使用加载任务脚本链接生成器直接生成加载任务脚本ETL转换任务开发使用sdmexcel集成工具进行开发便捷的生成源表、目标表的字段映射关系，生成ETL任务的sdm文档便捷的将映射关系插入到ETL映射表中，生成ETL映射元数据便捷的根据ETL映射元数据生成ETL转换任务脚本，自动存放在ETL相应目录下对较复杂的ETL任务脚本根据业务需求做少许的修改,ETL任务开发流程,ETL源数据加载任务开发,根据源数据结构生成dds文件dds文件格式ASCII文件dds文件内容包含表名称、字段名称、字段长度、数值型字段长度、数值型小数位数、字段中文名称等7列列之间使用Tab键分隔HCGDATV1R0_CGBJJLPF.dds文件示例：,ETL加载任务开发,使用脚本生成器生成加载任务脚本Usage:scriptmap.plSYSJOBDIRDBNAMETABLEDDSFILESYS：子系统名称JOBDIR：任务路径名称DBNAME：sourcetable所在库名TABLE：sourcetablenameDDSFILE:ddsfilename示例：scriptmap.plSHHHCGDATV1R0_CGBJJLPFdvs0ddlHCGDATV1R0_CGBJJLPFHCGDATV1R0_CGBJJLPF.dds生成源文件结构生成三个加载任务脚本hcgdatv1r0_cgbjjlpf0090.pl、hcgdatv1r0_cgbjjlpf0100.pl和hcgdatv1r0_cgbjjlpf0200.pl，脚本自动存放在$AUTO_HOMEAPPSHHHCGDATV1R0_CGBJJLPFbin目录下生成一个源数据结构文件hcgdatv1r0_cgbjjlpf.map，自动存放在$AUTO_HOMEAPPSHHHCGDATV1R0_CGBJJLPFddl目录下,Fastload语法,FastloadSyntaxSetrecord;Tenacity;Sleep;Session;Logon;BeginloadingERRORFILES;DefineINMOD(FILE)=;Insertvalues();Endloading;Logoff;,Fastload示例,FasloadExampleLOGONeduc2/user14,ziplock;BEGINLOADINGAccountsERRORFILESAccts_Err1,Accts_Err2;DEFINEin_Acctno(CHAR(9),in_Trnsdate(CHAR(10),in_Balcurr(CHAR(7),in_Balfwd(INTEGER),in_Status(CHAR(10)FILE=INFILE;INSERTINTOAccounts(Account_Number,Account_Status,Trans_Date,Balance_Forward,Balance_Current)VALUES(:in_Acctno,:in_Status,:in_Trnsdate(FormatYYYY-MM-DD),:in_Balfwd,:in_Balcurr);ENDLOADING;LOGOFF;,ETL转换任务开发,标准加载算法说明转换任务脚本生成器说明genhis200.exe-生成历史表加载脚本gentbl200.exe-生成除历史表外的加载脚本使用sdmexcel集成工具进行任务开发任务映射文档说明源表、目标表字段映射关系执行”生成脚本”语句生成任务脚本,标准加载算法,算法代码算法名称算法说明0其它加载方式手工书写sql1全表覆盖delete/insert2直接追加insertwhere3历史表加载历史表加载算法4主表、历史表加载主表、历史表加载算法5主表加载deletewhere主键in()/insert6初始、比对加载初始加载：insert每日加载：insertminus,历史表加载算法,2个重要概念开始日期：数据开始存在的日期结束日期：数据消失（或失效）的日期记录了数据的变动轨迹初始加载Insert开始日期为加载日期或最小日期（mindate)19800101结束日期为最大日期（maxdate）30001231日常加载新增数据直接插入目标表s_date=加载日期，e_date=最大日期消失或失效数据更新目标表结束日期字段值e_date=加载日期,历史表加载算法示例,历史表脚本生成器,genhis200.exe-历史表脚本生成器Usage:genhis200.exeSUBSYSJOBDIRDBNAMEHISTABLELOADMODEMASTABLE#Function:拉链历史表数据转换脚本生成#Usage:使用参数#SUBSYS-ETL自动流程所定义的子系统名，格式XXX#JOBDIR-历史表数据转换任务名#DBNAME-历史表所在数据库名#HISTABLE-历史表名#LOADMODE-目标表加载方式#1:全表覆盖2:直接追加3:插入历史表4:插入历史表和主表0:其它#MASTABLE-主表名(该参数若省略，则无主表)生成网站历史表加载任务clp_pty_mgr_dtl_src_his脚本示例：genhis200.exeCLPclp_pty_mgr_dtl_src_hisdvpdataclp_pty_mgr_dtl_src_his3脚本名称clp_pty_mgr_dtl_src_his0200.pl脚本自动存放在$AUTO_HOMEAPPCLPclp_pty_mgr_dtl_src_hisbin目录,除历史表外的加载脚本生成器,gentbl200.exe-除历史表外的加载脚本生成器Usage:gen200.plSUBSYSJOBDIRDBNAMEHISTABLELOADMODE_DDATEFLD#Function:目标表数据转换脚本生成#Usage:使用参数#SUBSYS-ETL自动流程所定义的子系统名，格式XXX#JOBDIR-目标表数据转换任务名#DBNAME-目标表所在数据库名#HISTABLE-目标表名#LOADMODE_D-目标表日常加载方式#【1:全表覆盖2:直接追加5:插入主表6:初始比对0:其它】#DATEFLD-加载方式为2、6时的“日期修改字段”生成深圳数据表act_s_acct_mdf_src示例（加载算法为直接追加）：gentbl200.exeACTact_s_acct_mdf_srcdvpdataact_s_acct_mdf_src2upd_date脚本名称act_s_acct_mdf_src0200.pl脚本自动存放在$AUTO_HOMEAPPCLPact_s_acct_mdf_srcbin目录,任务映射文档,文档名称:DEV_datamapping-文档建立日期.xls记录了目标表、源表之间的表级映射关系记录了目标表的业务主题、加载算法及加载说明记录了目标表的加载频率包含了众多的转换任务,sdmexcel工具模板（一）,为Excel文件包含3个excelsheet制作DataMap生成源表、目标表字段的映射关系DataMap任务存放从”任务映射文档”中拷贝过来的表级映射关系制作脚本存放“生成脚本”语句包含定制VB代码宏”宏:加载导入mapping表”数据库登录框,sdmexcel工具模板（二）,Mapping（映射）框4个文本框目标库(pdata)：目标表所在的库源库(s0ddl)：源表所在的库“选择任务：（输入rowid)”：”DataMap任务”sheet下任务映射所在的行号”目标表名:”:目标表名称，即从映射关系表中获取此目标表的字段映射8个命令按钮退出获取/维护Mapping信息：获取“目标库(pdata)”、”源库(s0ddl)“下的目标表、源表结构并显示在”制作DataMap”sheet；目标表结构显示在左上方，源表结构显示在右下方“插入mapping表数据到数据库：”：将”制作DataMap”sheet中的字段映射插入到数据库映射关系表中“清除相同的mapping表：”：将数据库映射关系表中目标表的字段映射记录删除GetmappingfromDB:从数据库映射关系表中获取”目标表名:”文本框中的目标表记录，并显示在”制作DataMap”sheet生成script脚本：在”制作脚本”sheet下产生“生成脚本”语句命令执行脚本生成：执行”制作脚本”sheet下“生成脚本”语句，生成转换任务脚本执行结果日志：查看脚本生成过程日志,“Mapping（映射）框”图,源表目标表字段映射关系evt_h_nontran_a_src示例,将sdmexcel工具模板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，出现”目标表：act_s_acct_std_rtn_src”,点击”获取/维护Mapping信息“，在”制作DataMap“sheet中生成目标表、源表的字段信息；对目标表和源表的字段进行映射映射完成后，点击“插入mapping表数据到数据库：”将字段映射关系插入到数据库映射关系表中,产生”生成脚本”语句,点击”生成scripts脚本”按钮，在“制作脚本”sheet中生成“生成脚本语句”,转换任务脚本生成及日志查看,点击”执行脚本生成”按钮，ETL脚本生成器读取数据库映射表数据自动生成转换任务脚本点击”执行结果日志”按钮，查看任务脚本生成情况,ETL任务测试,源数据加载任务测试语法测试数据检查转换任务测试语法测试业务规则检查数据检查备份任务测试语法测试测试结果ETL流程测试针对于批量上线的任务使用ETLAUTOMATION调度单个任务不需要流程测试,源数据加载任务测试,语法测试执行命令：”脚本所在绝对路径”+脚本名称dir+”.”+脚本任务名称+数据日期示例：D:dwETLAPPSHZSAGDTALIB_ZHGFHBLbinsagdtalib_zhgfhbl0100.pldir.sagdtalib_zhgfhbl20080228数据检查表与源文件记录数是否一致是否有乱码重点检查字段日期字段数值型字段中文字段,转换任务测试,语法测试执行命令：”脚本所在绝对路径”+脚本名称dir+”.”+脚本任务名称+数据日期示例：D:dwETLAPPACTACT_S_ACCT_STD_RTN_SRCbinact_s_acct_std_rtn_src0200.pldir.act_s_acct_std_rtn_src20080228业务规则检查与设计文档核对数据检查目标表数据与源表数据在某一时间（内）业务规则上的记录数是否一致是否有乱码重点检查字段日期字段数值型字段中文字段源表与目标表字段类型不一致的字段,测试文档,源数据加载任务测试文档转换任务测试文档,ETL开发、测试总结,基于ETL脚本生成器的高效、准确的开发模式高度集成的ETL转换任务开发架构便捷的生成sdm便捷的生成任务脚本分批次的ETL测试开发人员测试开发人员交叉测试ETL负责人员测试客户上线测试,ETL程序开发,INMOD程序开发Fastload调用INMOD程序将数据源文件直接加载至数据仓库使用C语言开发EMBEDDED-C程序开发将数据仓库表数据导出为dbf文件使用C语言开发业务报表程序开发执行配置sql文件，将数据仓库表数据生成word报表使用VB程序开发ETL服务程序开发根据ETL流程需要及业务需求开发程序使用perl语言开发,已开发的ETL程序,VisualSourceSafe（VSS）介绍,VSS是微软公司发布的版本管理器产品简单易用、方便高效并与Windows操作系统及微软开发工具高度集成完善的版本、安全保护和跟踪检查功能保存文件的多个版本，包括文件版本之间每一处微小的变动签出（checkout）文档，避免多人同时修改文档，保证文档的安全性对源代码和其他文件进行存储和早期版本的追踪只支持Windows平台需要快速大量的信息交换，因此仅适用于快速本地网络，而无法实现基于Web的快速操作,ETL开发成果归档,CSDCC项目使用VSS做为项目版本管理器，项目文档存放在VSS服务器上ETL开发、测试完成后，必须将所有开发成果归档到VSSdds文件sdm文件ETL任务脚本加载任务脚本转换任务脚本备份任务脚本ETL程序ETL相关的测试文档,议题,ETL介绍ETLAutomation说明ETL开发ETL管理维护ETL人员职责与技能要求,ETL任务管理ETL加载监控/错误处理ETL加载服务器管理主索引调整、分区及二级索引创建SQL优化（脚本优化）,ETL任务管理,使用ETLAdministrator或执行SQL语句对ETL任务进行管理、维护增/删/改ETLServer信息，可选择增/删/改ETLSys，可选择增/删/改ETLjob定义增/删/改ETLjobsource定义增/删/改ETLdependency，可选择增/删/改ETLstream，可选择增/删/改ETLrelated，可选择增/删/改ETL任务calendar，可选择增/删/改ETL任务组，可选择增/删/改ETL任务至任务组，可选择,ETL加载监控/错误处理,数据仓库系统ETL监控Web页面ETL人员开发的加载流程监控界面包含了所有数据源加载流程的各个阶段记录了流程各个阶段的完成时间、运行状态(完成、未完成、错误)等使用不同的颜色标识各个阶段的各个运行状态绿色：流程正常完成黄色：流程未完成红色：流程出现错误监控数据库空间使用情况点击各个阶段的链接，可以查看任务或数据库空间的详细信息错误报警显示数据源文件错误的详细信息显示出错任务的详细信息显示数据库空间不足错误显示联系人员信息使用ETLAdministrator/ETLMonitor进行加载监控、错误处理,数据仓库系统ETL流程监控图,ETL加载服务器管理,ETL加载服务器分类生产系统数据ETL加载服务器生产系统数据ETL备份服务器其他用途的ETL加载服务器管理加载服务器空间监控定时对磁盘空间监控数据备份、清理监控ETL服务监控ETL失败任务处理,主索引调整、分区及二级索引创建,数据仓库数据量随每日ETL加载而增大，为节约数据库空间及保证查询、加载的效率，需要考虑对一些表的主索引进行调整以及创建分区主索引或二级索引主索引的选择标准经常访问的字段取值唯一的字段值稳定的字段综合以上三点考虑，选择主索引分区主索引对表的部分数据进行扫描，为了提高查询效率通常选择日期或地区做为分区主索引二级主索引为了提高查询效率通常选择经常访问的字段做为二级主索