知识点总结——数仓表一览

大数据私房菜 07-26 234

前言：

目前各位老铁们对“mysql快照表”大致比较重视，大家都想要学习一些“mysql快照表”的相关文章。那么小编在网上搜集了一些对于“mysql快照表””的相关资讯，希望你们能喜欢，兄弟们快快来了解一下吧！

全量表

每天的所有的最新状态的数据。

1、全量表，有无变化，都要报

2、每次上报的数据都是所有的数据（变化的 + 没有变化的）

2020-04-06 mysql对应的user_info表

date_id = ‘2020-04-06’ 数仓表

wedw_dwd.test_user_info_df(抽取user_info表所有数据)

2020-04-07 mysql对应的user_info

date_id = ‘2020-04-07’ 数仓表

wedw_dwd.test_user_info_df(抽取user_info表所有数据)

增量表

增量表：新增数据，增量数据是上次导出之后的新数据。

1、记录每次增加的量，而不是总量；

2、增量表，只报变化量，无变化不用报

3、每天一个分区

4、业务库表中需有主键及创建时间，修改时间

2020-04-06 mysql对应的user_info表

date_id = ‘2020-04-06’ 数仓表

wedw_dwd.test_user_info_di

(第一次初始化的时候抽取user_info表所有数据)

2020-04-07 mysql对应的user_info表

数仓表wedw_ods.test_user_info_20200407表的数据

date_id = ‘2020-04-07’ 数仓表

wedw_dwd.test_user_info_di的数据

注：因为数仓都是T+1的，所以7号的数据是8号加工出来的。

加工逻辑：

1、根据修改时间，把修改时间等于昨天(即7号)的数据抽取到ods层wedw_ods.test_user_info_20200407中

2、和数仓表wedw_dwd.test_user_info_di 分区date_id = ‘2020-04-06’通过主键用户id进行关联，先把wedw_dwd.test_user_info_d存在并且wedw_ods.test_user_info_20200407不存在的数据插入到wedw_dwd.test_user_info_di分区date_id=2020-04-07中

3、最后把wedw_ods.test_user_info_20200407表的所有数据插入到wedw_dwd.test_user_info_di分区date_id=2020-04-07

insert overwrite table wedw_dwd.test_user_info_di PARTITION(date_id='2020-04-07') select     a.user_id    ,a.user_name    ,a.user_age    ,a.user_cellphone    ,a.create_time    ,a.update_timefrom wedw_dwd.test_user_info_di a left join wedw_ods.test_user_info_20200407 b    on  b.user_id=a.user_idwhere a.date_id = '2020-04-06'    and  b.user_id is null;insert into table wedw_dwd.test_user_info_di PARTITION(date_id='2020-04-07')select      coalesce(user_id'-99')                                          as user_id    ,coalesce(user_name,'-99')                                       as user_name    ,coalesce(user_age,0)                                            as user_age    ,coalesce(user_cellphone,'-99')                                  as user_cellphone    ,coalesce(create_time,cast('1700-01-01 00:00:00' as timestamp )) as create_time    ,coalesce(update_time,cast('1700-01-01 00:00:00' as timestamp )) as update_timefrom wedw_ods.test_user_info_20200407 b ;--仅保留近7天的分区alter table wedw_dwd.test_user_info_di drop if EXISTS partition(date_id='2020-03-31');

特殊增量表：da表，每天的分区就是当天的数据，其数据特点就是数据产生后就不会发生变化，如日志表。

拉链表

维护历史状态，以及最新状态数据

适用情况：

1.数据量比较大

2.表中的部分字段会被更新

3.需要查看某一个时间点或者时间段的历史快照信息

查看某一个订单在历史某一个时间点的状态

某一个用户在过去某一段时间，下单次数

4.更新的比例和频率不是很大

如果表中信息变化不是很大，每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费

优点

1、满足反应数据的历史状态

2、最大程度节省存储

2020-04-06 mysql对应的user_info表

2020-04-07 mysql对应的user_info表

2020-04-08 mysql对应的user_info表

历史拉链表：分区date_id=’2020-04-08’的数仓表

wedw_dwd.test_user_info_dz

增加两个字段：

start_time(表示该条记录的生命周期开始时间——周期快照时的状态)

end_time(该条记录的生命周期结束时间)

end_time= ‘9999-12-31’ 表示该条记录目前处于有效状态

查询当前所有有效的记录:

select * from wedw_dwd.test_user_info_dz where date_id = ‘2020-04-08’ and end_time = ‘9999-12-31’;

查询2020-04-07的历史快照:

select * from  wedw_dwd.test_user_info_dz where date_id = ‘2020-04-08’ and start_time <= ‘2020-04-07’ and end_time >= ‘2020-04-07’;

注：第一次加工的时候需要初始化所有数据，start_time设置为数据日期2020-04-06 ，end_time设置为9999-12-31

--分桶set hive.enforce.bucketing=FALSE;--分区set hive.exec.dynamic.partition=FALSE;set hive.exec.dynamic.partition.mode=nostrick;set hive.exec.compress.output=true;set mapred.output.compress=true;set mapred.output.compression.type=BLOCK;set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;alter table wedw_dwd.test_user_info_dz drop if EXISTS PARTITION(date_id='${HIVE_DATA_DATE}');-- wedw_dwd.test_user_info_dz 存在, wedw_ods.test_user_info_${DATA_DATE} 不存在的 --或者都存在的闭链的 插入到 wedw_dwd.test_user_info_dz 下一个分区insert overwrite table wedw_dwd.test_user_info_dz PARTITION(date_id='${HIVE_DATA_DATE}')select      a.user_id    ,a.user_name    ,a.user_age    ,a.user_cellphone    ,a.create_time    ,a.update_time    ,a.start_time    ,a.end_timefrom wedw_dwd.test_user_info_dz a left join wedw_ods.test_user_info_${DATA_DATE} b on b.user_id=a.user_id and b.create_time < '${HIVE_DATA_DATE+1}'   where a.date_id = '${HIVE_DATA_DATE-1}' and (b.user_id is null       or (b.user_id is not null and a.end_time <='${HIVE_DATA_DATE-1}')  );-- 把wedw_dwd.test_user_info_dz, wedw_ods.test_user_info_${DATA_DATE} 都存在的开链的 全部闭链  插入到 wedw_dwd.test_user_info_dz 下一个分区insert into table wedw_dwd.test_user_info_dz PARTITION(date_id='${HIVE_DATA_DATE}')select      a.user_id    ,a.user_name    ,a.user_age    ,a.user_cellphone    ,a.create_time    ,a.update_time    ,a.start_time    ,'${HIVE_DATA_DATE-1}' end_timefrom wedw_dwd.test_user_info_dz a inner join wedw_ods.test_user_info_${DATA_DATE} b on b.user_id=a.user_id and b.create_time < '${HIVE_DATA_DATE+1}'where a.date_id = '${HIVE_DATA_DATE-1}'and a.end_time >'${HIVE_DATA_DATE-1}';-- 把wedw_ods.test_user_info_${DATA_DATE}, 插入到 wedw_dwd.test_user_info_dz 下一个分区insert into table wedw_dwd.test_user_info_dz PARTITION(date_id='${HIVE_DATA_DATE}')select     a.user_id    ,a.user_name    ,a.user_age    ,a.user_cellphone    ,a.create_time    ,a.update_time    ,'${HIVE_DATA_DATE}' start_time,    ,'9999-12-31' end_timefrom wedw_ods.test_user_info_${DATA_DATE} bwhere b.create_time < '${HIVE_DATA_DATE+1}';--仅保留近7天的数据alter table wedw_dwd.test_user_info_dz drop if EXISTS partition(date_id='${HIVE_DATA_DATE-7}');

流水表

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

快照表

每一天的数据都是截止到那一天mysql(数据源)的全量数据。

本文地址：http://www.longkongtuishu.com/cae0aBARsAVAFAVc.html

标签： #mysql快照表