ETL是数据仓库里最重要的数据处理过程，也是最体现工作量的环节，一般会占到整个数据仓库项目工作量的一半以上。

● 抽取：从数据源获取数据。

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

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

数据仓库的本质就是要把来自于多个异构的源系统的数据集成在一起，放置于一个集中的位置用于数据分析。如果没有ETL，就无法对异构的数据进行结构化的分析。

1、为什么要用ETL呢

▶ 当数据来自不同的平台或系统时，这时候如使用SQL语句去处理的话，就显得比较吃力且开销也更大。

▶ 数据来源可以是各种不同的数据库或者文件，比如有的是音频，有的是视频、有的是文字、有的是图片、有的是网页等等乱七八糟的数据（尤其是智慧城市、智慧建筑中的数据源），这时候需要先把他们整理成统一的格式后才可以进行数据的处理，这一过程用代码实现显然有些麻烦。

▶ 在数据库中我们当然可以使用存储过程去处理数据，但是处理海量数据的时候存储过程显然比较吃力，而且会占用较多数据库的资源，这可能会导致数据资源不足，进而影响数据库的性能。

在我们的产品中，智慧园区是每时每刻都在抓取建筑运行的海量数据，尤其是安防类数据，不仅数据量大、数据类型复杂、实时性强，对整个园区的运营也起着重要的作用。针对智慧园区数据运营平台，为了满足项目运营方、物业管理方、入园企业方等不同利益相关方，需要满足不同角色、不同管理场景、不同数据价值下的应用数据。为了实现这一功能，数据清洗这一环节是重中之中。

接下来我们就看看ETL数据清洗的工作原理及常用的ETL工具--Kettle

2、数据抽取

从各个数据源获取不同数据给后面的数据仓库使用，这是ETL数据处理的第一步，也是最重要的一步。这些来源不一、类型不一的数据被成功抽取后，才可以进行转换并加载到数据仓库中。能否正确地获取所需数据直接关系到后面多元化场景数据分析的效果的。数据仓库典型的源系统是事务处理应用，例如，一个销售数据仓库的数据源，可能是一个微信小程序、原OA、PSM、企业微信里的客户聊天记录、发送的往来文件等，也包含当前每一个销售人员客户线索跟进过程中相关操作的全部记录。

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

抽取方法的选择高度依赖于数据源和目标数据仓库的业务需要。一般情况下，不能因为需要提升数据抽取的性能，而在数据源中添加额外的逻辑，也不能增加这些数据源的工作负载。下面介绍几种数据抽取的方法：

全量抽取

即数据源中的数据全部被抽取。因为这种抽取类型涉及数据源中的所有的有效数据，所以不需要跟踪自上次成功抽取以来的数据变化。数据源只需要原样提供现有的数据而不需要附加的逻辑信息（比如抽取时间等）。全表导出的数据文件或者查询源表所有数据的SQL语句，就是进行全量抽取最好的技术实现方式。

增量抽取

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

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

对变化数据的捕捉

在对源数据进行抽取处理的过程中需要重点考虑增量抽取，也被称为变化数据捕获。假设一个数据仓库，在每天夜里的12点开始从源系统抽取数据，那么增量抽取只需要抽出来过去24小时内发生变化的数据即可。

变化数据捕获也是建立准实时数据仓库的关键技术。当你能够识别并获得最近发生变化的数据时，抽取及其后面的转换、装载操作显然都会变得更高效，因为要处理的数据量会小很多。遗憾的是，很多源系统很难识别出最近变化的数据，变化数据捕获是数据抽取中典型的技术挑战。常用的变化数据捕获方法就是记录时间戳。

3、数据转换

从数据源获取需要的数据后 ，需要对其进行统一数据类型、处理拼写错误、消除数据歧义、解析为标准格式等转换，这就是数据转换。数据转换通常是最复杂的部分，也是ETL中用时最长的一步。数据转换的范围极广，从单纯的数据类型转化到极为复杂的数据清洗技术。在数据转换阶段，为了能够最终将数据装载到数据仓库中，需要在已经抽取来的数据上应用一系列的规则和函数。

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

只加载有内容的数据列。例如某列为空的的话，就不再加载。

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

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

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

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

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

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

行列转置。

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

合并重复列。

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

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

4、数据装载

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

即使经过了转换、过滤和清洗，去掉了部分噪声数据，但需要装载的数据量还是很大的。执行一次数据装载可能需要几个小时的时间，同时需要占用大量的系统资源。要提高装载的效率，加快装载速度，可以从以下几方面入手：

首先保证足够的系统资源。数据仓库存储的都是海量数据，所以要配置高性能的服务器，并且要独占资源，不要与别的系统共用。

其次是在进行装载时，要禁用数据库约束（唯一性、非空性，检查约束等）和索引，当装载过程完全结束后，再启用这些约束，重建索引，这种方法会很大的提高装载速度。在数据仓库环境中，一般不使用数据库来保证数据的参考完整性，即不使用数据库的外键约束，它应该由ETL工具或程序来维护。

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

装载到数据仓库里的数据，经过汇总、聚合等处理后交付数据可视化、仪表盘等报表工具做进一步的展示和数据分析了。

5、常见的ETL工具

1、kettle

底层是JAVA，无需安装，数据抽取高效稳定

扩展性好，成本低、免费开源、部署简单

缺点是处理速度慢性、不支持实时同步，支持千万级别数据，数量太大时会吃力；

2、datastage

ETL中处理速度最快，性能最强的工具

IBM开发的，收费用的

数据处理速度快、有技术支持、支持并行处理

部署复杂，对硬件要求高

3、informatica

数据处理速度快，稳定，有商业化的技术支持

部署难度适中

数据监控功能不直观、支持实时，效率低

国内市场占用率最高的是kettle、informatica，在这里我们简要说下kettle。

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

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

Kettle非常容易使用，其所有的功能都通过用户界面完成，不需要任何编码工作。你只需要告诉它做什么，而不用指示它怎么做，这大大提高了ETL过程的开发效率。