ETL开发规范.docx

ETL开发规范.docx

《ETL开发规范.docx》由会员分享，可在线阅读，更多相关《ETL开发规范.docx（28页珍藏版）》请在冰豆网上搜索。

ETL开发规范

ID：

LC-CMM-PE-TEMPLATE-22

中国电信省级经营分析概要设计规格书

（业务分册）

（V1.0）

南京联创科技股份有限公司

文档信息

项目名称

XX项目

项目经理

XXX

文档编号:

LC-CMM-PE-TEMPLATE-22

文档变更记录

版本编号

版本日期

作者

说明

V1.0

2003-11-11

XXX、XXX

初稿

审核

审核组织

审核代表

签字

时间

批准

批准组织

批准代表

签字

时间

1总则

1.1概述

本规范定义联创科技在中国电信公司省级经营分析系统（PBI）项目中ETL相关的设计、建设基本原则和规范。

1.2设计原则

本规范适用于联创科技股份公司省级电信经营分析项目ETL小组。

1.2.1统一设计、规范开发

1、省级经营分析系统由于数据庞大，因此，在数据ETL上花费的工作量是巨大的，为了减轻系统负担，提高系统效率，繁杂的统一编码、统一维度标识等工作需要在数据进入数据仓库之前完成，仓库区之后只负责有限范围内的ETL工作。

2、省级经营分析的接口为文件接口，此时，需要建立规范、清晰的文件接口格式及数据传送协议。

3、对于编程工作，需要定制统一的开发模板，及开发说明及修改模板，以利于和测试、工程等后续环节的良好衔接。

4、ETL编程的模块划分将配合系统规划进行各模块的划分，清晰地体现出各个模块之间的依赖关系。

并降低各模块之间的耦合，保证模块内的稳定性，和良好扩展性。

1.3数据获取和ETL处理

1.3.1数据获取

设计点和评估因子说明：

业务系统承载能力评估、网络承载能力评估、数据获取实时性需求、接口双方通讯机制与约定、接口数据的维护。

1.3.2ETL处理

设计点和评估因子：

数据转换处理逻辑、重抽支持、性能评估

1.4数据存储

1.4.1数据分表

进行按时间的数据分割处理，目的是能够方便、可行地进行索引建立、提取业务数据片断，同时也为ETL重抽提供基本保障。

1.4.2数据分区

分区实际上是数据分割技术更细节的延伸，对于省级经营分析系统来说，数据量庞大，对各地传送的数据，需要按地域进行分区以方便数据统计、ETL重抽。

1.4.3关于两个特征字段

说明：

数据仓库为所有的RECORDS（记录）设计了ETL_CYCLE_ID（ETL周期标识）和（ETL_TIME）记录实际加载时间两个特征字段，分别标识记录集合所属的ETL周期和记录的实际加载时间。

目的：

1、方便重抽处理

2、方便审核数据

例如：

200308月的帐单实际在2003年10月01日加载至数据仓库，那么这批记录的ETL_CYCLE_ID赋值为200308，加载时间赋值为2003年10月01日。

2ETL规范

2.1数据处理规范

2.1.1标识统一规范

省级经营分析的数据来源于各个系统，即使在数据接口之前就已将各个本地网之间的数据标识统一，但数据汇集之后，仍然难保证标识的一致性，因此，ETL小组约定，所有的标识均按以下规则进行数据转换：

1、所有标识位类型：

VARCHAR2（30）;

2、[地域编码（两位）]_[接口标识]_[后缀补码];

3、后缀补码的约定为字符9;

2.1.2数据分表命名规范

ETL的数据分表策略如下：

[模板表名]_[分割日期]

说明：

分割日期可以为天、月、年，具体由处理周期及存储策略决定。

分表在数据处理过程中直接生成。

2.1.3程序命名规范

程序命名需要做到见名知义，程序名尽量起得清晰，通俗：

业务模块命名规则：

[程序属性简写]_[处理业务模块表名]

遵循以上规则，名称超过规范长度的，可以适当进行简写。

例如：

对后端集市_市话主叫统计表（TBM_LS_CALLING）进行ETL的函数，则函数名为：

F_TBM_LS_CALLING

对后端集市_市话主叫统计表（TBM_LS_CALLING）进行ETL的程序，则程序名为：

P_TBM_LS_CALLING

技术模块的命名根据具体功能进行命名，不强制进行限定。

2.1.4日志规范

由ETL工具调度后统一记载日志，各业务ETL代码不需要重复记载此信息。

2.1.5动态建表规范

在程序执行过程中由程序自动建立的表为动态建表，ORACLE动态建表时必须指定以下几个参数：

1、表空间名（TABLESPACE）;

2、表初始化大小（INITIAL）;

3、表扩展大小（NEXT）

4、模板表名;

其余策略可以默认表空间的策略

2.1.6参数规范

所有的ETL程序均设置以下传入参数：

stringstrCycleID;//cycleid

stringstrTemplateTableName;//模板表名

stringstrTableSpaceName;//表空间名

stringstrInitialSpace;//初始尺寸

stringstrNextSpace;//扩展尺寸

intiAutoPartition;//是否自动分区

stringstrPartitionColumn;//分区列名

intiPartitionNum;//分区个数

stringstrStatUserName;//统计用户名

stringstrStatPassWord;//统计口令

stringstrStatServerName;//统计服务名

所有的ETL程序均设置以下传出参数：

psssparam->strRetCode="0";//返回代码

psssparam->strRetMsg="NULL";//返回消息

说明：

错误代码及信息统一定制，日后将形成附加文档

2.2ETL模块划分

由于ETL调度工具的特性，一个表对表的ETL过程必须单独形成一个独立可执行文件供ETL工具调度。

注：

数据仓库的数据由ETL工具直接生成，不需要编写代码;

2.3代码编写规范

2.3.1编写目的

编码规范对于程序员而言尤为重要，有以下几个原因：

Ø一个软件的生命周期中，80%的花费在于维护

Ø几乎没有任何一个软件，在其整个生命周期中，均由最初的开发人员来维护

Ø编码规范可以改善软件的可读性，可以让程序员尽快而彻底地理解新的代码

Ø如果你将源码作为产品发布，就需要确任它是否被很好的打包并且清晰无误，一如你已构建的其它任何产品

Ø为了执行规范，每个软件开发人员必须一致遵守编码规范。

好的代码规范是产品开发的生命线。

制定本规范是希望在整个小组内形成一个统一的代码风格，提高代码的可维护性。

2.3.2代码编写通用规范

无论是SQL，还是C/C++语言，ETL小组的程序员必须遵循以下几个共同准则：

2.3.2.1最重要的规则

保持简单、优雅的设计通常胜过复杂的编码。

2.3.2.2代码自文档化

要求编码人员将一部分基本文档直接以注释的形式输入代码中，并持续维护这些文档。

具体要求如下：

2.3.2.2.1模块代码头注释要求

模块头部说明：

要求编码人员在模块头部位置注释模块信息，包括模块名称、模块标识，模块的主要逻辑。

示例：

/*==============================================================

功能:

　　　删除保留周期之外的记录

输入参数：

iv_c_table_nameINVARCHAR2,--当前进行数据抽取表名

iv_c_table_nameINVARCHAR2,--表属主名

iv_c_cycle_idINVARCHAR2,--数据抽取对应周期

iv_n_data_cycleINNUMBER,--数据保留周期

iv_c_cycle_colINVARCHAR2,--周期字段

输出参数：

ov_n_sqlcodeOUTNUMBER,--处理结果

ov_c_sqlerrmOUTVARCHAR2--处理结果信息

程序说明：

因删除记录并不能释放ORACLE里被占用的数据块表空间.

一定保留周期之内的时候不可以采用DELETE记录的方式，

而需要数据转存后进行表的重新生成

版本号：

V

作者：

　　　魏凌　　　　　　　　　　　　　　　　　　　　

创建日期：

　2003-12-10

/*==============================================================*/

2.3.2.2.2代码段落注释要求

每个代码的段落必须进行简要注释，内容包括段落的大致功能，帮助读者进行总体理解，如果段落逻辑比较复杂，可以增加逻辑方面的注释。

示例：

--修改运行结束日志

SETend_time=iv_d_end_time,

run_result=iv_c_run_result,

err_msg=iv_c_err_msg

WHEREapp_id=iv_n_app_id

ANDetl_cycle_id=iv_c_cycle_id

ANDcycle_seq=v_n_max_cycle_seq;

COMMIT;

2.3.2.2.3代码行注释要求

1、避免不必要的行注释，保证代码、变量可读的前提下建议不使用行注释。

2、使用行注释解释对读者可能是晦涩的代码

示例：

--20P表示销帐，20Q表示欠费

SELECTCOUNT（*）INTON_CNTFROMACCT_ITEM_200308WHERE

ACCT_ITEM_STATENOTIN（‘20P’,‘20Q’）;

2.3.2.2.4新增、修改注释

修改和新增的代码段应该打上相应的标记：

修改日期，动作类型，修改者，版本号。

这样便于维护人员了解修改的内容，对于需要删除的代码段暂时只允许采用注释的方式进行删除，在一段时间后，统一整理删除，这样有利于代码的回滚

2.3.2.2.5其他类型的注释

其他类型的注释用来帮助记录代码或者自文档化时附加的信息，例如对正式发布的代码进行本地化修改时的注释等。

2.3.2.2.6代码和注释的维护要求

1、一致性

在代码的整个生命周期中，必须保障代码和相应的注释一致性。

2、历史性

对已经正式发布的代码进行维护、修改活动必须保留一定的痕迹。

2.3.3SQL代码编写规范

2.3.3.1统一编辑

为避免已经编辑好的代码格式被打乱，影响代码阅读，统一使用编辑器UltraEdit-32或是PLSQLDEVELOPER，版本不限。

使用缩进格式帮助表示代码结构、段落、分组，缩进可以使用TAB，TAB应该设为4位

按照模块代码的大致逻辑对代码进行分段，并使用两行空行进行段落划分。

2.3.3.2注释的位置和标识

注释一般位于相应代码的正上方或者尾随对应的代码，注释和相应的代码之间不应有分割象征意义的编辑符号存在（如：

空行）。

必须使用/*…*/标识段落注释，必须使用/*…*/标识头注释，必须使用--标识行注释，对于其他类型的注释不作标识符号的要求。

2.3.3.3代码运行跟踪

所有的SQL代码均需要定义位置变量v_c_place标识出代码目前执行到的位置，程序运行完成之后也需要将位置信息结合完成写入日志，以方便快捷地定位程序执行完成时代码所运行到的步骤;

2.3.3.4程序版本说明

在存储过程的变量区首部，必须定义一个变量v_VER，新创建时，值为，后面由SCM负责根据版本情况进行更改。

工程实施人员根据这个变量的值，来判定当前的程序是否是最新的程序。

2.3.3.5模块入口和出口

每个模块入口时必须初始化模块返回标志为正常状态。

每个模块只有一个正常出口，和一个出错出口（可以包含多个特定的出错处理但是必须有一个缺省定义的出错处理），要求将正常出口和出错出口统一编写、定义在模块代码的尾部，具体参见本小节中统一模块代码框架项目。

2.3.3.6模块代码框架例子

PROCEDUREP_TICUST_ALL（iv_c_cycle_idINVARCHAR2,

iv_n_cycle_typeINNUMBER,

ov_n_sqlcodeOUTNUMBER,

ov_c_sqlerrmOUTVARCHAR2）

IS

/*==============================================================

功能:

　　　进行接口系统客户档案相关数据抽取

输入参数：

iv_c_cycle_idINVARCHAR2,--数据抽取对应周期

iv_n_cycle_typeINNUMBER,--抽取周期类型

输出参数：

ov_n_sqlcodeOUTNUMBER,--ORACLE错误代码

ov_c_sqlerrmOUTVARCHAR2--ORACLE错误信息

版本号：

V

作者：

　　　LiHui　　　　　　　　　　　　　　　　　　　　

创建日期：

　2003-11-08

/*==============================================================*/

v_c_verVARCHAR2（10）:

='V';--程序版本号变量

v_n_app_idNUMBER;--程序APP_ID变量

v_d_start_timeDATE;--程序开始时间变量

v_c_placeVARCHAR2（100）;--程序运行位置变量

v_n_data_cycleNUMBER;--数据保留周期

v_c_sqlVARCHAR2（4000）;--动态SQL变量

v_c_begin_dateVARCHAR2（14）;--开始日期;

v_c_end_dateVARCHAR2（14）;--结束日期;

v_c_cycle_idVARCHAR2（14）;--抽取周期;

v_c_int_typeCHAR

（1）;--增量类型

v_e_errsEXCEPTION;

v_n_countNUMBER;

BEGIN

ov_n_sqlcode:

=0;

ov_c_sqlerrm:

='客户档案相关数据抽取P_TICUST_ALL';

--保留iv_c_cycle_id初值

v_c_cycle_id:

=iv_c_cycle_id;

v_c_place:

='计算当前周期标识';

PUB.PKG_PUB.SP_GET_CYCLE_DATE（v_c_cycle_id,iv_n_cycle_type,v_c_begin_date,v_c_end_date,ov_n_sqlcode,ov_c_sqlerrm）;

IFov_n_sqlcode<>0THEN

RAISEv_e_errs;

ENDIF;

v_c_place:

='读取程序对应APP_ID';

WHEREPROCESS_INFO='P_TICUST_ALL';

v_c_place:

='写程序开始日志';

v_d_start_time:

=sysdate;

PKG_PUB.SP_WRITE_RUN_LOG（v_n_app_id,v_c_ver,v_c_cycle_id,v_d_start_time,null,null,null）;

IFov_n_sqlcode<>0THEN

RAISEv_e_errs;

ENDIF;

--先依据传入的数据抽取周期调用公共函数，取出对应的开始和结束日期

v_c_place:

='读取周期对应开始和结束时间';

PKG_PUB.SP_GET_CYCLE_DATE（v_c_cycle_id,iv_n_cycle_type,v_c_begin_date,v_c_end_date,ov_n_sqlcode,ov_c_sqlerrm）;

IFov_n_sqlcode<>0THEN

RAISEv_e_errs;

ENDIF;

--判断数据抽取方式

IFLENGTH（v_c_cycle_id）=6THEN

v_c_int_type:

='F';

ELSE

v_c_int_type:

='I';

ENDIF;

v_c_place:

='读取TICUST_CUST表数据保留周期';

SELECTDATA_STORE_CYCLEINTOv_n_data_cycleFROMTIMAN_STORE_CYCLE_CFG

WHEREINT_TABLE_NAME='TICUST_CUST';

v_c_place:

='删除保留周期之外及当前周期的数据';

PKG_PUB.SP_DEL_SPILTH_REC（'TICUST_CUST','IPD',v_c_cycle_id,v_n_data_cycle,'INT_CYCLE_ID',ov_n_sqlcode,ov_c_sqlerrm）;

IFov_n_sqlcode<>0THEN

RAISEv_e_errs;

ENDIF;

SELECTCOUNT（*）intov_n_countFROMall_indexes

WHEREINDEX_NAME='IDX_TICUST_CUST_IC'ANDOWNER='IPD';

IFv_n_count>0THEN

v_c_place:

='删除已存在的索引IDX_TICUST_CUST_IC';

PKG_PUB.SP_EXEC_DSQL（'DROPINDEXIPD.IDX_TICUST_CUST_IC',ov_n_sqlcode）;

IFov_n_sqlcode<>0THEN

RAISEv_e_errs;

ENDIF;

ENDIF;

v_c_place:

='先取SICUST_CUST表中对应TICUST_CUST的数据';

--抽取客户档案数据

v_c_place:

='抽取客户档案数据';

v_c_sql:

='INSERTINTOTICUST_CUST'||

'（CUST_ID,CUST_SEQ,CUST_CODE,CUST_NAME,UP_CUST_ID,'||

'AREA_ID,CERT_NBR,VIP,CUST_TYPE_ID,CUST_GRADE_ID,'||

'CONS_GRADE_ID,INDUS_CAT_ID,ZIPCODE,CREATED_DATE,REGION,'||

'MEMO,MODI_DATE,ADDRESS_ID,'||

'STATE,'||

'INT_DATE,INT_FLAG,INT_CYCLE_ID）'||

'SELECT'||

'CUSTOMER_ID,NVL（CHG_SERN,0）,CUSTOMER_ID,CUST_NAME,PARENT_ID,'||

'XJDM,SOCIAL_ID,VIP_NBR,NVL（CUST_CLASS,''3''）,CUST_LEV,'||

'CUST_LEV,TRIM（CUST_STYLE）,POSTCODE,CREATE_DATE,LATN_ID,'||

'BZ,CHG_DATE,LPAD（CUSTOMER_ID,15,''0''）,'||

'DECODE（CUST_STATE,''70A'',''10A'',''70H'',''10X'',''70X'',''10X''）CUST_STATE,'||

'sysdate,''0'','||v_c_cycle_id||

'FROMSICUST_CUST';

IFv_c_int_type='I'THEN

v_c_sql:

=v_c_sql||'WHERECHG_DATE>=TO_DATE（'''||v_c_begin_date||''',''YYYYMMDDHH24MISS''）'||

'ANDCHG_DATE<=TO_DATE（'''||v_c_end_date||''',''YYYYMMDDHH24MISS''）';

ENDIF;

EXECUTEIMMEDIATE（v_c_sql）;

COMMIT;

v_c_place:

='创建索引IDX_TICUST_CUST_IC';

EXECUTEIMMEDIATE'CREATEINDEXIDX_TICUST_CUST_ICONTICUST_CUST（INT_CYCLE_ID）'||

'TABLESPACETBS_IPD_IDXstorage（initial5Mnext5M）';

v_c_place:

='程序正常结束';

ov_c_sqlerrm:

=v_c_place||ov_c_sqlerrm;

--写程序结束日志

PKG_PUB.SP_WRITE_RUN_LOG（v_n_app_id,v_c_ver,v_c_cycle_id,v_d_start_time,sysdate,'0',ov_c_sqlerrm）;

EXCEPTION

WHENv_e_errsTHEN

ROLLBACK;

ov_n_sqlcode:

=SQLCODE;

ov_c_sqlerrm:

=ov_n_sqlcode||v_c_place||ov_c_sqlerrm||SQLERRM;

--写程序结束日志

PKG_PUB.SP_WRITE_RUN_LOG（v_n_app_id,v_c_ver,v_c_cycle_id,v_d_start_time,sysdate,'1',ov_c_sqlerrm）;

WHENOTHERSTHEN

ROLLBACK;

ov_n_sqlcode:

=SQLCODE;

ov_c_sqlerrm:

=ov_n_sqlcode||v_c_place||ov_c_sqlerrm||SQLERRM;

--写程序结束日志

PKG_PUB.SP_WRITE_RUN_LOG（v_n_app_id,v_c_ver,v_c_cycle_id,v_d_start_time,sysdate,'1',ov_c_sqlerrm）;

ENDP_TICUST_ALL;

2.3.3.7SQL语句书写要求

1、SQL语句中，关键字、命令、函数，表名、字段名等一律采用大写字母标识;

2、SQL语句中，对自定义变量，自定义表名、字段