ETL开发规范.doc

ETL 开发规范开发规范 0 ETL 开发规范开发规范 2009 年年 2 月月 键入文字 文档控制文档控制 版本版本文文件准备人件准备人修改内容修改内容准备日期准备日期审核审核 ETL 开发规范开发规范 1 目目 录录 1前言前言 2 1 1读者读者对对象象 2 1 2编写目的编写目的 2 2ETL 简介简介 2 3开发开发 ETL 需要的背景知识需要的背景知识 3 4常用常用 SQL 知识介绍知识介绍 3 4 1NOT IN 与与 MINUS 3 4 2多表连接中的条件限制 多表连接中的条件限制 WHERE AND ON 3 4 3游标尽量少使用游标尽量少使用 5 4 4异常捕捉异常捕捉 5 5SQL 编写规范编写规范 6 5 1原则定义原则定义 6 5 2大小写规则大小写规则 6 5 3缩进与换行缩进与换行 6 5 4别名别名 7 5 5运算符前后间隔要求运算符前后间隔要求 8 5 6变量引用变量引用 8 5 7注释注释 8 5 8其它其它 9 6脚本编写规范脚本编写规范 9 6 1UltraEdit 9 6 2PERL 10 6 3SHELL 10 7ETL 开发流程开发流程 10 7 1ETL 开发流程开发流程 10 7 2文档编文档编写写要求要求 11 ETL 开发规范开发规范 2 1前言前言 1 1读者对象读者对象 1 所有的 ETL 开发人员 2 需要了解 ETL 开发过程的相关人员 3 数据仓库应用开发人员 开发中需要写 SQL 1 2编写目的编写目的 随着软件项目规模的日益扩大 随之而来的项目团队也不断增加 这给项 目代码组装和后期维护带来很大的挑战 为便于项目的代码组装以及降低项目 的后期维护成本 本文总结了 ETL 开发过程中各种共性的东西 包括需要优先 准备的一些背景知识 SQL 编写要求 脚本编写要求 开发流程 容易出现问 题的地方等 提供给所有参与 ETL 开发的同事使用 2ETL 简介简介 ETL 是指数据的抽取 Extract 转换 Transform 和加载 Loading 是 数据仓库系统实施的一个非常重要的环节 在项目实施的第一阶段中是项目实 施的工作重点 建立一套完整 正确 高效的数据抽取 转换和加载机制 是 数据仓库的基础性目标 在 ETL 的设计中 ETL 的模式可以是 ETL ELT ETLT 现在比较基本 的是 ETLT ETL 是指先从源系统中将数据抽取出来 然后通过使用高级语言编写 的应用程序进行文件级的转换 一般是指码制 格式等转换 不需要 使用数据库的表关联方式 然后进行加载 ELT 是指先从源系统中将数据抽取出来 然后加载到数据仓库中 然 后在数据仓库中实现数据的转换 一般通过 SQL 来实现 ETLT 是指先从源系统中将数据抽取出来 经过一定的转换再加载到数 据仓库中 然后再按照最终 PDM 的要求再次进行一次转换 ETL 开发规范开发规范 3 广义的 ETL 还包括产生数据集市的数据 产生 OLAP 数据等 即从用户 的源系统准备数据开始 直到数据仓库的最终用户可用的数据的过程 都属于 ETL 3开发开发 ETL 需要的背景知识需要的背景知识 这里只是指出需要优先学习的部分内容 1 ANSI SQL 2 数据库 如 ORACLE 10g 3 操作系统 如 AIX Linux 等 4常用常用 SQL 知识介绍知识介绍 4 1NOT IN 与与 MINUS NOT IN 和 MINUS 是在数据仓库的 SQL 中是经常使用的语句 但经常发 生使用错误 主要是 NULL 惹的祸 参与比较的双方 只要有一方为参与比较的双方 只要有一方为 NULL 比较的结果就只能是未知 比较的结果就只能是未知 请大家记住 使用 NOT IN 时 在两个运算集合中不可以有 NULL 出现 使用 MINUS 时 可以允许 NULL 出现 但从性能上考虑 NOT IN 要比 MINUS 要快很多 所以在使用时 如果你发现要比较的运算集合中每个字段都是非空 NOT NULL 建议使用 NOT IN 如果你不确定是否都是非空 建议使用 MINUS 会比较保险一些 4 2多表连接中的条件限制 多表连接中的条件限制 WHERE AND ON 对于内连接来说 使用 where and on 对连接条件进行限制 其结果都是一 样的 对于外连接来讲 其结果就大不相同了 AND 是对连接前的各个表进行是对连接前的各个表进行 限制 限制 WHERE 是对连接之后的结果集进行限制 是对连接之后的结果集进行限制 大家可以仔细体会下面几个 例子 ETL 开发规范开发规范 4 ETL 开发规范开发规范 5 4 3游标尽量少使用游标尽量少使用 数据库计算的优势在于集合计算 而人为的使用游标使得集合计算演变为 单记录计算 这样不仅很难充分利用数据库本身的优化器 而且影响事务处理 的原子性 所以对游标有以下建议 1 在数据进行清洗的过程中 需使用游标来对脏数据进行清洗 2 在数据清洗完成后的业务计算 全部采用集合计算 即不要使用游标 4 4异常捕捉异常捕捉 存储过程中 不允许对异常捕捉后不做任何处理 即异常丢失 所有的 SQL 都需要检查其 SQLCODE 值 并做相应记录 对 SQLCODE 为非零的情况 一定要返回给父进程进行后处理 这部分处理一般有两种方法 其一是标记错 误 其二直接出错退出 前者适合一般错误 如字段不符合业务规则等 后者 则针对严重错误 如维度表为空 ETL 开发规范开发规范 6 5SQL 编写规范编写规范 5 1原则定义原则定义 1 要求代码行清晰 整齐 具有一定的可观赏性 2 代码编写要充分考虑执行速度最优的原则 3 代码行整体层次分明 结构化强 4 代码中应有必要的注释以增强代码的可读性 5 规范要求非强制性约束代码开发人员的代码编写行为 在实际应用中在 不违反常规要求的前提下允许存在可理解的偏差 5 2大小写规则大小写规则 所有的 SQL 语句中的保留字均采用全部大写 不要使用缩写 表别名 也要大写 如 ALL AS CASE CREATE DATABASE DELETE FROM IN INSERT JOIN LEFT NO NOT NULL OUT SELECT TABLE TITLE UPDATE VIEW WHERE 等 表名 视图名 宏和存储过程名 全部大写 字段名 每个单词的首字母大写 其余部分小写 如 Party Id Cust Id Prod Inst Id Acct Id Type Id 等 存储过程中的内部变量名 全部小写 5 3缩进与换行缩进与换行 整个的 SQL 语句最好按照子句进行分行编写 SELECT FROM WHERE UPDATE INSERT 等每个关键字都要另起一行 如 同一级别的子句间要对齐 ETL 开发规范开发规范 7 逗号放在每行的开头 分号放在 SQL 语句的最后 单独占一行 每行宽度不超过 120120120 字符字符字符字符 每个字符为 8 个点阵宽 超过行宽的代码可 折行与上行对齐编排 下面的编写方式不是好的形式 在所有需要缩进的地方 每次缩进 4 格 在以下情况下需要缩进 不同层次的 SQL 语句之间 SELECT INSERT 等关键字之后的字段列表和关键字之间 5 4别名别名 SQL 语句别名的命名 分层命名 从第一层次至第四层次 分别用 P S U D 都是大写字母 表示 取意为 Part Segment Unit Detail 对于同一层次的多个子句 在字母后加 1 2 3 4 区分 如下图所示 ETL 开发规范开发规范 8 5 5运算符前后间隔要求运算符前后间隔要求 算术运算符 逻辑运算符的前后至少要保留一个空格 如下图所示 5 6变量引用变量引用 在 SQL 语句中引用主机变量 如 PERL SHELL 等 时 要在变量名 两端加花括号 对日期变量的引用要在单引号内 如 MYDATE 5 7注释注释 针对复杂的 SQL 语句 请尽量增加相应的注释说明 以便自己和其它同事 事后可以比较容易的读懂和修改 注释中应包含以下内容 编写人 编写日期 修改人 修改日期 该脚本的编写目的与主要内容 如果有特殊处理 特别的技巧等内容 一定要在注释中详细说明 每一大的模块之前要有注释 说明该模块的主要作用 以下是注释必需项 1 程序头注释 应说明以下几项 功能 创建日期 创建人 修改记录 修改人 修改日期 修改描述 ETL 开发规范开发规范 9 源表 目标表 参数 调用 以下是一个典型的程序头注释区 2 程序体注释 应说明以下几项 变量声明区 变量初始化区 主程序开始 垃圾回收区 5 8其它其它 对于内连接和外连接的使用 要求该使用外连接的地方都已经使用了外连 接 不需要外连接的地方一定不使用外连接 表中的字段若是从其它表引用的 要确保该字段在被引用的表中存在 此外 SQL 代码中尽量不要使用 必须明确具体字段列表 6脚本编写规范脚本编写规范 6 1UltraEdit 编写 SQL ETL 开发规范开发规范 10 6 2PERL 脚本开头可参照下面 6 3SHELL 7ETL 开发流程开发流程 7 1ETL 开发流程开发流程 ETL 的开发通常会以 PDM 的一个表或几个相关表为单位 我们称之为 JOB 每一个 JOB 的开发通常会包含以下步骤 1 编写加载说明文档 包括 MAPPING 文档 2 开发初始与日常加载脚本 3 测试初始与日常加载脚本 4 编写测试报告 ETL 开发规范开发规范 11 加载说明文档的模板和测试报告的模板因项目而异 格式可以参考每个项 目具体的文档模板 开发时最好以 JOB 为单位 由易到难 一个 JOB 结束以后再着手开发下 一个 JOB 当第一个 JOB 开发完成之后 由 ETL 相关负责人员确认文档 脚 本等都没有问题以后 再进行下一个 JOB 的开发 对于 JOB 的开发 我们实行 终身版权制终身版权制 在项目结束之前