ETL增量抽取方法

一、.ETL体系结构

    数据库--->抽取---> 转换--->加载--->目的数据源

二、ETL抽取方案

   1）.全量抽取

        全量抽取类似于数据迁移或数据复制，它将数据源中的表或视图的数据原封不动的从数据库中抽取出来，并转换成自己的ETL工具可以识别的格式，全量抽取比较简单。全量抽取一般只在系统初始化时使用，全量一次后，就要每天采用增量抽取。

   2）.增量抽取

        增量抽取只抽取自上次抽取以来数据库中要抽取的表中新增或者修改的数据。在ETL使用中，增量抽取使用更加广泛。

        怎样获取变化的数据是增量抽取的关键。对于捕获方法的要求一般有：准确性，一致性，完整性，性能。

        准确性：能够将业务系统中的变化数据按一定的规则准确的捕获到。

        一致性：源表和目标表中数据的一致。不能有字段的不一致。

        完整性：要求源表中应该放入到目标表中的数据均被放入到目标表中。

        性能：因为一个公司晚上一般有大量的跑批，像我们公司，从凌晨12点开始，N个系统排队跑批，一般排到早上七八点钟。所以这就要求性能一定要优。不能对业务系统造成太大的压力，影响现有业务。

 

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

1.时间戳

    在源表上增加一个时间戳字段，当系统中更新修改表数据的时候，同时修改时间戳字段。当进行数据抽取的时候，通过时间戳来抽取增量数据。

     优点：性能比较好。ETL系统设计清晰，源数据抽取相对清楚简单，可以实现数据的递增加载。

      缺点：对业务系统要求较高，需要添加时间戳字段。

                   无法获取到时间戳以前数据的delete操作和update操作，在数据准确性上受到了一定的限制。

2.触发器方式（又称快照式）

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

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

       缺点：要求业务表建立触发器，对业务系统有一定的影响。

3.全量删除插入方式

     每次先清空目标表数据，然后全量加载数据。

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

        缺点：出现问题不容易查询，性能慢

4.全表比对方式

5.日志表方式

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

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

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

目前我们用的就是时间戳方式，采取T+1取数方式，即今天凌晨获取昨天的业务数据，然后将昨天的业务数据的增量抽取过来。

这种方式性能比较好，但是delete和update操作时，不容易获取，因此出现过几个错误。再就是，对于时效性来说，一定要保证时间戳的抽取正确。

 

三、目前常用的加载策略有如下几种：
1) trunctae and insert.直接清空目标表，然后把新的数据加载进去。（日全量规则经常采用此方法）
2) deldte and insert.先根据规则清除当天的记录，然后把当天的新数据追加进去。（日增量规则经常采用此方法）
3) update and insert.用新数据与目标表中的历史数据进行比较，有变化的则更新，新记录则直接插入到目标表中。(暂时没有用到)

4) keep history change.保持历史的变化，通常是拉链记历史的方式实现。（暂时没有用过）

几种增量抽取方法优劣对比

注意：

在我从事的ETL工作中，大部分都是采用时间戳方式进行增量抽取，如银行业务，VT新开户，使用时间戳方式，可以在固定时间内，组织人员进行数据抽取，进行整合后，加载到目标系统。

而触发器方式，虽然可以自动进行抽取，但是执行频率过多，影响效率！第三种全量删除插入方式对于大数据量来说是非常不可取的，尤其是对于一些银行、电信行业，因为数据全量比较大，所以进行增量校对是比较耗时的，总起来说，个人趋向使用时间戳方式进行增量抽取。