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

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) 从数据库、磁带或其它各种介质抽取数据 或由数据提供方主动提供数据 加载(Load) 将数据源文件加载至数据仓库(Teradata) 使用fastload/multiload/tpump/TPT inmod/axsmod 转换(Transform) 将源表数据根据业务需要转换成目标表数据 使用bteq SQL （SELECT/DELETE/INSERT/UPDATE）,ETL vs. EtLT vs. ELT 总结,Teradata ETL 流程简要说明,ETL流程是数据触发的、全自动的数据流过程； ETL流程是将数据抽取、转换和装载到数据仓库的一个数据流的过程，它定义了对众多数据处理的顺序性、依赖性及触发性等关系； 使用ETL AUTOMATION进行统一的管理、调度； 使用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流程调度产品（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任务调度： 定义了目录架构及知识库 目录架构下不同的目录用于不同的目的 知识库中不同的表用于不同的目的 定义了目录架构下部分目录与知识库中相应表的对应关系规则 目录中的任务名称与知识库任务表中相应任务名称必须相同 目录中的日志文件名称与知识库日志表中相应日志名称必须相同 定义了控制文件的命名规则 控制文件作为Automation调度任务的必需条件之一 ETL Automation服务使用环境变量、控制文件和知识库对ETL任务进行调度,ETL Automation简介,ETL Automation功能说明 ETL任务的自动化调度 ETL任务管理 一般情况下使用图形界面工具（GUI）ETL Administrator和ETL Monitor对ETL任务进行管理、监控 添加/删除/修改ETL server（可选）、子系统（可选）、任务 定义任务之间的关系、任务组、日历等 任务定时 ETL日志清理 日志文件清理 日志记录清理 ETL任务讯息通知 邮件通知 手机短信通知 ETL远程访问管理,Perl对数据库的访问,ETL Automation目录架构,/ETL （Automation 主目录） |-/APP 存放ETL任务脚本。在此目录下先建子系统目录，然后建ETL任务目录 |-/DATA |-/complete 存放已成功执行过的数据. 以系统名称以及日期来建立子目录 |-/fail |-/bypass 存放不须执行的档案.以系统名称以及日期来建立子目录 |-/corrupt 存放大小不符合的档案. 以系统名称以及日期来建立子目录 |-/duplicate 存放重复接收的档案. 以系统名称以及日期来建立子目录 |-/error 存放执行作业过程中产生错误的档案. 以系统名称以及日期来建立子目录 |-/unknown 存放未定义在 ETL Automation 机制中的档案. 以日期来建立子目录 |-/message 存放要发送讯息通知的控制文件 |-/process 存放正在执行中的作业所使用的数据文件及控制档 |-/queue 存放准备要执行的作业所使用的数据文件及控制档 |-/receive 用来接收各来源系统所传送来的数据文件及控制文件 |-/LOG 存放 ETL Automation 系统程序及各作业在执行时所产生的记录档案 |-/bin 存放 ETL Automation 系统程序的执行文件 |-/etc 存放 ETL Automation 机制的一些设定档案 |-/lock 存放 ETL Automation 系统程序及各作业在执行时所产生的 lock 档案 |-/tmp 临时缓冲目录，存放临时文件,ETL Automation服务,ETL Automation处理流程,ETL 管理及组件,ETL 管理 自动流程管理：附以开发的ETL程序及ETL Automation产品进行自动的ETL流程管理 Automation是调度工具，不能自动捕获源数据文件 建议开发的ETL程序包括（1）、源文件处理程序，负责生成控制文件，传递给Automation进行任务提交；（2）、ETL错误处理及监控程序，对ETL任务进行监控及对异常的任务进行自动处理、不能处理的任务告警 手工维护：维护人员通过ETL Administrator/Monitor或直接访问ETL Repository/ETL 服务器进行管理、维护 ETL 组件 ETL Automation ETL Service ETL 目录架构 ETL Repository ETL Environment ETL Administrator工具（GUI） ETL Monitor工具（GUI） ETL任务脚本（ETL Job Scripts） 用户自定义ETL程序（User Defined ETL Program） ETL Server/Client服务器,ETL 管理示意图,议题,ETL 介绍 ETL Automation说明 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（脚本生成器）,尽可能的情况下，使用任务脚本生成器进行任务开发 ETL开发人员要逐步了解并能够独立开发脚本生成器 脚本生成器分为： 加载任务脚本生成器 生成加载（Load）任务脚本 转换任务脚本生成器 生成转换（Transform）任务脚本 备份任务脚本生成器 生成备份（Backup）任务脚本 脚本生成器高效的生成Perl代码脚本并存放在Automation相应目录下 生成的Perl代码脚本格式统一，有助于维护并增强安全性 对生成的Perl代码脚本可以自定义修改，一般情况下加载任务脚本不需要修改,ETL任务分类,ETL加载任务 将源数据文件加载到Teradata数据仓库 调用Fastload/Multiload/Tpum/TPT等工具 如果数据源比较复杂，需要调用Inmod/Axsmod等API接口 ETL转换任务 将源表（Source tables）数据根据业务需求进行转换加载到目标表（Target tables）中 调用Bteq工具 如果转换比较复杂，可以考虑使用UDF ETL导出任务 将数据仓库表数据导出以供接收方使用 调用Fastexport工具 如果对导出要求较高,需要调用Outmod/Preprocessor等接口 ETL备份任务 备份ETL数据及数据仓库表到磁带库磁带 调用Netvault工具,异常监控及错误处理开发,异常监控程序开发 监控源数据传输状态 监控ETL任务状态 异常监控页面开发 监控ETL流程加载情况 进行错误报警 ETL错误处理程序开发 自动处理ETL加载失败的任务,任务开发示例-CSDCC ETL任务开发说明,ETL任务开发 ETL任务开发流程 ETL源数据加载任务开发 ETL转换任务开发 标准加载算法 历史表加载算法 任务映射文档 sdm excel工具模板 ETL任务测试 ETL开发、测试总结 ETL程序开发 ETL开发成果归档,ETL任务开发,ETL加载任务开发 二进制文件加载任务 以dds文件为核心的开发模式 使用加载任务脚本生成器生成加载任务脚本 其他文件加载任务 使用加载任务脚本链接生成器直接生成加载任务脚本 ETL转换任务开发 使用sdm excel集成工具进行开发 便捷的生成源表、目标表的字段映射关系，生成ETL任务的sdm文档 便捷的将映射关系插入到ETL映射表中，生成ETL映射元数据 便捷的根据ETL映射元数据生成ETL转换任务脚本，自动存放在ETL相应目录下 对较复杂的ETL任务脚本根据业务需求做少许的修改,ETL任务开发流程,ETL源数据加载任务开发,根据源数据结构生成dds文件 dds文件格式 ASCII文件 dds文件内容 包含表名称、字段名称、字段长度、数值型字段长度、数值型小数位数、字段中文名称等7列 列之间使用Tab键分隔 HCGDATV1R0_CGBJJLPF.dds文件示例：,ETL加载任务开发,使用脚本生成器生成加载任务脚本 Usage: scriptmap.pl SYS JOBDIR DBNAME TABLE DDSFILE SYS：子系统名称 JOBDIR：任务路径名称 DBNAME：source table 所在库名 TABLE：source table name DDSFILE: 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_CGBJJLPFbin目录下 生成一个源数据结构文件hcgdatv1r0_cgbjjlpf.map，自动存放在$AUTO_HOMEAPPSHHHCGDATV1R0_CGBJJLPFddl目录下,Fastload语法,Fastload Syntax Set record ; Tenacity ; Sleep ; Session ; Logon ; Begin loading ERRORFILES ; Define INMOD(FILE)=; Insert values (); End loading; Logoff;,Fastload示例,Fasload Example LOGON educ2/user14,ziplock; BEGIN LOADING Accounts ERRORFILES 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_Status ,:in_Trnsdate (Format YYYY-MM-DD) ,:in_Balfwd ,:in_Balcurr); END LOADING; LOGOFF;,ETL转换任务开发,标准加载算法说明 转换任务脚本生成器说明 genhis200.exe -生成历史表加载脚本 gentbl200.exe -生成除历史表外的加载脚本 使用sdm excel集成工具进行任务开发 任务映射文档说明 源表、目标表字段映射关系 执行” 生成脚本”语句生成任务脚本,标准加载算法,算法代码 算法名称 算法说明 0 其它加载方式 手工书写sql 1 全表覆盖 delete/insert 2 直接追加 insert where 3 历史表加载 历史表加载算法 4 主表、历史表加载 主表、历史表加载算法 5 主表加载 delete where 主键 in()/insert 6 初始、比对加载 初始加载：insert 每日加载：insert minus ,历史表加载算法,2个重要概念 开始日期：数据开始存在的日期 结束日期：数据消失（或失效）的日期 记录了数据的变动轨迹 初始加载 Insert 开始日期为加载日期或最小日期（mindate)19800101 结束日期为最大日期（maxdate）30001231 日常加载 新增数据 直接插入目标表 s_date=加载日期，e_date=最大日期 消失或失效数据 更新目标表结束日期字段值 e_date=加载日期,历史表加载算法示例,历史表脚本生成器,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-主表名(该参数若省略，则无主表) 生成网站历史表加载任务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 LOADMODE_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 dvpdata 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任务 存放从”任务映射文档”中拷贝过来的表级映射关系 制作脚本 存放“生成脚本”语句 包含定制VB代码宏”宏:加载导入mapping表” 数据库登录框,sdm excel工具模板（二）,Mapping（映射）框 4个文本框 目标库(pdata)：目标表所在的库 源库(s0ddl)：源表所在的库 “选择任务：（输入rowid)”：”DataMap任务”sheet下任务映射所在的行号 ”目标表名:”:目标表名称，即从映射关系表中获取此目标表的字段映射 8个命令按钮 退出 获取/维护Mapping信息：获取“目标库(pdata)”、”源库(s0ddl)“下的目标表、源表结构并显示在”制作DataMap”sheet；目标表结构显示在左上方，源表结构显示在右下方 “插入mapping表数据到数据库：”：将”制作DataMap”sheet中的字段映射插入到数据库映射关系表中 “清除相同的mapping表：”：将数据库映射关系表中目标表的字段映射记录删除 Get mapping from DB:从数据库映射关系表中获取”目标表名:”文本框中的目标表记录，并显示在”制作DataMap”sheet 生成script脚本：在”制作脚本”sheet下产生“生成脚本”语句命令 执行脚本生成：执行”制作脚本”sheet下“生成脚本”语句，生成转换任务脚本 执行结果日志：查看脚本生成过程日志,“Mapping（映射）框”图,源表目标表字段映射关系evt_h_nontran_a_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，出现”目标表： act_s_acct_std_rtn_src”,点击”获取/维护Mapping信息“，在”制作DataMap“sheet中生成目标表、源表的字段信息；对目标表和源表的字段进行映射 映射完成后，点击“插入mapping表数据到数据库：”将字段映射关系插入到数据库映射关系表中,产生” 生成脚本”语句,点击”生成scripts脚本”按钮，在“制作脚本”sheet中生成“生成脚本语句”,转换任务脚本生成及日志查看,点击”执行脚本生成”按钮，ETL脚本生成器读取数据库映射表数据自动生成转换任务脚本 点击”执行结果日志”按钮，查看任务脚本生成情况,ETL任务测试,源数据加载任务测试 语法测试 数据检查 转换任务测试 语法测试 业务规则检查 数据检查 备份任务测试 语法测试 测试结果 ETL流程测试 针对于批量上线的任务 使用ETL AUTOMATION调度 单个任务不需要流程测试,源数据加载任务测试,语法测试 执行命令：”脚本所在绝对路径”+脚本名称 dir+”.”+脚本任务名称+数据日期 示例：D:dwETLAPPSHZSAGDTALIB_ZHGFHBLbinsagdtalib_zhgfhbl0100.pl dir.sagdtalib_zhgfhbl20080228 数据检查 表与源文件记录数是否一致 是否有乱码 重点检查字段 日期字段 数值型字段 中文字段,转换任务测试,语法测试 执行命令：”脚本所在绝对路径”+脚本名称 dir+”.”+脚本任务名称+数据日期 示例：D:dwETLAPPACTACT_S_ACCT_STD_RTN_SRCbinact_s_acct_std_rtn_src0200.pl dir. 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程序,Visual SourceSafe（VSS）介绍,VSS是微软公司发布的版本管理器产品 简单易用、方便高效并与Windows操作系统及微软开发工具高度集成 完善的版本、安全保护和跟踪检查功能 保存文件的多个版本，包括文件版本之间每一处微小的变动 签出（check out）文档，避免多人同时修改文档，保证文档的安全性 对源代码和其他文件进行存储和早期版本的追踪 只支持Windows平台 需要快速大量的信息交换，因此仅适用于快速本地网络，而无法实现基于Web的快速操作,ETL开发成果归档,CSDCC项目使用VSS做为项目版本管理器，项目文档存放在VSS服务器上 ETL开发、测试完成后，必须将所有开发成果归档到VSS dds文件 sdm文件 ETL任务脚本 加载任务脚本 转换任务脚本 备份任务脚本 ETL程序 ETL相关的测试文档 ,议题,ETL 介绍 ETL Automation说明 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，可选择 增/删/改ETL任务组，可选择 增/删/改ETL 任务至任务组，可选择,ETL 加载监控/错误处理,数据仓库系统ETL监控Web页面 ETL人员开发的加载流程监控界面 包含了所有数据源加载流程的各个阶段 记录了流程各个阶段的完成时间、运行状态(完成、未完成、错误)等 使用不同的颜色标识各个阶段的各个运行状态 绿色：流程正常完成 黄色：流程未完成 红色：流程出现错误 监控数据库空间使用情况 点击各个阶段的链接，可以查看任务或数据库空间的详细信息 错误报警 显示数据源文件错误的详细信息 显示出错任务的详细信息 显示数据库空间不足错误 显示联系人员信息 使用ETL Administrator/ ETL Monitor进行加载监控、错误处理,数据仓库系统ETL流程监控图,ETL加载服务器管理,ETL加载服务器分类 生产系统数据ETL加载服务器 生产系统数据ETL备份服务器 其他用途的ETL加载服务器 管理 加载服务器空间监控 定时对磁盘空间监控 数据备份、清理监控 ETL服务监控 ETL失败任务处理,主索引调整、分区及二级索引创建,数据仓库数据量随每日ETL加载而增大，为节约数据库空间及保证查询、加载的效率，需要考虑对一些表的主索引进行调整以及创建分区主索引或二级索引 主索引的选择标准 经常访问的字段 取值唯一的字段 值稳定的字段 综合以上三点考虑，选择主索引 分区主索引 对表的部分数据进行扫描，为了提高查询效率 通常选择日期或地区做为分区主索引 二级主索引 为了提高查询效率 通常选择经常访问的字段做为二级主索引 示例:上海交易过户表采用”股东帐户+交易日期”做为主索引(保证取值唯一性)、使用交易日期做为分区主索引(保证查询、关联效率)、使用股东帐户做为二级主索引(保证查询、关联效率)，这样既节省了实际可用空间，又提高了查询效率,SQL优化（脚本优化）,保证加载、查询效率、降低数据库(SPOOL)空间使用 经常采用的优化方法： 收集（临时）表统计信息 拆分冗长的SQL语句，创建临时表（注意临时表的PI选择），多步实现 去除不必要（使用）的字段，只选择有用字段 对于汇总（sum）关联语句，一般情况