ETL本质

做数据仓库系统，

ETL

是关键的一环。说大了，

ETL

是数据整合解决方案，说小了，

就是倒数据的工具。回忆一下工作这么些年来，处理数据迁移、转换的工作倒还真的

不少。

但是那些工作基本上是一次性工作或者很小数据量，

使用

access

、

DTS

或是自

己编个小程序搞定。可是在数据仓库系统中，

ETL

上升到了一定的理论高度，和原来

小打小闹的工具使用不同了。究竟什么不同，从名字上就可以看到，人家已经将倒数

据的过程分成

3

个步骤，

E

、

T

、

L

分别代表抽取、转换和装载。

 

其实

ETL

过程就是数据流动的过程，从不同的数据源流向不同的目标数据。但在数据

仓库中，

ETL

有几个特点，一是数据同步，它不是一次性倒完数据就拉到，它是经常

性的活动，按照固定周期运行的，甚至现在还有人提出了实时

ETL

的概念。二是数据

量，一般都是巨大的，值得你将数据流动的过程拆分成

E

、

T

和

L

。

 

现在有很多成熟的工具提供

ETL

功能，例如

datastage

、

powermart

等，且不说他

们的好坏。从应用角度来说，

ETL

的过程其实不是非常复杂，这些工具给数据仓库工

程带来和很大的便利性，特别是开发的便利和维护的便利。但另一方面，开发人员容

易迷失在这些工具中。举个例子，

VB

是一种非常简单的语言并且也是非常易用的编程

工具，

上手特别快，

但是真正

VB

的高手有多少？微软设计的产品通常有个原则是

“将

使用者当作傻瓜”，在这个原则下，微软的东西确实非常好用，但是对于开发者，如

果你自己也将自己当作傻瓜，那就真的傻了。

ETL

工具也是一样，这些工具为我们提

供图形化界面，让我们将主要的精力放在规则上，以期提高开发效率。从使用效果来

说，确实使用这些工具能够非常快速地构建一个

job

来处理某个数据，不过从整体来

看，并不见得他的整体效率会高多少。问题主要不是出在工具上，而是在设计、开发

人员上。他们迷失在工具中，没有去探求

ETL

的本质。

 

可以说这些工具应用了这么长时间，在这么多项目、环境中应用，它必然有它成功之

处，它必定体现了

ETL

的本质。如果我们不透过表面这些工具的简单使用去看它背后

蕴涵的思想，

最终我们作出来的东西也就是一个个独立的

job

，

将他们整合起来仍然有

巨大的工作量。大家都知道“理论与实践相结合”，如果在一个领域有所超越，必须

要在理论水平上达到一定的高度

 

09 : 26 

探求

ETL

本质之一

 

 

ETL

的过程就是数据流动的过程，从不同异构数据源流向统一的目标数据。其间，数据的

抽取、清洗、转换和装载形成串行或并行的过程。

ETL

的核心还是在于

T

这个过程，也就

是转换，而抽取和装载一般可以作为转换的输入和输出，或者，它们作为一个单独的部件，

其复杂度没有转换部件高。和

OLTP

系统中不同，那里充满这单条记录的

insert

、

update

和

select

等操作，

ETL

过程一般都是批量操作，例如它的装载多采用批量装载工具，一般

都是

DBMS

系统自身附带的工具，例如

Oracle SQLLoader

和

DB2

的

autoloader

等。

 

  

ETL

本身有一些特点，在一些工具中都有体现，下面以

datastage

和

powermart

举例来说。

 

  

1

、静态的

ETL

单元和动态的

ETL

单元实例；一次转换指明了某种格式的数据如何格式化

成另一种格式的数据，对于数据源的物理形式在设计时可以不用指定，它可以在运行时，当这个

ETL

单元创建一个实例时才指定。对于静态和动态的

ETL

单元，

Datastage

没有严

格区分，它的一个

Job

就是实现这个功能，在早期版本，一个

Job

同时不能运行两次，所

以一个

Job

相当于一个实例，在后期版本，它支持

multiple 

instances

，而且还不是默认选

项。

Powermart

中将这两个概念加以区分，

静态的叫做

Mapping

，

动态运行时叫做

Session

。

 

  

2

、

ETL

元数据；元数据是描述数据的数据，他的含义非常广泛，这里仅指

ETL

的元数据。

主要包括每次转换前后的数据结构和转换的规则。

ETL

元数据还包括形式参数的管理，形

式参数的

ETL

单元定义的参数，相对还有实参，它是运行时指定的参数，实参不在元数据

管理范围之内。

 

 

3

、数据流程的控制；要有可视化的流程编辑工具，提供流程定义和流程监控功能。流程调

度的最小单位是

ETL

单元实例，

ETL

单元是不能在细分的

ETL

过程，当然这由开发者来控

制，例如可以将抽取、转换放在一个

ETL

单元中，那样这个抽取和转换只能同时运行，而

如果将他们分作两个单元，可以分别运行，这有利于错误恢复操作。当然，

ETL

单元究竟

应该细分到什么程度应该依据具体应用来看，目前还没有找到很好的细分策略。比如，我

们可以规定将装载一个表的功能作为一个

ETL

单元，但是不可否认，这样的

ETL

单元之间

会有很多共同的操作，例如两个单元共用一个

Hash

表，要将这个

Hash

表装入内存两次。

 

 

4

、转换规则的定义方法；提供函数集提供常用规则方法，提供规则定义语言描述规则。

 

  

5

、对数据的快速索引；一般都是利用

Hash

技术，将参照关系表提前装入内存，在转换时

查找这个

hash

表。

Datastage

中有

Hash

文件技术，

Powermart

也有类似的

Lookup

功能。

 

 

16 : 23 

探求

ETL

本质之二（分类）

 

 

昨在

IT-Director

上阅读一篇报告，

关于

ETL

产品分类的。

一般来说，

我们眼中的

ETL

工具都是价格昂贵，能够处理海量数据的家伙，但是这是其中的一种。它可以分成

4

种，针对不同的需求，主要是从转换规则的复杂度和数据量大小来看。它们包括

 

1

、交互式运行环境，你可以指定数据源、目标数据，指定规则，立马

ETL

。这种交互

式的操作无疑非常方便，但是只能适合小数据量和复杂度不高的

ETL

过程，因为一旦

规则复杂了，可能需要语言级的描述，不能简简单单拖拖拽拽就可以的。还有数据量

的问题，这种交互式必然建立在解释型语言基础上，另外他的灵活性必然要牺牲一定

的性能为代价。所以如果要处理海量数据的话，每次读取一条记录，每次对规则进行

解释执行，每次在写入一条记录，这对性能影响是非常大的。

 

2

、专门编码型的，它提供了一个基于某种语言的程序框架，你可以不必将编程精力放

在一些周边的功能上，例如读文件功能、写数据库的功能，而将精力主要放在规则的

实现上面。这种近似手工代码的性能肯定是没话说，除非你的编程技巧不过关（这也

是不可忽视的因素之一）。对于处理大数据量，处理复杂转换逻辑，这种方式的

ETL

实现是非常直观的。

 

3

、代码生成器型的，它就像是一个

ETL

代码生成器，提供简单的图形化界面操作，

让你拖拖拽拽将转换规则都设定好，其实他的后台都是生成基于某种语言的程序，要

运行这个

ETL

过程，

必须要编译才行。

Datastage

就是类似这样的产品，

设计好的

job

必须要编译，这避免了每次转换的解释执行，但是不知道它生成的中间语言是什么。

以前我设计的

ETL

工具大挪移其实也是归属于这一类，

它提供了界面让用户编写规则，

最后生成

C++

语言，编译后即可运行。这类工具的特点就是要在界面上下狠功夫，必

须让用户轻松定义一个

ETL

过程，提供丰富的插件来完成读、写和转换函数。大挪移

在这方面就太弱了，规则必须手写，而且要写成标准

c++

语法，这未免还是有点难为

最终用户了，还不如做成一个专业编码型的产品呢。另外一点，这类工具必须提供面

向专家应用的功能，因为它不可能考虑到所有的转换规则和所有的读写，一方面提供

插件接口来让第三方编写特定的插件，另一方面还有提供特定语言来实现高级功能。

例如

Datastage

提供一种类

Basic

的语言，

不过他的

Job

的脚本化实现好像就做的不

太好，只能手工绘制

job

，而不能编程实现

Job

。

 

4

、最后还有一种类型叫做数据集线器，顾名思义，他就是像

Hub

一样地工作。将这

种类型分出来和上面几种分类在标准上有所差异，上面三种更多指

ETL

实现的方法，

此类主要从数据处理角度。目前有一些产品属于

EAI

（

Enterprise Application 

Integration

），它的数据集成主要是一种准实时性。所以这类产品就像

Hub

一样，

不断接收各种异构数据源来的数据，经过处理，在实施发送到不同的目标数据中去。

 

虽然，这些类看似各又千秋，特别在

BI

项目中，面对海量数据的

ETL

时，中间两种

的选择就开始了，在选择过程中，必须要考虑到开发效率、维护方面、性能、学习曲

线、人员技能等各方面因素，当然还有最重要也是最现实的因素就是客户的意象。

 

16 : 23 

探求

ETL

本质之三（转换）

 

 

ETL

探求之一中提到，

ETL

过程最复杂的部分就是

T

，

这个转换过程，

T

过程究竟有哪

些类型呢？

 

一、宏观输入输出

 

从对数据源的整个宏观处理分，看看一个

ETL

过程的输入输出，可以分成下面几类：

 

1

、大小交，这种处理在数据清洗过程是常见了，例如从数据源到

ODS

阶段，如果数

据仓库采用维度建模，而且维度基本采用代理键的话，必然存在代码到此键值的转换。

如果用

SQL

实现，必然需要将一个大表和一堆小表都

Join

起来，当然如果使用

ETL

工具的话，一般都是先将小表读入内存中再处理。这种情况，输出数据的粒度和大表

一样。

 

2

、大大交，大表和大表之间关联也是一个重要的课题，当然其中要有一个主表，在逻

辑上，

应当是主表

Left Join

辅表。

大表之间的关联存在最大的问题就是性能和稳定性，

对于海量数据来说，必须有优化的方法来处理他们的关联，另外，对于大数据的处理

无疑会占用太多的系统资源，出错的几率非常大，如何做到有效错误恢复也是个问题。

对于这种情况，我们建议还是尽量将大表拆分成适度的稍小一点的表，形成大小交的

类型。这类情况的输出数据粒度和主表一样。

 

3

、站着进来，躺着出去。事务系统中为了提高系统灵活性和扩展性，很多信息放在代

码表中维护，所以它的“事实表”就是一种窄表，而在数据仓库中，通常要进行宽化，

从行变成列，所以称这种处理情况叫做“站着进来，躺着出去”。大家对

Decode

肯

定不陌生，这是进行宽表化常见的手段之一。窄表变宽表的过程主要体现在对窄表中

那个代码字段的操作。这种情况，窄表是输入，宽表是输出，宽表的粒度必定要比窄

表粗一些，就粗在那个代码字段上。

 

4

、聚集。数据仓库中重要的任务就是沉淀数据，聚集是必不可少的操作，它是粗化数

据粒度的过程。聚集本身其实很简单，就是类似

SQL

中

Group by

的操作，选取特定

字段（维度），对度量字段再使用某种聚集函数。但是对于大数据量情况下，聚集算

法的优化仍是探究的一个课题。例如是直接使用

SQL

的

Group by

，还是先排序，在

处理。

 

二、微观规则

 

从数据的转换的微观细节分，可以分成下面的几个基本类型，当然还有一些复杂的组

合情况，例如先运算，在参照转换的规则，这种基于基本类型组合的情况就不在此列

了。

ETL

的规则是依赖目标数据的，目标数据有多少字段，就有多少条规则。

 

1

、直接映射，原来是什么就是什么，原封不动照搬过来，对这样的规则，如果数据源

字段和目标字段长度或精度不符，需要特别注意看是否真的可以直接映射还是需要做

一些简单运算。

 

2

、字段运算，数据源的一个或多个字段进行数学运算得到的目标字段，这种规则一般

对数值型字段而言。

 

3

、

参照转换，

在转换中通常要用数据源的一个或多个字段作为

Key

，去一个关联数组

中去搜索特定值，而且应该只能得到唯一值。这个关联数组使用

Hash

算法实现是比

较合适也是最常见的，在整个

ETL

开始之前，它就装入内存，对性能提高的帮助非常

大。

 

4

、字符串处理，从数据源某个字符串字段中经常可以获取特定信息，例如身份证号。

而且，经常会有数值型值以字符串形式体现。对字符串的操作通常有类型转换、字符

串截取等。但是由于字符类型字段的随意性也造成了脏数据的隐患，所以在处理这种

规则的时候，一定要加上异常处理。

 

5

、空值判断，对于空值的处理是数据仓库中一个常见问题，是将它作为脏数据还是作

为特定一种维成员？这恐怕还要看应用的情况，也是需要进一步探求的。但是无论怎

样，对于可能有

NULL

值的字段，不要采用“直接映射”的规则类型，必须对空值进

行判断，目前我们的建议是将它转换成特定的值。

 

6

、

日期转换，

在数据仓库中日期值一般都会有特定的，

不同于日期类型值的表示方法，

例如使用

8

位整型

20040801

表示日期。

而在数据源中，

这种字段基本都是日期类型

的，所以对于这样的规则，需要一些共通函数来处理将日期转换为

8

位日期值、

6

位

月份值等。

 

7

、日期运算，基于日期，我们通常会计算日差、月差、时长等。一般数据库提供的日

期运算函数都是基于日期型的，而在数据仓库中采用特定类型来表示日期的话，必须

有一套自己的日期运算函数集。

 

8

、聚集运算，对于事实表中的度量字段，他们通常是通过数据源一个或多个字段运用

聚集函数得来的，这些聚集函数为

SQL

标准中，包括

sum,count,avg,min,max

。

 

9

、既定取值，这种规则和以上各种类型规则的差别就在于它不依赖于数据源字段，对

目标字段取一个固定的或是依赖系统的值。

 

12 : 58 

探求

ETL

本质之四（数据质量）

 

 

“不要绝对的数据准确，但要知道为什么不准确。

”

 

这是我们在构建

BI

系统是对数据准确性的要求。确实，对绝对的数据准确谁也没有把握，

不仅是系统集成商，包括客户也是无法确定。准确的东西需要一个标准，但首先要保证这

个标准是准确的，至少现在还没有这样一个标准。客户会提出一个相对标准，例如将你的

OLAP

数据结果和报表结果对比。虽然这是一种不太公平的比较，你也只好认了吧。

 

  

首先在数据源那里，已经很难保证数据质量了，这一点也是事实。在这一层有哪些可能原

因导致数据质量问题？可以分为下面几类：

 

1

、

数据格式错误

，例如缺失数据、数据值超出范围或是数据格式非法等。要知道对于同样

处理大数据量的数据源系统，他们通常会舍弃一些数据库自身的检查机制，例如字段约束

等。他们尽可能将数据检查在入库前保证，但是这一点是很难确保的。这类情况诸如身份

证号码、手机号、非日期类型的日期字段等。

 

2

、

数据一致性

，同样，数据源系统为了性能的考虑，会在一定程度上舍弃外键约束，这通

常会导致数据不一致。例如在帐务表中会出现一个用户表中没有的用户

ID

，在例如有些代

码在代码表中找不到等。

 

3

、

业务逻辑的合理性

，这一点很难说对与错。通常，数据源系统的设计并不是非常严谨，

例如让用户开户日期晚于用户销户日期都是有可能发生的，一个用户表中存在多个用户

ID

也是有可能发生的。对这种情况，有什么办法吗？

 

  

构建一个

BI

系统，要做到完全理解数据源系统根本就是不可能的。特别是数据源系统在交

付后，有更多维护人员的即兴发挥，那更是要花大量的时间去寻找原因。以前曾经争辩过

设计人员对规则描述的问题，

有人提出要在

ETL

开始之前务必将所有的规则弄得一清二楚。

我并不同意这样的意见，倒是认为在

ETL

过程要有处理这些质量有问题数据的保证。一定

要正面这些脏数据，是丢弃还是处理，无法逃避。如果没有质量保证，那么在这个过程中，

错误会逐渐放大，抛开数据源质量问题，我们再来看看

ETL

过程中哪些因素对数据准确性

产生重大影响。

 

1

、

规则描述错误

。上面提到对设计人员对数据源系统理解的不充分，导致规则理解错误，

这是一方面。另一方面，是规则的描述，如果

无二义性地描述规则

也是要探求的一个课题。

规则是依附于目标字段的，在探求之三中，提到规则的分类。但是规则总不能总是用文字

描述，必须有严格的数学表达方式。我甚至想过，如果设计人员能够使用某种规则语言来

描述，那么我们的

ETL

单元就可以自动生成、同步，省去很多手工操作了。

 

2

、

ETL

开发错误

。

即时规则很明确，

ETL

开发的过程中也会发生一些错误，

例如逻辑错误、

书写错误等。例如对于一个分段值，开区间闭区间是需要指定的，但是常常开发人员没注