1.4 抽取-转换-装载

前面已经多次提到了ETL一词，它是Extract、Transform、Load三个英文单词首字母的简写，中文意为抽取、转换、装载。ETL是建立数据仓库最重要的处理过程，也是最体现工作量的环节，一般会占到整个数据仓库项目工作量的一半以上。

● 抽取：从操作型数据源获取数据。

● 转换：转换数据，使之转变为适用于查询和分析的形式和结构。

● 装载：将转换后的数据导入到最终的目标数据仓库。

建立一个数据仓库，就是要把来自于多个异构的源系统的数据集成在一起，放置于一个集中的位置用于数据分析。如果一开始这些源系统数据就是兼容的当然最好，但情况往往不是这样。ETL系统的工作就是要把异构的数据转换成同构的。如果没有ETL，不可能对异构的数据进行程序化的分析。

1.4.1 数据抽取

抽取操作从源系统获取数据给后续的数据仓库环境使用。这是ETL处理的第一步，也是最重要的一步。数据被成功抽取后，才可以进行转换并装载到数据仓库中。能否正确地获取数据直接关系到后面步骤的成败。数据仓库典型的源系统是事务处理应用，例如，一个销售分析数据仓库的源系统之一，可能是一个订单录入系统，其中包含当前销售订单相关操作的全部记录。

设计和建立数据抽取过程，在ETL处理乃至整个数据仓库处理过程中，一般是较为耗时的任务。源系统很可能非常复杂并且缺少相应的文档，因此只是决定需要抽取哪些数据可能就已经非常困难了。通常数据都不是只抽取一次，而是需要以一定的时间间隔反复抽取，通过这样的方式把数据的所有变化提供给数据仓库，并保持数据的及时性。除此之外，源系统一般不允许外部系统对它进行修改，也不允许外部系统对它的性能和可用性产生影响，数据仓库的抽取过程要能适应这样的需求。如果已经明确了需要抽取的数据，下一步就该考虑从源系统抽取数据的方法了。

对抽取方法的选择高度依赖于源系统和目标数据仓库环境的业务需要。一般情况下，不可能因为需要提升数据抽取的性能，而在源系统中添加额外的逻辑，也不能增加这些源系统的工作负载。有时，用户甚至都不允许增加任何“开箱即用”的外部应用系统，这叫做对源系统具有侵入性。下面分别从逻辑和物理两方面介绍数据抽取方法。

1．逻辑抽取

有两种逻辑抽取类型：全量抽取和增量抽取。

（1）全量抽取

源系统的数据全部被抽取。因为这种抽取类型影响源系统上当前所有有效的数据，所以不需要跟踪自上次成功抽取以来的数据变化。源系统只需要原样提供现有的数据而不需要附加的逻辑信息（比如时间戳等）。一个全表导出的数据文件或者一个查询源表所有数据的SQL语句，都是全量抽取的例子。

（2）增量抽取

只抽取某个事件发生的特定时间点之后的数据。通过该事件发生的时间顺序能够反映数据的历史变化，它可能是最后一次成功抽取，也可能是一个复杂的业务事件，如最后一次财务结算等。必须能够标识出特定时间点之后所有的数据变化。这些发生变化的数据可以由源系统自身来提供，例如能够反映数据最后发生变化的时间戳列，或者是一个原始事务处理之外的，只用于跟踪数据变化的变更日志表。大多数情况下，使用后者意味着需要在源系统上增加抽取逻辑。

在许多数据仓库中，抽取过程不含任何变化数据捕获技术。取而代之的是，把源系统中的整个表抽取到数据仓库过渡区，然后用这个表的数据和上次从源系统抽取得到的表数据作比对，从而找出发生变化的数据。虽然这种方法不会对源系统造成很大的影响，但显然需要考虑给数据仓库处理增加的负担，尤其是当数据量很大的时候。

2．物理抽取

依赖于选择的逻辑抽取方法和能够对源系统所做的操作和所受的限制，存在两种物理数据抽取机制：直接从源系统联机抽取或者间接从一个脱机结构抽取数据。这个脱机结构有可能已经存在，也可能需要由抽取程序生成。

（1）联机抽取

数据直接从源系统抽取。抽取进程或者直连源系统数据库，访问它们的数据表，或者连接到一个存储快照日志或变更记录表的中间层系统。注意这个中间层系统并不需要必须和源系统物理分离。

（2）脱机抽取

数据不从源系统直接抽取，而是从一个源系统以外的过渡区抽取。过渡区可能已经存在（例如数据库备份文件、关系数据库系统的重做日志、归档日志等），或者抽取程序自己建立。应该考虑以下的存储结构：

● 数据库备份文件。一般需要数据还原操作才能使用。

● 备用数据库。如Oracle的DataGuard和MySQL的数据复制等技术。

● 平面文件。数据定义成普通格式，关于源对象的附加信息（列名、数据类型等）需要另外处理。

● 导出文件。关系数据库大都自带数据导出功能，如Oracle的exp/expdp程序和MySQL的mysqldump程序，都可以用于生成导出数据文件。

● 重做日志和归档日志。每种数据库系统都有自己的日志格式和解析工具。

3．变化数据捕获

抽取处理需要重点考虑增量抽取，也被称为变化数据捕获，简称CDC。假设一个数据仓库系统，在每天夜里的业务低峰时间从操作型源系统抽取数据，那么增量抽取只需要过去24小时内发生变化的数据。变化数据捕获也是建立准实时数据仓库的关键技术。

当你能够识别并获得最近发生变化的数据时，抽取及其后面的转换、装载操作显然都会变得更高效，因为要处理的数据量会小很多。遗憾的是，很多源系统很难识别出最近变化的数据，或者必须侵入源系统才能做到。变化数据捕获是数据抽取中典型的技术挑战。

常用的变化数据捕获方法有时间戳、快照、触发器和日志四种。相信熟悉数据库的读者对这些方法都不会陌生。时间戳方法需要源系统有相应的数据列表示最后的数据变化。快照方法可以使用数据库系统自带的机制实现，如Oracle的物化视图技术，也可以自己实现相关逻辑，但会比较复杂。触发器是关系数据库系统具有的特性，源表上建立的触发器会在对该表执行insert、update、delete等语句时被触发，触发器中的逻辑用于捕获数据的变化。日志可以使用应用日志或系统日志，这种方式对源系统不具有侵入性，但需要额外的日志解析工作。关于这4种方案的特点，将会在本书第7章“数据抽取”具体说明。

1.4.2 数据转换

数据从操作型源系统获取后，需要进行多种转换操作。如统一数据类型、处理拼写错误、消除数据歧义、解析为标准格式等。数据转换通常是最复杂的部分，也是ETL开发中用时最长的一步。数据转换的范围极广，从单纯的数据类型转化到极为复杂的数据清洗技术。

在数据转换阶段，为了能够最终将数据装载到数据仓库中，需要在已经抽取来的数据上应用一系列的规则和函数。有些数据可能不需要转换就能直接导入到数据仓库。

数据转换一个最重要的功能是清洗数据，目的是只有“合规”的数据才能进入目标数据仓库。这步操作在不同系统间交互和通信时尤其必要，例如，一个系统的字符集在另一个系统中可能是无效的。另一方面，由于某些业务和技术的需要，也需要进行多种数据转换，例如下面的情况：

● 只装载特定的数据列。例如，某列为空的数据不装载。

● 统一数据编码。例如，性别字段，有些系统使用的是1和0，有些是‘M’和‘F'，有些是‘男’和‘女’，统一成‘M’和‘F'。

● 自由值编码。例如，将‘Male’改成‘M'。

● 预计算。例如，产品单价 * 购买数量 = 金额。

● 基于某些规则重新排序以提高查询性能。

● 合并多个数据源的数据并去重。

● 预聚合。例如，汇总销售数据。

● 行列转置。

● 将一列转为多列。例如，某列存储的数据是以逗号作为分隔符的字符串，将其分割成多列的单个值。

● 合并重复列。

● 预连接。例如，查询多个关联表的数据。

● 数据验证。针对验证的结果采取不同的处理，通过验证的数据交给装载步骤，验证失败的数据或直接丢弃，或记录下来做进一步检查。

1.4.3 数据装载

ETL的最后步骤是把转换后的数据装载进目标数据仓库。这步操作需要重点考虑两个问题，一是数据装载的效率问题，二是一旦装载过程中途失败了，如何再次重复执行装载过程。

即使经过了转换、过滤和清洗，去掉了部分噪声数据，但需要装载的数据量还是很大的。执行一次数据装载可能需要几个小时的时间，同时需要占用大量的系统资源。要提高装载的效率，加快装载速度，可以从以下几方面入手。首先保证足够的系统资源。数据仓库存储的都是海量数据，所以要配置高性能的服务器，并且要独占资源，不要与别的系统共用。在进行数据装载时，要禁用数据库约束（唯一性、非空性，检查约束等）和索引，当装载过程完全结束后，再启用这些约束，重建索引，这种方法会很大的提高装载速度。在数据仓库环境中，一般不使用数据库来保证数据的参考完整性，即不使用数据库的外键约束，它应该由ETL工具或程序来维护。

数据装载过程可能由于多种原因而失败，比如装载过程中某些源表和目标表的结构不一致而导致失败，而这时已经有部分表装载成功了。在数据量很大的情况下，如何能在重新执行装载过程时只装载失败的部分是一个不小的挑战。对于这种情况，实现可重复装载的关键是要记录下失败点，并在装载程序中处理相关的逻辑。还有一种情况，就是装载成功后，数据又发生了改变（比如有些滞后的数据在ETL执行完才进入系统，就会带来数据的更新或新增），这时需要重新再执行一遍装载过程，已经正确装载的数据可以被覆盖，但相同数据不能重复新增。简单的实现方式是先删除再插入，或者用replace into、merge into等类似功能的操作。

装载到数据仓库里的数据，经过汇总、聚合等处理后交付给多维立方体或数据可视化、仪表盘等报表工具、BI工具做进一步的数据分析。

1.4.4 开发ETL系统的方法

ETL系统一般都会从多个应用系统整合数据，典型的情况是这些应用系统运行在不同的软硬件平台上，由不同的厂商所支持，各个系统的开发团队也是彼此独立的，随之而来的数据多样性增加了ETL系统的复杂性。

开发一个ETL系统，常用的方式是使用数据库标准的SQL及其程序化语言，如Oracle的PL/SQL和MySQL的存储过程、用户自定义函数（UDF）等。还可以使用Kettle这样的ETL工具，这些工具都提供多种数据库连接器和多种文件格式的处理能力，并且对ETL处理进行了优化。使用工具的最大好处是减少编程工作量，提高工作效率。如果遇到特殊需求或特别复杂的情况，可能还是需要使用Shell、Java、Python等编程语言开发自己的应用程序。

ETL过程要面对大量的数据，因此需要较长的处理时间。为了提高ETL的效率，通常这三步操作会并行执行。当数据被抽取时，转换进程同时处理已经收到的数据。一旦某些数据被转换过程处理完，装载进程就会将这些数据导入目标数据仓库，而不会等到前一步工作执行完才开始。

1.4.5 常见ETL工具

传统大的软件厂商一般都提供ETL工具软件，如Oracle的OWB和ODI、微软的SQL Server Integration Services、SAP的Data Integrator、IBM的InfoSphere DataStage、Informatica等。这里简单介绍另外一种开源的ETL工具——Kettle。

Kettle是Pentaho公司的数据整合产品，它可能是现在世界上最流行的开源ETL工具，经常被用于数据仓库环境。Kettle的使用场景包括：在应用或数据库间迁移数据、把数据库中的数据导出成平面文件、向数据库大批量导入数据、数据转换和清洗、应用整合等。

Kettle里主要有“转换”和“作业”两个功能模块。转换是ETL解决方案中最主要的部分，它处理ETL各阶段各种对数据的操作。转换有输入、输出、检验、映射、加密、脚本等很多分类，每个分类中包括多个步骤，如输入转换中就有表输入、CSV文件输入、文本文件输入等很多步骤。转换里的步骤通过跳（hop）来连接，跳定义了一个单向通道，允许数据从一个步骤流向另外一个步骤。在Kettle里，数据的单位是行，数据流就是数据行从一个步骤到另一个步骤的移动。

转换是以并行方式执行的，而作业则是以串行方式处理的，验证数据表是否存在这样的操作就需要作业来完成。一个作业包括一个或多个作业项，作业项是以某种顺序来执行的，作业执行顺序由作业项之间的跳（hop）和每个作业项的执行结果决定。和转换一样，作业也有很多分类，每个分类中包括多个作业项，如转换就是一个通用分类里的作业项。作业项也可以是一个作业，此时称该作业为子作业。

Kettle非常容易使用，其所有的功能都通过用户界面完成，不需要任何编码工作。你只需要告诉它做什么，而不用指示它怎么做，这大大提高了ETL过程的开发效率。本书第5章将会详细说明怎样使用Kettle操作Hadoop数据。