ETL介绍

ETL是什么

ETL，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、交互转换（transform）、加载（load）至目的端的过程。ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程，因而也称为数据仓库技术。其目的是将分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。

ETL流程

Extract：

这一部分需要在调研阶段做大量的工作，首先要搞清楚数据是从几个业务系统中来,各个业务系统的数据库服务器运行什么DBMS,是否存在手工数据，手工数据量有多大，是否存在非结构化的数据等等，当收集完这些信息之后才可以进行数据抽取的设计。

数据抽取是数据从数据源中抽取数据的过程，实际应用中数据源较多采集的是关系型数据库。从数据库抽取数据一般有以下几种方式：

全量抽取：

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

增量抽取：

增量抽取只是抽取上次抽取以来数据库中要抽取的表中新增或者修改过的数据。在ETL使用过程中，增量抽取较全量抽取应用更广泛。如何捕获变化的数据是增量抽取的关键。对捕获方法一般有两点要求：

一.准确性：能够将业务系统中的变化数据按一定的频率准确的捕获。

二.性能：不能对业务系统造成太大压力，影响现有业务。

目前数据抽取中常用的捕获变化数据的方法有：

• 触发器方式（又称快照方式）

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

优点：数据抽取的性能高，ETL加载规则简单，速度快、不需要修改业务系统报表结构，可以实现数据的递增加载。

缺点：要求业务表建立触发器，对业务系统有一定影响，容易对源数据库构成威胁。

• 时间戳方式

它是一种基于快照比较的变化数据捕获方式，在源表上增加一个时间戳字段，系统中更新修改表中的数据的时候，同时修改时间戳字段的值。当进行数据抽取时，通过比较上一次抽取时间与时间戳字段的值来决定抽取哪些数据。有的数据库的时间戳支持自动更新，即表的其他字段的值发生变化时自动更新时间戳的值。有的数据库不支持时间戳自动更新，这就要求业务系统在更新业务时，手动更新时间戳字段。

优点:同触发器一样，时间戳的方式性能更好，ETL系统设计清晰，源数据抽取相对简单，可以实现数据的递增加载。

缺点：时间戳的维护需要由业务系统去完成，对业务系统有很大侵入性，特别是那些不支持时间戳自动更新的数据库。另外，无法捕获对时间戳以前数据的delete和update操作，在数据准确性上受到限制。

• 全表删除插入方式

每次ETL操作均删除目标表数据，由ETL全新加载数据

优点：ETL加载规则简单，速度快。

缺点：对于维表加外键不适应，当业务系统产生删除数据操作时，综合数据库将不会记录到所删的历史数据，不可以实现数据的递增加载；同时对于目标表所建立的关联关系，需要重新进行创建。

• 全表对比方式

全表对比的方式是ETL工具事先为要抽取的表建立一个结构类似的临时表，该临时表记录源表主键以及根据所有字段的数据计算出来，每次进行数据抽取时，对源表和临时表进行的对比，如有不同，进行Update操作，如目标表没有存在在该主键值，表示该记录还没有，即进行Insert操作。

优点：对已有系统结构不产生影响，不需要修改业务操作程序，所有抽取规则由ETL完成，管理维护统一，可以实现数据的递增加载，没有风险。

缺点：ETL表较复杂，设计较为复杂，速度慢。与触发器和时间戳方式中的主动通知不同，全表对比方式是被动的进行全表数据的比对，性能差。当表中没有主键或者唯一列含有重复记录时，全表对比方式的准确性差。

• 日志方式

在业务系统中添加系统日志表，当业务数据发生变化时，更新维护日志表内容,当做ETL加载时，通过读日志表数据决定加载哪些数据及如何加载

优点：不需要修改业务系统表结构，源数据抽取清楚，速度快。可以实现数据的递增加载。

缺点：日志维护需要由系统去完成，需要对业务系统业务操作程序做修改，记录日志信息。日志表维护较为麻烦，对原有系统有较大影响。工作量大，改动较大，有一定风险。

Transform

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

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

• 在ETL引擎中的数据加载转换和加工

ETL引擎中一般以组件化的方式实现数据转换。常用的数据转换组件有字段映射、数据过滤、数据清洗、数据替换、数据计算、数据验证、数据加密解密、数据合并、数据拆分等。这些组件如同流水线上的一道道工序，他们是可插拔的，并且可以任意组装，各组件之间通过数据总线共享数据。同时ETL工具还提供了脚本支持，使得用户可以以一种编程的方式制定数据的转换和加工行为。

相比在数据库中加工，性能加高，但不容易进行修改，也不直观。

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

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

相比在ETL引擎中进行数据转换和加工，直接在SQL语句中进行转换和加工更加简单清晰；但依赖SQL语句，有些数据加工通过SQL语句可能无法实现，对于SQL语句无法处理的可以交给ETL引擎处理。

Load

将转换和加工后的数据装载到目的库中通常是ETL过程的最后步骤。装载数据的最佳方式取决于所执行操作的类型以及需要装入多少数据。当目的库是关系型数据库时，一般有两种装载方式：

1.直接SQL语句进行Insert、Update、Delete操作。

2.采用批量装载方式，sqlldr等。

大多数情况下使用第一种方法，因为他们进行了日志记录并且是可恢复的。但是，批量装载操作易于使用，并且在装入大量数据时效率较高。采用哪种装载方式取决于业务系统的需要。