数据仓库ETL技术探究

ETL概述

在构建商业智能系统的时候，如何正确有效地将分散在各个不同数据源中的信息整合到系统中成为了整个系统成败的关键，直接影响到系统的运行效率和最终结果。
ETL正是解决这一问题的有力工具。
ETL是指把数据从数据源装人数据仓库的过程，即数据的抽取(Extract)、转换(Transform)和装载(Load)过程。ETL过程的实质就是符合特定规则的数据流动过程，从不同异构数据源流向统一的目标数据。其间，数据的抽取、清洗、转换和装载形成串行或并行的过程，每个过程都必须符合特定的规则。根据国内外众多实践得到的共识，ETL规则设计和实施所需工作量约占整个项目的60%~80%。由于ETL过程的重要性和复杂性，如何设计正确、高效的ETL过程已经成为了商业智能系统构建过程中无法回避的重要问题。

1 数据理解

在设计ETL过程之前，有一项非常重要但经常被人们所忽略的工作，那就是数据理解。
数据理解是通过大量的调研和统计工作，了解数据的存储方式、数据量的大小、数据的格式、数据的业务含义等信息，同时还需要统计各种数值型数据的最大值、最小值和平均值，统计非数值型数据中各种不同的取值以及各种不同取值的个数。有了以上信息，ETL以后各个步骤的设计才能做到有的放矢，达到正确、高效的目的。

2 数据抽取

从源文件和源数据库中获取相关数据用于填充数据仓库，称为数据抽取。并非所有包含在不同操作型业务系统中的数据都需要抽取,通常只需要其中的一个子集。抽取数据的一个子集是基于对源系统和目标系统的扩展分析，一般会由终端用户和数据仓库专家共同决定。
在集成端进行数据的初始化时，一般需要将数据源端的全部数据装载进来，这时需要进行全量抽取。全量抽取类似于数据迁移或数据复制，它将数据源中的表或视图的数据全部从数据库中抽取出来，再进行后续的转换和加载操作。全量抽取可以使用数据复制、导入或者备份的方式完成，实现机制比较简单。
全量抽取完成后，后续的抽取操作只需抽取自上次抽取以来表中新增或修改的数据，这就是增量抽取。

目前，数据抽取所涉及的单个技术环节都已相对成熟，但整体的集成度还很不够。市场上所提供的大多是数据抽取工具，这些工具通过用户选定源数据和目标数据的对应关系，会自动生成数据抽取的代码。但数据抽取工具支持的数据种类是有限的，同时数据抽取过程涉及数据的转换，它是一个与实际应用密切相关的部分，其复杂性使得不可嵌入用户编程的抽取工具往往不能满足要求。
因此，实际的数据仓库实施过程中往往不一定使用抽取工具。整个抽取过程能否因工具的使用而纳入有效的管理、调度和维护则更为重要。从市场发展来看，以数据抽取、异构互连产品为主项的数据仓库厂商一般都很有可能被其它拥有数据库产品的公司吞并。在数据仓库的世界里，它们只能成为辅助的角色。

3 数据清洗

数据仓库的外部数据源所提供的数据内容并不完美，存在着“脏数据”，即数据有空缺、噪声等缺陷，而且在数据仓库的各数据源之间，其内容也存在着不一致的现象。
为了控制这些“脏数据”对数据仓库分析结果的影响程度，必须采取各种有效的措施，对其进行处理，这一处理过程称为**“数据清洗”(Data Cleaning)。对于任何数据仓库而言，数据清洗过程都是必不可少的。
不同类型的“脏数据”，清洗处理的方法是不同的。
对于数据空缺**:忽略元组、用一个全局常量填充、用属性的平均值填充、使用与给定元组同类的所有样本的平均值填充空缺值、使用最可能的值填充空缺值、使用像Bayesian公式或判定树这样的基于推断的方法;
对于噪声数据，可以用分箱或聚类等方法处理;
对于不一致的数据，则必须依据数据仓库所应用领域的特点，使用特定的方法加以解决。

4 数据转换

数据转换指接收来自不同运作系统的输入并将其转换成目标数据仓库中需要的格式的过程，包括数据的合并、汇总、过滤、转换等。
在设计数据转换时，由于数据源之间往往存在着不一致的问题，因此数据转换必须做到数据名称及格式的统一，同时对于源数据库中可能不存在的数据需要创建新的数据逻辑视图并进行相应的转换。

(1）直接映射。数据源字段和目标字段长度或精度相同，则无需做任何处理。
(2）字符串处理。从数据源的字符串字段中获取特定信息作为目标数据库的某个字段，则对字符串的操作有类型转换、字符串截取等。由于字符类型字段的随意性也可能造成脏数据的出现，所以在处理这种规则的时候，需要异常处理。
(3）字段运算。对于数值型字段来说，有时数据源的一个或多个字段进行数学运算而得到目标字段，则需要某些字段运算。
(4）空值判断。对于数据源字段中的NULL值，可能在目标数据库进行分析处理时会出问题，因此必须对空值进行判断，并转换成特定的值。
(5）日期转换。由于目标数据库中的日期类型格式是统一的，所以对数据源字段的日期格式需要相应的转换。
(6）聚集运算。对于目标数据库事实表中的一些度量字段，通常需要通过数据源一个或多个字段运用聚集函数进行聚集运算得来，常用的聚集函数有: sum. count，avg，min，max。
(7）既定取值。这条规则对于目标字段取一个固定的或是依赖系统的值，而不依赖于数据源字段。

5 数据加载

数据加载负责将经过前几步清洗和转换后的数据按照目标数据库元数据定义的表结构装入数据仓库。加载数据到目标数据仓库的两个基本方式是刷新方式和更新方式
**刷新方式(Refresh Mode）**是一种填充数据仓库的方法，采用在定期的间隔对目标数据进行批量重写的技术。也就是说，目标数据起初被写进数据仓库，然后每隔一定的时间，数据仓库被重写，替换以前的内容。这种加载方式越来越不流行。
**更新方式(Update Mode)**是一种只将源数据中的数据改变写进数据仓库的方法。为了支持数据仓库的周期，便于历史分析，新记录通常被写进数据仓库中，但不覆盖或删除以前的记录，而是通过时间戳来分辩它们。
刷新方式通常用于数据仓库首次被创建时填充数据仓库。更新方式通常用于目标数据仓库的维护。刷新方式通常与全量抽取相结合，而更新方式常与增量抽取相结合。

6 ETL过程建模

ETL过程是传统的数据处理过程，其输入是数据仓库数据源的各种业务处理系统的数据库，输出部分是数据仓库。ETL过程通常可以看做是一个以数据处理为中心的工作流，工作流中包括了数据抽取、数据转换、清洗以及数据加载等操作。ETL工具是负责处理这一类流程的数据集成工具。ETL过程设计的正确与否关系到数据仓库的可用性。
ETL过程的设计质量往往取决于能否对业务需求和数据仓库环境进行形式化建模。ET工作流模型包括ETL概念模型和ETL逻辑模型两部分。
在ETL流程设计中，首先应该根据业务需求和相关数据源的结构建立概念模型，确定源数据库与目标数据库之间的映射关系，然后根据概念模型建立逻辑模型。

6.1 ETL概念模型

概念建模是整个ETL流程设计的最初阶段。在这个阶段，ETL流程设计者的主要任务是搜集用户的需求，然后分析相关数据源的结构及其内容，确定抽取操作所使用的数据源。ETl概念模型主要是建立数据源与数据仓库的模式或者属性之间的映射关系，以及在映射过程所需要的转换和清洗操作。
ETL概念模型并不针对具体的工具制定，与数据库系统无关，与应用程序无关，与工具无关。在这一概念级中，不必考虑物理实现的细节，只把注意力集中在构造源与目标实体及属性之间的映射和转换关系上。

1.为了保证目标数据仓库中数据的完整性，需要从sl和s2两个数据源抽取数据，对它们执行并(union)操作。
2.为了与数据仓库中事实表的模式一致，必须对s1中的customer表和city表执行外连接，而且还要将s2中date属性的数据类型为日期格式(mm / dd / yyyy)转换成中国的日期格式(vy / mm / dd)，还需要根据s2的其他属性组合(name、email)为s2表计算生成一个主键。
3.并运算、外连接运算以及日期转换属于概念模型中的转换transform),PK则属于约束(ETLconstrain)。

6.2 ETL逻辑模型

ETL概念模型并不是一个完整的工作流模型，它定义了数据源与目标数据仓库属性之间的映射关系以及中间必要的转换，但并不关注转换的语义以及执行顺序。
ETL流程的逻辑模型是一个以数据为中心的工作流模型，在逻辑建模阶段，ETL流程的设计者需要明确定义数据流所经过的各个活动的操作语义，并确定整个流程中各个活动的执行顺序。