ETL概念
- 将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程
- ETL负责将分布的、异构数据源中的数据如关系数据、平面数据文件等抽取到临时中间层后进行清洗、转换、集成， 最后加载到数据仓库或数据集市中，成为联机分析处理、 数据挖掘的基础
数据抽取
全量抽取
- 类似于数据迁移或数据复制，它将数据源中的表或视图的数据原封不动的从数据库中抽取出来，并转换成自己的ETL工具可以识别的格式
增量抽取
- 只抽取自上次抽取以来数据库中要抽取的表中新增或修改的数据，如何捕获变化的数据是增量抽取的关键
- 对捕获方法一般有两点要求：准确性，能够将业务系统中的变化数据按一定的频率准确地捕获到；性能， 不能对业务系统造成太大的压力，影响现有业务
常用的捕获方法
| 方法 | 说明 | 优点 | 缺点 |
|---|---|---|---|
| 触发器 | 在要抽取的表上建立需要的触发器，一般要建立插入、修改、删除三个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个临时表，抽取线程从临时表中抽取数据，临时表中抽取过的数据被标记或删除 | 利用数据库本身的触发器机制，契合度高，可靠性高，不会存在有增量数据未被捕获到的现象 | 对源系统有较大的影响，需要建立触发器机制，增加运维人员，还要建立临时表，储存临时表，增加储存成本和运维成本 |
| 时间戳 | 它是一种基于快照比较的变化数据捕获方式，在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。当进行数据抽取时，通过比较系统时间与时间戳字段的值来决定抽取哪些数据 | 数据处理逻辑清楚，速度较快，成本低廉，流程简单 | 要求源表的时间字段必须是随表变动而变动的不为空数据，由于是直接读取表数据，该方法无法获取删除类型的数据 |
| 全表比对 | 典型的全表比对的方式是采用MD5校验码。ETL工具事先为要抽取的表建立一个结构类似的MD5临时表，该临时表记录源表主键以及根据所有字段的数据计算出来的MD5校验码。每次进行数据抽取时，对源表和MD5临时表进行MD5校验码的比对，从而决定源表中的数据是新增、修改还是删除，同时更新MD5校验码 | 对源系统的倾入性较小 | MD5方式是被动的进行全表数据的比对，性能较差，当表中没有主键或唯一列且含有重复记录时，MD5方式的准确性较差 |
| 日志对比 | 通过分析数据库自身的日志来判断变化的数据，Oracle的改变数据捕获（CDC，Changed Data Capture）技术是这方面的代表，CDC体系结构基于发布者/订阅者模型，发布者捕捉变化数据并提供给订阅者，订阅者使用从发布者那里获得的变化数据，通常，CDC系统拥有一个发布者和多个订阅者 | 实时、高效、相对复杂、异步、非侵入式 | 环境配置复杂，需要占用数据库系统的一定资源，ETL规则复杂 |
- ETL处理的数据源除了关系数据库外，还可能是txt文件、excel文件、xml文件等
- 对文件数据的抽取一般是进行全量抽取，一次抽取前可保存文件的时间戳或计算文件的MD5校验码，下次抽取时进行比对，如果相同则可忽略本次抽取
数据转换与处理
- 从数据源中抽取的数据不一定完全满足目的库的要求，例如数据格式的不一致、数据输入错误、数据不完整等，因此有必要对抽取出的数据进行数据转换和处理
- 数据转换和处理可以在ETL引擎中进行，也可以在数据抽取过程中利用关系数据库的特性同时进行
ETL引擎中的数据转换和处理
- 一般以组件化的方式实现数据转换，常用的数据转换组件有字段映射、数据过滤、数据清洗、数据替换、数据计算、数据验证、数据加解密、数据合并、数据拆分等
- 有些ETL工具还提供了脚本支持，使得用户可以以一种编程的方式定制数据的转换和处理行为
在数据库中进行数据处理
- 关系数据库本身已经提供了强大的SQL、函数来支持数据的加工，如在SQL查询语句中添加where条件进行过滤，查询中重命名字段名与目的表进行映射，substr函数，case条件判断等
- 相比在ETL引擎中进行数据转换和处理，直接在SQL语句中进行转换和处理更加简单清晰，性能更高，对于SQL语句无法处理的可以交由ETL引擎处理
数据加载
- 将转换和加工后的数据装载到目的库中，装载数据的最佳方法取决于所执行操作的类型以及需要装入多少数据
当目的库是关系数据库时，一般来说有两种装载方式：
- 直接SQL语句进行insert、update、delete操作，此方法进行了日志记录并且是可恢复的
- 采用批量装载方法，如bcp、bulk、关系数据库特有的批量装载工具或api，此方法易于使用，并且在装入大量数据时效率较高
ETL常用工具
Kettle
- Kettle是一款国外开源的ETL工具，纯java编写，可以在Windows、Linux、Unix上运行，数据抽取高效稳定，包括4个产品：Spoon、Pan、CHEF、Kitchen
| 产品 | 说明 |
| ---- | ---- |
| SPOON | 通过图形界面来设计ETL转换过程 |
|PAN|批量运行由Spoon设计的ETL转换，Pan是一个后台执行的程序，没有图形界面 |
|CHEF|可以创建任务(Job)，更有利于自动化更新数据仓库的复杂工作，任务将会被检查，看看是否正确地运行了 |
|KITCHEN|批量使用由Chef设计的任务(例如使用一个时间调度器)，KITCHEN也是一个后台运行的程序 |
DataPipeline
DataPipeline是一家为企业用户提供数据基础架构服务的科技公司，DataPipeline数据质量平台整合了数据质量分析、质量校验、质量监控等多方面特性，以保证数据质量的完整性、一致性、准确性及唯一性，彻底解决数据孤岛和数据定义进化的问题
Talend
Talend可运行于Hadoop集群之间，直接生成MapReduce代码供Hadoop运行，从而可以降低部署难度和成本，加快分析速度，而且Talend还支持可进行并发事务处理的Hadoop2.0
Informatica
- Informatica PowerCenter用于访问和集成几乎任何业务系统、任何格式的数据，它可以按任意速度在企业内交付数据，具有高性能、高可扩展性、高可用性的特点
- Informatica PowerCenter包括4个不同版本，即：标准版，实时版，高级版，云计算版
- 它还提供了多个可选的组件，以扩展Informatica PowerCenter的核心数据集成功能，这些组件包括：数据清洗和匹配、数据屏蔽、数据验证、Teradata双负载、企业网格、元数据交换、下推优化(Pushdown Optimization)、团队开发和非结构化数据等
Datax
DataX是阿里巴巴集团内被广泛使用的离线数据同步工具/平台，实现包括MySQL、Oracle、SqlServer、Postgre、HDFS、Hive、ADS、HBase、TableStore(OTS)、MaxCompute(ODPS)、DRDS等各种异构数据源之间高效的数据同步功能
Oracle Goldengate
- 一种基于日志的结构化数据复制软件，GoldenGate 能够实现大量交易数据的实时捕捉、变换和投递，实现源数据库与目标数据库的数据同步，保持亚秒级的数据延迟
- 源端通过抽取进程提取redo log或archive log日志内容，通过pump进程(TCP/IP协议)发送到目标端，最后目标端的rep进程接收日志、解析并应用到目标端，进而完成数据同步