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

TeradataTraineeTechnicalBookletsCourse5 ETL开发与维护 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 home bin目录配置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 HOME APP SHH HCGDATV1R0 CGBJJLPF bin目录下生成一个源数据结构文件hcgdatv1r0 cgbjjlpf map 自动存放在 AUTO HOME APP SHH HCGDATV1R0 CGBJJLPF ddl目录下 Fastload语法 FastloadSyntaxSetrecord Tenacity Sleep Session Logon Beginloading ERRORFILES Define INMOD FILE Insert values 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 Format YYYY MM DD in Balfwd in Balcurr ENDLOADING LOGOFF ETL转换任务开发 标准加载算法说明转换任务脚本生成器说明genhis200 exe 生成历史表加载脚本gentbl200 exe 生成除历史表外的加载脚本使用sdmexcel集成工具进行任务开发任务映射文档说明源表 目标表字段映射关系执行 生成脚本 语句生成任务脚本 标准加载算法 算法代码算法名称算法说明0其它加载方式手工书写sql1全表覆盖delete insert2直接追加insertwhere 3历史表加载历史表加载算法4主表 历史表加载主表 历史表加载算法5主表加载deletewhere主键in insert6初始 比对加载初始加载 insert每日加载 insert minus 历史表加载算法 2个重要概念开始日期 数据开始存在的日期结束日期 数据消失 或失效 的日期记录了数据的变动轨迹初始加载Insert开始日期为加载日期或最小日期 mindate 19800101结束日期为最大日期 maxdate 30001231日常加载新增数据直接插入目标表s date 加载日期 e date 最大日期消失或失效数据更新目标表结束日期字段值e date 加载日期 历史表加载算法示例 历史表脚本生成器 genhis200 exe 历史表脚本生成器Usage genhis200 exeSUBSYSJOBDIRDBNAMEHISTABLELOADMODE MASTABLE 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 HOME APP CLP clp pty mgr dtl src his bin目录 除历史表外的加载脚本生成器 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 HOME APP CLP act s acct mdf src bin目录 任务映射文档 文档名称 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 dwETL APP SHZ SAGDTALIB ZHGFHBL bin sagdtalib zhgfhbl0100 pldir sagdtalib zhgfhbl20080228数据检查表与源文件记录数是否一致是否有乱码重点检查字段日期字段数值型字段中文字段 转换任务测试 语法测试执行命令 脚本所在绝对路径 脚本名称dir 脚本任务名称 数据日期示例 D dwETL APP ACT ACT S ACCT STD RTN SRC bin act 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加载而增大 为节约数据库空间及保证查询 加载的效率 需要考虑对一些表的主索引进行调整以及创建分区主索引或二级索引主索引的选择标准经常访问的字段取值唯一的字段值稳定的字段综合以上三点考虑 选择主索引分区主索引对表的部分数据进行扫描 为了提高查询效率通常选择日期或地区做为分区主索引二级主索引为了提高查询效率通常选择经常访问的字段做为二级主索引示例 上海交易过户表采用 股东帐户 交易日期 做为主索引 保证取值唯一性 使用交易日期做为分区主索引 保证查询 关联效率 使用股东帐户做为二级主索引 保证查询 关联效率 这样既节省了实际可用空间 又提高了查询效率 SQL优化 脚本优化 保证加载 查询效率 降低数据库 SPOOL 空间使用经常采用的优化方法 收集 临时 表统计信息拆分冗长的SQL语句 创建临时表 注意临时表的PI选择 多步实现去除不必要 使用 的字段 只选择有用字段对于汇总 sum 关联语句 一般