上海农商银行ODSETL流程设计.docx

1、上海农商银行ODSETL流程设计上海农商银行ODS项目ETL流程设计当前版本：V1.0版本日期：2010年1月29日文件信息文件标题ETL作业流程设计项目名称上海农商银行ODS系统项目经理项目管理阶段编写人编写日期2009-11-12修订记录日 期版 本修订描述作 者2009-11-12V0.1ETL流程设计，细节尚待讨论陈刚2009-11-21V0.2按照实际情况修改部分流程设计陈刚2009-12-2V0.2更新作业样例、SEQ JOB封装、标准化实现、ETL调度策略陈刚2009-12-10v0.3更新完善ETL调度架构，设计思路陈刚2010-01-31v0.1更新文档陈刚ETL作业流程设计

2、1ETL流程按照目前的规划，ETL流程简单概括如下：源头业务系统将数据卸载到文件，通过FTP方式上传至ETL服务器指定目录，供ETL环节使用在拿到数据之后，ETL环节首先检查数据的完整性，确认无误后开始调度相关作业数据校验完毕后，对文件内容进行预处理，剔除文件中可能包含的逗号、双引号以及回车换行等字符数据经过ODM(文件)-SDM(DB)-FDM(DB)三层，在相关ETL作业中被转换、清洗，最终生成目标需要的数据在数据进入FDM层后，将向ALM供数，载体为文件其中：1.检查数据完整性的部分由shell或C程序完成，当一张目标表对应的所有文件均确认无误后，开始对文件进行预处理，预处理完毕后，调起

3、相应DS作业2.ODM-SDM的文件与数据表是一一对应的关系，而SDM-FDM会存在多张SDM表映射到同一张FDM表的情况，因此需要设置一定的依赖关系。3.按照FDM的每一张目标表为基准，将所有有依赖关系的作业，配置先后执行的顺序，即：假设当ODM-SDM的a、b、c三张表的ETL作业完成后，已可满足FDM中X表取数的需求，针对X表的SDM-FDM作业即开始执行。4.容错机制，在调度程序执行期间发生了意外错误导致中断，需要人为干预，找到问题所在并修复后，重新执行调度程序即可，调度程序会根据作业调度表中的作业状态继续调度过程。5.FDM数据处理完毕后，即可向ALM供数，此部分相对独立于ODM、S

4、DM、FDM层之外2ETL作业设计2.1ETL作业样例2.1.1ODM-SDM层作业样例：图2.1: ETL作业样例ODM-SDM图2.1中，编号1-8是标准化的过程，9-12是异常处理的过程，图中各编号对应的含义：1：Sequential file stage：源数据接口文本文件2：Sequential file stage：无法正常抽取的数据3：Transformer stage：对正常抽取出来的源数据做预处理，为数据标准化做准备4：DB2 stage：标准化代码对照表，获取新旧代码对照信息5：Filter stage：对标准化代码按类别进行分流6：Lookup stage：各分流后的数据

5、与源数据相应的字段进行匹配，获取标准化代码7：Transformer stage：主要转换stage，对源数据值做数据转换并映射到目标字段，（如果有标准化后的字段为空，则进入9开始异常数据处理过程。）8：DB2 stage：转换完成的数据，选择相应的入库模式加载到目标表。9：Transformer stage：将源接口文件字段按原始数据类型写入文本，供重跑数据使用。10：Sequential stage：与源接口文件类型相同的数据文本文件。11：Transformer stage：为每个无法正常标准化的字段添加注释信息。12：DB2 stage：将包含注释信息的字段使用Upsert方式写入“异

6、常数据登记表（ODS.ODS_STDERR）”中，供维护人员查阅。2.1.2SDM-FDM层对于SDM-FDM的作业，有两种情况：1.全量初始化作业2.增量作业对于全量初始化作业，仅需执行一次，且FDM目标表中无数据，因此过程非常简单，直接表到表写入记录即可，作业样例如下图：图2.2: 全量ETL作业样例SDM-FDM对于增量作业，又分为：1.每日全量加载2.历史时间拉链表两种情况，对于每日全量加载的表，处理逻辑与全量初始化相同，直接表到表加载即可，而对于历史拉链表，则相对复杂一些了。 因为对于每条增量数据，需要按主键更新目标表中已有的记录，将其终止时间update成当日业务日期，并且插入一条

7、生效日期等于当前业务日期、失效日期是2999-12-31的数据。 在实际的DataStage Job设计中，插入新记录的方式我们使用load可适当提高数据写入性能，但是考虑到其并行处理的机制，load需要锁表，因此必须将update和load分开操作，所以，我们在设计中，将JOB拆分成两个，一个负责update，一个负责load，然后在调度依赖关系表中配置它们的先后执行顺序，即可实现功能需求，又可提高执行效率。作业样例如下：1.update过程：使用Upsert方式，需要用户自定义SQL，并在where子句中加上生效日期小于当前业务日期（即非当日增量数据）、且终止日期等于2999-12-31（

8、即有效记录）的判断。2.load（Insert过程）使用load方式需要注意：1.Write Mode = Append （数据使用追加方式）2.Non-Recoverable Transaction = True （归档日志模式下防止load锁表空间）3.Use ASCII Delimited Format = True （对变长文件使用ASCII分隔符）2.2代码标准化在图1所示的ETL作业流程中：源系统和ODS的特定代码都统一在ODS内进行编码，对于源系统的每一条代码进行对照翻译，获取其在数据仓库内的标准的ID，其对照关系保存在数据表中以便ETL过程进行加工处理。“标准化代码对照表”在O

9、DS系统中的表名是：ODS_COMP，它的结构如下：图2.4: 标准化代码对照表其中“TYPE(类型)”字段记录了各个数据域的类型代码，内容遵循“上海农商行数据模型数据域标准表”中的域代码定义，以此表的”SYSCODE”,”TYPE”,”OLD_VALUE”三个字段为条件，即可获取标准化后的新代码”STD_VALUE”，如图：第一步，从ODS_COMP中抽取数据，在Filter中按域类型将数据分流：图2.5: Filter过滤条件设置第二步，经过分流的数据，分别与源字段值进行匹配，获取标准化代码，如图：图2.5: Lookup的关联设置第三步，在Transformer中将获取的标准代码映射到目

10、标表相应字段：图2.6: Transformer中的字段映射3ETL 加载策略 在DB2服务器配置DS节点，直接通过DB2 Enterprise Stage将数据入库，入库时，对全量和增量有不同的加载策略：1、全量加载使用“Load/append”模式（在全量加载前会执行清除表的操作），此操作对所有目标表均进行一次即可。2、增量加载有如下几种情况：a)目标表每日记载全量：”Load/Append”直接插入所有数据b)目标表为历史拉链表，需要按照主键关联，将发生变化的END_DT日期从2999-12-31 update为ETL_DT，新数据则直接insert到目标表，且新数据的结束日期设置为29

11、99-12-31，实现方法有两种：i.使用DataStage处理，将需要Update和需要insert的记录分成两个JOB，分别修改它们的END_DT，需要Update的记录END_DT=ETL_DT，需要Insert的记录END_DT=2999-12-31，然后分别将其写入目标表（需配置两个JOB的先后执行顺序）。ii.在数据库端使用Trigger，每条数据按主键判断加载方式是Update或Insert，每条记录的END_DT处理逻辑与上面相同（需考虑Trigger对DB的影响）。 由于全量和增量加载策略的不同，对于DS JOB来说，需要配备两套不同的作业分别处理全量和增量加载，当进行初始化

12、时，执行全量加载作业一次，然后每次执行增量加载。4ETL调度调度模式：使用DataStage的Sequence Job将作业打包，设计依赖关系，然后使用shell脚本通过命令行调用“dsjob”命令调度作业需要考虑的是，依赖关系由谁来配置，目前预期有两种方法：1.使用DataStage Job Sequence来配置大部分的JOB依赖关系，如图4.1所示：图4.1 Job Sequence示例a)优点：图形化配置界面，开发方便，在job数量不多，关系不是太复杂的情况下较直观。b)缺点：job数量多时，依赖关系配置较繁琐，且不便定位错误，后期维护也较难。2.在DB2中维护一张依赖关系表，然后使用

13、shell脚本连接数据库查询依赖关系，按照相应的顺序对作业进行调度： a)优点：粒度较小，定位错误、重新调度单个表的ETL作业流程较方便，维护依赖关系更灵活，便于扩展b)缺点：需要手工编写shell脚本或程序但是目前这两种方法，都必须考虑将来整合进农商行的统一调度平台时，与调度平台的接口是否能够契合。从功能上考虑，建议采用SHELL或C程序的方式调度，在扩展方面更加灵活。4.1调度的组成调度系统由以下几个部分组成：4.1.1调度模块 整个ETL调度的基础，按照分工大致可分为4个模块：初始化模块、文件检查/预处理模块、作业调度模块、作业执行模块，结构如下图：1初始化程序 负责每日调度的初始化过程

14、，获取所有需要执行的作业列表（文件作业、DS作业），并对作业的状态做初始化（置为WAITING）。2文件检查、预处理程序 对作业元数据表中，类型为文件的作业进行循环检查和处理，包括检查文件完整性，文本文件预处理，检查正常并处理完成后，将作业状态更新为DONE，否则状态维持WAITING不变，等待下一个周期的循环检查。3作业调度程序 当所有前置文件作业检查完毕，将非文件作业取出按优先级排序，在并行度允许和其前置作业完成的前提条件下，进行作业调度，调起执行程序执行作业，并将作业状态从WAITING置为RUNNING，作业执行完毕后，接收执行程序返回的完成信号对作业监控表内相应的作业状态进行更新(”

15、DONE”或”ERROR”)。4作业执行程序 接收作业调度程序发起的指令，按照作业预先配置的命令开始执行，无论失败与否都返回一个完成信号给作业调度程序。然后执行程序退出，等待下一次被调度程序调起。5其他 外围的文件、数据复制、检查等过程。4.1.2DB2数据表 记录各调度作业的元数据、依赖关系、运行状态、并行度控制等，各数据表名称、字段名、类型、定义设定如下： 下面对这些表的结构含义做一下分析：A.JOB_METADATA 此表保存所有作业的元数据，每个最小调度单元对应一条记录，每日运行的初始化基本数据就从此表中获得：（注：作业资源占用量：JOB_WKRES字段，具体数值按照作业实际运行时所占

16、用的系统资源来人为评估，例如：普通作业可设定为1，复杂作业或数据量大的作业可设定为2或更大的值，作业并发调度时，每个正在运行的作业工作量累加不超过最大工作量限制，保障服务器并发执行多个作业时不至于消耗过多系统资源造成服务器假死或当机。最大工作量在JOB_RES_CTRL表中进行限定。）B.JOB_SEQ此表保存作业的依赖关系，表结构如下：C.JOB_SCHE此表保存当日所有需要运行的作业信息、状态信息、运行时间等（可考虑保存历史信息）：作业序号JOB_SEQ_ID是一个使用DB2序列自动生成的递增整数。D.JOB_RES_CTRL此表限定了各分类最大可运行的工作量，并在作业调度期间实时更新当前正在运行的作业已使用的工作量，控制并发执行的作业数量，从而减轻服务器的压力：还有几张表是配合调度运行的数据表，罗列如下：ODS_SOU_SYS_STS(ods源系统状态表)：ODS_TBLIST(ODS源系统文件列表):SOURCESYS(源系统定义表)：SYSTEMPARA（系统参数表）：ODS_STDERROR（标准化异常登记表）：4.1.3调度流程关于详细的调度流程，请查看：上海农商银行_ODS_日常运营操作手册.doc