使用Hive实现时间拉链功能

最新推荐文章于 2023-03-07 19:16:59 发布
最新推荐文章于 2023-03-07 19:16:59 发布
阅读量448 收藏 2
点赞数
分类专栏： hive 文章标签： hive

背景：

在数据仓库的数据模型设计过程中，经常会遇到如下的业务需求：
1. 表的数据量很大，大几千万或上亿;
2. 表中的部分字段会被update更新操作，如用户的上级领导，产品的描述信息，订单的状态等等;
3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态;
4. 变化的比例和频率不是很大，比如，总共有8000万的用户，每天新增和发生变化的有30万左右;
5. 如果对这边表每天都保留一份全量，那么每次全量中会保存很多不变的信息，对存储是极大的浪费;
6. 时间拉链历史表，既能满足反应数据的历史状态，又可以最大程度的节省存储空间。


一. 演示的数据


2015-08-21 以及之前的订单表数据：

1|2015-08-18|2015-08-18|创建

2|2015-08-18|2015-08-18|创建

3|2015-08-19|2015-08-21|支付

4|2015-08-19|2015-08-21|完成

5|2015-08-19|2015-08-20|支付

6|2015-08-20|2015-08-20|创建

7|2015-08-20|2015-08-21|支付

8|2015-08-21|2015-08-21|创建


2015-08-22 订单表数据：

1|2015-08-18|2015-08-22|支付

2|2015-08-18|2015-08-22|完成

6|2015-08-20|2015-08-22|支付

8|2015-08-21|2015-08-22|支付

9|2015-08-22|2015-08-22|创建

10|2015-08-22|2015-08-22|支付


2015-08-23 订单表数据：

1|2015-08-18|2015-08-23|完成

3|2015-08-19|2015-08-23|完成

5|2015-08-19|2015-08-23|完成

8|2015-08-21|2015-08-23|完成

11|2015-08-23|2015-08-23|创建

12|2015-08-23|2015-08-23|创建

13|2015-08-23|2015-08-23|支付


将上面所有的数据全部保存到如下文件中：

/home/Hadoop/hivetestdata/Time_zipper/orders.txt


二. 表结构

源系统中订单表结构：

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. use timezipper;  
  2. create table orders (  
  3.     orderid int,  
  4.     createtime string,  
  5.     modifiedtime string,  
  6.     status string  
  7. ) row format delimited fields terminated by '|';  
  8.   
  9. load data local inpath "file:///home/hadoop/hivetestdata/Time_zipper/orders.txt" into table orders;  

在数据仓库的ODS层，有一张订单的增量数据表，按天分区，存放每天的增量数据：

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. create table t_ods_orders_inc (  
  2.     orderid int,  
  3.     createtime string,  
  4.     modifiedtime string,  
  5.     status string  
  6. ) partitioned by (day string) row format delimited fields terminated by '|';  

在数据仓库的DW层，有一张订单的历史数据拉链表，存放订单的历史状态数据：

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. create table t_dw_orders_his (  
  2.     orderid int,  
  3.     createtime string,  
  4.     modifiedtime string,  
  5.     status string,  
  6.     dw_start_date string,  
  7.     dw_end_date string  
  8. );  


三. 全量数据初始化

在数据从源业务系统每天正常抽取和刷新到DW订单历史表之前，

需要做一次全量的初始化，就是从源订单表中指定某一天以前的数据全部抽取到ODW，并刷新到DW。


以上面的数据为例，比如在2015-08-22这天做全量初始化，那么我需要将2015-08-21以及之前的所有的数据都抽取并刷新到DW，步骤如下：

第一步：抽取全量数据到ODS：

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. use timezipper;  
  2. INSERT overwrite TABLE t_ods_orders_inc PARTITION (day='2015-08-21')  
  3. SELECT orderid,createtime,modifiedtime,status  
  4. FROM orders  
  5. WHERE modifiedtime <= '2015-08-21';  

第二步：从ODS刷新到DW：

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. use timezipper;  
  2. INSERT overwrite TABLE t_dw_orders_his  
  3. SELECT orderid,createtime,modifiedtime,status,  
  4. createtime AS dw_start_date,  
  5. '9999-12-31' AS dw_end_date  
  6. FROM t_ods_orders_inc  
  7. WHERE day = '2015-08-21';  

四. 增量抽取历史数据并计算生成时间拉链结果表

从2015-08-23开始，需要每天正常刷新前一天（2015-08-22）的增量数据到历史表，步骤如下：

第一步：通过增量抽取，将2015-08-22的数据抽取到ODS：

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. INSERT overwrite TABLE t_ods_orders_inc PARTITION (day = '2015-08-22')  
  2. SELECT orderid,createtime,modifiedtime,status  
  3. FROM orders  
  4. WHERE modifiedtime = '2015-08-22';  


第二步：通过DW历史数据(数据日期为2015-08-21(包含2015-08-21以及之前的数据))，和ODS增量数据(2015-08-22)，刷新历史表：

先把数据放到一张临时表中：

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. drop table if exists t_dw_orders_his_tmp;  
  2. create table t_dw_orders_his_tmp as   
  3. select  oo.orderid,  
  4.        oo.createtime,  
  5.        oo.modifiedtime,  
  6.        oo.status,  
  7.        oo.dw_start_date,  
  8.        oo.dw_end_date  
  9. from (select   
  10.        tt.orderid,  
  11.        tt.createtime,  
  12.        tt.modifiedtime,  
  13.        tt.status,  
  14.        tt.dw_start_date,  
  15.        tt.dw_end_date,  
  16.        row_number() over(distribute by tt.orderid,tt.createtime,tt.modifiedtime,status 
  17. sort by tt.dw_end_date desc) rn  
  18.     from (  
  19.          select a.orderid,  
  20.                 a.createtime,  
  21.                 a.modifiedtime,  
  22.                 a.status,  
  23.                 a.dw_start_date,  
  24.                 -- 2015-08-21保存的都是历史数据  
  25.                 case when b.orderid is not null and a.dw_end_date > '2015-08-22' then '2015-08-21'   
  26.                      else a.dw_end_date end as dw_end_date   
  27.                 from t_dw_orders_his a   
  28.                 left outer join (select * from t_ods_orders_inc where day = '2015-08-22') b   
  29.                 on (a.orderid = b.orderid)   
  30.          
  31.         union all   
  32.        
  33.         select orderid,  
  34.                createtime,  
  35.                modifiedtime,  
  36.                status,  
  37.                modifiedtime as dw_start_date,  
  38.                '9999-12-31' as dw_end_date   
  39.           from t_ods_orders_inc   
  40.         where day = '2015-08-22') tt   
  41. ) oo where oo.rn =1;  

说明：

UNION ALL的两个结果集中，

第一个是用历史表left outer join 日期为 ${yyy-MM-dd} 的增量表，能关联上的，并且dw_end_date > ${yyy-MM-dd}，说明状态有变化，则把原来的dw_end_date置为(${yyy-MM-dd} – 1), 

关联不上的，说明状态无变化，dw_end_date无变化。

第二个结果集是直接将增量数据插入历史表，并将dw_end_date设为9999-12-31​。


最后把临时表中数据插入历史表：

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. INSERT overwrite TABLE t_dw_orders_his  
  2. SELECT * FROM t_dw_orders_his_tmp;  

五. 同上面的步骤一样，增量抽取历史数据并计算生成时间拉链结果表

再看将2015-08-23的增量数据刷新到历史表：

[sql]  view plain  copy
  在CODE上查看代码片 派生到我的代码片
  1. insert overwrite table t_ods_orders_inc partition (day = '2015-08-23')   
  2. select orderid,createtime,modifiedtime,status   
  3. from orders   
  4. where modifiedtime = '2015-08-23';  
  5.    
  6. drop table if exists t_dw_orders_his_tmp;  
  7. create table t_dw_orders_his_tmp as   
  8. select  oo.orderid,  
  9.        oo.createtime,  
  10.        oo.modifiedtime,  
  11.        oo.status,  
  12.        oo.dw_start_date,  
  13.        oo.dw_end_date  
  14. from (select   
  15.        tt.orderid,  
  16.        tt.createtime,  
  17.        tt.modifiedtime,  
  18.        tt.status,  
  19.        tt.dw_start_date,  
  20.        tt.dw_end_date,  
  21.        row_number() over(distribute by tt.orderid,tt.createtime,tt.modifiedtime,status 
  22. sort by tt.dw_end_date desc) rn  
  23. from (  
  24.      select a.orderid,  
  25.             a.createtime,  
  26.             a.modifiedtime,  
  27.             a.status,  
  28.             a.dw_start_date,  
  29.             -- 2015-08-20保存的都是历史数据  
  30.             case when b.orderid is not null and a.dw_end_date > '2015-08-23' then '2015-08-22'   
  31.                  else a.dw_end_date end as dw_end_date   
  32.             from t_dw_orders_his a   
  33.             left outer join (select * from t_ods_orders_inc where day = '2015-08-23') b   
  34.             on (a.orderid = b.orderid)   
  35.      
  36.     union all   
  37.    
  38.     select orderid,  
  39.            createtime,  
  40.            modifiedtime,  
  41.            status,  
  42.            modifiedtime as dw_start_date,  
  43.            '9999-12-31' as dw_end_date   
  44.       from t_ods_orders_inc   
  45.     where day = '2015-08-23') tt   
  46. ) oo where oo.rn =1;  
  47.    
  48.    
  49. insert overwrite table t_dw_orders_his   
  50. select * from t_dw_orders_his_tmp;  

六. 查看上面步骤生成的时间拉链结果表

按照上面的方法刷新完后，生成的时间拉链的历史表数据如下：

1 2015-08-18 2015-08-18 创建 2015-08-182015-08-21
1 2015-08-18 2015-08-22 支付 2015-08-222015-08-22
1 2015-08-18 2015-08-23 完成 2015-08-239999-12-31
2 2015-08-18 2015-08-18 创建 2015-08-182015-08-21
2 2015-08-18 2015-08-22 完成 2015-08-229999-12-31
3 2015-08-19 2015-08-21 支付 2015-08-192015-08-22
3 2015-08-19 2015-08-23 完成 2015-08-239999-12-31
4 2015-08-19 2015-08-21 完成 2015-08-199999-12-31
5 2015-08-19 2015-08-20 支付 2015-08-192015-08-22
5 2015-08-19 2015-08-23 完成 2015-08-239999-12-31
6 2015-08-20 2015-08-20 创建 2015-08-202015-08-21
6 2015-08-20 2015-08-22 支付 2015-08-229999-12-31
7 2015-08-20 2015-08-21 支付 2015-08-209999-12-31
8 2015-08-21 2015-08-21 创建 2015-08-21 2015-08-21
8 2015-08-21 2015-08-22 支付 2015-08-22 2015-08-22
8 2015-08-21 2015-08-23 完成 2015-08-23 9999-12-31
9 2015-08-22 2015-08-22 创建 2015-08-229999-12-31
10 2015-08-22 2015-08-22 支付 2015-08-229999-12-31
11 2015-08-23 2015-08-23 创建 2015-08-239999-12-31
12 2015-08-23 2015-08-23 创建 2015-08-239999-12-31
13 2015-08-23 2015-08-23 支付 2015-08-239999-12-31


比如我们查看订单8, 可以发现订单8从2015-08-21-2015-08-23号，状态变化了三次(创建->支付->完成)，因此历史表中有三条记录。

xiaoL_clo
关注
专栏目录
【大数据hive】hive 拉链表设计与实现
congge_study的博客
06-19 7590
hive 拉链表设计与实现
大数据运维存档（8）数仓构建与Hive实现常识整理、生产中调优
Big_data_implicit_
12-27 4696
目录 一、数仓基础 1、数仓基本介绍 1.1、数仓基本概念 1.2、数仓的定义 1.2.1、面向主题 1.2.2、集成性 1.2.3、稳定性 1.2.4、时变性 1.3、数据仓库与数据库的区别 1.4、构建数仓常用手段 1.5、数仓分层 1.5.1、数仓分层描述 1.5.2、为什么要进行数仓分层 1.6、获取数据 1.7、同步策略 2、数据仓库建模 2.1、范式建模法（Third Normal Form 3NF） 2.2、维度建模法 2.2.1、维度表 2.2.2、事
数据仓库01- 时间拉链的全量备份
wankao的博客
10-26 492
时间拉链的全量备份 mysql中演示 在mysql演示时间拉链的全量备份： 一、演示第一次及第二次全量备份。 1、首先存在业务系统表。 //进入test数据库。 use test //创建业务表user_data，并赋予最初的业务数据。 create table user_data( userid int, ustate varchar(4) ); insert into user_data values(1,'1'); insert into user_data values(2,'2'); insert
时间拉链介绍
weixin_40179437的博客
01-02 1130
https://blog.csdn.net/baidu_21088863/article/details/77802758
[ Hive ] 函数应用 : 多字节分隔符.url,json解析.拉链表
jason_bone_的博客
04-07 443
1 多字节分隔符 应用场景: 情况一:每一行数据的分隔符是多字节分隔符，例如：”||”、“–”等 情况二:数据的字段中包含了分隔符 解决方案: 方案一:在ETL阶段通过一个MapReduce程序，将数据中的“||”替换为单字节的分隔符“|” 该方式实现较为简单,只需替换字符串即可,但,无法满足情况二的需求 方案二: 1)正则加载数据 建表时使用指定SERDE类: ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.RegexSerDe’ 指定正则表达式 W
时间拉链【数据仓库】
互联网金融爱好者
02-25 1973
表结构设计之拉链表 (一)概念 拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。 在历史表中对客户的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题： （NAME）人名  （START-DATE）开始日期  （END-DT）结束日期  （STAT）状态 client
Hive数据仓库之全量表、增量表、快照表、切片表和拉链表
weixin_43230682的博客
07-03 7540
待整理实现！ https://blog.csdn.net/mtj66/article/details/78019370?
第5章：Hive函数重要应用案例1
08-08
拉链表用于合并具有时间序列特性的数据，Hive中可以通过自连接或其他方法实现。 综上，本章将详细介绍如何在Hive中应对各种复杂的分隔符问题，以及如何利用不同函数和工具处理特殊数据格式，以满足实际业务需求。
Hive+Spark离线数仓工业项目实战--数仓设计及数据采集（1）
技术专家
12-23 2008
Hive+Spark离线数仓工业项目实战--数仓设计及数据采集
hive窗口函数在拉链表上的运用案例
m0_46507516的博客
10-11 808
hive开窗函数
mysql 时间拉链表_拉链表 - 夜半钟声到客船 - 博客园
weixin_39810196的博客
02-07 964
1.为什么要做拉链表拉链表适用于同步新增和变化的数据！对于周期型事实表，采用同步新增和变化的方式！使用场景：1)比如订单状态：三天内该订单状态发生了变化，但是同一个事物内进行的2)人的出行轨迹2.拉链表在hive中使用以同步order_info为例以下为Mysql中数据的记录形式2020-02-232020-02-24如果该mysql的表是每天全量同步，如果不采用拉链表，存在在一张hive的表中...
关于拉链表
lysvanilla的专栏
08-03 758
(一)概念      拉链表是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。         在历史表中对客户的一生的记录可能就这样几条记录，避免了按每一天记录客户状态造成的海量存储的问题：（NAME）人名 （START-DATE）开始日期 （END-DT）结束日期 （STAT）状态     client   ...
mysql 时间拉链表_03-数据仓库之拉链表
weixin_39646018的博客
02-07 667
1、拉链表：①记录每条信息的生命周期为单位②一旦一条记录的生命周期结束，就重新开始一条新的记录，并把当前日期作为此记录的生效日期③如果当前信息至今有效，在生效结束日期中填入一个极大值(如9999-12-31、9999-99-99)用处：①需要查看某些业务信息的某一个时间点当日的信息②数据会发生变化，但是大部分是不变的。(无法做每日增量)③数据量有一定的规模，无法按照每日全量的方法保存 。(无法做每...
oracle 时间拉链代码,Hive实现时间拉链功能
weixin_39885383的博客
04-03 209
背景：在数据仓库的数据模型设计过程中，经常会遇到如下的业务需求：1. 表的数据量很大，大几千万或上亿;2. 表中的部分字段会被update更新操作，如用户的上级领导，产品的描述信息，订单的状态等等;3. 需要查看某一个时间点或者时间段的历史快照信息，比如，查看某一个订单在历史某一个时间点的状态;4. 变化的比例和频率不是很大，比如，总共有8000万的用户，每天新增和发生变化的有30万左右;5. 如...
oracle 时间拉链代码,数据仓库拉链算法在ORACLE中的实现
weixin_31083901的博客
04-03 292
1、拉链算法用到的相关表结构建表脚本(源表、目的表、临时表).sql--生成目标表create table EDW_T100_STATUS_H(ID VARCHAR2(8) not null,STATUS VARCHAR2(8) not null,START_DATE DATE not null,END_DATE DATE not null);--生成源表create ...
hive拉链工具
dingyanming的博客
07-10 2009
一款自动化hive拉链的工具！
Hive 拉链表实践
weixin_47209187的博客
04-22 334
背景 拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的；顾名思义，所谓拉链表，就是记录历史。记录一个事务从开始一直到当前状态的所有变化的信息。 拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。 应用场景 现假设有如下场景：一个企业拥有5000万会员信息，每天有20万会员资料变更，需要在数仓中记录会员表的历史变化以备分析...
Hive---拉链表
大呱的博客
03-07 2526
拉链表
hive 拉链表实现
最新发布
08-31
在Hive中，可以通过以下步骤来实现拉链表： 1. 创建维度表和事实表：首先，创建维度表和事实表的Hive表。维度表用于存储维度字段的详细信息，例如员工表、产品表等；事实表用于存储与维度表关联的度量数据，例如...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值