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

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

《上海农商银行ODSETL流程设计.docx》由会员分享，可在线阅读，更多相关《上海农商银行ODSETL流程设计.docx（14页珍藏版）》请在冰豆网上搜索。

上海农商银行ODSETL流程设计

上海农商银行ODS项目

ETL流程设计

当前版本：

V1.0

版本日期：

2010年1月29日

文件信息

文件标题

ETL作业流程设计

项目名称

上海农商银行ODS系统

项目经理

项目管理阶段

编写人

编写日期

2009-11-12

修订记录

日期

版本

修订描述

作者

2009-11-12

V0.1

ETL流程设计，细节尚待讨论

陈刚

2009-11-21

V0.2

按照实际情况修改部分流程设计

陈刚

2009-12-2

V0.2

更新作业样例、SEQJOB封装、标准化实现、ETL调度策略

陈刚

2009-12-10

v0.3

更新完善ETL调度架构，设计思路

陈刚

2010-01-31

v0.1

更新文档

陈刚

ETL作业流程设计

1ETL流程

按照目前的规划，ETL流程简单概括如下：

●源头业务系统将数据卸载到文件，通过FTP方式上传至ETL服务器指定目录，供ETL环节使用

●在拿到数据之后，ETL环节首先检查数据的完整性，确认无误后开始调度相关作业

●数据校验完毕后，对文件内容进行预处理，剔除文件中可能包含的逗号、双引号以及回车换行等字符

●数据经过ODM（文件）->SDM（DB）->FDM（DB）三层，在相关ETL作业中被转换、清洗，最终生成目标需要的数据

●在数据进入FDM层后，将向ALM供数，载体为文件

其中：

1.检查数据完整性的部分由shell或C程序完成，当一张目标表对应的所有文件均确认无误后，开始对文件进行预处理，预处理完毕后，调起相应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、SDM、FDM层之外

2ETL作业设计

2.1ETL作业样例

2.1.1ODM->SDM层

作业样例：

图2.1:

ETL作业样例ODM->SDM

图2.1中，编号1-8是标准化的过程，9-12是异常处理的过程，图中各编号对应的含义：

1：

Sequentialfilestage：

源数据接口文本文件

2：

Sequentialfilestage：

无法正常抽取的数据

3：

Transformerstage：

对正常抽取出来的源数据做预处理，为数据标准化做准备

4：

DB2stage：

标准化代码对照表，获取新旧代码对照信息

5：

Filterstage：

对标准化代码按类别进行分流

6：

Lookupstage：

各分流后的数据与源数据相应的字段进行匹配，获取标准化代码

7：

Transformerstage：

主要转换stage，对源数据值做数据转换并映射到目标字段，（如果有标准化后的字段为空，则进入9开始异常数据处理过程。

）

8：

DB2stage：

转换完成的数据，选择相应的入库模式加载到目标表。

9：

Transformerstage：

将源接口文件字段按原始数据类型写入文本，供重跑数据使用。

10：

Sequentialstage：

与源接口文件类型相同的数据文本文件。

11：

Transformerstage：

为每个无法正常标准化的字段添加注释信息。

12：

DB2stage：

将包含注释信息的字段使用Upsert方式写入“异常数据登记表（ODS.ODS_STDERR）”中，供维护人员查阅。

2.1.2SDM->FDM层

对于SDM->FDM的作业，有两种情况：

1.全量初始化作业

2.增量作业

对于全量初始化作业，仅需执行一次，且FDM目标表中无数据，因此过程非常简单，直接表到表写入记录即可，作业样例如下图：

图2.2:

全量ETL作业样例SDM->FDM

对于增量作业，又分为：

1.每日全量加载

2.历史时间拉链表

两种情况，对于每日全量加载的表，处理逻辑与全量初始化相同，直接表到表加载即可，而对于历史拉链表，则相对复杂一些了。

因为对于每条增量数据，需要按主键更新目标表中已有的记录，将其终止时间update成当日业务日期，并且插入一条生效日期等于当前业务日期、失效日期是’2999-12-31’的数据。

在实际的DataStageJob设计中，插入新记录的方式我们使用load可适当提高数据写入性能，但是考虑到其并行处理的机制，load需要锁表，因此必须将update和load分开操作，所以，我们在设计中，将JOB拆分成两个，一个负责update，一个负责load，然后在调度依赖关系表中配置它们的先后执行顺序，即可实现功能需求，又可提高执行效率。

作业样例如下：

1.update过程：

使用Upsert方式，需要用户自定义SQL，并在where子句中加上生效日期小于当前业务日期（即非当日增量数据）、且终止日期等于’2999-12-31’（即有效记录）的判断。

2.load（Insert过程）

使用load方式需要注意：

1.WriteMode=Append（数据使用追加方式）

2.Non-RecoverableTransaction=True（归档日志模式下防止load锁表空间）

3.UseASCIIDelimitedFormat=True（对变长文件使用ASCII分隔符）

2.2代码标准化

在图1所示的ETL作业流程中：

源系统和ODS的特定代码都统一在ODS内进行编码，对于源系统的每一条代码进行对照翻译，获取其在数据仓库内的标准的ID，其对照关系保存在数据表中以便ETL过程进行加工处理。

“标准化代码对照表”在ODS系统中的表名是：

ODS_COMP，它的结构如下：

图2.4:

标准化代码对照表

其中“TYPE（类型）”字段记录了各个数据域的类型代码，内容遵循“上海农商行数据模型数据域标准表”中的域代码定义，以此表的”SYSCODE”,”TYPE”,”OLD_VALUE”三个字段为条件，即可获取标准化后的新代码”STD_VALUE”，如图：

第一步，从ODS_COMP中抽取数据，在Filter中按域类型将数据分流：

图2.5:

Filter过滤条件设置

第二步，经过分流的数据，分别与源字段值进行匹配，获取标准化代码，如图：

图2.5:

Lookup的关联设置

第三步，在Transformer中将获取的标准代码映射到目标表相应字段：

图2.6:

Transformer中的字段映射

3ETL加载策略

在DB2服务器配置DS节点，直接通过DB2EnterpriseStage将数据入库，入库时，对全量和增量有不同的加载策略：

1、全量加载使用“Load/append”模式（在全量加载前会执行清除表的操作），此操作对所有目标表均进行一次即可。

2、增量加载有如下几种情况：

a）目标表每日记载全量：

”Load/Append”直接插入所有数据

b）目标表为历史拉链表，需要按照主键关联，将发生变化的’END_DT’日期从’2999-12-31’update为’ETL_DT’，新数据则直接insert到目标表，且新数据的结束日期设置为’2999-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的影响）。

由于全量和增量加载策略的不同，对于DSJOB来说，需要配备两套不同的作业分别处理全量和增量加载，当进行初始化时，执行全量加载作业一次，然后每次执行增量加载。

4ETL调度

调度模式：

使用DataStage的SequenceJob将作业打包，设计依赖关系，然后使用shell脚本通过命令行调用“dsjob”命令调度作业

需要考虑的是，依赖关系由谁来配置，目前预期有两种方法：

1.使用DataStageJobSequence来配置大部分的JOB依赖关系，如图4.1所示：

图4.1JobSequence示例

a）优点：

图形化配置界面，开发方便，在job数量不多，关系不是太复杂的情况下较直观。

b）缺点：

job数量多时，依赖关系配置较繁琐，且不便定位错误，后期维护也较难。

2.在DB2中维护一张依赖关系表，然后使用shell脚本连接数据库查询依赖关系，按照相应的顺序对作业进行调度：

a）优点：

粒度较小，定位错误、重新调度单个表的ETL作业流程较方便，维护依赖关系更灵活，便于扩展

b）缺点：

需要手工编写shell脚本或程序

但是目前这两种方法，都必须考虑将来整合进农商行的统一调度平台时，与调度平台的接口是否能够契合。

从功能上考虑，建议采用SHELL或C程序的方式调度，在扩展方面更加灵活。

4.1调度的组成

调度系统由以下几个部分组成：

4.1.1调度模块

整个ETL调度的基础，按照分工大致可分为4个模块：

初始化模块、文件检查/预处理模块、作业调度模块、作业执行模块，结构如下图：

1初始化程序

负责每日调度的初始化过程，获取所有需要执行的作业列表（文件作业、DS作业），并对作业的状态做初始化（置为WAITING）。

2文件检查、预处理程序

对作业元数据表中，类型为文件的作业进行循环检查和处理，包括检查文件完整性，文本文件预处理，检查正常并处理完成后，将作业状态更新为DONE，否则状态维持WAITING不变，等待下一个周期的循环检查。

3作业调度程序

当所有前置文件作业检查完毕，将非文件作业取出按优先级排序，在并行度允许和其前置作业完成的前提条件下，进行作业调度，调起执行程序执行作业，并将作业状态从WAITING置为RUNNING，作业执行完毕后，接收执行程序返回的完成信号对作业监控表内相应的作业状态进行更新（”DONE”或”ERROR”）。

4作业执行程序

接收作业调度程序发起的指令，按照作业预先配置的命令开始执行，无论失败与否都返回一个完成信号给作业调度程序。

然后执行程序退出，等待下一次被调度程序调起。

5其他

外围的文件、数据复制、检查等过程。

4.1.2DB2数据表

记录各调度作业的元数据、依赖关系、运行状态、并行度控制等，各数据表名称、字段名、类型、定义设定如下：

下面对这些表的结构含义做一下分析：

A.JOB_METADATA

此表保存所有作业的元数据，每个最小调度单元对应一条记录，每日运行的初始化基本数据就从此表中获得：

（注：

作业资源占用量：

JOB_WKRES字段，具体数值按照作业实际运行时所占用的系统资源来人为评估，例如：

普通作业可设定为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》