ETL过程及数据库操作

关于ETL的过程以及数据库的常用操作

讲解人：

时间：2015年1月目录

ETL定义

ETL过程

ETL测试方法

SQL语句

PL/SQL介绍ETL定义ETL定义涉及以下内容：ETL定义ETL前提ETL原则ETL定义定义：数据的抽取(Extract)、转换(Transform)、装载(Load)的过程目标：数据优化。以最小代价(包括对日常操作的影响和对技能的要求)将针对日常业务操作的数据转化为针对数据仓库而存储的决策支持型数据ETL的前提确定ETL范围通过对目标表信息的收集，确定ETL的范围选择ETL工具（Kettle、OWB、ODI等）考虑资金运行的平台、对源和目标的支持程度、可编程的灵活性、对源数据变化的监测、数据处理时间的控制、管理和调度功能、对异常情况的处理确定解决方案抽取分析、变化数据的捕获、目标表的刷新策略、数据的转换及数据验证ETL过程中应尽量遵循以下原则：应尽量利用数据中转区对运营数据进行预处理。保证数据的安全性、集成与加载的高效性。ETL的过程应是主动“拉取”，而不是从内部“推送”，其可控性将大为增强。流程化的配置管理和标准协议数据质量的保证正确性、一致性、完整性、有效性、可获取性

ETL定义

ETL过程

ETL测试方法

SQL语句

PL/SQL介绍目录ETL过程ETL过程：数据抽取数据清洗数据转换数据加载ETL过程-0层DFD1层-数据抽取1层-数据清洗1层-数据转换1层-数据加载ETL过程-数据抽取数据来源文件系统，业务系统抽取方式根据具体业务进行全量或增量抽取抽取效率将数据按一定的规则拆分成几部分进行并行处理抽取策略根据具体业务制定抽取的时间、频度，以及抽取的流程ETL过程-数据清洗清洗规则：数据补缺对空数据、缺失数据进行数据补缺操作，无法处理的作标记数据替换对无效数据进行数据的替换格式规范化将源数据抽取的数据格式转换成为便于进入仓库处理的目标数据格式主外键约束通过建立主外键约束，对非法数据进行替换或导出到错误文件重新处理ETL过程-数据转换转换规则数据合并多用表关联实现，大小表关联用lookup，大大表相交用join（每个字段加索引，保证关联查询的效率）数据拆分按一定规则进行数据拆分行列互换排序/修改序号去除重复记录数据验证：lookup，sum，count实现方式在ETL引擎中进行（SQL无法实现的）在数据库中进行（SQL可以实现）ETL过程-数据加载实现方式优点缺点时戳方式在业务表中统一添加字段作为时戳，当OLTP系统更新修改业务数据时，同时修改时戳字段值源数据抽取相对简单清楚，速度快，适合数据的增量加载需要修改业务表中的数据结构，业务数据变动时工作量比较大，相对风险较大日志表方式在OLTP系统中添加日志表，业务数据发生变化时，更新维护日志表内容不需要修改业务表中的数据结构。源数据抽取简单清楚，速度快，适合数据的增量加载业务系统中更新记录日志操作麻烦全表对比方式抽取所有源数据，在更新目标表之前先根据主键和字段进行数据比对，有更新的进行update或insert对系统表结构没有任何影响，管理维护统一，可以实现数据的增量加载数据比对复杂，设计比较复杂，执行速度慢全表删除插入方式删除目标表数据，将源数据全部插入ETL规则简单，速度快对维表加代理健不适应，OLTP系统有删除数据时，不能在数据仓库体现被删数据，不能实现增量加载

ETL定义

ETL过程

ETL测试方法

SQL语句

PL/SQL介绍目录ETL测试方法1、数据量统计源表和目标表数据量2、

转换规则测试1）数据格式的合法性。对于数据源中时间、数值、字符等数据的处理，是否符合数据仓库规则，是否进行统一的转换。2）值域的有效性。是否有超出维表或者业务值域的范围。3）空值的处理。是否捕获字段空值，或者需要对空值进行替换为其他含义值的处理。4）主键的有效性。主键是否唯一。5）乱码的检查。特殊符号或者乱码符号的护理规则。6）脏数据的处理。比如不符合业务逻辑的数据ETL测试方法3.关键字段测试通过转换规则，查询关键字段是否正确。比如保费收入字段，看其是否乘以汇率，共保比率等;一般表中会添加时间戳，时间戳数据和数据格式是否正确4.抽样测试

通过抽样测试源表和目标表映射是否正确。5.加载规则测试，一般加载方式有两种：全量加载和增量加载

增量加载一般是先删后插（deleteandinsert）。

全量加载一般是先清空再插入（truncateandinsert)，

ETL定义

ETL过程

ETL测试方法

SQL语句

PL/SQL介绍目录SQL语句介绍1.SQL的特点综合统一：

集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体。高度非过程化：用SQL语言进行数据操作，只要提出“做什么”，无须指明“怎么做”，因此无需了解存取路径。2.SQL概述1.SQL的特点语言简洁，易学易用：完成核心功能只用9个动词：2.1数据定义语句DDL定义、修改与删除基本表2.2查询SELECT语句格式：SELECT[ALL|DISTINCT]<目标列表达式>[，<目标列表达式>]…FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名1>[HAVING<条件表达式>]][ORDERBY<列名2>[ASC|DESC]]；2.3数据操纵语句DML插入语句INSERT插入单个元组例：INSERTINTOstudents(num,name,sex)VALUES(‘E0001’,’小明’,’男’);插入子查询结果例：INSERTINTODeptage(Sdept，Avgage)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；2.3数据操纵语句DML修改语句UPDATE三种修改方式修改某一个元组的值例：UPDATEStudentSETSage=22WHERESno=‘95001’；修改多个元组的值例：UPDATEStudentSETSage=Sage+1修改子查询的结果例：UPDATESCSETGrade=0WHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)2.3数据操纵语句DML删除语句DELETE删除某一个元组的值

例：DELETEFROMStudentWHERESno='95019'删除多个元组的值例：DELETEFROMSC带子查询的删除语句

例：DELETEFROMSCWHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；

ETL定义

ETL过程

ETL测试方法

SQL语句

PL/SQL介绍目录PL/SQLDeveloperPL/SQLDeveloper是一个集成开发环境，专门面向Oracle数据库存储程序单元的开发。打开安装目录，找到配置文件tnsnames.ora，配置地址即可打开数据库。例：hive=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=11.23.31.17)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))PL/SQLDeveloper常用操作

PL/SQLDeveloper常用操作常用的操作按钮：新建sql窗口、执行、提交、退回、登陆、格式化按钮在左侧的allobjects中找到Tables,选中所要查看的表右击选择查看能查看到该表的详细信息，点击下方的查看Sql,能查看到该表的sql表达。PL/SQLDeveloper常用操作PL/SQLDeveloper常用操作PL/SQLDeveloper常用操作--CreatetablecreatetableABC(cust_idnoVARCHAR2(255),cust_nameVARCHAR2(255),identify_typeVARCHAR2(255),identify_numVARCHAR2(255),insured_flagVARCHAR2(255),)tablespaceECIF_MGRpctfree10initrans1maxtrans255storage

(initial64next1minextents1maxextentsunlimited

);PL/SQLDeveloper常用操作“╋”增加一条新的空白记录“━”删除记录“√”记入改变PL/SQLDeveloper常用操作PL/SQL的常用对象类型：表、视图、索引、存储过程、触发器……操作方法如同之前介绍的表的操作。要进行查看就选择