ETL设计方案

ETL设计方案1 ETL调度系统设计（ETL Schedule）1.1 调度系统的功能1）可配置JOB任务的前后依赖关系，确保顺利调度。2）可配置JOB任务优先级，指定调度模块的先后顺序。3）可配置JOB任务调度频率，如日调度/月调度/年调度等。4）通过高配置性和可视化界面，提高开发人员和维护人员工作效率。1.2 调度系统功能图示1.3 调度系统流程说明1） 手工配置调度配置表：首先JOB任务上线后，由开发人员将新开发的JOB任务配置到调度配置表。2） 每天初始化调度状态表：每天调度系统在【调度配置表】中将当天需要调度的任务列表新增到【调度状态表】中，新增任务的状态均为“未执行”。3） 轮询调度调度状态表任务：调度系统从指定时间开始（如凌晨0:30），依照【调度状态表】顺序，依次执行每一个JOB任务，其中判断任务的前提条件是否符合。4） 反馈调度状态：根据日志表任务执行情况，向【调度状态表】/【调度日志流水表】写JOB任务调度状态。如当任务执行成功，将该任务记录从【调度状态表】中删除，并在【调度日志流水表】中新增完成记录。当任务执行失败，则将任务记录在【调度状态表】中的标志设为失败。1.4 调度平台功能设计1）JOB任务当天状态（实用功能）：显示各任务的执行状态（未执行/执行中/失败/成功），尤其发生异常时，为维护人员提供友好界面，以便及时发现并解决。2）JOB调度系统负荷（实用功能）：显示调度系统中的历史负荷，如并发数量，同时读写同一个表情况等。3）JOB任务配置功能（实用功能）：在查询后，可实现（增删改）功能。4）JOB任务树状结构（实用功能）：将所有JOB的前后置关系以树状结构显示出来。在【调度配置表】循环SQL查询并画到前台。5）JOB任务迷失功能（该功能在开发中期开始实用）：在开发一段时间后，会产生一些没有前后依赖关系的JOB任务（可在【调度配置表】查询获得），或者长期未调度的JOB任务（可在【调度配置表】与【调度日志流水表】关联查询获得），该功能负责将其找出，并取消在树状结构上表现。1.5 调度维护1）调度系统参数维护，对调度系统JOB任务的参数设置和修改。2）调度系统运行情况的监控、处理、重跑、上报。3）梳理JOB任务前后置关系。2 日志管理2.1 日志系统的功能1）记录功能：记录调度过程日志，管理记录调度中的主要过程和异常信息，如调度开始、调度完成、数据库操作异常和读写文件异常的日志。2）管理功能：管理JOB执行信息的日志，提供该日志的查询、删除和执行状态重置功能。3）数据波动检查：提供详细事件日志，从日志中获取JOB任务处理数据量。2.2 日志系统功能图示2.3 日志流程说明：1） 日志触发：每个JOB流程每一次执行都会产生3条流水记录，分别插入到3个流水表【调度流水表】、【任务执行日志】、【任务详细日志】。2） 输出任务详细日志：保存每次JOB任务的运行状态，数据从DS的日志中捕捉。其中需要包含任务执行中对多少条记录进行操作，多少条记录拒绝。3） 输出任务执行日志：对【任务详细日志】进行简化，并关联【调度配置表】。从关联后得到JOB输入表协助调查是否存在数据质量问题。4） 输出调度流水日志：从【任务详细日志】获取状态，为调度系统提供支持。2.4 日志监控功能：流水日志监控：监控JOB任务的历史运行情况，如调度时间、处理记录数等。3 ETL调度设计3.1 ETL总体流程图3.2 ETL的功能类型及数据处理1） 外部文档的装载：将各个部门的源文件预装载到ETL服务器。2） 配置当天任务列表：自动启动ETL，从调度配置表中，获得当天需要调度的JOB任务列表，并插入到【调度状态表】中。3） 数据质量检查：对预处理数据质量进行检查，如果数据异常则为装载失败，并返回失败标志给任务列表。4） 消息通知：当数据或者任务发生异常时，有及时通知的消息机制。5） 正式装载ODS表：将数据质量及格的表重命名为正式表名。6） 执行JOB任务：调度JOB任务。3.3 数据质量检查策略3.3.1 数据质量检查主要检查几个方面：1） 数据装载错位检查：错位对外部文件装载时，因文件格式问题，造成字段数值错位，引起数据质量问题。2） 装载0条记录的检查：当天传递数据是否为空记录。3） 脏数据检查：装载数据与目标表字段类型不符的检查。4） 数据不标准的检查：新装载的数据不在实际数据列表中。3.3.2 数据质量检查方法：1） 装载0条记录的检查：统计预装载表的行数，为0则告警；2） 数据装载错位检查：预装载表比正式表多4个字段，数据文件装载后，后4字段不全为空则有外错行，倒数第5字段为空，则发生内错行。3） 脏数据检查：需要检查的字段值进行统计计算，正态分布的置信度99%以外的记录为可疑脏数据。该功能在DATASTAGE有对应组件可用。4） 数据不标准的检查：通过枚举字典比较，找出不达标数据。3.3.33.4 ETL调度策略采取条件驱动的策略来进行Job的调度，Job任务满足驱动条件便开始运行。针对快速见效系统的ETL情况，定义三种类型的驱动条件：1）外部文件驱动：当外部文件到达后，驱动JOB任务执行。2）前置任务事件驱动：每个任务必须依赖一些前提的数据条件，在任务调度中，可以体现为依赖于生产源表的任务是否完成。因此可以通过配置任务的依赖关系来保障源数据已经准备好。3）手工驱动：允许用户手工驱动任务，重跑某些数据。3.5 Job的并发设计（这里需要张磊设计一下）每个Job只要满足了驱动关系后，便开始以后台方式运行。这样便实现了不同区域和同一区域的Job的最大限度的并行。考虑系统资源的情况，可以事先设定最大并行数。当并行跑的Job都需要共同使用同一资源的时候，会产生资源占用的冲突，ETL过程中通常的冲突, 用令牌的方式来避免冲突，只有获得令牌的JOB才能跑，否则等待令牌释放。3.6 消息设计：重要信息（成功/失败）的通知1. 成功退出1) 分段提交方式，当分段提交的当次任务都正确完成，即Job运行状态临时表中登记的作业状态全部为完成时，退出ETL调度。2) 自动提交方式，当当期所有的任务都正确完成，即Job运行状态表中登记的作业状态全部为完成时，退出ETL调度。2. 失败退出1）关键作业异常，关键作业运行异常时，影响剩下的作业不能运行时，则退出ETL调度。2）超过ETL时限，当超过预先设定的ETL 时限时，退出ETL调度。3）数据库异常，当不能正常操作数据库时，退出ETL调度。4）操作系统异常，当发生操作系统异常，导致程序不能正常运行，如文件系统异常导致读写文件错时，需要退出ETL调度。5）手工退出，需要人为干预ETL调度的时候，能以手工操作的方式退出ETL调度。3.7 ETl流程及调度模块说明模块概要说明模块概要说明（总体层次）模块名称模块编号开发工具/环境模块功能说明备注作业调度 J01 AIX+Shell+C ETL调度主模块，负责整个ETL过程的调度处理。维护员作业管理 J02 EXECL+BRIO+C 定义作业步对应的实际ETL处理过程，生成作业编号，定义作业类型和作业的驱动条件。管理员异常重跑 J03 AIX+Shell+C当ETL运行出现异常时，提供断点重跑机制。维护员参数管理 J04 Brio EIS 调度系统的公共参数：期数、进程数、数据日期、本期开始日期和本期结束日期进行设置和修改。管理员日志管理 J05 BrioSDK,EIS包括调度过程日志、Job执行日志和Job详细事件日志的查询和删除。管理员3.8 模块概要说明（明细层次）模块名称模块编号模块类型开发环境模块功能说明备注CTLINIT J01_01 作业调度 AIX,PROC 检查输入参数和驱动关系，初始化资源令牌状态/Job运行状态/下传文件状态。UNCOMPR J01_02 作业调度 AIX,PROC,SHELL 检测下传目录下的压缩/打包的文件，并将这些文件解压缩/展开，之后移动到非下传目录下。FILEREG J01_03 作业调度 AIX,PROC 根据下传文件控制表检查各文件头的控制信息，将检查结果记录在下传文件控制表中。CHKREGJOB J01_04 作业调度 AIX,PROC 扫描Job运行状态（或临时）表看所有（或本次提交需要完成的）Job是否完成，根据Job执行日志信息登记完成的Job(在Job运行状态表登记)。CALREGJOB J01_05 作业调度 AIX,PROC 根据Job运行状态/下传文件状态/ Job驱动关系计算可已运行的Job，并在Job运行状态表中将这些Job登记为“就绪“状态。JOBRUN J01_06 作业调度 AIX,PROC,SHELL 依据当前进程数/资源令牌占用清况发出调度一个Job运行的命令，并登记Job运行状态为“正运行“。CRTJOB J02_01 作业管理 EXECL 生成作业列表和作业对应的驱动条件。CHKJOB J02_02 作业管理 PROC 检查作业列表的合法性，包括驱动关系的合理性检查。RERUN J03_01 异常重跑 AIX,PROC,SHELL 当ETL运行出现异常时，提供断点重跑机制。PARADM J04_01 参数管