ETL概述

ETL

概述

ETL是将数据进行抽取（Extract）、转换（Transform）和加载（Load）的过程。

数据从不同的数据源抽取到ODS（操作性数据存储）进行清洗转换后，加载到DW（数据仓库）中。

其中花费时间最长的是清洗转换的部分。

一、ETL的实现

ETL工具

Oracle的OWB、SQL Server 2000的DTS、SQL Server2005的SSIS服务和Informatic

优点：可以快速建立ETL工程，不需要复杂的编码，提高效率，降低难度

缺点：缺少灵活性

SQL

优点：灵活，能提高效率

缺点：编码复杂，技术难度高

ETL工具+SQL

综合前两种

二、抽取（Extract）

抽取可以作用在从不同的数据源中，在抽取过程中可以进行一些清洗转换工作，最后会将数据抽取到ODS中。

同时需要明白数据是存放于什么业务系统，业务系统中的数据库是使用的什么类型数据库，是否有手工数据，手工数据量有多大，是否存在非结构化的数据。

还要确定数据抽取的方法：主动抽取或由数据库推送。增量抽取还是全量。每日抽取还是每月抽取。

1、相同数据库的数据源处理方法

数据库（如oracle，SQL Server）会提供同种数据库相互连接功能，在建立了连接关系后直接使用select语句就可以访问。

2、不同数据库的数据源处理方法

可以通过ODBC的方式建立连接，如果相互之间不能建立连接，可以通过数据库提供的工具将数据导出，再导入到ODS。还可以通过程序的方式来处理。

3、文件类型数据源

直接按照第二种方式，将文件导入到ODS中。

4、增量更新

如果数据量庞大，需要考虑进行增量更新。在进行增量更新时，需要记录下时间戳，每次进行抽取时判断ODS中最大的时间，再根据这个时间去数据库中取大于这个时间的所有记录。

三、数据清洗转换（Transform）

数据仓库分为ODS和DW两部分。

将数据抽取到ODS中做清洗（即将脏数据、不完整数据以及重复数据给过滤掉），再从ODS到DW的过程中转换，进行一些业务规则的计算和整合。

1、数据清洗

数据清洗主要是过滤掉不符合要求的数据，过滤后需要将过滤后的结果交给主管部门，来确定最后是进行过滤还是进行修复。

其中，不符合要求的数据包括不完整的数据、错误的数据以及重复的数据。

（1）不完整的数据：主要是数据中有信息的缺失，对于这类数据，需要将其提取出来交给专门的人员来进行数据修复，修复完整后才能够写入DW。

（2）错误的数据：主要是由于代码没有对用户写入的数据进行判断，导致将不符合规则的数据写入到了数据库，如特殊字符或格式不正确等，对于这类数据，需要通过SQL语句的方式提取出来，然后交给专门的人员来进行修复。

（3）重复的数据：同样的，需要将重复的数据交给主管部门来确认并整理。

2、数据转换

数据转换主要是进行不一致的数据转换、数据粒度的转换以及商务规则的计算。

（1）不一致的数据转换：将不同业务数据库中的相同类型的数据统一，如某个字段在A数据库是A01，在B数据库中是B01，进行统一后，就只使用A01。

（2）数据粒度的转换：业务数据库中会存储很明细的数据，而数据仓库只需要用来分析数据，不需要非常明细的数据，所以会根据数据仓库的粒度来整理业务系统的数据。

（3）商务规则的计算：业务中的数据指标通常不是简单的计算就能完成，需要将这些数据计算好了之后再存入数据仓库库中。

四、ETL和ELT

ETL是按照E T L的操作顺序进行处理的架构：抽取、转换、加载。而ELT是抽取后先进行加载，最后进行转换的架构。

在ETL中如果需要提高效率，则需要提高ETL工具的服务器配置，而ELT则需要对相应的数据库进行调优。

ETL架构的优势

1、可以分担数据库的压力

2、相对于ELT可以实现更复杂的转换逻辑

3、使用单独的服务器，不需要数据库来分担

4、和底层的数据库无关

ELT架构的优势

1、可以充分利用数据库

2、保证数据一直在数据库中，不需要额外的加载操作，提高效率

3、可以对相关数据库进行调优，以此来获得更高的效率

五、ETL的四种模式

ETL有四种主要实现模式：触发器、增量字段、全量同步、日志比对。

1、触发器

触发器是普遍采取的一种模式。这种模式根据抽取的要求，在对应的表上建立对应的插入、修改、删除3个触发器，当源表的数据发生变化时，触发器会把变化的数据写入另一张增量表，后续ETL的抽取就直接从这张增量表中取出而不是源表，增量表中抽取过的数据要进行标记。

优点：抽取的效率高，不需要修改源表结构。

缺点：需要建立触发器，数据库有更大压力。

2、增量字段

增量字段是在源表中增加一个字段，如时间戳，当源表中的数据有新增或者修改等操作时，这个字段也会修改成对应的时间，当使用ETL工具来进行增量数据获取时，只需要对比已经抽取过的数据中最大的时间戳，再使用这个时间戳在数据库中比对，就能够判断出哪些是新增/修改数据。

优点：使用时间戳的方式提取效率快，方式也比较简单。

缺点：时间戳字段的维护需要业务代码来实现，需要在代码中加入额外的时间戳字段，对老数据的删除/更新操作有限制，数据准确度不高。

3、全量同步

全量同步是指在抽取之前先获取源表数据，删除目标表数据，最后再加载进去的方式。但最好是在数据量不大时使用。

优点：对表结构没有影响，不需要修改业务代码，由于是全量的方式，所以基本上没有风险。

缺点：处理时需要对全表的数据进行比对，性能较低。

4、日志比对

5、总结

增量机制	兼容性	完备性	抽取性能	源库压力	源库改动量	实现难度
触发器	关系型数据库	高	优			容易
增量字段	关系型数据库．具有”字段”结构的其它数据格式	低	较优
全表同步	任何数据格式		极差	中	无
日志比对	关系型数据库(oracle/mysql)					较难