大数据-数据仓库：快照表、拉链表，全量表，增量表

小鱼儿 2023-10-05 19:22 61阅读 0赞

## 一、拉链表 ##

数据仓库的数据模型设计过程中，经常会遇到这样的需求：

1.  表中的部分字段会被update，例如：
    
     *  用户的地址，产品的描述信息，品牌信息等等;
2.  需要查看某一个时间点或者时间段的历史快照信息，例如：
    
     *  查看某一个产品在历史某一时间点的状态
     *  查看某一个用户在过去某一段时间内，更新过几次等等
3.  变化的比例和频率不是很大，例如：
    
     *  总共有1000万的会员，每天新增和发生变化的有10万左右

##### 商品历史快照案例 #####

需求：

有一个商品表：

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th>类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>goods_id</td> 
   <td>varchar(50)</td> 
   <td>商品编号</td> 
  </tr> 
  <tr> 
   <td>goods_status</td> 
   <td>varchar(50)</td> 
   <td>商品状态（待审核、待售、在售、已删除）</td> 
  </tr> 
  <tr> 
   <td>createtime</td> 
   <td>varchar(50)</td> 
   <td>商品创建日期</td> 
  </tr> 
  <tr> 
   <td>modifytime</td> 
   <td>varchar(50)</td> 
   <td>商品修改日期</td> 
  </tr> 
 </tbody> 
</table>

2019年12月20日的数据如下所示：

<table> 
 <thead> 
  <tr> 
   <th>goods_id</th> 
   <th>goods_status</th> 
   <th>createtime</th> 
   <th>modifytime</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>001</td> 
   <td>待审核</td> 
   <td>2019-12-20</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>002</td> 
   <td>待售</td> 
   <td>2019-12-20</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>003</td> 
   <td>在售</td> 
   <td>2019-12-20</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>004</td> 
   <td>已删除</td> 
   <td>2019-12-20</td> 
   <td>2019-12-20</td> 
  </tr> 
 </tbody> 
</table>

商品的状态，会随着时间推移而变化，我们需要将商品的所有变化的历史信息都保存下来。如何实现呢？

### 方案一：快照每一天的数据到数仓（冗余数据） ###

#### 1、方案介绍 ####

该方案为：

*  每一天都保存一份全量，将所有数据同步到数仓中
 *  很多记录都是重复保存，没有任何变化

12月20日（4条数据）

<table> 
 <thead> 
  <tr> 
   <th>goods_id</th> 
   <th>goods_status</th> 
   <th>createtime</th> 
   <th>modifytime</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>001</td> 
   <td>待审核</td> 
   <td>2019-12-18</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>002</td> 
   <td>待售</td> 
   <td>2019-12-19</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>003</td> 
   <td>在售</td> 
   <td>2019-12-20</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>004</td> 
   <td>已删除</td> 
   <td>2019-12-15</td> 
   <td>2019-12-20</td> 
  </tr> 
 </tbody> 
</table>

12月21日（10条数据）

12月22日（18条数据）

<table> 
 <thead> 
  <tr> 
   <th>goods_id</th> 
   <th>goods_status</th> 
   <th>createtime</th> 
   <th>modifytime</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>以下为12月20日快照数据</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>001</td> 
   <td>待审核</td> 
   <td>2019-12-18</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>002</td> 
   <td>待售</td> 
   <td>2019-12-19</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>003</td> 
   <td>在售</td> 
   <td>2019-12-20</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>004</td> 
   <td>已删除</td> 
   <td>2019-12-15</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>以下为12月21日快照数据</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>001</td> 
   <td><strong>待售（从待审核到待售）</strong></td> 
   <td>2019-12-18</td> 
   <td><strong>2019-12-21</strong></td> 
  </tr> 
  <tr> 
   <td>002</td> 
   <td>待售</td> 
   <td>2019-12-19</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>003</td> 
   <td>在售</td> 
   <td>2019-12-20</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>004</td> 
   <td>已删除</td> 
   <td>2019-12-15</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td>005</td> 
   <td>待审核</td> 
   <td>2019-12-21</td> 
   <td>2019-12-21</td> 
  </tr> 
  <tr> 
   <td>006</td> 
   <td>待审核</td> 
   <td>2019-12-21</td> 
   <td>2019-12-21</td> 
  </tr> 
  <tr> 
   <td>以下为12月22日快照数据</td> 
   <td></td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>001</td> 
   <td>待售</td> 
   <td>2019-12-18</td> 
   <td>2019-12-21</td> 
  </tr> 
  <tr> 
   <td>002</td> 
   <td>待售</td> 
   <td>2019-12-19</td> 
   <td>2019-12-20</td> 
  </tr> 
  <tr> 
   <td><strong>003</strong></td> 
   <td><strong>已删除（从在售到已删除）</strong></td> 
   <td><strong>2019-12-20</strong></td> 
   <td><strong>2019-12-22</strong></td> 
  </tr> 
  <tr> 
   <td>004</td> 
   <td>待审核</td> 
   <td>2019-12-21</td> 
   <td>2019-12-21</td> 
  </tr> 
  <tr> 
   <td>005</td> 
   <td>待审核</td> 
   <td>2019-12-21</td> 
   <td>2019-12-21</td> 
  </tr> 
  <tr> 
   <td><strong>006</strong></td> 
   <td><strong>已删除（从待审核到已删除）</strong></td> 
   <td><strong>2019-12-21</strong></td> 
   <td><strong>2019-12-22</strong></td> 
  </tr> 
  <tr> 
   <td><strong>007</strong></td> 
   <td><strong>待审核</strong></td> 
   <td><strong>2019-12-22</strong></td> 
   <td><strong>2019-12-22</strong></td> 
  </tr> 
  <tr> 
   <td><strong>008</strong></td> 
   <td><strong>待审核</strong></td> 
   <td><strong>2019-12-22</strong></td> 
   <td><strong>2019-12-22</strong></td> 
  </tr> 
 </tbody> 
</table>

#### 2、MySQL到Hive数仓代码实现 ####

**MySQL&Hive初始化**

1 在MySQL demo库中 创建表

-- 创建数据库
    CREATE DATABASE demo DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
    
    -- 创建商品表
    create table if not exists `demo`.`t_product`(
    	goods_id varchar(50),		-- 商品编号
        goods_status varchar(50),	-- 商品状态
        createtime varchar(50),		-- 商品创建时间
        modifytime varchar(50)		-- 商品修改时间
    );

2 在Hive中 demo库创建表

-- 创建表
    create database if not exists `demo`;
    
    -- 创建ods层表
    create table if not exists `demo`.`ods_product`(
        goods_id string,        -- 商品编号
        goods_status string,    -- 商品状态
        createtime string,      -- 商品创建时间
        modifytime string       -- 商品修改时间
    )
    partitioned by (dt string)
    row format delimited fields terminated by ',' stored as TEXTFILE;
    
    -- 创建dw层表
    create table if not exists `demo`.`dw_product`(
        goods_id string,        -- 商品编号
        goods_status string,    -- 商品状态
        createtime string,      -- 商品创建时间
        modifytime string       -- 商品修改时间
    )
    partitioned by (dt string)
    row format delimited fields terminated by ',' stored as TEXTFILE;

**增量导入12月20日数据**

1 MySQL数据库导入12月20日数据（4条数据）

insert into `demo`.`t_product`(goods_id, goods_status, createtime, modifytime) values
    ('001', '待审核', '2019-12-18', '2019-12-20'),
    ('002', '待售', '2019-12-19', '2019-12-20'