数据仓库数据同步策略

电玩女神 2021-12-18 14:34 752阅读 0赞

## 增量型 ##

1.  无状态变更数据

假设数据源是流水数据，此类数据没有状态变更，写入数据库后基本不再改变，数据中一般包含 Created\_time 信息，可以根据 Created\_time 的值获取增量数据，或者记录上次的获取到的 ID，然后从下一个 ID 开始获取，这是一种纯增量采集。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Z3azE5ODQwMzAx_size_16_color_FFFFFF_t_70][]

2.有状态变更数据

假设表比较大，比如说一些订单表，这些表的状态变化周期一般偏长，状态变化一直会更新，而且状态变化会跨零点。

此时要求所有的源系统数据库的设计需要加入 Created\_time、updated\_time，在 Source

层把昨天发生变化的数据抽取过来（updated\_time >= T-1 0:0:0），没有设置 updated\_time < T0:0:0，因为你昨天对数据进行了修改，可能在今天采集数据之前又进行了修改，因此，只 要昨天修改过就符合抽取条件，如果想更更精准地获取数据，或者说让抽取的数据量小一点， 那么可以加入 Created\_time 的限制条件（updated\_time >=  T-1  0:0:0 and Created\_time <  T0:0:0），通过这种抽取条件就可以获得昨天更新的数据和昨天新增的数据。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Z3azE5ODQwMzAx_size_16_color_FFFFFF_t_70 1][]

获取到更新数据后进行合并，两张表进行比对（根据主键进行比对）：（A 表为 T-2 全量数据，B 表为增量数据）

1.  如果 A 表中包含的数据（根据自增主键判断）在 B 表中也包含，那么就代表数据在昨天发生了变化，那么就用 B 中的数据替换 A 中的数据；
2.  如果 A 表中没有的数据在 B 表中包含，那么就代表数据时昨天新插入的，那么就将B 中数据插入 A；
3.  如果 A 表中包含的数据在 B 表中没有，那么就代表数据在昨天没有被更新；

经过上述逻辑后，将更新后的全量数据放到 A 表的 T - 1  分区中，成为了 T - 1  的全量数据，按照这一规则，每一天的全量数据都会被保存一份。完成数据更新后 B 表就可以被丢弃了。

## **全量型** ##

**假设表不是很大，而且数据状态会发生变化，可以进行全量采集，采集所有数据**。此表格每天一个快照，累计时间长了之后，数据量也会很大。

数据仓库中的表格 Dw\_a 按照日期进行分区：

![20190702153450420.png][]

每个分区中保存的是全量数据（全量快照）。不论是全量抽取还是增量抽取，每个分区

Dt 中保存的都是截止到当日最后一刻，这张表的快照。

05-09 是 5 月 9 号的全量快照，5 月 10 号时，采集当天的增量数据 A\_incr-20180510， 然后用增量数据 A\_incr-20180510 和 Dt=2018-05-09 中的数据进行合并，写入 Dt=2018-05-10 分区中，这样就能保证 0510 是全量数据。如果没有发生变化，那么 5 月 10 号的快照和 5月 9 号的快照是一样的。

![20190702153415489.png][]

**所有的表都可以采用这种方式，但是我们考虑，如果表的数据不是很大，那么使用全量采集也没有问题，但是随着数据量的不断增加，每天进行全量采集的代价越来越高，此****时可以考虑使用增量采集**。例如订单表，本身数据量很大，变化周期长，那么可以直接采用

增量采集。

## **一、稳定维度** ##

**部分维度表的维度是在维度表产生后，属性是稳定的、无变化的**。比如时间维度、区 域维度等，针对这种维度，设计维度表的时候，仅需要完整的数据，不需要天的快照数据， 因为当前数据状态就是历史数据状态。

![2019070215215988.png][]

图 1-1  稳定维度

**1.增量存储**

当事实表数据无状态变化时，采用增量存储，即每周期仅处理增量部分的数据，纯增量采集。

**2.全量快照**

状态有变化，但每天保存当前的快照数据，对于数据量在可控范围内的情况可以采用。保存策略：

1.  如果存储空间和成本可接受，完整存储，确保能够追溯到历史每天数据状态；
2.  存储空间有限，考虑移动历史快照数据到冷盘，需要使用的时候可恢复；
3.  数据历史状态数据无太大价值，可以考虑部分删除，比如近保留每月最后一天的快照数据；

## **二、缓慢渐变维** ##

**维度数据会随着时间发生变化，变化速度比较缓慢，这种维度数据通常称作缓慢渐变****维**，例如电商平台的用户维度表，用户可能会随着时间推移改变收件地址，因此用户维度表

中的收件地址就是一个缓慢变化维。由于数据仓库需要追溯历史变化，尤其是一些重要的数据，所以历史状态也需要采取一定的措施进行保存，保存历史状态的方式有以下三种：

1.  每天保存当前数据的全量快照数据（每天一个新增分区），该方案适合数据量较小（根据公司具体的配置而定）的维度，使用简单的方式保存历史状态。
2.  在维表中添加关键属性值的历史字段，仅保留上一个的状态值。可能同时有多个属性都非常重要，而且只能追溯上一个数据，不是所有的历史数据，这种范式应用场景较少。
3.  **拉链表：当维度数据发生变化时，将旧数据置为失效，将更改后的数据当作新的记录插入到维度表中，并开始生效，这样能够记录数据在某种粒度上的变化历史。**

--------------------

## **代理键** ##

**维度表中必须有一个能够唯一标识一行记录的列**（最好是原子性的列，不要是组合键）， 通过该列维护维度表与事实表之间的关系，一般在维度表中业务主键符合条件可以当作维度主键。

但是，数据仓库是整个公司数据的整合，这会涉及到**多个数据源有相同维度**，那么就会出现以下两个问题：

1.  当整合多个数据源的维度时，不同数据源的业务主键重复怎么办？
2.  涉及维度拉链表时，同一主体多条记录，业务键重复怎么办？

表 1.1 财务部门维度表

<table style="margin-left:10.5pt;width:426.2pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:142pt;"> ID </td> 
 <td style="vertical-align:top;width:142.1pt;"> name </td> 
 <td style="vertical-align:top;width:142.1pt;"> note </td> 
 </tr> 
 <tr> 
 <td style="width:142pt;"> 1 </td> 
 <td style="width:142.1pt;"> Chen </td> 
 <td style="width:142.1pt;"> Finc </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:142pt;"> 2 </td> 
 <td style="vertical-align:top;width:142.1pt;"> Zhang </td> 
 <td style="vertical-align:top;width:142.1pt;"> Finc </td> 
 </tr> 
 </tbody> 
</table>

表 1.2 研发部门维度表

如上图所示，业务键重复，我们可以引入代理键，如下表所示：

表 1.3 引入代理键

<table style="margin-left:10.5pt;width:426.2pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:102.5pt;"> GID </td> 
 <td style="vertical-align:top;width:105.25pt;"> ID </td> 
 <td style="vertical-align:top;width:109.95pt;"> name </td> 
 <td style="vertical-align:top;width:108.5pt;"> note </td> 
 </tr> 
 <tr> 
 <td style="width:102.5pt;"> 1 </td> 
 <td style="width:105.25pt;"> 1 </td> 
 <td style="width:109.95pt;"> Chen </td> 
 <td style="width:108.5pt;"> Finc </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:102.5pt;"> 2 </td> 
 <td style="vertical-align:top;width:105.25pt;"> 2 </td> 
 <td style="vertical-align:top;width:109.95pt;"> Zhang </td> 
 <td style="vertical-align:top;width:108.5pt;"> Finc </td> 
 </tr> 
 <tr> 
 <td style="width:102.5pt;"> 3 </td> 
 <td style="width:105.25pt;"> 1 </td> 
 <td style="width:109.95pt;"> Li </td> 
 <td style="width:108.5pt;"> Risk </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:102.5pt;"> 4 </td> 
 <td style="vertical-align:top;width:105.25pt;"> 2 </td> 
 <td style="vertical-align:top;width:109.95pt;"> Zhou </td> 
 <td style="vertical-align:top;width:108.5pt;"> Risk </td> 
 </tr> 
 </tbody> 
</table>

把多个系统的数据复合在一起，同时再维护一个代理键，而且代理键在这个维度表里是唯一标识一条记录的，类似于业务系统的业务键。

**代理键是由数据仓库处理过程中产生的、与业务本身无关的、唯一标识维度表中一条记录并充当维度表主键的列，也是描述维度表与事实表关系的纽带。**

**在设计有代理键的维度表中，事实表中的关联键是代理键而不是原有的业务主键，即**

**业务关系是靠代理键维护，这样有效避免源系统变化对数仓数据对影响**。

在实际业务中，代理键通常是数值型、自增的值。

### **拉链表详解** ###

**将数据的变更当做流水记录下来 ，旧的设为失效，新的设为生效，如果粒度为天，那么就可以得到一天的最终状态作为最终状态**。

表 1-4拉链表

<table style="margin-left:10.5pt;width:426.2pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:85.2pt;"> ID </td> 
 <td style="vertical-align:top;width:85.25pt;"> name </td> 
 <td style="vertical-align:top;width:85.2pt;"> addr </td> 
 <td style="vertical-align:top;width:85.2pt;"> start_date </td> 
 <td style="vertical-align:top;width:85.35pt;"> end_date </td> 
 </tr> 
 <tr> 
 <td style="width:85.2pt;"> 1 </td> 
 <td style="width:85.25pt;"> Zhou </td> 
 <td style="width:85.2pt;"> dept2 </td> 
 <td style="width:85.2pt;"> 2018-05-01 </td> 
 <td style="width:85.35pt;"> 2018-06-09 </td> 
 </tr> 
 <tr> 
 <td style="width:85.2pt;"> 1 </td> 
 <td style="width:85.25pt;"> Zhou </td> 
 <td style="width:85.2pt;"> dept3 </td> 
 <td style="width:85.2pt;"> 2018-06-10 </td> 
 <td style="width:85.35pt;"> 2018-06-14 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:85.2pt;"> 1 </td> 
 <td style="vertical-align:top;width:85.25pt;"> Zhou </td> 
 <td style="vertical-align:top;width:85.2pt;"> dept1 </td> 
 <td style="vertical-align:top;width:85.2pt;"> 2018-06-15 </td> 
 <td style="vertical-align:top;width:85.35pt;"> 9999-12-31 </td> 
 </tr> 
 </tbody> 
</table>

表1-4中每条记录都有一个 End\_date，当有新的数据产生时，在旧数据的 End\_date 字段中插入日期，然后新插入一条数据，新数据的 End\_date 字段中是一个永久有效的值，如果再发生更新，上一次更新数据的 End\_date 字段设置为当前日期，然后再次插入新数据， 新数据的 End\_date 字段中设置一个永久有效的值。

如果想知道某个员工在 5 月 22 号时在哪个部门，那么可以通过如下 SQL：

Select \* from user where start\_date<= 2018-05-22 and end\_date>= 2018-05-22

**根据拉链表的结构，如果对维度表做拉链，那么一个维度实体必然存在多条记录，也就是一个主键 ID 对应多条数据，此时维度表的原子性主键也就没有意义了。**

维度表做拉链后会失去原子性主键，那么拉链维度表如何和事实表进行关联呢？ 此时就要用到代理键，也就是在事实表和维度表中同时添加代理键，如下图所示：

表 1-5用户详情拉链表

<table style="margin-left:10.5pt;width:426.25pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:63.5pt;"> UID </td> 
 <td style="vertical-align:top;width:66.15pt;"> ID </td> 
 <td style="vertical-align:top;width:70.7pt;"> name </td> 
 <td style="vertical-align:top;width:70.2pt;"> addr </td> 
 <td style="vertical-align:top;width:77.9pt;"> start_date </td> 
 <td style="vertical-align:top;width:77.8pt;"> end_date </td> 
 </tr> 
 <tr> 
 <td style="width:63.5pt;"> 1 </td> 
 <td style="width:66.15pt;"> 1 </td> 
 <td style="width:70.7pt;"> Zhou </td> 
 <td style="width:70.2pt;"> dept2 </td> 
 <td style="width:77.9pt;"> 2018-05-01 </td> 
 <td style="width:77.8pt;"> 2018-06-09 </td> 
 </tr> 
 <tr> 
 <td style="width:63.5pt;"> 2 </td> 
 <td style="width:66.15pt;"> 1 </td> 
 <td style="width:70.7pt;"> Zhou </td> 
 <td style="width:70.2pt;"> dept3 </td> 
 <td style="width:77.9pt;"> 2018-06-10 </td> 
 <td style="width:77.8pt;"> 2018-06-14 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:63.5pt;"> 3 </td> 
 <td style="vertical-align:top;width:66.15pt;"> 1 </td> 
 <td style="vertical-align:top;width:70.7pt;"> Zhou </td> 
 <td style="vertical-align:top;width:70.2pt;"> dept1 </td> 
 <td style="vertical-align:top;width:77.9pt;"> 2018-06-15 </td> 
 <td style="vertical-align:top;width:77.8pt;"> 9999-12-31 </td> 
 </tr> 
 </tbody> 
</table>

表 1-6 订单表

<table style="margin-left:88.15pt;width:270.95pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:63.4pt;"> OID </td> 
 <td style="vertical-align:top;width:66.25pt;"> ID -&gt; UID </td> 
 <td style="vertical-align:top;width:70.6pt;"> Tim_ID </td> 
 <td style="vertical-align:top;width:70.7pt;"> amount </td> 
 </tr> 
 <tr> 
 <td style="width:63.4pt;"> 1 </td> 
 <td style="width:66.25pt;"> 1 </td> 
 <td style="width:70.6pt;"> 5 </td> 
 <td style="width:70.7pt;"> 998 </td> 
 </tr> 
 <tr> 
 <td style="width:63.4pt;"> 2 </td> 
 <td style="width:66.25pt;"> 1 </td> 
 <td style="width:70.6pt;"> 9 </td> 
 <td style="width:70.7pt;"> 1000 </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;width:63.4pt;"> 3 </td> 
 <td style="vertical-align:top;width:66.25pt;"> 2 </td> 
 <td style="vertical-align:top;width:70.6pt;"> 10 </td> 
 <td style="vertical-align:top;width:70.7pt;"> 1499 </td> 
 </tr> 
 </tbody> 
</table>

完成代理键的添加后，在之后的统计中，按照代理键进行聚合即可。

事实表来源于业务事务表，代理键和业务本身没有关系，那么怎么在新增数据时在事实表中装载代理键？

**当事实表中有新增数据时，新增数据中记录了维度表中原有的原子性主键，可以根据原有的主键匹配维度表中的数据，然后根据新增数据的时间范围找到匹配的代理键，然后在事实表的新增数据中加入代理键。**

代理键是维度建模中极力推荐的方式，它的应用能有效的隔离源端变化带来的数仓结构

不稳定问题，同时也能够提高数据检索性能。

但是**代理键维护代价非常高，尤其是数据装载过程中，对事实表带来了较大的影响， 在基于** **hive** **的数据仓库建设影响更加严重**，比如代理键的生成、事实表中关联键的装载、

不支持非等值关联等问题，带来 ETL 过程更加复杂。

**因此，在大数据体系下，谨慎使用代理键，同时对于缓慢渐变维场景，可以考虑用空间换取时间，每天保留维表全量快照，但这样会带来存储成本，根据实际情况衡量。**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Z3azE5ODQwMzAx_size_16_color_FFFFFF_t_70]: /images/20211218/a97acd900cc24063a1a42b7a54152a97.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2Z3azE5ODQwMzAx_size_16_color_FFFFFF_t_70 1]: /images/20211218/fce4318e9e7145f393f743e7150cc343.png
[20190702153450420.png]: /images/20211218/3c05a364df9c421daa488fe61923c9d4.png
[20190702153415489.png]: /images/20211218/5c6d35b3770f4778a1a6cb41867a844e.png
[2019070215215988.png]: /images/20211218/c02b7bb5f9b449e187cdce74f16acf3e.png