ETL规范.docx

1、ETL规范第1章ETL规范概述1.1.规范定义ETL规范是为保证ETL正确设计、实施和维护所定义的一些规则和方法。1.2.应用范围本规范适用于包括设计、实施、维护在内的各个环节。在ETL整个的过程中为设计人员、实施人员和维护人员提供一套完整的流程规范，对ETL涉及到的各个步骤和细节提供参考案例。 1.3.实施价值本规范是根据石竹公司多年ETL项目咨询和实施经验总结而成，具有高度的可行性和应用价值。运用本规范管理ETL的全过程，可以有效地防止一些常见问题的发生，使整个过程设计更科学、实施更快捷、维护更简便。1.4.预期读者从事ETL设计、开发以及维护的技术人员，具备ETL项目实施经验的人员可略过

2、第二章内容。1.5.主要章节第一章：“概述”，介绍ETL规范的定义和应用范围。第二章：“ETL简介”，介绍ETL的定义及应用。第三章：“ETL设计规范”，介绍ETL设计阶段应该遵循的规则。第四章：“ETL开发规范”，介绍ETL开发阶段应该遵循的规则。第五章：“ETL维护规范”，介绍ETL维护阶段应该遵循的规则。第六章：“IBM WebSphere DataStage”，简单介绍ETL工具DataStage的功能和使用。1.6.文档约定缩写：规范突出显示注意的词语或事项：需要突出显示注意的词语或事项将以红色斜体下划线显示。本规范适用于以DataStage作为开发工具的ETL项目。第2章ETL简介

3、2.1.ETL含义ETL是数据抽取（Extract）、转换（Transform）、装载（Loading）的缩写。2.1.1.数据抽取数据抽取是指从数据源获取所需数据的过程。数据抽取过程会过滤掉目标数据集中不需要的源数据字段或数据记录。数据抽取可以采用PULL和PUSH两种方式：PUSH就是指由源系统按照双方定义的数据格式，主动将符合要求的数据抽取出来，形成接口数据表或数据视图供ETL系统使用。PULL则是由ETL程序直接访问数据源来获取数据的方式。2.1.2.数据转换数据转换（Transform）是按照目标表的数据结构，对一个或多个源数据的字段进行翻译、匹配、聚合等操作得到目标数据的字段。数据

4、转换主要包括：格式转换字段合并与拆分数据翻译数据匹配数据聚合其他复杂计算2.1.3.数据装载把源数据通过数据转换目标数据，就可以直接通过数据加载工具加载到目标数据库中。数据加载工作一般分为3步进行 ：1）Pre-Load：删除索引2）Load ：将转换后的源数据加载到目标数据库的表中 3）Post-Load：重新生成索引；清理临时文件 2.2.ETL应用ETL应用实现将多个数据源的数据按照一定的逻辑规则进行抽取、转换和并装载到目标数据集，如下图：完整的ETL应用过程包含三个阶段：设计阶段分析源和目标数据集的数据结构，定义合理的数据转换逻辑；实施阶段按照设计阶段制定的逻辑规则进行编码，实现数据的

5、E（Extract）、T（Transform）、L（Loading）过程；维护阶段对于非一次性数据整合项目（如很多数据仓库项目中的数据整合过程），ETL过程需要重复执行，同时也需要不间断的维护和完善。2.3.ETL开发模式：ETL开发根据项目的复杂度、实施时间、性能要求分为几种不同的模式：标准式；小型快速式；复杂调优式；2.3.1.标准式标准式就是根据ETL的实施流程，严格分为：抽取、清洗、转换、装载四步骤进行开发，不同步骤之间的作业通过中间临时文件进行数据交换；再根据不同的作业步对作业进行封装；然后对封装后的作业进行调度开发。这种开发模式十分规范，适合那些开发时间比较充裕、项目要求比较严谨、

6、性能要求不太苛刻的项目。2.3.2.小型快速式小型快速式就是根据作业所满足的功能，在一个作业里面就把抽取、清洗、转换和装载的所有流程完成；然后对各个功能模块的作业进行封装调度。 这种开发模式实施起来很快速，而且因为作业量小，管理维护起来也很方便，适合那种小型的、需要快速实施的项目。2.3.3.复杂调优式复杂调优式就是综合评估项目的所有资源：CPU、内存、I/O、数据库连接、网络传输等，根据各个资源的负荷程度，拆分、合并不同的作业，把一些作业的公共功能（如抽取、转换）放在一起进行，或者把一些复杂的操作拆分成几个不同的作业进行，尽量使机器的资源能得到最大程度的利用，避免遇到瓶颈，从而使作业的处理性

7、能得到最大的调优。这种开发模式能够使作业的性能达到最优化，适合那种作业逻辑复杂、性能要求高的项目。第3章ETL设计规范ETL设计规范主要应用于ETL编码的前期工作。由于ETL全过程是面向数据的，主要工作为数据的抽取（Extract）、转换（Transform）、装载（Loading），正确界定所涉及到的数据范围和应当应用的转换逻辑对于后续的编码工作非常重用，这些数据关系的确定，我们称之为Mapping（数据映射）。正确定义数据映射关系是ETL成功实施的前提，一个完善的Mapping应该包含以下几个部分：3.1.源数据集属性此部分应该详细描述数据源的相关属性，包括：实体名称含DSN、所有者等信息

8、；字段名英文名称；字段简述中文名称，如为参数信息应该有相关取值解释，如性别字段（1：男；2：女；0：不详）类型字段类型，含长度和精度信息；非空属性字段是否可以为空；3.2.目标数据集属性此部分应该详细描述目标数据集的相关属性，包括：实体名称含DSN、所有者等信息；字段名英文名称，建议根据字段含义来命名，而不是简单用拼音来定义字段（此部分由负责设计数据集的人员控制）；字段简述中文名称，对于保留字段应该给出默认值；类型字段类型，含长度和精度信息；非空属性字段是否可以为空；3.3.ETL规则主要描述ETL各个环节的转换规则，包括：数据源过滤规则描述从源数据集获取数据过程中过滤掉记录的规则；关联规则当

9、源数据集为多个时，描述相互之间的关联关系；列转换规则描述源数据集到目标数据集的字段间的转换规则；此规则非常重要，要清晰描述字段间的逻辑关系，包括业务逻辑；目标数据集更新规则描述目标数据集的更新策略，包括更新机制和更新频度，如“每日全量更新”、“每周增量更新”；ETL作业列表由于ETL所开发的作业之间包含一定的业务逻辑和编码逻辑，所以调度过程中应遵循一定的逻辑顺序，此部分主要用来明确调度的顺序，包括：作业名称实现Mapping的作业名称，包括该作业功能描述；调度顺序用序号或者是流程图模式描述作业的调度顺序，需要综合考虑业务逻辑、编码逻辑以及系统资源等多方面情况，在保证业务逻辑和编码逻辑的基础上，

10、通过控制调度，最大限度地合理利用系统资源；参数列表列举每个作业中所使用的参数，不同作业中的相同参数最好使用相同的名称，便于调度时进行控制；注意：此部分内容主要是对ETL所开发的作业进行部署，所以应该在开发阶段进行补充，而不是在开发前拟订的。3.4.Mapping版本管理ETL设计会随着对业务、系统理解的深入以及结构框架的变化而发生变化，所以Mapping设计也应该同步更新。在开发过程中，要严格遵守一个规则：当规则发生变更时，要先变更Mappig，然后才变更相应的作业设计。在Mapping变更管理方面，应该有详细的版本变更记录，以便追踪到ETL开发的变动情况。变更记录包括如下内容：版本每次变更应

11、给出一个新的版本号；作者变更人；更新时间变更时间；更新内容简要说明变更内容；备注可用于记录变更的原因等相关信息；第4章ETL开发规范ETL项目的开发往往是多人团队开发模式，由于周期较长，期间难免会出现开发人员交替现象。无论是开发过程中的人员交替还是从开发到系统运营之间的转换，都需要良好的交接。为保证项目开发各个时期的平稳过度及顺利交接，在开发过程中，应该遵循一定的开发规范。主要包括：命名规范功能定义规范结构规范代码封装规范4.1.命名规范4.1.1.作业命名规范ETL是一个复杂的工作，数据仓库的建设一般会花费70以上的工作量在ETL上面，而且这是一个细致活，任何一点小的错误都有可能造成后续工作

12、的出错和失败；此外，数据仓库需要不断的更新维护，相应的ETL程序也需要进行相关的更新维护，所花费的成本也很大。一个好的命名规范，能够使ETL的程序更清晰易懂，很好地避免了开发过程中出错；同时更好的可读性，也极大地减低了ETL程序的更新维护成本。按照组织目录组织完作业，按规则为作业命名，规则如下JOBTYPEDESCRIPTIONSEQNUMJOBTYPE:Ex,Ld,DTD(DB TO DB),Seq,RunSeqDESCRIPTION:有效的描述信息SEQNUM:作业的编号。由于一定的原因，有可能需要拆分作业数据多次加载数据，这个编号就可以区分加载。如果一次加载可以成功的话这个编号就不需要了

13、。4.1.2.Stage与link命名规范对于作业内部的Stage与Link的命名，应该遵循以下原则代码的可读性与可传递性。Datastage的作业是由stage通过link连接构成的，每个stage都赋予标示功能的名称，此外在作业设计界面内加有代码说明，这些都增加作业的可读性与传递性。命名规则：FunctionDescriptionFunction表示stage的用途，Ex:抽取，Create:创建，Ld:装载，Lookup:查找等Description:有意义的描述，比如数据库表名，文件名等。4.1.3.作业注释规范ETL开发过程往往要经历一个较长时间段，为了便于团队开发和后期维护，除开发

14、设计文档要齐全外，还应该在作业设计界面中适当加注释信息，主要包括作业功能说明、所属模块、开发时间、开发人员等信息。4.2.功能定义规范4.2.1.SequenceFile的文件定义：作业设计过程中不可避免要使用文件作为中间存储或者最终结果输出，在DataStage里，使用Sequential File Stage来定义顺序文件。对于Sequential File Stage，通常遵循以下规则进行定义：文件名：#rootpath#%功能目录%FILENAME，其中：#rootpath#：路径参数；%功能目录%：根据文件的目的进行存放，例如EXF（抽取），LD（装载）,REJ(拒绝)FILENAM

15、E命名规则：MODEL_DESCRIPTION_SEQNUM.%后缀%MODEL:对模块的英文描述DESCRIPTION:有效的描述信息SEQNUM:作业的编号。由于一定的原因，有可能需要拆分作业数据多次加载数据，这个编号就可以区分加载。如果一次加载可以成功的话这个编号就不需要了。 %后缀%：可供任意选择，推荐与功能目录同名。选项参数：分隔符: | 引号符:000 字符集:默认4.2.2.Hash_File定义：ETL在数据转换过程中经常需要做多个源之间的关联或者是中间结果的转码，这些工作在DataStage里通过Hash File Stage来实现，对于Hash File Stage的定义，

16、我们遵循如下规则： 1general中选择use directory path,输入 #rootpath#Hash_xxxinput中文件命名：MODEL_DESCRIPTION_SEQNUM.HSMODEL:对模块的英文描述DESCRIPTION:有效的描述信息SEQNUM:作业的编号。由于一定的原因，有可能需要拆分作业数据多次加载数据，这个编号就可以区分加载。如果一次加载可以成功的话这个编号就不需要了。input中其他设置：可根据实际情况设置，不建议选择allow stage write cacheoutput中文件名称要与中一致，pre-load file to memory在小数据量的

17、时候可以选择enable,否则最好是disable.4.2.3.参数化作业原则：作业的灵活性与复用性。数据库连接信息，文件路径，抽数的时间参数是一定要使用的参数。对于数据库连接信息的参数命名：DB别名_DSN, DB别名_USER, DB别名_PWD抽取的时间参数：START_DATE,END_DATE用于使用时间增量标志进行数据抽取；RUNDATE用于创建该日ETL运行目录。参数一律按格式写入参数文件，由专门的routine从文件中获取参数，简化运行操作。4.3.结构规范4.3.1.作业目录组织结构对于ETL PROJECT而言，按照模块分类是有效的方法。ETL的作业本身可以是普通的SERV

18、ER JOB,也可以是SEQ JOB。当然SERVER JOB有图形化的设计也有代码开发的灵活方式，这样我们需要有一定的规则组织起来，这样无论是对ETL设计人员，运行人员，维护人员，都可以有很大的帮助。目录的参照依据：1体现作业的依赖关系。2体现作业功能。3独立组织二次开发功能作业。实例分析：图2.2.1 深圳平安CDQA项目作业目录组织结构管理目录：ManageTool：存放二次开发的功能作业与维护作业。主目录：CDQACDQA下二级目录：Staging,Staging2Atom，Atom2CDQA（三者存在先后依赖关系）Staging目录：存放业务系统到Staging库的作业，作业逻辑相对

19、简单。Staging2Atom目录：原子层基础数据ETL作业。Atom2CDQA 目录：IIW数据仓库建维与事实表ETL作业。MainControlSEQ目录：定义作业依赖关系的SEQ作业与主控作业封装。4.3.2.模块化结构ETL的按照处理逻辑分为E阶段（Extract）、T阶段（Transformer）和L阶段（Loading）。E、T、L阶段可能分处不同的服务器上，需要跨网络运行，他们之间的配置情况可能存在较大的差异，而多数ETL设计的处理机制是串行运行机制，基于数据行处理。如果E、T、L的过程同时运行，那么整体的处理能力应该是三个环节中效率最低的那个环节的处理水平。如果我们将三个环节的

20、设计分开，实行模块化流程处理，即E环节、T环节、L环节均设计成可独立运行的模块，那么在整个处理过程中，将可以最大限度发挥功效，不会因为串性处理机制而相互制约。另外，模块化的流程设计，对于开发阶段的调试以及维护阶段的出错处理都提供了良好的辅助作用。4.3.3.临时文件目录组织考虑将来的ETL扩展需要，每个PROJECT都需要有各自的运行目录，之下有CFG,Hash_xxx，LOG和日期目录，ETL运行时会自动产生这个日期目录，存放该日运行使用的临时文件。CFG目录下存放参数文件，参数文件存放数据库信息，文件路径，时间参数等ETL元素，参数文件的写与读都由相应的ROUTINES来完成。Hash_x

21、xx存放hash文件，用于ETL关联，Lookup等操作产生的hash文件。LOG是对ETL运行日志进行二次开发后产生的日志文件。4.4.代码封装规范4.4.1.Routine与Shared Containers使用原则Routine和Shared Containers的共同点是可以提高代码的重用性、便于维护。不同点在于Routine一般用来处理相对简单的重用逻辑，而Shared Containers通过图形化界面封装复杂的可重用的处理过程。对于重用性高的逻辑进行封装，除了降低重复开发的工作量，同时也大大降低了维护的工作量，当被封装的逻辑发生变化时，只需要一次性修改Routine或者Share

22、d Containers即可，所有调用该逻辑的地方都自动更新。因此，当对重用性较高的逻辑进行处理时，应该尽量时代码模块化，从而达到重用的目的。4.4.2.代码封装Seq作业可能包含很多的参数，运行的时候为了简化运行人员工作，需要对seq作业进行封装，使其能够自动获得参数，并且针对seq作业监控其运行结果。因此，采用代码型server job来设计开发。这类作业只需要cfgpath参数指定ds.cfg路径。里面典型的代码有：a.获得参数：* Routine declare Deffun RTGetParamValue(rootpath,ParamName) Calling DSU.RTGetPa

23、ramValue* Get Params from Config File CfgPath=cfgpath rootpath=RTGetParamValue(CfgPath,rootpath) sharepath=RTGetParamValue(CfgPath,sharepath)ftpserver=RTGetParamValue(CfgPath,ftpserver)b.重置作业状态和作业赋参：黑体部分需要手工加入！斜体部分是对作业进行赋参，变量都是在a步得到的。* Setup ISeInnerDataExportDailyRun, run it, wait for it to finish,

24、 and test for success hJob1 = DSAttachJob(ISeInnerDataExportDailyRun, DSJ.ERRFATAL) If NOT(hJob1) Then Call DSLogFatal(Job Attach Failed: ISeInnerDataExportDailyRun, JobControl) Abort End Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS) If Status = DSJS.RUNFAILED or Status = DSJS.STOPPED Then * Reset Job

25、 Status ErrCode = DSRunJob(hJob1, DSJ.RUNRESET) ErrCode = DSWaitForJob(hJob1) ErrCode = DSDetachJob(hJob1) hJob1 = DSAttachJob(ISeInnerDataExportDailyRun, DSJ.ERRFATAL) End ErrCode = DSSetParam(hJob1, cfgpath, rootpath) ErrCode = DSSetParam(hJob1, sharepath, sharepath) ErrCode = DSRunJob(hJob1, DSJ.

26、RUNNORMAL) ErrCode = DSWaitForJob(hJob1) Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS) If Status = DSJS.RUNFAILED Or Status = DSJS.CRASHED Then * Fatal Error - No Return Call DSLogFatal(Job Failed: ISeInnerDataExportDailyRun, JobControl)Endc.ETLLOG作业：作业调用之后增加etllog作业来监控作业的运行状态。也是通过job control来add job,

27、重置和赋好参数即可。注意，ETLLOG是MULTI-INSTANCE设计，所以在ID采用相应的SEQ作业名字用于区别。ETLLOG只有一个参数rootpath需要用变量来指代，其余都是常量。Sequencename:seq作业名称 Projectname:项目名称。 Model:所属模块 * Setup ETLLOG.ISeInnerDataExportDailyRun, run it, wait for it to finish, and test for success hJob1 = DSAttachJob(ETLLOG.ISeInnerDataExportDailyRun, DSJ.E

28、RRFATAL) If NOT(hJob1) Then Call DSLogFatal(Job Attach Failed: ETLLOG.ISeInnerDataExportDailyRun, JobControl) Abort End Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS) If Status = DSJS.RUNFAILED or Status = DSJS.STOPPED Then * Reset Job Status ErrCode = DSRunJob(hJob1, DSJ.RUNRESET) ErrCode = DSWaitForJ

29、ob(hJob1) ErrCode = DSDetachJob(hJob1) hJob1 = DSAttachJob(ETLLOG.ISeInnerDataExportDailyRun, DSJ.ERRFATAL) End ErrCode = DSSetParam(hJob1, runpath, runpath) ErrCode = DSSetParam(hJob1, sequencename, ISeInnerDataExportDailyRun) ErrCode = DSSetParam(hJob1, projectname, PAIC) ErrCode = DSSetParam(hJob

30、1, model, 财产险) ErrCode = DSRunJob(hJob1, DSJ.RUNNORMAL) ErrCode = DSWaitForJob(hJob1) Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS) If Status = DSJS.RUNFAILED Or Status = DSJS.CRASHED Then * Fatal Error - No Return Call DSLogFatal(Job Failed: ETLLOG.ISeInnerDataExportDailyRun, JobControl) End*注意红色字体部分的替换！4.4.3.日志处理ETL过程中的产生的日志信息对于数据检查、故障定位和恢复非常有用，如何妥善处理日志信息是ETL开发过程中需要考虑的。通常在ETL运行结束后，获取相关日志信息并记载到DB中是一个较好的处理方法。可以通过DataStage所提供的图形化工具和自定义Routine来完成此工作，对基于DataStage的开发，我们提供一套完整的日志处理方案，包括ex作业（获取日志信息）由代码开发，ld作业（将日志写回数据库）采用图形化开发，可以进行简单修改将日志导入数据库等容器中生成维护报告。这些公用的作业和公用的Routine可以打包在新建Project时导入。4.4.4.公共