ECIFETL总体设计说明书V011209

《ECIFETL总体设计说明书V011209》由会员分享，可在线阅读，更多相关《ECIFETL总体设计说明书V011209（28页珍藏版）》请在装配图网上搜索。

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 Plus.63.1.2存储过程.

2、63.1.3Unix Shell.73.2 开发工具选择结论.7第第 4 章章 ETL 开开发发流程流程设计设计.84.1 数据分析.84.2 开发流程.84.3 测试流程.104.4 上线流程.11第第 5 章章 ETL 总总体体设计设计.125.1 ETL 总体逻辑架构.125.2 ETL 关键任务设计.135.3 ETL 调度控制设计.155.3.1实现目标.165.3.2触发动作.165.3.3检查运行环境.165.3.4执行作业.175.3.5调度配置.175.3.6日志记录.185.3.7系统参数.185.4 ETL 备份&恢复设计.195.4.1程序备份.195.4.2数据文件备

3、份.195.4.3日志文件备份.195.5 ETL 测试设计.195.5.1ETL 功能测试.195.5.2数据准确性测试.205.5.3性能测试.20第第 6 章章 ETL 开开发规发规范范.216.1 ETL 程序开发规范.216.2 目录及权限定义规范.216.3 文件格式定义规范.226.3.1日志文件格式定义规范.226.3.2源数据文件格式定义规范.22章 1 章 引言引言1.1编编写目的写目的本文档是为明确中国大地保险 ECIF 系统的 ETL 架构设计而编制的，为中国大地保险 ECIF 项目的 ETL 系统开发后续工作提供指南。ETL 开发小组的将以本设计文档为基础，进行相应的

4、功能概要设计和详细设计。1.2预预期期读读者者本文档的预期读者为用户代表、数据库设计人员、概要设计和详细设计人员、开发人员、测试人员和项目相关手册编写人员。1.3定定义义ECIF：企业级客户信息管理(Enterprise Customer Information Facility)。ETL：Extraction（抽取）、Transformation（转换）、Load（加载）。Reject 文件：ETL Plus 处理过程非法数据的输出文件；ETL Plus JOB：ETL Plus 开发的 JOB。1.4参考参考资资料料大地保险数据质量评估报告章 2 章 ETL 设计设计目目标标和原和原则则2

5、.1系系统统目目标标建设一个实现 ECIF 项目的转换、加载和调度全过程的 ETL 平台。2.2数据目数据目标标按照模型的要求完成从源表到 ECIF 系统目标表的转换处理：包含完整的获取系统需要的源表和字段，对数据进行清洗和加载，完成个人/企业客户的归并，从客户出发完成各类指标的分析，形成最终的 ECIF 模型的客户数据，并保证数据的正确性。2.3功能目功能目标标数据加载:将源系统提供的数据文件经过清洗后加载到 ECIF 系统的缓冲区中，供后续的转换工作使用；ETL 调度：ETL 调度需要完成整个系统的依赖关系，转换过程无需人工干预；错误和异常处理：提供 ETL 系统的错误及异常处理机制，增强

6、系统的可靠性；提取公共模块：以提取公共模块的方式提高 ETL 作业的复用性，降低 ETL 代码的维护难度；2.4设计设计原原则则提供 ETL 各模块的结构详细定义、实现详细逻辑、步骤等。考虑关键路径处理效率的最优考虑 JOB 的拆分整合关系考虑数据的重复利用考虑文件落地策略考虑 JOB 间依赖的适中章 3 章 开开发发工具工具选择选择3.1开开发发工具比工具比较较3.1.1 ETL Plus（1）优点：快速的 ETL 开发工具。可管理性强、可维护性强。支持并行运行（包括：Job 内部各 Stage 之间的并行；Stage 在各节点的并行），未来实现网格技术后移植性强。（2）缺点：除 ETL 外

7、，要实现其它功能局限性非常强。比如：不支持循环。图形化组件拖拽/设置方式，难以自定义过程。若 Job 复杂，难以阅读，不利于扩展与维护。开发过程不便于调试与纠错。与底层操作系统的交互性方面较差。比如：调用 OS 的可执行程序只能通过Execute Command 组件进行封装调用，但是除了返回码外其它参数的传递较难实现。（3）适用范围：单表（或多表的简单整合）的简单加工类需求，尤其是 ETL 方面。3.1.2 存存储过储过程程（1）优点：最全面深入地利用数据库核心技术。过程处理/控制能力强。开发过程中调试与纠错方便。支持事务回滚。（2）缺点：并行处理能力差，容易造成数据库服务器处理压力大需要自

8、行编写代码实现。（3）适用范围：需要数据库技术。3.1.3 Unix Shell（1）优点：本身属于操作系统的一部分，通用性和可移植高。（2）缺点：局限性很强，不适合进行复杂的应用开发。（3）适用范围：基于操作系统层面的一些简单特色应用。3.2开开发发工具工具选择结论选择结论基于以上对各种常用工具的分析，以下总结了在工具选择上的结论：任务的调度处理应尽量使用 ETL Plus。数据抽取、数据清洗、复杂加工（计算）使用存储过程。基于操作系统的简单特色应用使用 Unix Shell。章 4 章 ETL 开开发发流程流程设计设计4.1数据分析数据分析对于数据源的需求分析分为模型组的数据源分析和 ET

9、L 组的数据源分析，二者的注重的分析点是不一样的。ETL 注重的是分析数据质量，及模型 Mapping 关系验证；而模型组注重的是数据体现出来的业务对象和业务逻辑。本章重点关注 ETL 的数据源分析。4.1.1.1 分析方法分析方法依据源系统数据字典，分析字段是否存在脏数据情况，如数据唯一性检查，日期合法性检查，值域范围检查；依据大地保险 ECIF 系统模型，分析源系统数据是否满足表与表之间关系是否完全成立，如主外键关系检查；4.1.1.2 分析内容分析内容技术分析数据唯一性：逻辑主键是否成立日期合法性：非 Date 类型的日期是否合法非空格约束：非空格数据质量非空值约束：非空值约束是否成立值

10、域范围：结合字段含义，阈值范围是否成立最大值：最大值是否可信最小值：最小值是否可信记录总数4.2测试测试流程流程1、 模型组提供的Mapping 文档作为 ETL 开发的输入2、 ETL 组需要利用测试数据对Mapping 规则进行验证，并提出反馈意见3、 数据验证通过的 Mapping 规则需要经过 Job 的设计上的验证，是否满足性能及拆分上的需要4、 开发完毕的 Job 立即进行单元测试5、 整个过程迭代进行模型设计Mapping分析Mapping逻辑校验JOB设计和开发单元测试Mapping反馈反馈反馈反馈4.3测试测试流程流程设计测试用例编写测试脚本执行测试脚本提出缺陷分析缺陷修复缺

11、陷重新组织测试1. 测试之前需要设计测试案例，案例中应包含测试的功能点和测试方法；2. 由测试人员根据测试案例编写相应的测试代码，并执行测试代码；3. 测试人员根据测试结果，提出缺陷，并提交测试负责人；4. 测试负责人首先分析测试缺陷，判断是否是缺陷，以及缺陷的类型，并制定缺陷的轻重缓急。5. 测试负责人将分类汇总后的缺陷反馈给开发组，开发组负责人再将缺陷分配给相应的开发人员。6. 开发人员修复缺陷，并将查找到的问题原因和具体修改的内容反馈给测试负责人进行登记。7. 测试组重新组织测试。4.4上上线线流程流程UAT测试完毕制定上线计划&方案上线培训转入日常运维不通过不成功通过成功上线确认评审1

12、、 评审不通过，需要重新准备上线方案和计划。2、 上线确认不成功，需要重新准备上线方案和计划，并再次评审，准备下次上线。章 5 章 ETL 总总体体设计设计5.1ETL 总总体体逻辑逻辑架构架构数据文件区源系统时间窗口元数据管理元数据抽取清洗临时数据区Staging Area数据安全管理、错误处理任务管理、调度、监控输出前端页面展示加载源系统预处理转换过程结果输出目标辅助表客户信息电销客服理赔95590承保归并ESB数据总线输出说明：源系统：指 ECIF 的数据源，包含承保类系统、电销系统、客服系统、理赔类系统、95590 网站系统，这些系统含有本地的业务数据及客户资料，后续 ECIF 系统建

13、设完成逐步从ECIF 系统同步和查询信息。抽取：利用 Oracle 数据库存储过程把各个源系统文件落地成数据接口文件，供后续过程加载。数据文件区：用目录存放从各个源系统抽取落地的数据接口文件。临时数据区： oracle 数据库临时表，主要用于存储来源于源系统获取的客户数据和业务数据。辅助目标表：oracle 数据库辅助表，主要用于存储清洗完成的保单客户信息。客户信息表：oracle 数据库客户表，主要用于存储归并或增强后的客户信息。前端页面输出：在经过清洗、归并完成客户信息后，搭建客户信息访问 Web 页面，供不同岗位和角色的人员进行客户信息的访问。元数据管理：ETL 过程的元数据包括业务元数

14、据和技术元数据，其中转换任务，存储过程、日志等都作为技术元数据进行管理。元数据管理可以采用文件或数据库表的方式进行管理，此项目采用数据库表的方式进行管理。5.2ETL 关关键键任任务设计务设计5.2.1.1 数据接口文件的数据接口文件的设设定定数据接口文件存放在 DAT 目录下对应的工作日数据存储区（/ECIFETL/DAT/YYYYMMDD）中。一个源表对应一个数据文件，文件的后缀名为dat。对于文件暂存区的数据保存三个月的数据，超过三个月的数据手工清除；5.2.1.2 数据加数据加载载数据加载主要利用 oracle 工具 SqlLoad 完成将经过处理的接口文件或数据库表的数据加载到中间表

15、中。为提高加载效率，一般在数据加载前将涉及到的表的索引删除，加载完成后重新创建删除的索引。通常用到的数据加载方式有三种：1)Insert：只需要将文件所有数据完全 Insert 到目标表中。2)Upsert：需要对目标表同时做 Update 及 Insert 操作，根据 primary key，对于已有的记录进行 Update 操作，对于不存在的记录做 Insert 的操作，对于数据量大的表，由于此操作的效率非常低，可以采用先将数据文件分割为 Delete 文件及 Insert 文件，然后先将 Delete 文件中的记录根据 primay key 对应从数据库中删除，然后再从 Insert文件

16、中将所有记录全部 Insert 到目标表中。3)Refresh：即将目标表的数据完全更新，一般的做法是先 Truncate 目标表的数据，然后再完全 Insert 要加载的记录；基于本系统的特点，系统在全量加载过程中都采用第 3 种 Refresh 方式进行数据加载,在增量加载过程中采用第一种 Insert 的方式进行数据加载，临时表到辅助表的数据加载利用存储过程实现加载。5.2.1.3 数据清洗数据清洗/变换变换在本系统中数据的清洗/变换工作将在数据接口文件加载进临时表后进行，数据清洗/变换是指将数据源字段根据数据映像表的转换规则，转换为遵循 ECIF 系统标准的数据格式。即对数据类型和数据

17、格式进行转换，并对空字段赋予适当的缺省值，形成规整的数据结构。这个工作主要在数据接口文件加载到辅助表的过程中及在加载到辅助表后进行处理，符合清洗和转换规则的数据加载到辅助数据表中，不符合清洗和转换规则的数据输出到拒绝文件中。数据清洗/变换的处理流程图：接口文件是否满足清洗转换规则格式转换赋缺省值类型转换长度转换代码转换是不符合格式数据写入日志表辅助表否中间表数据清洗/变换的内容：1)格式变换：依据目标表，将源数据对应的字段转为目标字段的指定格式。如将日期转为 yyyy-mm-dd hh:mm:ss；2)赋缺省值：在目标表中定义取值不为空的字段在源数据对应的字段可能存在没有取值的记录，这时根据业

18、务需要将该记录写入到日志表中进行记录，由业务部门根据日志表记录检查并修补源数据，或者直接赋一个默认值；3)类型变换, 依据目标表，将源数据对应的字段类型转为目标字段的指定类型。如将源系统中为整形的字段转换为字符型的；4)长度变换, 依据目标表，将源数据对应的字段长度转为目标字段的指定长度；5)代码转换，依据目标表，将源系统的某些字段经过代码升级以后，将老的代码转换为新的代码等。6）特殊字符处理，对源表的字符型数据中存在换行符等特殊的字符，应将这些特殊字符在进入文件缓存区之前替换掉。清洗完毕加载到辅助表后，会用 SP 进行进一步的清洗和保单级的增强。5.2.1.4 数据数据转换转换数据转换是按照

19、目标表的数据结构，对一个或多个源数据的字段进行翻译、匹配、聚合等操作得到目标数据的字段。生成与辅助表一一对应的指标数据并存放到辅助表中。数据转换主要内容：1)完成源与目标的映射关系。2)多个数据文件的关联与匹配。对应 ECIF 目标表字段的数据源有多个，需要将多个数据源关联并进行匹配最终的临时表字段。ECIF 的数据转换主要在 Oracle 数据库服务器进行，根据模型的和 ETL 处理特点，进行数据处理和转换。通过存储过程实现数据库临时区到数据辅助表的转换过程。5.3ETL 调调度控制度控制设计设计整个系统的逻辑关系比较复杂，暂考虑用 ETL Plus 进行 ETL 作业的调度，为了提高系统的

20、可管理、可使用、可运维性，需要设计合理有效的调度控制程序。本系统的调度控制的执行流程如下图所示： 检查运行环境触发动作结束符合日志记录执行下一个任务不符合顺次读取作业配置信息是否全部作业执行完成DSSP执行作业作业是否成功完成否调度作业配置信息ETL JOBSP5.3.1 实现实现目目标标最终要达到整个 ETL 过程的自动化的，且中间每个环节之间根据需要设定依赖或自动的触发机制（时间或标志文件）。5.3.2 触触发动发动作作可分为手工触发或自动触发两大类，自动触发又可分为：时间触发、文件触发等，本系统采用手工触发方式。5.3.3 检查检查运行运行环环境境根据后续任务的要求，检查系统运行的必要条

21、件。如数据接口文件是否到达、数据库是否可用、机器性能是否满足等。5.3.4 执执行作行作业业本系统共有两大类作业：SqlLoad JOB 和 SP。SqlLoad JOB 主要用来完成数据清洗和加载工作；SP 主要用于处理复杂逻辑业务工作。调度程序调用单个作业的基本流程：读取JOB运行参数JOB依赖是否全部满足运行JOBJOB是否正常完成调度流程调用返回调度流程否是是否等待重置JOB状态5.3.5 调调度配置度配置整个调度的作业执行的顺序和依赖关系基于工具 ETL Plus 完成。在后续设计和开发工作中需要重点关注完成作业的封装和作业配置。作业配置应包含以下内容：作业描述：对作业功能等进行描述

22、，不超过 64 个汉字（128 个英文字符）;作业类型：SqlLoad Job、SP、可执行文件等;所属作业组：该作业所属作业组;触发作业：说明该作业完成后触发的后续作业。作业启动时间：设定作业的启动时间，或根据设定的触发条件刻启动该作业5.3.6日志日志记录记录整个调度过程会记录每个任务执行的开始时间、结束时间、执行的状态等。同时要求，每个任务依据情况详细记录其日志。管理人员可通过日志监控整个 ETL 调度的执行状态。5.3.7系系统统参数参数为了提高 ETL JOB 的灵活性，需要设置一些公共的环境参数供 ETL JOB 在运行过程中动态使用，这些参数在运行过程中可以由运行人员根据实际的运

23、行情况进行调整。以下是 JobParams.cfg 文件中的部分参数定义列表参数名称说明WORKDATE当前 ETL 数据日期，格式 yyyymmddETLDATEXF 文件的存放目录DBNAME目标库的数据库名DBUSR目标库用户 IDDBPWD目标库用户密码5.4ETL 备备份份&恢复恢复设计设计5.4.1 程序程序备备份份手工定期进行程序备份、数据库存储过程备份、SqlLoad Project 备份5.4.2 数据文件数据文件备备份份源系统抽取后的数据接口文件保留前三次的接口文件备份。超过三次以前的接口文件手工删除或手工备份到磁带。5.4.3 日志文件日志文件备备份份手工定期进行日志文件

24、备份5.5ETL 测试设计测试设计5.5.1 ETL 功能功能测试测试5.5.1.1 模模块块功能功能功能模块功能子模块测试指标测试方法源文件监控与检核文件是否正常是否遗漏文件记录数及文件大小是否符合可在较小的时间窗口内轮询，分批放入测试文件ETL 数据处理数据清洗清洗是否成功是否有干净数据被清洗性能是否满足要求针对不同清洗规则，选择若干数据文件进行测试ETL 监控文件监控是否有遗漏文件未监控作业监控能否正确捕捉作业的执行状态，统计信息是否正确资源监控资源是否能有效获取数据库监控数据库信息是否能有效获取5.5.1.2 调调度功能度功能测试内容测试指标测试方法ETL 调度是否正确执行调度任务，参

25、数传递是否正确，Job 是否按照预定顺序执行，调度序列是否正确。作业之间的依赖关系是否正确5.5.2 数据准确性数据准确性测试测试5.5.2.1 准确性准确性测试测试的原的原则则模型对数据处理的要求，体现在 Mapping 文档中，应当依据 Mapping 文档对处理的诠释，进行数据准确性测试需制定完整的指标检核体系，应当依据这套检核指标体系进行数据准确性测试5.5.2.2 准确性准确性测试测试的方法的方法数据准确性主要由用户测试进行保证，用户测试的方法请参考用户测试相关文档5.5.3 性能性能测试测试5.5.3.1 测试测试方法方法统计各作业的运行时间，调整作业依赖关系及串并行关系监控主机资

26、源、数据库资源利用状况5.5.3.2 调优调优原原则则充分利用主机资源调整数据库参数配置调整 SqlLoad Job 开发逻辑调整 SP 开发逻辑章 6 章 ETL 开开发规发规范范6.1ETL 程序开程序开发规发规范范为了提高 ETL 的开发质量，降低开发周期，增强代码的可重用性和易读性，使程序便于维护，开发人员间便于交流和协作，需指定 ETL 的开发规范，在本项目中包含ETL Plus、Oracle PL/SQL、Shell 三种开发规范，请参看相应文档。6.2目目录录及及权权限定限定义规义规范范开发、测试环境的目录如下：目目录录说说明明/ECIFETLECIF 项目 ETL 根目录/EC

27、IFETL/ 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 。