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

第三节 ETL的加载方法

    12.3.1  以时间戳作为加载条件

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

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

    (1)设计源表和目标表

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

 图12-13  源表

图12-13  源表

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

 图12-14  目标表

图12-14  目标表

    (2)设计Mapping

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

 图12-15  Mapping设计(一)

图12-15  Mapping设计(一)

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

 图12-16  Mapping设计(二)

图12-16  Mapping设计(二)

    (3)组件的设计

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

 图12-16  Mapping设计(二)

图12-17  FILTRANS组件设计

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

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

 图12-18  FILTRANS(过滤器)代码

图12-18  FILTRANS(过滤器)代码

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

 图12-19  Lookup组件设计

图12-19  Lookup组件设计

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

 图12-19  Lookup组件设计

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

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

 图12-21  Update组件设计

图12-21  Update组件设计

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

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

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

    (4)源表到目标表的映射

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

表12-1  源表的数据

 表12-1  源表的数据

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

表12-2  目标表的数据

 表12-2  目标表的数据

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

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

 表12-2  目标表的数据

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

表12-4  目标表的新数据

 表12-4  目标表的新数据

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

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

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

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

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

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

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

    (1)设计源表和目标表

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

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

 图12-24  源表

图12-24  源表

 图12-25  目标表

图12-25  目标表

    (2)设计Mapping

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

 图12-26  Mapping设计(一)

图12-26  Mapping设计(一)

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

 图12-27  Mapping设计(二)

图12-27  Mapping设计(二)

    (3)组件的设计

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

 图12-28  Lookup组件设计

图12-28  Lookup组件设计

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

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

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

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

 图12-30  Update组件设计

图12-30  Update组件设计

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

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

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

    (4)源表到目标表的映射

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

表12-5  源表的数据

 表12-5  源表的数据

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

表12-6  目标表的数据

 表12-6  目标表的数据

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

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

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

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

表12-8  目标表的新数据

 表12-8  目标表的新数据

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

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

 图12-32  设定Attribute

图12-32  设定Attribute

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

 图12-33  ETL的加载方法

图12-33  ETL的加载方法