《ECIFETL总体设计说明书V01 1209.doc》由会员分享，可在线阅读，更多相关《ECIFETL总体设计说明书V01 1209.doc（23页珍藏版）》请在三一办公上搜索。
1、中国大地财产保险股份有限公司企业级客户信息管理平台项目（ECIF）ETL总体设计说明书文档修订记录表版本号作者操作日期说明目 录第1章引言41.1编写目的41.2预期读者41.3定义41.4参考资料4第2章ETL设计目标和原则52.1系统目标52.2数据目标52.3功能目标52.4设计原则5第3章开发工具选择63.1开发工具比较63.1.1ETL Plus63.1.2存储过程63.1.3Unix Shell73.2开发工具选择结论7第4章ETL开发流程设计84.1数据分析84.2开发流程84.3测试流程104.4上线流程11第5章ETL总体设计125.1ETL总体逻辑架构125.2ETL关键任
2、务设计135.3ETL调度控制设计155.3.1实现目标165.3.2触发动作165.3.3检查运行环境165.3.4执行作业175.3.5调度配置175.3.6日志记录185.3.7系统参数185.4ETL备份&恢复设计195.4.1程序备份195.4.2数据文件备份195.4.3日志文件备份195.5ETL测试设计195.5.1ETL功能测试195.5.2数据准确性测试205.5.3性能测试20第6章ETL开发规范216.1ETL程序开发规范216.2目录及权限定义规范216.3文件格式定义规范226.3.1日志文件格式定义规范226.3.2源数据文件格式定义规范22第1章 引言1.1 编
3、写目的本文档是为明确中国大地保险ECIF系统的ETL架构设计而编制的，为中国大地保险ECIF项目的ETL系统开发后续工作提供指南。ETL开发小组的将以本设计文档为基础，进行相应的功能概要设计和详细设计。1.2 预期读者本文档的预期读者为用户代表、数据库设计人员、概要设计和详细设计人员、开发人员、测试人员和项目相关手册编写人员。1.3 定义ECIF：企业级客户信息管理(Enterprise Customer Information Facility)。ETL：Extraction（抽取）、Transformation（转换）、Load（加载）。Reject文件：ETL Plus 处理过程非法数据
4、的输出文件；ETL Plus JOB：ETL Plus开发的JOB。1.4 参考资料大地保险数据质量评估报告第2章 ETL设计目标和原则2.1 系统目标建设一个实现ECIF项目的转换、加载和调度全过程的ETL平台。2.2 数据目标按照模型的要求完成从源表到ECIF系统目标表的转换处理：包含完整的获取系统需要的源表和字段，对数据进行清洗和加载，完成个人/企业客户的归并，从客户出发完成各类指标的分析，形成最终的ECIF模型的客户数据，并保证数据的正确性。2.3 功能目标n 数据加载:将源系统提供的数据文件经过清洗后加载到ECIF系统的缓冲区中，供后续的转换工作使用；n ETL调度：ETL调度需要完
5、成整个系统的依赖关系，转换过程无需人工干预；n 错误和异常处理：提供ETL系统的错误及异常处理机制，增强系统的可靠性；n 提取公共模块：以提取公共模块的方式提高ETL作业的复用性，降低ETL代码的维护难度；2.4 设计原则提供ETL各模块的结构详细定义、实现详细逻辑、步骤等。n 考虑关键路径处理效率的最优n 考虑JOB的拆分整合关系n 考虑数据的重复利用n 考虑文件落地策略n 考虑JOB间依赖的适中第3章 开发工具选择3.1 开发工具比较3.1.1 ETL Plus（1）优点：n 快速的ETL开发工具。n 可管理性强、可维护性强。n 支持并行运行（包括：Job内部各Stage之间的并行；Sta
6、ge在各节点的并行），未来实现网格技术后移植性强。（2）缺点：n 除ETL外，要实现其它功能局限性非常强。比如：不支持循环。n 图形化组件拖拽/设置方式，难以自定义过程。n 若Job复杂，难以阅读，不利于扩展与维护。n 开发过程不便于调试与纠错。n 与底层操作系统的交互性方面较差。比如：调用OS的可执行程序只能通过Execute Command组件进行封装调用，但是除了返回码外其它参数的传递较难实现。（3）适用范围：n 单表（或多表的简单整合）的简单加工类需求，尤其是ETL方面。3.1.2 存储过程（1）优点：n 最全面深入地利用数据库核心技术。n 过程处理/控制能力强。n 开发过程中调试与纠
7、错方便。n 支持事务回滚。（2）缺点：n 并行处理能力差，容易造成数据库服务器处理压力大n 需要自行编写代码实现。（3）适用范围：n 需要数据库技术。3.1.3 Unix Shell（1）优点：n 本身属于操作系统的一部分，通用性和可移植高。（2）缺点：n 局限性很强，不适合进行复杂的应用开发。（3）适用范围：n 基于操作系统层面的一些简单特色应用。3.2 开发工具选择结论基于以上对各种常用工具的分析，以下总结了在工具选择上的结论：n 任务的调度处理应尽量使用ETL Plus。n 数据抽取、数据清洗、复杂加工（计算）使用存储过程。n 基于操作系统的简单特色应用使用Unix Shell。第4章
8、ETL开发流程设计4.1 数据分析对于数据源的需求分析分为模型组的数据源分析和ETL组的数据源分析，二者的注重的分析点是不一样的。ETL注重的是分析数据质量，及模型Mapping关系验证；而模型组注重的是数据体现出来的业务对象和业务逻辑。本章重点关注ETL的数据源分析。4.1.1.1 分析方法n 依据源系统数据字典，分析字段是否存在脏数据情况，如数据唯一性检查，日期合法性检查，值域范围检查；n 依据大地保险ECIF系统模型，分析源系统数据是否满足表与表之间关系是否完全成立，如主外键关系检查；4.1.1.2 分析内容n 技术分析 数据唯一性：逻辑主键是否成立 日期合法性：非Date类型的日期是否
9、合法 非空格约束：非空格数据质量 非空值约束：非空值约束是否成立 值域范围：结合字段含义，阈值范围是否成立 最大值：最大值是否可信 最小值：最小值是否可信 记录总数4.2 测试流程1、 模型组提供的Mapping文档作为ETL开发的输入2、 ETL组需要利用测试数据对Mapping规则进行验证，并提出反馈意见3、 数据验证通过的Mapping规则需要经过Job的设计上的验证，是否满足性能及拆分上的需要4、 开发完毕的Job立即进行单元测试5、 整个过程迭代进行4.3 测试流程1. 测试之前需要设计测试案例，案例中应包含测试的功能点和测试方法；2. 由测试人员根据测试案例编写相应的测试代码，并执
10、行测试代码；3. 测试人员根据测试结果，提出缺陷，并提交测试负责人；4. 测试负责人首先分析测试缺陷，判断是否是缺陷，以及缺陷的类型，并制定缺陷的轻重缓急。5. 测试负责人将分类汇总后的缺陷反馈给开发组，开发组负责人再将缺陷分配给相应的开发人员。6. 开发人员修复缺陷，并将查找到的问题原因和具体修改的内容反馈给测试负责人进行登记。7. 测试组重新组织测试。4.4 上线流程1、 评审不通过，需要重新准备上线方案和计划。2、 上线确认不成功，需要重新准备上线方案和计划，并再次评审，准备下次上线。第5章 ETL总体设计5.1 ETL总体逻辑架构说明：源系统：指ECIF的数据源，包含承保类系统、电销系
11、统、客服系统、理赔类系统、95590网站系统，这些系统含有本地的业务数据及客户资料，后续ECIF系统建设完成逐步从ECIF系统同步和查询信息。抽取：利用Oracle数据库存储过程把各个源系统文件落地成数据接口文件，供后续过程加载。数据文件区：用目录存放从各个源系统抽取落地的数据接口文件。临时数据区： oracle数据库临时表，主要用于存储来源于源系统获取的客户数据和业务数据。辅助目标表：oracle数据库辅助表，主要用于存储清洗完成的保单客户信息。客户信息表：oracle数据库客户表，主要用于存储归并或增强后的客户信息。前端页面输出：在经过清洗、归并完成客户信息后，搭建客户信息访问Web页面，
12、供不同岗位和角色的人员进行客户信息的访问。元数据管理：ETL过程的元数据包括业务元数据和技术元数据，其中转换任务，存储过程、日志等都作为技术元数据进行管理。元数据管理可以采用文件或数据库表的方式进行管理，此项目采用数据库表的方式进行管理。5.2 ETL关键任务设计5.2.1.1 数据接口文件的设定数据接口文件存放在DAT目录下对应的工作日数据存储区（/ECIFETL/DAT/YYYYMMDD）中。一个源表对应一个数据文件，文件的后缀名为dat。对于文件暂存区的数据保存三个月的数据，超过三个月的数据手工清除；5.2.1.2 数据加载数据加载主要利用oracle工具SqlLoad完成将经过处理的接
13、口文件或数据库表的数据加载到中间表中。为提高加载效率，一般在数据加载前将涉及到的表的索引删除，加载完成后重新创建删除的索引。通常用到的数据加载方式有三种：1)Insert：只需要将文件所有数据完全Insert到目标表中。2)Upsert：需要对目标表同时做Update及Insert操作，根据primary key，对于已有的记录进行Update操作，对于不存在的记录做Insert的操作，对于数据量大的表，由于此操作的效率非常低，可以采用先将数据文件分割为Delete文件及Insert文件，然后先将Delete文件中的记录根据primay key对应从数据库中删除，然后再从Insert文件中将所
14、有记录全部Insert到目标表中。3)Refresh：即将目标表的数据完全更新，一般的做法是先Truncate目标表的数据，然后再完全Insert要加载的记录；基于本系统的特点，系统在全量加载过程中都采用第3种Refresh方式进行数据加载,在增量加载过程中采用第一种Insert的方式进行数据加载，临时表到辅助表的数据加载利用存储过程实现加载。5.2.1.3 数据清洗/变换在本系统中数据的清洗/变换工作将在数据接口文件加载进临时表后进行，数据清洗/变换是指将数据源字段根据数据映像表的转换规则，转换为遵循ECIF系统标准的数据格式。即对数据类型和数据格式进行转换，并对空字段赋予适当的缺省值，形成
15、规整的数据结构。这个工作主要在数据接口文件加载到辅助表的过程中及在加载到辅助表后进行处理，符合清洗和转换规则的数据加载到辅助数据表中，不符合清洗和转换规则的数据输出到拒绝文件中。数据清洗/变换的处理流程图：数据清洗/变换的内容：1)格式变换：依据目标表，将源数据对应的字段转为目标字段的指定格式。如将日期转为yyyy-mm-dd hh:mm:ss；2)赋缺省值：在目标表中定义取值不为空的字段在源数据对应的字段可能存在没有取值的记录，这时根据业务需要将该记录写入到日志表中进行记录，由业务部门根据日志表记录检查并修补源数据，或者直接赋一个默认值；3)类型变换, 依据目标表，将源数据对应的字段类型转为
16、目标字段的指定类型。如将源系统中为整形的字段转换为字符型的；4)长度变换, 依据目标表，将源数据对应的字段长度转为目标字段的指定长度；5)代码转换，依据目标表，将源系统的某些字段经过代码升级以后，将老的代码转换为新的代码等。6）特殊字符处理，对源表的字符型数据中存在换行符等特殊的字符，应将这些特殊字符在进入文件缓存区之前替换掉。清洗完毕加载到辅助表后，会用SP进行进一步的清洗和保单级的增强。5.2.1.4 数据转换数据转换是按照目标表的数据结构，对一个或多个源数据的字段进行翻译、匹配、聚合等操作得到目标数据的字段。生成与辅助表一一对应的指标数据并存放到辅助表中。数据转换主要内容：1)完成源与目
17、标的映射关系。2)多个数据文件的关联与匹配。对应ECIF目标表字段的数据源有多个，需要将多个数据源关联并进行匹配最终的临时表字段。ECIF的数据转换主要在Oracle数据库服务器进行，根据模型的和ETL处理特点，进行数据处理和转换。通过存储过程实现数据库临时区到数据辅助表的转换过程。5.3 ETL调度控制设计整个系统的逻辑关系比较复杂，暂考虑用ETL Plus进行ETL作业的调度，为了提高系统的可管理、可使用、可运维性，需要设计合理有效的调度控制程序。本系统的调度控制的执行流程如下图所示： 5.3.1 实现目标最终要达到整个ETL过程的自动化的，且中间每个环节之间根据需要设定依赖或自动的触发机
18、制（时间或标志文件）。5.3.2 触发动作可分为手工触发或自动触发两大类，自动触发又可分为：时间触发、文件触发等，本系统采用手工触发方式。5.3.3 检查运行环境根据后续任务的要求，检查系统运行的必要条件。如数据接口文件是否到达、数据库是否可用、机器性能是否满足等。5.3.4 执行作业本系统共有两大类作业：SqlLoad JOB和SP。SqlLoad JOB主要用来完成数据清洗和加载工作；SP主要用于处理复杂逻辑业务工作。调度程序调用单个作业的基本流程：5.3.5 调度配置整个调度的作业执行的顺序和依赖关系基于工具ETL Plus完成。在后续设计和开发工作中需要重点关注完成作业的封装和作业配置
19、。作业配置应包含以下内容：n 作业描述：对作业功能等进行描述，不超过64个汉字（128个英文字符）;n 作业类型：SqlLoad Job、SP、可执行文件等;n 所属作业组：该作业所属作业组;n 触发作业：说明该作业完成后触发的后续作业。n 作业启动时间：设定作业的启动时间，或根据设定的触发条件刻启动该作业5.3.6 日志记录整个调度过程会记录每个任务执行的开始时间、结束时间、执行的状态等。同时要求，每个任务依据情况详细记录其日志。管理人员可通过日志监控整个ETL调度的执行状态。5.3.7 系统参数为了提高ETL JOB的灵活性，需要设置一些公共的环境参数供ETL JOB在运行过程中动态使用，
20、这些参数在运行过程中可以由运行人员根据实际的运行情况进行调整。以下是JobParams.cfg文件中的部分参数定义列表参数名称说明WORKDATE当前ETL数据日期，格式yyyymmddETLDATEXF文件的存放目录DBNAME目标库的数据库名DBUSR目标库用户IDDBPWD目标库用户密码5.4 ETL备份&恢复设计5.4.1 程序备份n 手工定期进行程序备份、数据库存储过程备份、SqlLoad Project备份5.4.2 数据文件备份n 源系统抽取后的数据接口文件保留前三次的接口文件备份。超过三次以前的接口文件手工删除或手工备份到磁带。5.4.3 日志文件备份n 手工定期进行日志文件备
21、份5.5 ETL测试设计5.5.1 ETL功能测试5.5.1.1 模块功能功能模块功能子模块测试指标测试方法ETL数据处理源文件监控与检核文件是否正常是否遗漏文件记录数及文件大小是否符合可在较小的时间窗口内轮询，分批放入测试文件数据清洗清洗是否成功是否有干净数据被清洗性能是否满足要求针对不同清洗规则，选择若干数据文件进行测试ETL监控文件监控是否有遗漏文件未监控作业监控能否正确捕捉作业的执行状态，统计信息是否正确资源监控资源是否能有效获取数据库监控数据库信息是否能有效获取5.5.1.2 调度功能测试内容测试指标测试方法ETL调度是否正确执行调度任务，参数传递是否正确，Job是否按照预定顺序执行
22、，调度序列是否正确。作业之间的依赖关系是否正确5.5.2 数据准确性测试5.5.2.1 准确性测试的原则n 模型对数据处理的要求，体现在Mapping文档中，应当依据Mapping文档对处理的诠释，进行数据准确性测试n 需制定完整的指标检核体系，应当依据这套检核指标体系进行数据准确性测试5.5.2.2 准确性测试的方法数据准确性主要由用户测试进行保证，用户测试的方法请参考用户测试相关文档5.5.3 性能测试5.5.3.1 测试方法n 统计各作业的运行时间，调整作业依赖关系及串并行关系n 监控主机资源、数据库资源利用状况5.5.3.2 调优原则n 充分利用主机资源n 调整数据库参数配置n 调整S
23、qlLoad Job开发逻辑n 调整SP开发逻辑第6章 ETL开发规范6.1 ETL程序开发规范为了提高ETL的开发质量，降低开发周期，增强代码的可重用性和易读性，使程序便于维护，开发人员间便于交流和协作，需指定ETL的开发规范，在本项目中包含ETL Plus、Oracle PL/SQL、Shell三种开发规范，请参看相应文档。6.2 目录及权限定义规范开发、测试环境的目录如下：目录说明/ECIFETLECIF项目ETL根目录/ECIFETL/ DAT/WORKDATE存放ETL过程生成的DAT文件/ECIFETL/TMP存放ETL过程生成的临时文件/ECIFETL/Script存放Script程序/ECIFETL/PARA存放参数配置文件/ECIFETL/LOG存放日志文件数据库用户及权限安排如下：用户权限ECIF_own接口库和集市库所有者ECIF_etl接口库和集市库读写权限，主要供ETL过程使用ECIF_dm集市库读写权限，主要供分析过程使用ECIF_qry接口库和集市库只读读权限，供普通用户查询用6.3 文件格式定义规范6.3.1 日志文件格式定义规范日志文件应记录作业的类型，作业运行的时间，日志信息的类型（一般信息，警告信息，错误信息）等，具体设计请参看详细设计文档。6.3.2 源数据文件格式定义规范请参考xxx.doc 。
