第一十二章 数据抽取、转换和加载

    12.3.1  以时间戳作为加载条件

    利用时间戳的方式对目标表进行数据加载是相对简单的一种方法。可以利用业务源数据表已有的时间字段，或者添加一个时间字段作为时间戳，当业务数据表里的数据修改或者添加时，同时也会修改或者添加时间字段里的值，可以通过系统时间与源数据表中的时间字段进行比较，来判断该记录是否应该加载到目标表中。这种以时间戳作为数据加载条件的优点是使ETL设计简单清晰，抽取的效率相对较高，并且可以实现ETL数据加载的需求；缺点是需要对业务源数据的时间戳进行维护。

    下面使用Informatica 工具，用时间戳的方式进行数据加载。

    （1）设计源表和目标表

    源表如图12-13所示。其中ID是主键，YEAR、MON是时间戳。

图12-13  源表

    目标表如图12-14所示。其中ID是主键。

图12-14  目标表

    （2）设计Mapping

    Mapping 的设计如图12-15所示：它的抽取频率被设置成每月抽取一次。

图12-15  Mapping设计（一）

    Mapping展开后如图12-16所示。

图12-16  Mapping设计（二）

    （3）组件的设计

    FILTRANS(过滤器)组件设计如图12-17所示。

图12-17  FILTRANS组件设计

    FILTRANS过滤方法是判断源表的时间戳YEAR、MON字段是否等于ETL服务器的系统时间，或者时间戳YEAR、MON字段等于固定的数值。

    FILTRANS的代码如图12-18所示。

图12-18  FILTRANS（过滤器）代码

    Lookup组件设计如图12-19所示。

图12-19  Lookup组件设计

    Lookup组件的查询条件如图12-20所示。

图12-20  Lookup组件的查询条件

    Update组件设计如图12-21所示。

图12-21  Update组件设计

    Update组件的过滤代码如图12-22所示。

图12-22  Update组件的过滤代码

    （4）源表到目标表的映射

    假设当前ETL服务器的系统时间为2011年1月31日，源表的数据见表12-1。

表12-1  源表的数据

    以时间戳作为加载条件，经过ETL抽取之后目标表的数据见表12-2。

表12-2  目标表的数据

    源表修改后的数据见表12-3。

表12-3  源表修改后的数据

    再经过ETL数据抽取之后，目标表的数据见表12-4。

表12-4  目标表的新数据

    12.3.2  利用源表的日志信息对目标表进行数据加载

    在业务系统中为源表添加日志表，当源表中的数据增加、删除、修改时，及时更新该源表的日志表。在进行ETL数据加载时，通过读取源表的日志信息决定为目标表加载哪些源表的数据。优点是不需要更改数据源表的表结构和数据，降低了数据加载的风险。缺点是需要维护日志表的数据，增加了系统的开销。更新日志表的方式可以通过触发器对日志表的信息进行修改，如图12-23所示。

图12-23  利用源表的日志信息对目标表进行数据加载

    12.3.3  通过全表对比的方式进行数据加载

    通过全表对比的方式进行数据加载，是对目标表的每条数据进行比较，当目标表不存在该主键值时，进行插入操作，如果该主键值已经存在，则需要对其余字段进行比较，如果有不相同的数值，则需要进行更新操作。全表对比的方式不需要对业务源数据表进行任何修改，安全性较高，但是由于流程较为复杂，一般抽取的效率较低。

    下面利用Informatica 工具，通过全表对比的方式进行数据加载。

    （1）设计源表和目标表

    源表如图12-24所示。其中ID是主键，YEAR、MON是时间戳。

    目标表如图12-25所示。其中ID是主键。

图12-24  源表

图12-25  目标表

    （2）设计Mapping

    Mapping 的设计如图12-26所示：它的抽取频率被设置成每月抽取一次。

图12-26  Mapping设计（一）

    Mapping展开后如图12-27所示。

图12-27  Mapping设计（二）

    （3）组件的设计

    Lookup组件设计如图12-28所示。

图12-28  Lookup组件设计

    Lookup组件的查询条件如图12-29所示。

图12-29  Lookup组件的查询条件

    Update组件设计如图12-30所示。

图12-30  Update组件设计

    Update组件的过滤代码如图12-31所示。

图12-31  Update组件的过滤代码

    （4）源表到目标表的映射

    假设当前ETL服务器的系统时间为2011年1月31日，源表的数据见表12-5。

表12-5  源表的数据

    通过全表对比的方式进行数据加载，经过ETL抽取之后目标表的数据见表12-6。

表12-6  目标表的数据

修改源表的数据，见表12-7。

表12-7  源表修改后的数据

    再经过ETL数据抽取之后，目标表的数据见表12-8。

表12-8  目标表的新数据

    12.3.4  全表删除后再进行数据加载的方式

    全表删除后再进行数据加载的方式的实现相对简单。但是不能实现数据的递增加载，只能删除目标表的数据之后，再经过ETL为目标表加载数据，Informatica工具提供了这样的一种功能，在WorkFlow的Session中设定Attribute(属性定义)，再选择Truncate target table option即可，如图12-32所示。

图12-32  设定Attribute

    总结：ETL的加载方法包括全量加载和增量加载，如图12-33所示。从技术角度上说，全量加载的方式比增量加载要简单很多，使用增量加载的方法，难度在于必须设计正确有效的方法从数据源中抽取变化的数据，同时将这些变化的数据更新到数据仓库中，如果每次抽取都有超过1/4的业务源数据需要更新，就应该考虑更改ETL的加载方法，由增量抽取改为全量抽取。全量抽取对于数据量较小，更新频率较低的系统比较适用。

图12-33  ETL的加载方法