ETL 工作流程

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

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

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

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

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

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

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

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

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

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

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

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

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

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

全量抽取比较简单。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

适用：

优缺点:

感知增量的方式如下：

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

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

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

优缺点

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

关系数据库本身已经提供了强大的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引擎处理。

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

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

比如:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

名词解释： 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