ETL简介

ETL（Extract-Transform-Load数据仓库技术）

ETL是将业务系统的数据经过抽取、清洗转换之后加载到数据仓库的过程，目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析依据。

数据抽取 Extract

数据的抽取是从各个不同的数据源抽取到ODS（Operational Data Store，操作型数据存储）。

• 确定数据源，需要确定从哪些源系统进行数据抽取
• 定义数据接口，对每个源文件及系统的每个字段进行详细说明
• 确定数据抽取的方法：是主动抽取还是由源系统推送？是增量抽取还是全量抽取？是按照每日抽取还是按照每月抽取？

全量抽取：

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

增量抽取：

• 增量抽取只抽取自上次抽取以来数据库中要抽取的表中新增或修改的数据。增量抽取较全量抽取应用更广。增量抽取的关键在于捕获变化的数据。目前常用捕捉变化的方式有：触发器、时间戳、全表对比、日志对比。
• 触发器：在要抽取的表上建立需要的触发器，一般要建立插入、修改、删除三个触发器，每当源表中的数据发生变化，就被相应的触发器将变化的数据写入一个临时表，抽取线程从临时表中抽取数据，临时表中抽取过的数据被标记或删除。触发器方式的优点是数据抽取的性能较高，缺点是要求业务表建立触发器，对业务系统有一定的影响。
• 时间戳：它是一种基于快照比较的变化数据捕获方式，在源表上增加一个时间戳字段，系统中更新修改表数据的时候，同时修改时间戳字段的值。当进行数据抽取时，通过比较系统时间与时间戳字段的值来决定抽取哪些数据。有的数据库的时间戳支持自动更新，有的数据库不支持时间戳的自动更新，这就要求业务系统在更新业务数据时，手工更新时间戳字段。同触发器方式一样，时间戳方式的性能也比较好，数据抽取相对清楚简单，但对业务系统也有很大的侵入性。
• 全表比对：典型的全表比对的方式是采用MD5校验码。ETL工具事先为要抽取的表建立一个结构类似的MD5临时表，该临时表记录源表主键以及根据所有字段的数据计算出来的MD5校验码。每次进行数据抽取时，对源表和MD5临时表进行MD5校验码的比对，从而决定源表中的数据是新增、修改还是删除，同时更新MD5校验码。MD5方式的准确性较差。
• 日志对比：通过分析数据库自身的日志来判断变化的数据。

数据的清洗转换 Transform

一般情况下，数据仓库分为ODS、DW两部分。通常的做法是从业务系统到ODS做清洗，将脏数据和不完整数据过滤掉，在从ODS到DW的过程中转换，进行一些业务规则的计算和聚合。花费时间最长。
数据清洗的任务是过滤那些不符合要求的数据：

• 不完整的数据：应该有的信息缺失，按缺失的内容分别写入不同Excel文件向客户提交，要求在规定的时间内补全，补全后才写入数据仓库。
• 错误的数据：日期格式不正确、日期越界等。只能通过写SQL语句的方式找出来，然后要求客户在业务系统修正之后抽取。
• 重复的数据。

数据转换：

• 不一致数据转换：这个过程是一个整合的过程，将不同业务系统的相同类型的数据统一，比如同一个供应商在结算系统的编码是XX0001,而在CRM中编码是YY0001，这样在抽取过来之后统一转换成一个编码。
• 数据粒度的转换：业务系统一般存储非常明细的数据，而数据仓库中数据是用来分析的，不需要非常明细的数据。一般情况下，会将业务系统数据按照数据仓库粒度进行聚合。
• 商务规则的计算：不同的企业有不同的业务规则、不同的数据指标，这个时候需要在ETL中将这些数据指标计算好了之后存储在数据仓库中，以供分析使用。

数据装载 Load

使用哪种数据装载方法取决于业务系统的需要，可以是通过SQL语句insert update等，也可以是采用批量装载方法。

需要注意的是从原系统抽数到ODS再到DW的Transform中，原系统的表结构可能会发生变化，在数据装载时应注意。