数据仓库数据集成算法

1.数据仓库数据集成

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

2.历史表数据加载算法

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

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

    1
    2
    3
    4
    INSERT INTO ${ND}( $COLS )
    SELECT $COLS
    FROM ${SRC_DB}.${SRC_TBNAME}
    ;
  • 得到目标表上日有效数据OD

    • 全量:获取上日全部有效数据

      1
      2
      3
      4
      5
      INSERT INTO OD
      SELECT * FROM ${DB}.${TBNAME}
      WHERE ${HT_E_DT} > ${TX_DATE}
      AND ${HT_S_DT} <= ${TX_DATE}
      ;
    • 增量:获取上日主键在ND中的有效数据

      1
      2
      3
      4
      5
      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(当日数据不在昨日数据中)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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中

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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表将目标表的数据关链

    1
    2
    3
    4
    5
    6
    7
    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表将新增的数据和变化的数据插入到目标表
    包含两部分数据:上日数据存在但属性值变化的、新增数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    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层,但某些流水数据可能来源于多个源系统,为了后续数据使用方便,也将多个来源的流水表集成到一起。

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

    1
    2
    3
    DELETE FROM ${DB}.${TBNAME}
    WHERE Data_Dt = $DATA_DT
    ;
  • 将源表数据按照映射插入到目标表

    • 可以先将数据加载到临时表ND,再从ND插入目标表
    • 也可以直接从源表将数据插入到目标表
      1
      2
      3
      4
      5
      6
      7
      INSERT INTO ${DB}.${TBNAME}(
      $COLS
      ,DATA_DT)
      SELECT
      $COLS
      ,DATA_DT
      FROM ${SRC_DB}.${SRC_TABLENAME};

4.当前表数据加载算法

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

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

    1
    2
    3
    4
    INSERT INTO ${ND}( $COLS )
    SELECT $COLS
    FROM ${SRC_DB}.${SRC_TBNAME}
    ;
  • 得到目标表上日有效数据OD

    • 全量:获取上日全部有效数据

      1
      2
      3
      4
      5
      INSERT INTO OD
      SELECT * FROM ${DB}.${TBNAME}
      WHERE ${HT_E_DT} > ${TX_DATE}
      AND ${HT_S_DT} <= ${TX_DATE}
      ;
    • 增量:获取上日主键在ND中的有效数据

      1
      2
      3
      4
      5
      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(当日数据不在昨日数据中)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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中

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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表将目标表的数据删除

    1
    2
    3
    4
    5
    DELETE T1
    FROM ${DB}.${TABLENAME} T1
    ,wt_u T2
    WHERE T1.PK1 = T2.PK2
    ;
  • 根据wt_i表将新增的数据和变化的数据插入到目标表
    包含两部分数据:上日数据存在但属性值变化的、新增数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    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;