《上海农商银行.docx》由会员分享，可在线阅读，更多相关《上海农商银行.docx（19页珍藏版）》请在三一办公上搜索。
1、上海农商银行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更新文档陈刚1 ETL 流程 1-42 ETL作业设计2-52.1
2、ETL作业样例 2-52.1.1 ODM-SDM 层2-52.1.2 SDM-FDM 层2-52.2 代码标准化2-83 ETL加载策略3-94 ETL 调度4-104.1 调度的组成4-114.1.1 调度模块4-114.1.2 DB2 数据表4-124.1.3 调度流程4-15ETL作业流程设计1 ETL流程按照目前的规划，ETL流程简单概括如下：源头业务系统将数据卸载到文件，通过FTP方式上传至ETL服务器指定目录，供 ETL环节使用在拿到数据之后，ETL环节首先检查数据的完整性，确认无误后开始调度相关作业 数据校验完毕后，对文件内容进行预处理，剔除文件中可能包含的逗号、双引号以 及回车
3、换行等字符 数据经过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
4、作业完成后，已可满足FDM 中X表取数的需求，针对X表的SDM-FDM作业即开始执行。4. 容错机制，在调度程序执行期间发生了意外错误导致中断，需要人为干预，找到问 题所在并修复后，重新执行调度程序即可，调度程序会根据作业调度表中的作业状 态继续调度过程。5. FDM数据处理完毕后，即可向ALM供数，此部分相对独立于ODM、SDM、FDM层 之外2 ETL作业设计2.1 ETL作业样例2.1.1 ODM-SDM 层作业样例:图2.1: ETL作业样例ODM-SDM图2.1中，编号1-8是标准化的过程，9-12是异常处理的过程，图中各编号对应的含义:1： Sequential file stag
5、e:源数据接口文本文件2： Sequential file stage:无法正常抽取的数据3： Transformer stage：对正常抽取出来的源数据做预处理，为数据标准化做准备4： DB2 stage：标准化代码对照表，获取新旧代码对照信息5： Filter stage：对标准化代码按类别进行分流6： Lookup stage：各分流后的数据与源数据相应的字段进行匹配，获取标准化代码7： Transformer stage:主要转换stage，对源数据值做数据转换并映射到目标字段， (如果有标准化后的字段为空，则进入9开始异常数据处理过程。)8： DB2 stage：转换完成的数据，选择
6、相应的入库模式加载到目标表。9： Transformer stage：将源接口文件字段按原始数据类型写入文本，供重跑数据使用。10： Sequential stage:与源接口文件类型相同的数据文本文件。11： Transformer stage：为每个无法正常标准化的字段添加注释信息。12 : DB2 stage :将包含注释信息的字段使用 Upsert方式写入“异常数据登记表 (ODS.ODS_STDERR)”中，供维护人员查阅。2.1.2 SDM-FDM层对于SDM-FDM的作业，有两种情况：1. 全量初始化作业2. 增量作业对于全量初始化作业，仅需执行一次，且FDM目标表中无数据，因此
7、过程非常简单，直接 表到表写入记录即可，作业样例如下图：:2009-11-17置箱笑*笛为存瓮甘置筑纂簸茎胡律* 创建时间:2009-11-17 创建人:陈刚 濯系统；核心 数据海表：壶捉输人:SDM. S_01_TDP_FB0Tk输出： TDM. F DF S眦 SATDP注意事项：描述值彳戢至意，避免说忘的信息问题岌观；问题描述修改记录：L.修改记录一修改人2.修改记录二修改人图2.2:全量ETL作业样例SDM-FDM对于增量作业，又分为：1. 每日全量加载2. 历史时间拉链表两种情况，对于每日全量加载的表，处理逻辑与全量初始化相同，直接表到表加载即可，而 对于历史拉链表，则相对复杂一些了
8、。因为对于每条增量数据，需要按主键更新目标表中已有的记录，将其终止时间update 成当日业务日期，并且插入一条生效日期等于当前业务日期、失效日期是 2999-12-31 的数据。在实际的DataStage Job设计中，插入新记录的方式我们使用load可适当提高数 据写入性能，但是考虑到其并行处理的机制，load需要锁表，因此必须将update和load 分开操作，所以，我们在设计中，将JOB拆分成两个，一个负责update，一个负责load， 然后在调度依赖关系表中配置它们的先后执行顺序，即可实现功能需求，又可提高执行效率。 作业样例如下：1. update 过程：林冲漆林将村中 功曲：F
9、 DP_E 猬W 通右亢:。T1 数据旋：s_ *州（士叱倒咏士*耳:Stage InputIrpit riane: |TRJOl_piitp-utColWTlIlS.Eroperties | F华iitioiling |ColaniL |S-tJ Tarset Up 如牝知L 二 Ifra虹E F丽.F_JF_AVQ3 UpsQrt Made：二 Uw-dcifiud. ITpda.*tQ Write Method.二 Ups art= J Citeti :-Tl CLl ent Inzt aiice 肝皿七=#$ODS_DE_& Database = #$ai6_BB_D5I#& rass
10、weri 二 SjQIiSJBJIYBfl0 Server =都叩5_DB-5EfMEK_IN5I祥由 Ue Default liatabase 二 TalseQi Ifcb DaLiilt Sarver 二 &1eq& lk“ 二辫HiIEJlEJE睇匚匚J Ufi皿*Q3 Output Re ieat.5 - F ilssBpd.ale SQLAvaLlablg prop er ti es to add:UTDAIE FUil. IJiF_3A_SAIHf SET EJfDLATE = uJuCHESTFJjl. EHIUATEYKERE (IFJUCro =ORCHESTRATE TP
11、版加 KJfD STSIH =匚底 | CincslheL? DB2_FDM_F_DPAV_SATDP - DB2PJDB EnterprisBDB2_5PJL5_ai_ SAE.IAHAATRO 1M2_FD1LF_DF_SHT一噩TDF使用Upsert方式，需要用户自定义SQL,并在where子句中加上生效日期小于当前业务 日期（即非当日增量数据）、且终止日期等于 2999-12-31（即有效记录）的判断。2. load ( Insert 过程) + K+*X*K- IlM二、TTT2 引! F DP f s.款等 H.Sifi: 01- 京据源衰：WTKJ01_Injiut _Out p
12、utTRODB2S)lL3_ai_SlE_IO?AADBS-FDNLF.HP-SAV-SAITiP使用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，其对照关系保 存在
13、数据表中以便ETL过程进行加工处理。标准化代码对照表在ODS系统中的表名是：ODS COMP，它的结构如下：NameTypeLerigthScale NullsD&scSYSCODECHARACTER2Y源系貌编号TYPEVAR CHAR20Y类型STD_CNVAR CHAR256Y类型中文名 LD_VALUEVAR CHAR50Y源系狙类型值OLD_VALUE_DE5CVAR CHAR256Y源系蜿类型值中文名STD_VALUEVARCHAR50Y类型标推取值STD_VALUE_DESCVARCHAR256Y类型标莅职值中文名图2.4:标准化代码对照表其中、TYPE（类型）”字段记录了各个数
14、据域的类型代码，内容遵循上海农商行数据模 型数据域标准表中的域代码定义，以此表的SYSCODE，TYPE，OLD_VALUE三个字 段为条件，即可获取标准化后的新代码”STD_VALUE”，如图：第一步，从ODS_COMP中抽取数据，在Filter中按域类型将数据分流：X FLT_O1 - Filteriage | Input Output St age rL=jne:pSraSnSJJWGeTier aJ. PrcpertL es Xi1v：=ltic ed. Link Ur lea- i ng | 1ILS Lu csle- Frdi citfis-Where Clause = STSCO
15、IE=#SYS_CODE# A1TD mi- OEGMOJ Output Link = 0-Where Clause = SYSCOIE=#SYS_CODE# A1TD TTPE=J IETPJ Output Link - 1-Where Clause = SYSCOIE=#SYS_CODE# A1TD THE=; SBBHO; Output Link = 2- ）Opti unsQ Uutjiut Re -j e c t e = F：il seQ Output Rw 口以匚曰=Falm图2.5: Filter过滤条件设置第二步，经过分流的数据，分别与源字段值进行匹配，获取标准化代码，如图:I
16、 | ywurz FIE 须!源代码蓿匹配rBJOL_Outpni. KD0FD7JBDFDTran L .Output, kdoptlpboftlrBB L_0utpui. FIIHDI2PBVIDI2nuoi jutr-ut rrjoojmGwo fbaohojrgwrnDL_autput FBnrn_sirBBD FurrcBjiiraHaEBJOL .Output. FEinrD?PHMIHTruoLOutput FunrrLFBWHLrHDL_Output FBCLD7FBELDTrBfll_Outpiit H1CLIIFBELTZrfiJ0L_O-atpu.t. fUDJSX.FB
17、tUSRebjol .Output. FusmrusicBrBfl L _0utpiit HlIRTT_rB.7PFUrETfJIRTFSTD_YXL1TEEC .DE). STE_VAJlireSTD_VmrE_DBGJC92_0E. STTi_iZSrDJrmE_EB.7PTEC1_Diitpot. JTiITrD_SUBJiDFDITCD项JDJTOFDIRrF_IEIT，TRDl_Dutput. TDACHD_aRGHDSC_O1. 皿唯幅STD_ALJlfE_lfBJCiJTiACtn_OR3ffO| ni.r_M_iirE5TD_miTE图2.5: Lookup的关联设置第三步，在
18、Transformer中将获取的标准代码映射到目标表相应字段:FDOFIIIyanfiLtdwthiFDmLrDCLDTFDCL7E7DWDI2FDTCBIDEICB EUBHOTDrETF IRTFSTD.VALlfEjDIJTOSrD_VALlJEjEafBIi：iFDAOiia URGWaSFD VALUE liTP图 2.6:JLK_Hniral i i_ir j nt. ： LOitlicm seDririai.t.i onCoJLvn. UTOj.Outjiit FDACKl：iFBftCWr tni.-i -iTransformer中的字段映射3 ETL加载策略在DB2服务器配置
19、DS节点，直接通过DB2 Enterprise Stage将数据入库，入库 时，对全量和增量有不同的加载策略：1、全量加载使用“Load/append”模式（在全量加载前会执行清除表的操作），此操作对所 有目标表均进行一次即可。2、增量加载有如下几种情况：a）目标表每日记载全量：”Load/Append”直接插入所有数据b）目标表为历史拉链表，需要按照主键关联，将发生变化的END_DT日期 从 2999-12-31 update为ETL_DT，新数据则直接insert至1目标表，且 新数据的结束日期设置为 2999-12-31，实现方法有两种：i. 使用DataStage处理，将需要Updat
20、e和需要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来说，需要配备两套不同的作业分别 处理全量和增量加载，当进行初始化时，执行全量加载作业一次，然后每次执行增量加载。4 ETL调度调度模式
21、：使用DataStage的Sequence Job将作业打包，设计依赖关系，然后使用shell 脚本通过命令行调用“dsjob”命令调度作业需要考虑的是，依赖关系由谁来配置，目前预期有两种方法:1.使用DataStage Job Sequence来配置大部分的JOB依赖关系，如图4.1所示:图 4.1 Job Sequence 示例a）优点：图形化配置界面，开发方便，在job数量不多，关系不是太复杂的情况下 较直观。b）缺点：job数量多时，依赖关系配置较繁琐，且不便定位错误，后期维护也较难。2. 在DB2中维护一张依赖关系表，然后使用shell脚本连接数据库查询依赖关系，按照相应的顺序对作业
22、进行调度：a）优点：粒度较小，定位错误、重新调度单个表的ETL作业流程较方便，维护依赖关系更灵活，便于扩展b）缺点：需要手工编写shell脚本或程序但是目前这两种方法，都必须考虑将来整合进农商行的统一调度平台时，与调度平台的 接口是否能够契合。从功能上考虑，建议采用SHELL或C程序的方式调度，在扩展方面更加灵活。4.1调度的组成调度系统由以下几个部分组成：4.1.1调度模块整个ETL调度的基础，按照分工大致可分为4个模块：初始化模块、文件检查/预处理 模块、作业调度模块、作业执行模块，结构如下图：调度模块的组成 初始化程序负责每日调度的初始化过程，获取所有需要执行的作业列表（文件作业、DS作
23、业）， 并对作业的状态做初始化（置为WAITING）。 文件检查、预处理程序对作业元数据表中，类型为文件的作业进行循环检查和处理，包括检查文件完整性， 文本文件预处理，检查正常并处理完成后，将作业状态更新为DONE，否则状态维持 WAITING不变，等待下一个周期的循环检查。 作业调度程序当所有前置文件作业检查完毕，将非文件作业取出按优先级排序，在并行度允许和 其前置作业完成的前提条件下，进行作业调度，调起执行程序执行作业，并将作业状态 从WAITING置为RUNNING，作业执行完毕后，接收执行程序返回的完成信号对作业 监控表内相应的作业状态进行更新（DONE”或ERROR）。 作业执行程序
24、接收作业调度程序发起的指令，按照作业预先配置的命令开始执行，无论失败与否 都返回一个完成信号给作业调度程序。然后执行程序退出，等待下一次被调度程序调起。 其他外围的文件、数据复制、检查等过程。4.1.2 DB2数据表记录各调度作业的元数据、依赖关系、运行状态、并行度控制等，各数据表名称、字段 名、类型、定义设定如下：JOB_SEQJOB SCHESYSTEM PAHAVARQ-W(1 28) (FK) =1PRE JOB: VARCHAR(12B) (FK).job seclid： IMTEGERJOB_METADATA哥 JOB.NM:VARCHAR(128：iJOB JOB JOB JOB
25、 JOB JOB JOB JOBNM：VARCHAR(12B) STATUS: VARCHAR(a) PRJOHNTEQER SCHE_DATE: DATE QEGIH_DT： DATE BEGIN_TM:TIME END_DT- DATE END TM.T1MEJQBJOBJOBJQBJOBJQBJOBJOBJOB JOB.RE S_TiPE. VM=CHiR(12 D) (FK) RUN_T/FE: VAR.CHAR(6) RUN_DT: VARCHAR(20：j TYPE: VARCHAR(B) CMD: VARCHAR(200J PAR: VARCHAR(20CQ PRIO: INTE
26、GERVJKRES: INTEGER TIMES: INTEGERQDS_SD U_S YS_STSSY5CUDECHAP(2)SQDS DATE； DATEPACK7VFE： VARCHAR(3)PRC_STS: VARCHAR(3D)RE豚RCHARQ 56)ODS.TBLISTSYSC0DE:CHARl2)STBMAME- VARO-R(5D：iSTS_hMEj：JJ: VARCHAR(200：i S GDMO DEL: VARCHAP.fZi RE WARK VARCI-R(256JJOB_RES_CTFIL3JOB RES TYPE: VARCHARC20SOURCESYSCURDA
27、TH:DACHAR(3)NOTE:VARCHAR(50：i下面对这些表的结构含义做一下分析：A. JOB_METADATA此表保存所有作业的元数据，每个最小调度单元对应一条记录，每日运行的初始化 基本数据就从此表中获得：字段名类型长度主键允许空含义JOB_NMVARCHAR128YN作业英文名称，START/END是两个自定义作 业，代表调度的起点和终点JOB_CNMVARCHAR : 128Y作业中文名JOBRESTYPEnVARCHAR : 20N作业资源类型JOB_RUN_TYPEVARCHAR ： 6N作业运行时点类型。/D每天/W每周/M每月/丫每年/叫月底/Z每年年底一JOB_RU
28、N_DTVARCHAR : 20Y作业运行时点。同 jOB_ruN_type共同祚JOB_TYPEJOB_CMDJOB PARnVARCHAR j 8VARCHAR : 200VARCHAR : 200NNN-用作业类型（NODE/DS/SEQ/SQL/SHELL/SP）.作业命令作业参数JOB_PRIOINTEGERN作业优先级，数字越大，优先级越高JOB_WKRESINTEGERN作业资源占用量，可调整、估算JOB_TIMESINTEGERY作业占用时间，估算，调度不使用REMARKVARCHAR256Y备注信息（注：作业资源占用量：JOB_WKRES字段，具体数值按照作业实际运行时所占用
29、的系 统资源来人为评估，例如：普通作业可设定为1，复杂作业或数据量大的作业可设定为 2或更大的值，作业并发调度时，每个正在运行的作业工作量累加不超过最大工作量限 制，保障服务器并发执行多个作业时不至于消耗过多系统资源造成服务器假死或当机。 最大工作量在JOB_RES_CTRL表中进行限定。）B. JOB_SEQ此表保存作业的依赖关系，表结构如下：r字段名类型长度主键允许空含义JOB NMVARCHAR128YN作业名称jPRE JOBVARCHAR128YN_前置作业名称C. JOBSCHE此表保存当日所有需要运行的作业信息、状态信息、运行时间等（可考虑保存历史信息）:字段名类型一一长度主键允
30、许空含义默认值JOB SEQ IDINTEGERYN作业序号JOB NMVARCHAR128Y作业英文名称JOB_STATUSVARCHAR8N作业状态(PRE/WAITING/RUNNING/DONE/ERROR)JOB_PRIOINTEGERY作业优先级，数字越大， 优先级越高0JOB SCHE DATEDATEYODS应用处理数据日期JOBBEGINDTJOB BEGIN TMDATETIMEYY.作业运行开始日期作业运行开始时间JOB END DTDATEY作业运行结束日期JOB END TMTIMEY作业运行结束时间作业序号JOB_SEQ_ID是一个使用DB2序列自动生成的递增整数。
31、D. JOB_RES_CTRL此表限定了各分类最大可运行的工作量，并在作业调度期间实时更新当前正在运行的作业已使用的工作量，控制并发执行的作业数量，从而减轻服务器的压力:.字段名:类型一一一.长度主键.允许空J食义JOBRES TYPE:VARCHAR! 20 9 YN作业资源类型JOBRES NMVARCHARj 200 jY:资源留愁JOBRES MAXINTEGER1Y最大资源量JOBRES IDLEINTEGER:jY可用资源量:REMARKVARCHAR256 :Y备注还有几张表是配合调度运行的数据表，罗列如下:ODS_SOU_SYS_STS（ods 源系统状态表）：字段名类型长度主
32、键允许空含义SYSCODECHARACTER2YN源系统代码ODS DATEDATE4YNODS处理日期:PACKTYPEVARCHAR3N源提供数据打包方式（DFT:default;TAR:tar）:PRC_STSVARCHAR30N当前状态(WAITING/CHECKING/PROPRESS/DONE/ERROR)ODS_TBLIST（ODS源系统文件列表）：E字段名.类型.长度.主键.乙允许空.含义SYSCODECHARACTER2Y ! N源系统代码STBNAMEVARCHAR50Y ： N源系统英文表名STB NAME CNVARCHAR200:Y源系统中文表名SGDMODELVAR
33、CHAR20:Y取数模式EREMARKVARCHAR256:Y备注：字段名类型长度主键允许空含义SYSCODECHARACTER2N源系统代码SYSCNMVARCHAR100NODS处理日期INUSEVARCHAR3N.源提供数据打包方式（DFT:default;TAR:tar）SOURCESYS（源系统定义表）:3PACKTYPE VARCHARN当前状态(WAITING/CHECKING/PROPRESS/DONE/ERROR)SYSTEMPARA (系统参数表):.字段名.1类型长度主键允茹含义CURDATE:DATE一_N_ 一、当前处理日期PROCSTEPWarcharj 10 jY
34、 !所处阶段(WAITING/PROCESSING/PAUSE)CURY：CHARACTER:4 :N :当前日期年份CURM:CHARACTER:2 :N ；当前日期月份CURDCHARACTER:2 :一_N_ 一当前旦期日数CURWjCHARACTER! 1 !N】星期值：7-周日6-周六1-周一BMONTH:CHARACTERii:1 :Y表否月底.Y-是.N-不是BYEAR:CHARACTER；2 ；Y ；是否年底Y-是N-不是ODMDAYS:INTEGERY0DM数据保存天数SDMDAYS:INTEGERI1Y: SDM数据保存天数FDMDAYS；INTEGER11TTY. FD.
35、M数据保存天数LOGDAYS ! INTEGER ! YOG数据保存天数：ODS_STDERROR （标准化异常登记表）：字段名类型长度主键允许空含义默认值ETL DATEDATEYN皿日期CURRENT DATESYSCODECHARACTER2YN源系统编号、TYPEVARCHAR20YN类型TYPE CNVARCHAR256Y类型中文名OLD FIELDVARCHAR20YN源字段英文名OLD FIELD CNVARCHAR256Y源字段中文名_OLD VALUEVARCHAR50YN源系统类型值SOUTABVARCHAR50YN源系统表名NOTEVARCHAR50Y备注4.1.3调度流程关于详细的调度流程，请查看：上海农商银行_ODS_日常运营操作手册.doc
