数据仓库数据集成算法

2017-09-07 本文已影响0人不圆的石头

1.数据仓库数据集成

数据仓库将源系统数据抽取到ODS或者ODS提供数据给数据仓库后，需要将不同来源的数据根据业务需求集成在同一模型中。总体来说，集成算法与ODS算法基本相同，区别在于集成时当日数据来源并不是唯一的。从模型上来说，大体分为历史表模型，当前表模型，流水表模型，特殊数据模型将根据业务需求特殊加载。

2.历史表数据加载算法

历史表指当源系统数据被删除（物理删除或逻辑删除）或者失效后，数据仓库不会将数据物理删除，只将数据的结束日期闭链，从而保证将源系统数据变化的过程保留下来。基本加载算法如下：

先根据映射将源数据加载到临时表ND(可能多个源)

    INSERT INTO ${ND}( $COLS )
    SELECT $COLS
    FROM ${SRC_DB}.${SRC_TBNAME}
    ;

得到目标表上日有效数据OD

全量：获取上日全部有效数据

INSERT INTO OD
SELECT * FROM ${DB}.${TBNAME}
WHERE ${HT_E_DT} > ${TX_DATE}   
AND   ${HT_S_DT} <= ${TX_DATE}
;

增量：获取上日主键在ND中的有效数据

 INSERT INTO OD
 SELECT * FROM ${DB}.${TBNAME}
 WHERE ($PRI_KEY_NAME) IN (SELECT $PRI_KEY_NAME FROM ${ND}) 
 AND ${HT_E_DT} > ${TX_DATE}   
 AND ${HT_S_DT} <= ${TX_DATE}

产生需要插入的数据wt_i（当日数据不在昨日数据中）

        INSERT INTO WT_I(
                $COLS
               ,${HT_S_DT}
               ,${HT_E_DT}
               ,DATA_DT )
            SELECT
                $COLS
               ,$TX_DATE
               ,$MAX_DT
               ,$TX_DATE
            FROM ${ND}
            WHERE ($COLS_NOT_NULL)
              NOT IN (SELECT $COLS_NOT_NULL
                      FROM OD)
            ;

产生关链数据(昨日数据不在当日数据中,源系统已经失效数据)，并将数据加在到临时表WT_U中

        INSERT INTO WT_U(
             $COLS
            ,${HT_S_DT}
            ,${HT_E_DT}
            ,DATA_DT )
        SELECT
             $COLS
            ,${HT_S_DT}
            ,${TX_DATE}
            ,DATA_DT
        FROM OD
        WHERE ( $COLS_NOT_NULL )
          NOT IN (SELECT $COLS_NOT_NULL
                  FROM ${ND})
        ;

根据wt_u表将目标表的数据关链

UPDATE  ${DB}.${TBNAME}
SET     ${HT_E_DT} =${TX_DATE}
WHERE   ($COLS_NOT_NULL) IN (SELECT $COLS_NOT_NULL FROM WT_U)
AND ($PRI_KEY_NAME) IN (SELECT $PRI_KEY_NAME FROM ${ND}) 
AND ${HT_E_DT} > ${TX_DATE}   
AND ${HT_S_DT} <= ${TX_DATE} 
;

根据wt_i表将新增的数据和变化的数据插入到目标表
包含两部分数据：上日数据存在但属性值变化的、新增数据

INSERT INTO ${DB}.${TBNAME}(
     $COLS
    ,${HT_S_DT}
    ,${HT_E_DT}
    ,DATA_DT)
SELECT
     $COLS
    ,${HT_S_DT}
    ,${HT_E_DT}
    ,DATA_DT
FROM WT_I;

3.流水表数据加载算法

通常流水表只保留在ODS层，但某些流水数据可能来源于多个源系统，为了后续数据使用方便，也将多个来源的流水表集成到一起。

删除数据日期为当日的数据，目的是支持重跑

  DELETE FROM ${DB}.${TBNAME} 
  WHERE Data_Dt = $DATA_DT
;
  ```

将源表数据按照映射插入到目标表
- 可以先将数据加载到临时表ND，再从ND插入目标表
- 也可以直接从源表将数据插入到目标表
```
INSERT INTO ${DB}.${TBNAME}(
    $COLS
   ,DATA_DT)
SELECT
    $COLS
   ,DATA_DT
FROM ${SRC_DB}.${SRC_TABLENAME};
```

4.当前表数据加载算法

当前表是指只保留最新的数据，源系统失效或者删除（物理删除或逻辑删除）的数据，在数据仓库集成的时候，不再保留，直接物理删除。当前表只保留有效的数据。算法与历史表基本相同(只是wt_u是直接删除，而不是关链)

先根据映射将源数据加载到临时表ND

    INSERT INTO ${ND}( $COLS )
    SELECT $COLS
    FROM ${SRC_DB}.${SRC_TBNAME}
    ;