hive内部表和外部表的区别_HIVE拉链表实现

背景

https://www.cnblogs.com/lxbmaomao/p/9821128.html​www.cnblogs.com

拉链表是针对数据仓库设计中表存储数据的方式而定义的，主要是维护历史状态，以及最新状态数据的一种表，拉链表根据拉链粒度的不同，实际上相当于快照，只不过做了优化，去除了一部分不变的记录，通过拉链表可以很方便的还原出拉链时点的客户记录。

拉链表相对来说应用场景比较有限，一般使用hive的分区表、或者全量、增量更新表或者存储快照方便之后查询。

全量表：每天的所有的最新状态的数据；

增量表：每天的新增数据；

拉链表：维护历史状态，以及最新状态数据；

流水表：对于表中的每一个修改都会记录，可以用于反映实际记录的变更；

拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储。

应用场景

• 有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用ORC压缩，单张表的存储也会超过100G，在HDFS使用双备份或者三备份的话就更大一些。
• 表中的部分字段会被update更新操作，如用户联系方式，产品的描述信息，订单的状态等等。
• 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态。
• 表中的记录变化的比例和频率不是很大，比如，总共有10亿的用户，每天新增和发生变化的有200万左右，变化的比例占的很小。

那么对于这种表我该如何设计呢？下面有几种方案可选：

• 方案一：每天只留最新的一份，比如抽取最新的一份全量数据到Hive中。
• 方案二：每天保留一份全量的切片数据。
• 方案三：使用拉链表。

现在我们对前面提到的三种进行逐个的分析。

方案一

这种方案就不用多说了，实现起来很简单，每天drop掉前一天的数据，重新抽一份最新的。

优点很明显，节省空间，一些普通的使用也很方便，不用在选择表的时候加一个时间分区什么的。

缺点同样明显，没有历史数据，先翻翻旧账只能通过其它方式，比如从流水表里面抽。

方案二

每天一份全量的切片是一种比较稳妥的方案，而且历史数据也在。

缺点就是存储空间占用量太大太大了，如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费。

当然我们也可以做一些取舍，比如只保留近一个月的数据？但是，需求是无耻的，数据的生命周期不是我们能完全左右的。

此时可以考虑使用拉链表。首先它在空间上做了一个取舍，虽说不像方案一那样占用量那么小，但是它每日的增量可能只有方案二的千分之一甚至是万分之一。其次它能满足方案二所能满足的需求，既能获取最新的数据，也能添加筛选条件也获取历史的数据。

拉链表的设计和实现

有一张用户表

在2017-01-01这一天表中的数据是：

在2017-01-02这一天表中的数据是， 用户002和004资料进行了修改，005是新增用户：

在2017-01-03这一天表中的数据是， 用户004和005资料进行了修改，006是新增用户：

如果在数据仓库中设计成历史拉链表保存该表，则会有下面这样一张表，这是最新一天（即2017-01-03）的数据：

说明

• t_start_date表示该条记录的生命周期开始时间，t_end_date表示该条记录的生命周期结束时间。
• t_end_date = '9999-12-31'表示该条记录目前处于有效状态。
• 如果查询当前所有有效的记录，则select * from user where t_end_date = '9999-12-31'。
• 如果查询2017-01-02的历史快照，则select * from user where t_start_date <= '2017-01-02' and t_end_date >= '2017-01-02'。（此处要好好理解，是拉链表比较重要的一块）

HIVE实例

数据基本上都是每天全量同步，那么怎么来实现拉链表呢。

ods层的店铺资料表的结构如下（没有快照，只存一份，所以一旦查历史的快照很麻烦）：

CREATE 

创建拉链表

CREATE EXTERNAL TABLE IF NOT EXISTS db_business.ods_shp_zipper_target_dd
( 
id int comment '主键',
shop_code string comment '店铺code',
shop_name string comment '店铺名称',
date_create timestamp comment '创建时间',
date_update timestamp comment '更新时间',
date_delete timestamp comment '删除时间',
t_start string comment '该条记录的生命周期开始时间',
t_end string comment '该条记录的生命周期开始时间'
)
COMMENT '店铺的健康目标拉链表--tes'
ROW FORMAT DELIMITED
NULL DEFINED AS ""
STORED AS TEXTFILE
LOCATION 'hdfs://NameNodeHACluster/apps/hive/warehouse/db_business.db/ods_shp_zipper_target_dd';

INSERT OVERWRITE TABLE db_business.ods_shp_zipper_target_dd
select id,
shop_code,
shop_name,
date_create,
date_update,
date_delete,
'#yesterday_ds#'  AS t_start,
'9999-12-31' AS t_end
from db_business.ods_shp_target_dd

代码实现逻辑

1、查询拉链表中的数据在ods表中哪些被删除了，将所有删除的数据t_end时间改为当天。
2、拉链表去除删除的数据，在union已经将删除的数据t_end时间改为当天的数据。
3、在union ods表后distinct 变成一张新表。
4、对这张新表查询t_start is null就为每天有变化的数据，将t_end改为9999-12-31。
5、在判断哪些数据是新增的，哪些是更新的，将更新的数据 中的历史数据t_end改为当天。

流程图如下：

这个实现的逻辑是基于Hive表来实现的，相对来说比较繁琐，正常的业务逻辑可以通过mysql事务去更新这类数据。