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

一、拉链表

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

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

商品历史快照案例

需求：

有一个商品表：

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

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

goods_id	goods_status	createtime	modifytime
001	待审核	2019-12-20	2019-12-20
002	待售	2019-12-20	2019-12-20
003	在售	2019-12-20	2019-12-20
004	已删除	2019-12-20	2019-12-20

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

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

1、方案介绍

该方案为：

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

12月20日（4条数据）

goods_id	goods_status	createtime	modifytime
001	待审核	2019-12-18	2019-12-20
002	待售	2019-12-19	2019-12-20
003	在售	2019-12-20	2019-12-20
004	已删除	2019-12-15	2019-12-20

12月21日（10条数据）

goods_id	goods_status	createtime	modifytime
以下为12月20日快照数据
001	待审核	2019-12-18	2019-12-20
002	待售	2019-12-19	2019-12-20
003	在售	2019-12-20	2019-12-20
004	已删除	2019-12-15	2019-12-20
以下为12月21日快照数据
001	待售（从待审核到待售）	2019-12-18	2019-12-21
002	待售	2019-12-19	2019-12-20
003	在售	2019-12-20	2019-12-20
004	已删除	2019-12-15	2019-12-20
005（新商品）	待审核	2019-12-21	2019-12-21
006（新商品）	待审核	2019-12-21	2019-12-21

12月22日（18条数据）

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

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',