ETL 工作流程

ETL

ETL是英文Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。

常见于数据仓库开发中将数据由业务系统归集到数据仓库（DW）或者数据集市的过程。

ETL负责将分布的、异构数据源中的数据如关系数据、
平面数据文件等抽取到临时中间层后进行清洗、转换、集成，最后加载到数据仓库或数据集市中，成为联机分析处理、数据挖掘的基础。

相对于关系数据库，数据仓库技术没有严格的数学理论基础，它更面向实际工程应用。

所以从工程应用的角度来考虑，按着物理数据模型的要求加载数据并对数据进行一些系列处理，处理过程与经验直接相关，同时这部分的工作直接关系数据仓库中数据的质量，从而影响到联机分析处理和数据挖掘的结果的质量。

数据仓库是一个独立的数据环境，需要通过抽取过程将数据从联机事务处理环境、外部数据源和脱机的数据存储介质导入到数据仓库中；

在技术上，ETL主要涉及到关联、转换、增量、调度和监控等几个方面；

数据仓库系统中数据不要求与联机事务处理系统中数据实时同步，所以ETL可以定时进行。

但多个ETL的操作时间、顺序和成败对数据仓库中信息的有效性至关重要。

在ETL三个部分中，花费时间最长的是“T”(Transform，清洗、转换)的部分，一般情况下这部分工作量是整个ETL的2/3。

ETL关键技术

ETL过程中的主要环节就是数据抽取、数据转换和加工、数据装载。

为了实现这些功能，各个ETL工具一般会进行一些功能上的扩充，例如工作流、调度引擎、规则引擎、脚本支持、统计信息等。

1. 抽取作业

数据抽取是从数据源中抽取数据的过程。实际应用中，数据源较多采用的是关系数据库。

1.1 从数据库中抽取数据的方式：

1.1.1 全量抽取

全量抽取类似于数据迁移或数据复制，它将数据源中的表或视图的数据原封不动的从数据库中抽取出来，并转换成自己的ETL工具可以识别的格式。

全量抽取比较简单。

1.1.2 增量抽取

增量抽取只抽取自上次抽取以来数据库中要抽取的表中新增或修改的数据。

在ETL使用过程中，增量抽取较全量抽取应用更广，如何捕获变化的数据是增量抽取的关键。

对捕获方法一般有两点要求：

• 准确性，能够将业务系统中的变化数据按一定的频率准确地捕获到；
• 性能，不能对业务系统造成太大的压力，影响现有业务。

1.2 手工开发抽取作业时候的常用方法：

1.2.1 当数据源和DW为同一类数据库时

一般情况下，DBMS(SQLServer、Oracle)都会提供数据库链接功能，可以在数据源（业务系统）和DW内建立数据库链接（如DB2的联邦数据库NICKNAME），然后在DW内直接SELECT访问。

• 优点是实现使用简单，逻辑简单；
• 缺点是容易被滥用对源数据库造成较大的负载压力。

1.2.2 当数据源和ODS为不同类型数据库时

• 将源数据库的数据导出为文本文件，利用FTP协议进行传输导入ODS区域。

• 优点是实现简单，对源系统压力较小。
• 缺点是传输步骤增加了，处理需要的时间增加。

• 将部分数据库间能通过ODBC建立源数据库和目标数据库链接，此时也能直接使用SELECT获取数据。

• 优点是实现使用简单，逻辑简单；
• 缺点是容易被滥用对源数据库造成较大的负载压力，且建立时较为复杂。

1.3 更新数据的时间和数量的问题

1.3.1 实时抽取数据

这类抽取方式在数据仓库中很少见到，因为一般来说数据仓库对数据的实时性要求并不高。

实时抽取常见于BI中的CRM系统，比如在实时营销中，客户一旦进行了某类操作就实时触发对应的营销行为。

1. 时间戳方式

要求源表中存在一个或多个字段(时间戳),其值随着新纪录的增加而不断增加，执行数据抽取时，程序定时循环检查通过时间戳对数据进行过滤，抽取结束后，程序记录时间戳信息。

• 优点是对源系统的侵入较小
• 缺点是抽取程序需要不断扫描源系统的表，对其 有一定压力

2. 触发器方式

要求用户在源数据库中有创建触发器和临时表的权限，触发器捕获新增的数据到临时表中，执行抽取时，程序自动从临时表中读取数据。

• 优点是实时性极高
• 缺点是对源系统的侵入性较大，同时会对源数据库造成很大的压力（行级触发器），很可能影响源系统的正常业务

3. 程序接口方式

改造源系统，在修改数据时通过程序接口同步发送数据至目标库，发送数据的动作可以跟业务修改数据动作脱耦，独立发送。

• 优点是对源系统的造成压力较小，实时性较强；
• 缺点是需要对源系统的侵入性较强，需要源系统做较大的改造。

1.3.2 批量抽取数据

为了保证数据抽取时数据的准确性、完整性和唯一性，同时降低抽取作业对源数据库造成的压力，抽取作业的加载必须避开源数据的生成时间。

这种方法一般用于实时性要求不高的数据。

比如T+1或者每月1日进行抽取。

1.3.2.1常用实现

1. 日志检查

需要源数据库生成数据完毕之后，在外部生成日志。

抽取程序定时检查源系统的执行日志，发现完成标志后发起抽取作业。

• 优点是可靠性高，对源数据库造成的压力较小。
• 缺点是需要源数据库配合生成可供检查的外部日志。

2. 约定时间抽取

可以直接约定一个加载完毕同时对源数据库压力较小的时间（如每日凌晨2点），抽取程序建立定时任务，时间一到自动发起抽取作业。

• 优点是对源数据库的侵入性和造成的压力较小；
• 缺点是可靠性不高，可能会发生数据未生成完毕也直接进行抽取的情况。

1.3.2.2根据下载时候对数据的筛选方式可以分为

1. 全量下载

适用：

• 源数据量较小，如维表。
• 数据变化较大，比如90%的数据都产生了变化的表。
• 变化的数据不能预期，无法标示，如账户表。

优缺点:

• 优点在于下载较为简单且能容纳任何情况的数据变化；
• 缺点是如果数据量较大，需要抽取相当长的时间，同时会占用大量的IO和网络资源。

2. 增量下载

• 常用于数据只增不减的表，如交易明细表等。

感知增量的方式如下：

1. 触发器

在要抽取的表上建立需要的触发器，一般要建立插入、修改、删除三个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个临时表，抽取线程从临时表中抽取数据，临时表中抽取过的数据被标记或删除。

• 优点是数据抽取的性能较高，下载的数据较小，速度较快，占用资源少。
• 缺点是要求业务表建立触发器，对业务系统有一定的影响，使用限制较大，有时候需要源系统进行改造支持。

2. 时间戳

它是一种基于快照比较的变化数据捕获方式，在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。

当进行数据抽取时，通过比较系统时间与时间戳字段的值来决定抽取哪些数据。

• 有的数据库的时间戳支持自动更新，即表的其它字段的数据发生改变时，自动更新时间戳字段的值。
• 有的数据库不支持时间戳的自动更新，这就要求业务系统在更新业务数据时，手工更新时间戳字段。

优缺点

• 优点：同触发器方式一样，时间戳方式的性能也比较好，数据抽取相对清楚简单；
• 缺点：对业务系统也有很大的倾入性（加入额外的时间戳字段），特别是对不支持时间戳的自动更新的数据库，还要求业务系统进行额外的更新时间戳操作。
  另外，无法捕获对时间戳以前数据的delete和update操作,在数据准确性上受到了一定的限制。

3. 全表比对

典型的全表比对的方式是采用MD5校验码。

ETL工具事先为要抽取的表建立一个结构类似的MD5临时表，该临时表记录源表主键以及根据所有字段的数据计算出来的MD5校验码。

每次进行数据抽取时，对源表和MD5临时表进行MD5校验码的比对，从而决定源表中的数据是新增、修改还是删除，同时更新MD5校验码。

• 优点是对源系统的倾入性较小（仅需要建立一个MD5临时表）
• 缺点也是显而易见的，与触发器和时间戳方式中的主动通知不同，MD5方式是被动的进行全表数据的比对，性能较差。
  当表中没有主键或唯一列且含有重复记录时，MD5方式的准确性较差。

4. 日志对比

• 通过分析数据库自身的日志来判断变化的数据。

ETL处理的数据源除了关系数据库外，还可能是文件，例如txt文件、excel文件、xml文件等。

对文件数据的抽取一般是进行全量抽取，一次抽取前可保存文件的时间戳或计算文件的MD5校验码，下次抽取时进行比对，如果相同则可忽略本次抽取。

2. 转换作业

这一步包含了数据的清洗和转换。

从数据源中抽取的数据不一定完全满足目的库的要求，例如数据格式的不一致、数据输入错误、数据不完整等等，因此有必要对抽取出的数据进行数据转换和加工。

数据的转换和加工可以在ETL引擎中进行，也可以在数据抽取过程中利用关系数据库的特性同时进行。

2.1 数据清洗

任务是过滤不符合条件或者错误的数据。

这一步常常出现在刚刚开始建立数据仓库或者源业务系统仍未成熟的时候，此时发现错误数据需要联系源业务系统进行更正，部分可预期的空值或者测试用数据可以过滤掉。

2.2 数据转换和加工

这一步是整个ETL流程中最为占用时间和资源的一步。

数据转换包含了简单的数据不一致转换，数据粒度转换和耗时的数据关联整合或拆分动作。

这里可能存在各种各样千奇百怪的需求。

对于核心数据仓库来说，里面往往是对数据进行按照主题划分合并的动作。

同时，也会添加一些为了提升执行效率而进行反范式化添加的冗余字段。

数据的转换和加工可以在ETL引擎中进行，也可以在数据抽取过程中利用关系数据库的特性同时进行。

根据实现方式的不同，可以区分为使用数据库存储过程转换和使用高级语言转换

2.2.1 ETL引擎中的数据转换和加工

ETL引擎中一般以组件化的方式实现数据转换。

常用的数据转换组件有字段映射、数据过滤、数据清洗、数据替换、数据计算、数据验证、数据加解密、数据合并、数据拆分等。

这些组件如同一条流水线上的一道道工序，它们是可插拔的，且可以任意组装，各组件之间通过数据总线共享数据。

有些ETL工具还提供了脚本支持，使得用户可以以一种编程的方式定制数据的转换和加工行为。

2.2.2 在数据库中进行数据加工

关系数据库本身已经提供了强大的SQL、函数来支持数据的加工，如在SQL查询语句中添加where条件进行过滤，查询中重命名字段名与目的表进行映射，substr函数，case条件判断等等。

下面是一个SQL查询的例子。

select ID as USERID, substr(TITLE, 1, 20) as TITLE, case when REMARK is null then ' ' else REMARK end as CONTENT from TB_REMARK where ID > 100;

相比在ETL引擎中进行数据转换和加工，直接在SQL语句中进行转换和加工更加简单清晰，性能更高。

对于SQL语句无法处理的可以交由ETL引擎处理。

2.2.3 使用数据库存储过程转换

使用SQL开发存储过程完成转换作业是很多银行常用的方法。

• 优点是开发简单、能支持绝大部分转换场景；
• 缺点在于占用资源多且受制于单一数据库性能，无法做到横向扩展。

因此，除了业务的理解能力外，对SQL海量数据处理的优化能力在此也非常重要。

比如:

• 利用数据库的分区性，选择良好的分区键。
• 建表时合理选择主键和索引，关联时候必须使用主键或索引进行关联。
• 关注数据库对SQL的流程优化逻辑，尽量选择拆分复杂SQL，引导数据库根据你选择流程进行数据处理
• 合理反范式化设计表，留出适当的冗余字段，减少关联动作。

具体的优化根据不同的数据库有着不同的处理方式，根据所选用的数据库不同而定。

2.2.4 使用高级语言转换

使用高级语言包含了常用的开发C/C++/JAVA等程序对抽取的数据进行预处理。

• 优点是运行效率较高，可以通过横向扩展服务器数量来提高系统的转换作业处理能力；
• 缺点是开发较为复杂，同时虽然能进行较为复杂的逻辑的开发，但是对于大数据量的关联的支持能力较弱，特别是有复数的服务器并行处理的时候。

3. 加载作业

将转换和加工后的数据装载到目的库中通常是ETL过程的最后步骤。

转换作业生成的数据有可能直接插入目标数据库，一般来说，这种情况常见于使用数据库存储过程进行转换作业的方案。

此时，ETL作业位于目标数据库上，加载作业只需要使用INSERT或者LOAD的方式导入目标表即可。

此时转换作业和加载作业往往是在同一加工中完成的。

当使用高级语言开发时，ETL作业有着专门的ETL服务器，此时，转换作业生成的往往是文本文件，在转换作业完成后需要使用目标库特有的工具导入或者通过INSERT入目标库。

同时，根据抽取作业的数据抽取方式的不同（全量、增量），对目标表进行替换或者插入动作。

装载数据的最佳方法取决于所执行操作的类型以及需要装入多少数据。当目的库是关系数据库时，一般来说有两种装载方式：

1. 直接SQL语句进行insert、update、delete操作。
2. 采用批量装载方法，如bcp、bulk、关系数据库特有的批量装载工具或api。

大多数情况下会使用第一种方法，因为它们进行了日志记录并且是可恢复的。

但是，批量装载操作易于使用，并且在装入大量数据时效率较高。使用哪种数据装载方法取决于业务系统的需要。

4. 流程控制

抽取加载和转换作业需要一个集中的调度平台控制他们的运行，决定执行顺序，进行错误捕捉和处理。

较为原始的ETL系统就是使用CRON做定时控制，定时调起相应的程序或者存储过程。

但是这种方式过于原始，只能进行简单的调起动作，无法实现流程依赖行为，同时按步执行的流程控制能力也弱，错误处理能力几乎没有。

只适合于极其简单的情况。

对于自行开发的较为完善的ETL系统，往往需要具有以下几个能力：

• 流程步骤控制能力

调度平台必须能够控制整个ETL流程（抽取加载和转换作业），进行集中化管理，不能有流程游离于系统外部。

• 系统的划分和前后流程的依赖

由于整个ETL系统里面可能跨越数十个业务系统，开发人员有数十拨人，必须支持按照业务系统对ETL流程进行划分管理的能力。

同时必须具有根据流程依赖进行调度的能力，使得适当的流程能在适当的时间调起。

• 合理的调度算法

同一时间调起过多流程可能造成对源数据库和ETL服务器还有目标数据库形成较大负载压力，故必须有较为合理的调度算法。

• 日志和警告系统

必须对每一步的流程记录日志，起始时间，完成时间，错误原因等，方便ETL流程开发人员检查错误。

对于发生错误的流程，能及时通知错误人员进行错误检查和修复。

• 较高可靠性

5. 常用商业ETL工具

常用的ETL工具有Ascential公司的Datastage、Informatica公司的Powercenter、 NCR Teradata公司的ETL Automation等。

• Datastage
  是使用高级语言进行开发ETL服务器的代表。使用JAVA进行开发E/T/L的整个流程，同时支持平行添加服务器提升处理效率的方法。
• Powercenter
  与Datastage类似，但元数据更加开放，存放在关系数据库中，可以很容易被访问。再有Powercenter不能像Datastage运行多个实例，且不支持定制开发，参数控制更乱。
• Automation
  基于Teradata的TD数据库的ETL调度框架。其ETL流程是使用DSQL的存储过程进行开发，利用TD数据库的海量数据处理能力，也具有一定的平行扩展能力。

名词解释：
DW(Data Warehouse) : 数据仓库https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93
ODS（Operational Data Store）：https://zh.wikipedia.org/wiki/ODS
CRM（customer relationship management）：客户关系管理https://zh.wikipedia.org/wiki/%E5%AE%A2%E6%88%B7%E5%85%B3%E7%B3%BB%E7%AE%A1%E7%90%86
BI(Business Intelligence):商业智能
https://baike.baidu.com/item/BI/4579902

参见：
ETL流程概述及常用实现方法：https://blog.csdn.net/btkuangxp/article/details/48224187
ETL介绍与ETL工具比较：
https://blog.csdn.net/wl044090432/article/details/60329843