关于对PL/SQL中开发ETL程序的指导规范
背景
目前项目组大部分ETL程序都是在数据库中直接用PL/SQL开发,随着对ETL程序不断增加/修改,导致对源代码的维护和管理工作日益加大,程序的版本控制问题凸现,在实际工作中经常发现代码版本不正确等问题,给正常开发工作带来了很大影响。
介于上述问题,经项目组决定,将对ETL开发部分进行整体工作进行规范化管理,引入代码版本控制管理、编码规范检查与程序设计文档检查工作。
目的
实现PL/SQL与VSS结合共同管理程序源代码
实现PL/SQL与开发规范结合,自动生成程序设计文档
实现对ETL开发人员工作进行全局指导/检查
如何与VSS结合使用
前提条件
安装oracle客户端
安装vss客户端
安装pl/sql软件
直接解压缩plsql developer8.rar文档(此版本为8.0,已经将VSS插件设置好)
配置与VSS的结合
安装VSS客户端软件(安装过程略)
如何设置工程路径与登陆用户
首先点击菜单 VCS ,在点击OPEN PROJECT 菜单,系统进入如下页面
在此对话框选择输入相应的参数:
**选择工程(Project)路径与用户,如下输入VSS帐户,密码,及VSS库路径:
选择OK之后会让你选择VSS服务器的工程.
下一步选择OK弹出下面窗口,在此窗口选择上一步选择的工程存放到本机的位置
可以自由选择
点击确定回到此菜单
下一步确定
设置完成后点击OK按钮工程设置完成
配置客户端
点击菜单栏VCSàPreferences 在VCS Preferences选项全选.
下次再登陆的时候会在连接数据库之后提示输入密码,而不用在新建项目的时候输入密码
如上**处
如何将程序添加到VSS工程
1在需要添加到vss工程的程序上鼠标右键,点击 ADD TO VCS,系统进入如下页面,设置注释
截至到此,程序已经添加到vss工程中。
2.点击菜单VCSàCheck Out File..
可以选择程序下载到本地配置好的文件内.
2.点击菜单VCSàCheck in File..
如何生成设计文档
注意:文档生成时需要依赖代码注释,所以在编写代码时需要严格按照编码规范编写注视。
设置文档属性
点击菜单中tools->plsqldoc->configure..,进入设置页面
生成文档
在程序上右健点击“Generate Documentation”生成文档,点击“View Documenttation”查看文档。
编码规范
程序设计
1、 程序要遵循模块化设计,保证可拆分性。
2、 计算复杂、运行效率低的指标要考虑补数机制,建议每个指标一个过程,当数据发生错误时,可以单独对一个指标进行补数,避免程序运行等待。
3、 权衡依赖,不要设计依赖关系过多的程序(如a1为一个单独程序,a2运行前必须运行a1),保证程序的可拆分性、增量、补数机制。
代码注释
程序每个部分需要加入明确注释。
1、 包体注释
/*
作 者: 付贺超
创建日期: 20##-3-17
功能描述: 个险渠道指标计算程序包。用于计算DMA_PERS_COMM1、DMA_PERS_COMM2、DMA_PERS_COMM3集市数据。
*/
2、 过程/函数注释
/*
作 者: 付贺超
创建日期: 20##-3-17
功能描述: 处理DM_个险销售人力分析(月报)数据集市所有指标。
参数注释:
V_WORK_S_DATE --工作开始日期
V_WORK_E_DATE --工作结束日期
V_INCR_FLAG --装载0 存量 ,1增量
V_ADDDATA_FLAG --补数机制 1 补数操作 0无效
依赖关系:如果有写上程序名称
基 本 表: DM_CNT人力公共集市
备 注:
修改记录:
1、修改起初在职人力算法,具体修改参加sql部分。(修改人:付贺超
日期:20##-7-21)
2、…
*/
3、 语句注释
/*计算月初在职人力到临时数据表(TMP_DM_PERS_AGENT_M1),用于DM_个险销售人力分析(月报)数据集市*/
4、 变量注释
V_SQL_DES VARCHAR2(200); --语句功能描述
V_SQL_ROWS NUMBER(20); --语句涉及的表数据行数
5、 行注释(字段/条件)
INSERT /*+ APPEND */
INTO TMP_DM_PERS_AGENT_M1
(DATEID, --时间ID
ORGAN_ID, --管理机构ID
SALES_DEPT, --营业部
SALES_GRP, --营业组
AGENT_BEGIN) --期初在职人力
SELECT TO_CHAR(V_STAT_DATE, 'YYYY-MM'), --时间ID
ORGAN_ID, --管理机构ID
SALES_DEPT, --营业部
SALES_GRP, --营业组
SUM(AGENT_BEGIN) --期初在职人力
FROM TMP_DM_PERS_AGENT_D11 T1
WHERE T1.DATEID = TRUNC(V_STAT_DATE, 'MM') --日期等于今天
GROUP BY
TO_CHAR(V_STAT_DATE, 'YYYY-MM'),
ORGAN_ID,
SALES_DEPT,
SALES_GRP;
代码格式
1、 程序格式全部采用pl/sql美化器自动调整
2、 统一代码大小写,建议全部采用大写格式
3、 Select/From/Where/Order by/Group by/Union all等子句必须另其一行写
4、 Select 后如果多个字段,每个字段单独占一行,并且加字段注释
5、 Insert 后如果多个字段,每个字段单独占一行,并且加字段注释
6、 From 后如果多个表,每个join on 单独以行 ,并且加注释
7、 Where 后如果多个条件,每个条件单独占一行,并且加注释
8、 Group by 后如果多个字段,每个字段单独一行,并且加注释
优化方面
1、 如遇到单个语句执行时间超过5分钟未出结果的,需要上报项目在技术主管或项目经理。
2、 编写程序时,如果单个语句连接表超过5个,需要上报给技术主管或项目经理。
3、 所有sql语句在执行前,查看执行计划,检查是否利用上索引,若没有使用任何索引需要给技术主管或项目经理提出索引建议。
4、 避免使用多个case when 嵌套,如要使用需向技术经理或项目经理汇报。
5、 表间连接时,相等连接使用inner join 替代where。
6、 用UNION 替代UNION ALL。
7、 INSERT INTO (SELELCT UNION ALL …)遇到此类语句建议分拆成多个INSERT语句处理,每次插入一个数据集。
8、 查询中要使用别名
9、 检查源表条件字段是否索引
10、 检查大数据量语句是否有ORDER BY
11、 检查CASE WHEN语句,建议替换为DECODE
12、 (大量数据存在前提)检查是否有IN NOT IN 语句,建议替换为EXISTS NOT EXISTS
13、 检查条件字段是否有IS NOT NULL ,建议用>0替代
14、 当条件字段中OR较多时,建议用UNION替代
15、 当条件字段为索引时,不要用<> != 操作符或转换函数,将导致索引无效
16、 将>1 替换为 >=2 小于同此
17、 2张表关联时,将数据量小的表名写在后面
18、 多张表关联时,将交叉表放在最后
19、 多提交COMMIT,释放资源
20、 用具体字段替代SELECT *
21、 COUNT(*) 如果索引字段存在,建议用COUNT(INDEX )
22、 所有临时表采用TRUNCATE模式删除数据
23、 所有临时表在插入数据时采用 append nologging 模式
24、 建议将dw层/ods层/dm层 TABLESPACE分开
25、 建议将单独几张大表 单独建立TABLESPACE
26、 减少使用子查询
27、 适当调整sort_area_size/hash_area_size 大小
28、 使用表分区处理大数据量表
29、 使用并行技术处理加速create table /create index / insert
第二篇:ETl开发规范
项目组希望我出一个ETL的规范,凭着自己的感觉和经验总结了一些步骤,我还会不断改进。
本ETL流程规范试图建立一个通用的ETL流程开发规范,针对不同项目组的实际情况,可自行进行逐步的完善和修改。
本流程应该是在需求分析阶段结束后实施。
ETL流程:
可以把ETL分为五个阶段,按照开发的顺序分:
1) 准备阶段:
? 根据需求定义映射关系,产出物为《ETL映射文档》。
? 分析数据源质量,针对数据源中有问题的数据制定数据抽取原则,产出物为《数据源质量分析报告》、《问题数据处理规范》。
? 确定数据抽取的技术架构,产出物为《全量数据抽取策略》、《增量数据抽取策略》、《数据抽取中异常处理规范》。
? 确定ETL的实现方式,采用ETL工具还是自己开发代码。产出物分别是:《ETL工具使用手册》、《ETL代码开发手册》。这两个文档主要用于员工培训。
2) 开发阶段:
? 根据上个阶段的产出物进行ETL的实现。
3) 测试阶段:
? 测试和优化两个阶段实际上是相辅相成的,测试阶段除了要测试ETL逻辑的准确性,测试过程就可以得出ETL过程的性能结果。产出物为《ETL数据抽取测试报告》。
4) 优化阶段:
? 优化涉及到ETL过程的各个部分,从数据中转区到目标区,从程序代码的优化到数据库参数的调优,从ETL抽取逻辑的优化到技术架构的优化。具体优化的建议过程见《数据仓库优化建议.doc》。
5) 迁移阶段
? 我们经常碰到从一个环境迁移到另一个环境的情况,ETL迁移的过程应该放在数据仓库模型迁移后,也可以根据项目的实际情况一起进行迁移。产出物《ETL迁移文档》。
关键点:
ETL的重点在于元数据的管理、数据质量的管理
(一) 元数据的管理对于ETL来说是非常重要的,现在有了很多元数据管理工具,但我认为最重要的也是最有效的就是从管理机制入手,建立一个有效的管理元数据的制度,虽然这个方法显得土了一点,但对于数据仓库项目来说,还是比较实际的。
1) 建立ETL映射文档。ETL映射文档是元数据的主要依据,它记录数据从数据源到目标表的转换和对应关系。映射可以是一对一、一对多、多对一的。
2) 建立ETL变更流程。ETL变更流程其实是与需求变更紧密结合在一起的,项目中需求的变化是很常见的,需求的变化有可能会导致ETL映射关系、以及ETL业务逻辑的变化。修改ETL映射文件和业务逻辑文件必须从文档开始,并且应该有统一的入口。修改文档应该包括:
? 版本号:
? 修改原因描述:
? 修改过程:
? 修改时间:
? 修改的影响范围:
(二) 数据质量的好坏关系到项目的成败,前端的各种数据分析和展现的手段都是建立在良好的数据质量基础上的。
? 首先数据质量要从防患于未然开始。
我们通过制定统一的ETL规范,严格要求ETL过程中的每一步都要按照规范制定的步骤来执行。规范的制定可以根据各个项目组的实际情况作适当的增减,但是一些原则性的步骤一
定要坚持执行。规范一旦制定下来,就必须严格执行。从源头来保证数据质量的可信度。原则性的步骤包括:
ü 《ETL映射文档》
ü 《数据源质量分析报告》
ü 《问题数据处理规范》
ü 《数据抽取中异常处理规范》
? 建立数据核对和数据效验流程
这部分应该是根据各个项目组的实际情况来考虑制定的。数据效验的方式有多种,常见的是通过报表数据与已有系统中的数据进行比对,比对如果出现问题,关键点在于要明确不同的原因,原因无非有几种:
ü 统计口径不同
ü 已有系统数据有误
ü 自己的数据有误
不要轻易下结论说对方的系统数据错误,如果我们前面的功夫下的足够,我们就可以根据我们上面提到的规范和文档来验证我们的数据是否正确。
? 建立数据修改流程
有些错误是可以通过制定效验规则来自动进行修改的;
而另一部分错误是必须有人工来判断错误原因,并且由人工或者由其他系统来修改
本文来自CSDN博客,转载请标明出处:/Chrisjian/archive/2009/09/01/4507462.aspx