数据仓库之极限存储实施

数据仓库之极限存储实施

一、前言

本文参考大数据之路--阿里巴巴大数据实践内容，针对数据仓库中拉链表的存储进行了设计与实践。在本公司实际处理拉链表过程中，采用的是每天分区全量存储历史所有数据，这样极大地增加的存储的成本和数据查询效率。针对此种情况，通过极限存储的方式进行优化，此种方式会增加两个时间戳字段（分区字段：开始时间分区：start_date和结束时间分区：end_date），将所有以天为粒度的变更数据都记录下来。

二、原累计表处理方式

2.1 原数仓表结构：

CREATE TABLE IF NOT EXISTS dw.dwd_usr_sens_device_user_info_test_d(
    device_id           string     COMMENT '设备ID'，

    status int COMMENT '状态'，

   create_time bigint COMMENT '创建时间'，

   update_time bigint COMMENT '更新时间'，

   其他字段...
) COMMENT '设备用户信息累计表（每天存储全量设备用户）'
PARTITIONED BY (stat_day string comment '日期分区');

2.2 数据示例：

device_id	status	create_time	update_time	stat_day
A001	1	1596277252	1596277252	20200801
A002	1	1596277282	1596277282	20200801
A001	2	1596367300	1596367300	20200802
A002	1	1596367404	1596367404	20200802
A003	1	1596367666	1596367666	20200802

说明：

20200801中，用户设备A001、A002访问了APP并进行了注册（status=1），即20200801分区生成t1、t2两条记录；20200802中，设备A001注销了账号，A003访问了APP并进行了注册，则20200802分区新增了A003记录，且A001记录status改成2，A002记录不变。

优劣势：

优势：

1. 处理逻辑简单，只需要全量处理即可。简单粗暴。

劣势：

1. 每个分区都是历史全量，存在大量的冗余数据，日积月累数据会越来越庞大，非常消耗存储资源。
2. 每个分区数据量太大导致查询效率降低。

三、优化后的数据拉链表处理方式

3.1 极限存储设计-数仓表结构：

CREATE TABLE IF NOT EXISTS dw.dwd_usr_sens_device_user_info_test_d(
    device_id           string     COMMENT '设备ID'，

    status int COMMENT '状态'，

   create_time bigint COMMENT '创建时间'，

   update_time bigint COMMENT '更新时间'，

   其他字段...
) COMMENT '设备用户信息拉链表'
PARTITIONED BY (start_date string comment '日期分区起',end_date string comment '日期分区止');

 3.2 数据示例

device_id	status	create_time	update_time	start_date	end_date
A001	1	1596277252	1596277252	20200801	20200802
A002	1	1596277282	1596277282	20200801	99991231
A001	2	1596367300	1596367300	20200802	99991231
A003	1	1596367666	1596367666	20200802	99991231

 说明：

采用历史拉链表形式，对于不变的数据，不再重复存储。这样只需要通过start_date<='20200801' and end_date>'20200801'即可访问20200801时间节点的历史状态数据。

优势：

1. 减少了数据存储成本。
2. 提高查询效率。

劣势：

1. 处理逻辑复杂，采用动态分区插入，hive设置分区数需要在100以上。
2. start_date和end_date查询会存在一定的理解障碍，增加解释成本。
3. start_date和end_date做分区，随着时间的推移，分区数量会极度膨胀，而现行数据库都有分区数量限制。

解决方案： 

1. 透明化：通过在上层做一个视图操作或者在Hive里做一个Hook，对于下游用户来说，极限存储表和全量存储方式是一样的。
2. 分月做历史拉链表：假设用start_date和end_date做分区，分月和不做限制处理产生的分区数如下。
   1. 不做限制在最坏的情况下，最多可产生(365*364)/2=66430个分区，中间文件数=目录数*reduce数，会产生无数的小文件，对计算性能影响大。
   2. 按月存储一年最多可能产生的分区数是：12*(1+(30+29)/2)=5232个。

四、总结

通过优化后数据，对优化前和优化的存储和查询效率进行数据比对。

优化前：

1、存储：采用snappy压缩存储，存储大大压缩了，没法比对。按无压缩用户量在5000千万，一天的分区大概在10GB左右，一年365*10 = 3650GB存储。

2、查询效率：在查询的时候，由于是压缩成snappy，所以在查询的时候解压缩会消耗一定的时间。

优化后：

1、存储：每个月月初第一天的数据总的累计用户，在当月其他天是当天的活跃数据及死亡数据。所以一个月月初的数据是10GB，但其他月大概累计200M。粗略的算了一下，10GB*12+200M*（365-12）≈ 160GB。存储减少了20倍。

2、查询效率：通过start_date和end_date组合的方式查询，效率在秒级。但由于优化前是压缩的数据，存在解压缩。没法比对。

五、注意

1. 跨月加工问题：T+1形式也就是每个月的2号执行脚本，将历史有效数据insert到当月月初，且end_date='99991231'；历史有效数据排除当日活跃数据，再union all当日活跃数据。这里有一点需要注意，月末的那个时间的变化无法通过两个分区查看，需要有业务字段说明。
2. 跨月修改上月分区问题：跨月加工的时候，需要将上个月的有效数据的end_date='99991231'改成当月月初。例如：start_date,end_date='99991231'=>start_date,end_date='20200801'。此处通过shell脚本执行hive语句实现：

   # 月初修改上个月月末的end_date时间为月初
   if [ '${dd}' -eq '01' ]; then
   	echo "月初批量修改上个月的end_date为当月月初"
       for stat_day in `seq '${yyyyMM&-1month}01' '${yyyyMMdd&-1d}'`
   	do
   	hive -e "alter table dw.dwd_usr_sens_device_user_info_test_d partition (start_date='${stat_day}',end_date='99991231') rename to partition (start_date='${stat_day}',end_date='${yyyyMMdd}');";
   	echo "修改表dw.dwd_usr_sens_device_user_info_test_d的分区$stat_day数据"
   	done
   fi

3. 分区数过大问题：

   set hive.exec.max.dynamic.partitions=1000;
   set hive.exec.max.dynamic.partitions.pernode=1000;

4. 查询视图问题：create view viewTable select * from tableName where start_date<='20200701' and end_date>'20200701'。但只能看到当天的视图，这个有点不好，也没想到好的方案