【数据仓库基础（三）】抽取-转换-装载

一. ETL概念

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

ETL的工作：

● 抽取：从操作型数据源获取数据。
● 转换：转换数据，使之转变为适用于查询和分析的形式和结构。
● 装载：将转换后的数据导入到最终的目标数据仓库。

ETL将异构统一为同构：

建立一个数据仓库，就是要把来自于多个异构的源系统的数据集成在一起，放置于一个集中的位置用于数据分析。ETL系统的工作就是要把异构的数据转换成同构的。如果没有ETL，不可能对异构的数据进行程序化的分析。

 

二. 数据抽取

抽取操作从源系统获取数据给后续的数据仓库环境使用。

抽取数据难度大：

源系统很可能非常复杂并且缺少相应的文档，因此只是决定需要抽取哪些数据可能就已经非常困难了。如果已经明确了需要抽取的数据，下一步就该考虑从源系统抽取数据的方法了。

抽取原则：

一般情况下，不可能因为需要提升数据抽取的性能，而在源系统中添加额外的逻辑，也不能增加这些源系统的工作负载。有时，用户甚至都不允许增加任何“开箱即用”的外部应用系统，这叫做对源系统具有侵入性。

下面分别从逻辑和物理两方面介绍数据抽取方法。

1．逻辑抽取

逻辑抽取有两种逻辑抽取类型：全量抽取和增量抽取
（1）全量抽取源系统的数据全部被抽取。

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

（2）只抽取某个事件发生的特定时间点之后的数据。

必须能够标识出特定时间点之后所有的数据变化。这些发生变化的数据可以由:

1. 源系统自身来提供，例如能够反映数据最后发生变化的时间戳列；
2. 一个原始事务处理之外的，只用于跟踪数据变化的变更日志表。但意味着需要在源系统上增加抽取逻辑。

待优化的问题

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

 

2．物理抽取

两种物理数据抽取机制：直接从源系统联机抽取或者间接从一个脱机结构抽取数据。

1）联机抽取数据直接从源系统抽取

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

2）脱机抽取数据不从源系统直接抽取，而是从一个源系统以外的过渡区抽取

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

• 数据库备份文件。一般需要数据还原操作才能使用。
• 备用数据库。如Oracle的DataGuard和MySQL的数据复制等技术。
• 平面文件。数据定义成普通格式，关于源对象的附加信息（列名、数据类型等）需要另外处理。
• 导出文件。关系数据库大都自带数据导出功能，如Oracle的exp/expdp程序和MySQL的mysql dump程序，都可以用于生成导出数据文件。
• 重做日志和归档日志。每种数据库系统都有自己的日志格式和解析工具。

 

3．变化数据捕获

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

变化数据捕获也是建立准实时数据仓库的关键技术。

当你能够识别并获得最近发生变化的数据时，抽取及其后面的转换、装载操作显然都会变得更高效，因为要处理的数据量会小很多。

遗憾的是，很多源系统很难识别出最近变化的数据，或者必须侵入源系统才能做到。变化数据捕获是数据抽取中典型的技术挑战。（flink cdc）。

 
常用的变化数据捕获方法有时间戳、快照、触发器和日志四种:

• 时间戳方法需要源系统有相应的数据列表示最后的数据变化。
• 快照方法可以使用数据库系统自带的机制实现，如Oracle的物化视图技术，也可以自己实现相关逻辑，但会比较复杂。
• 触发器是关系数据库系统具有的特性，源表上建立的触发器会在对该表执行insert、update、delete等语句时被触发，触发器中的逻辑用于捕获数据的变化。
• 日志可以使用应用日志或系统日志，这种方式对源系统不具有侵入性，但需要额外的日志解析工作。

 
 

三. 数据转换

数据转换一个最重要的功能是清洗数据，目的是只有“合规”的数据才能进入目标数据仓库。

例如，一个系统的字符集在另一个系统中可能是无效的。
另一方面，由于某些业务和技术的需要，也需要进行多种数据转换，例如下面的情况：

• 只装载特定的数据列。例如，某列为空的数据不装载。
• 统一数据编码。例如，性别字段，有些系统使用的是1和0，有些是‘M’和‘F’，有些是‘男’和‘女’，统一成‘M’和‘F’。
• 自由值编码。例如，将‘Male’改成‘M’。
• 预计算。例如，产品单价 * 购买数量 = 金额。
• 基于某些规则重新排序以提高查询性能。
• 合并多个数据源的数据并去重。
• 预聚合。例如，汇总销售数据。
• 行列转置。
• 将一列转为多列。例如，某列存储的数据是以逗号作为分隔符的字符串，将其分割成多列的单个值。
• 合并重复列。
• 预连接。例如，查询多个关联表的数据。
• 数据验证。针对验证的结果采取不同的处理，通过验证的数据交给装载步骤，验证失败的数据或直接丢弃，或记录下来做进一步检查。

 
 

四. 数据装载

ETL的最后步骤是把转换后的数据装载进目标数据仓库。
这步操作需要重点考虑两个问题，

1. 数据装载的效率问题
2. 一旦装载过程中途失败了，如何再次重复执行装载过程。

即使经过了转换、过滤和清洗，去掉了部分噪声数据，但需要装载的数据量还是很大的。执行一次数据装载可能需要几个小时的时间，同时需要占用大量的系统资源。
 

先看装载效率：

要提高装载的效率，加快装载速度，可以从以下几方面入手。

• 首先保证足够的系统资源。数据仓库存储的都是海量数据，所以要配置高性能的服务器，并且要独占资源，不要与别的系统共用。
• 在进行数据装载时，（输出源）要禁用数据库约束（唯一性、非空性，检查约束等）和索引，当装载过程完全结束后，再启用这些约束，重建索引，这种方法会很大的提高装载速度。在数据仓库环境中，一般不使用数据库来保证数据的参考完整性，即不使用数据库的外键约束，它应该由ETL工具或程序来维护。

 
接着看可重复装载：

• 实现可重复装载的关键是要记录下失败点，并在装载程序中处理相关的逻辑。
• 还有一种情况，就是装载成功后，数据又发生了改变，这时需要重新再执行一遍装载过程，已经正确装载的数据可以被覆盖，但相同数据不能重复新增。简单的实现方式是先删除再插入，或者用replace into、merge into等类似功能的操作。

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

 
 
参考：《Hadoop构建数据仓库实践》