HIVE拉链表

已于 2023-03-08 10:17:20 修改
阅读量334 收藏
点赞数
分类专栏： Hadoop Hive 文章标签： hive 数据库 hadoop
于 2023-03-06 16:40:21 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_72036027/article/details/129363091
版权

文章目录

拉链表概念

拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。

拉链表实现过程

1、采集当日全量数据到ND(NowDay当日)表；

2、可从历史表中取出昨日全量数据存储到OD(OldDay上日)表；

3、两个表进行全字段比较，(ND-OD)就是当日新增和变化的数据，也就是当天的增量，用W_I表示；

4、两个表进行全字段比较，(OD-ND)为状态到此结束需要封链的数据，需要修改END_DATE，用W_U表示；

5、将W_I表的内容全部插入到历史表中，这些是新增记录，start_date为当天，而end_date为max值，可以设为’9999-12-31‘；

6、对历史表进行W_U部份的更新操作，start_date保持不变，而end_date改为当天，也就是关链操作，历史表（OD）和W_U表比较，START_DATE，END_DATE除外，以W_U表为准，两者交集将其END_DATE改成当日，说明该记录失效。

拉链表举例说明

3月3日当天三条订单记录

订单ID创建时间状态开始时间订单状态
12023-03-032023-03-03创建
22023-03-032023-03-03创建
32023-03-032023-03-03创建

3月4日当天3条订单记录

订单ID创建时间状态开始时间订单状态
12023-03-032023-03-04支付
22023-03-032023-03-04支付
42023-03-042023-03-04创建

3月5日当天3条订单记录

订单ID创建时间状态开始时间订单状态
12023-03-032023-03-05完成
32023-03-032023-03-05支付
42023-03-042023-03-05支付
52023-03-052023-03-05创建

将订单记录文件上传到HDFS文件目录
创建对应数据库和原始ODS表

create database lalian;
use lalian;
create external table  orders(
    orderid int,
    createdate string,
    modifiedtime string,
    status string
)
row format delimited fields terminated by '\t'
location '/tmp/lalian/orders';

创建按天分区的ODS表

create  table  ods_orders_inc(
    orderid int,
    createdate string,
    modifiedtime string,
    status string
)partitioned by (day string)
row format delimited fields terminated by '\t';

按分区插入数据

//3月3号数据
insert overwrite table ods_orders_inc partition (day='2023-03-03')
select orderid,createdate,modifiedtime,status from orders;
//3月4号数据
insert overwrite table ods_orders_inc partition (day='2023-03-04')
select orderid,createdate,modifiedtime,status from orders
where modifiedtime='2023-03-04';
//3月号数据
insert overwrite table  ods_orders_inc partition (day='2023-03-05')
select orderid,createdate,modifiedtime,status from orders
where modifiedtime='2023-03-05' or (createdate='2023-03-05' and modifiedtime='2023-03-05');

创建dws拉链表

create table dws_orders_his(
    orderid int,
    createdate string,
    modifiedtime string,
    status string,
    start_time string,
    end_time string
)
row format delimited fields terminated by '\t';

将每日数据进行处理后插入

//处理并插入3月3日数据
insert overwrite table dws_orders_his
select orderid,createdate,modifiedtime,status,modifiedtime,'9999-12-31'
from ods_orders_inc where day='2023-03-03';

3月3日当天拉链表

订单ID创建时间状态开始时间订单状态开始时间结束时间
12023-03-032023-03-03创建2023-03-039999-12-31
22023-03-032023-03-03创建2023-03-039999-12-31
32023-03-032023-03-03创建2023-03-039999-12-31

根据3月4日数据更新当前DWS表状态

insert overwrite table dws_orders_his
select
t1.orderid,
       createdate,
       modifiedtime ,
       status,
       start_time,
       case when t2.orderid is not null and end_time>'2023-03-04'then '2023-03-04' else end_time end end_time
from dws_orders_his t1 left join
    (select orderid from ods_orders_inc where day='2023-03-04')t2
on t1.orderid=t2.orderid
union all
select orderid,createdate,modifiedtime,status,modifiedtime as start_time,'9999-12-31' as end_time from ods_orders_inc
where day='2023-03-04';

3月4日当天拉链表

订单ID创建时间状态开始时间订单状态开始时间结束时间
12023-03-032023-03-03创建2023-03-032023-03-04
12023-03-032023-03-04支付2023-03-049999-12-31
22023-03-032023-03-03创建2023-03-032023-03-04
22023-03-032023-03-04支付2023-03-049999-12-31
32023-03-032023-03-03创建2023-03-039999-12-31
42023-03-042023-03-04创建2023-03-049999-12-31

根据3月5日数据更新当前DWS表状态

insert overwrite table dws_orders_his
select tb1.* from (
                  (select orderid,
                          createdate,
                          modifiedtime,
                          status,
                          modifiedtime as start_time,
                          '9999-12-31' as end_time
                   from ods_orders_inc
                   where day = '2023-03-05')
                  union all
                  (select t1.orderid,
                          createdate,
                          t1.modifiedtime,
                          status,
                          t1.start_time,
                          if(t2.orderid is not null and t1.end_time > '2023-03-05', '2023-03-05', t1.end_time) end_time
                   from dws_orders_his t1
                            left join (select orderid, modifiedtime
                                       from ods_orders_inc
                                       where day = '2023-03-05') t2 on t1.orderid = t2.orderid)
              )tb1 order by tb1.orderid,tb1.modifiedtime;

3月5日当天拉链表

订单ID创建时间状态开始时间订单状态开始时间结束时间
12023-03-032023-03-03创建2023-03-032023-03-04
12023-03-032023-03-04支付2023-03-042023-03-05
12023-03-032023-03-05完成2023-03-059999-12-31
22023-03-032023-03-03创建2023-03-032023-03-04
22023-03-032023-03-04支付2023-03-049999-12-31
32023-03-032023-03-03创建2023-03-032023-03-05
32023-03-032023-03-05支付2023-03-059999-12-31
42023-03-042023-03-04创建2023-03-042023-03-05
42023-03-042023-03-05支付2023-03-059999-12-31
52023-03-052023-03-05创建2023-03-059999-12-31

依次更新每日拉链信息保证拉链表的更新

在这里插入图片描述

拉链表补充

  1. 使用拉链表的时候可以不加t_end_date，即失效日期，但是加上之后，能优化很多查询。
  2. 可以加上当前行状态标识，能快速定位到当前状态。
  3. 在拉链表的设计中可以加一些内容，因为我们每天保存一个状态，如果我们在这个状态里面加一个字段，比如如当天修改次数，那么拉链表的作用就会更大。
Topson23
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
hive拉链表实现的四种方式_拉链表实现方式
2401_84182073的博客
04-11 860
-------------------------执行ods层执行包含生命周期表的文件sql。--------------------------执行ods层执行包含时间戳表的文件sql。------------------创建表和插入测试数据。
hive 表结构导出
05-19
支持hive 源数据、表结构导出
拉链表的详细实现过程（好文点赞收藏！！）
热门推荐
CLKTOY的博客
09-24 1万+
目录1-什么是拉链表2-为什么要做拉链表2.1-如何使用拉链表3-拉链表的形成过程4-拉链表的制作过程4.1-初始化拉链表（首次独立执行）4.2-制作当日变动数据（包括新增，修改）每日执行4.3-先合并变动信息，再追加新增信息，插入到临时表中4.4-把临时表覆盖给拉链表5-总结 1-什么是拉链表 本文以订单拉链表为例进行详细讲解。 2-为什么要做拉链表 2.1-如何使用拉链表 3-拉链表的形成过程 4-拉链表的制作过程 4.1-初始化拉链表（首次独立执行） 先生成10条原始订单数据（测试数
大数据Hive拉链表的设计与实现
赵广陆
09-19 6774
7.1 数据同步问题 7.1.1 数据同步的场景 Hive在实际工作中主要用于构建离线数据仓库，定期的从各种数据源中同步采集数据到Hive中，经过分层转换提供数据应用。例如，每天需要从MySQL中同步最新的订单信息、用户信息、店铺信息等到数据仓库中，进行订单分析、用户分析。 例如：MySQL中有一张用户表：tb_user，每个用户注册完成以后，就会在用户表中新增该用户的信息，记录该用户的id、手机号码、用户名、性别、地址等信息。 每天都会有用户注册，产生新的用户信息，我们每天都需要将MySQL中的用户
拉链表的实现过程
一个技术迷的博客
11-19 9331
拉链表的优势我就不说了，具体请参考百度百科： 拉链表-百度百科 推荐一个比较详细的参考文章： 拉链表示例 主要总结一下实现过程： 分析：拉链表就是用来存储变化的数据的，每一份数据都有对应的有效期，我们需要进行的操作就是将变动的数据进行新增，同时将变动对应的前一条数据的有效期进行变更。 说明：一般都是今天处理昨天的数据，本文所说的当天为所处理的数据的产生的当天。 在这之前需要熟悉一下需要用到的表： 表1：订单表（记录原始的数据） 表2：增量数据表（记录每日变更的数据） 表3：历史拉链表（我们要得到的就是这张表
数仓--拉链表（原理、设计以及在Hive中的实现）
qq_46893497的博客
12-06 1万+
什么是拉链表？ 所谓拉链，就是记录历史 记录一个事物从开始，一直到当前状态的所有变化的信息。 我们可以使用这张表拿到最新的当天的最新数据以及之前的历史数据。 谈到拉链表就不得不谈SCD(缓慢变化维问题) 一些维度表的数据不是静态的,而是会随时间缓慢变化(相对事实表变化速度) 如果,用户修改用户数据,出生年月/地址/… 缓慢变化维怎么解决?(粗看有五种) 保留初始值(不让改) 用户体验不好 改写属性值 用户需要历史数据怎么办 我要分析历史变化数据怎么办 增加维度新行 引出了拉链表 增加维度新列
拉链表的制作过程
zzf135的博客
05-21 4872
**背景：**对于一些维度表，数据量特别大，每天又会有新增或者修改的数据，但是这部分数据总数据量的比重不大。如果做成每日全量数据，会导致大量的重复数据，占用存储资源；如果做成全量最新，则会缺少历史数据。针对这种情况，可以考虑使用拉链表。 **设计思路：**每行数据加上两个字段，开始时间，结束时间。初始值，开始时间设置为当天，结束时间设为9999-99-99；新增数据，开始时间设置为当天，结束时间设为9999-99-99；修改数据，原数据的开始时间不变，结束时间改为修改当天，修改的数据开始时间设置为修改当天，
详细记录拉链表的实现过程
atwdy的博客
12-28 2437
1）直接更新，也就是用业务库中发生变化的数据覆写数仓中的数据，这种实现方式可以保持数仓与业务库数据保持一致，缺点是只能保存当前最新的数据，对这条数据修改之前的历史数据不做维护，一般很少采用这种实现方式，除非历史数据本身就是错误的，没有使用价值。此时这张拉链表就是只包含最新的记录信息，而上面那张拉链表中包含的既可能有最新的信息，也可能有历史的记录信息，此时我们需要把原始的那张拉链表中的。后续对拉链表的更新都是只同步每天发生更新的数据，然后把这部分数据做成一个新的拉链表，再修改原始拉链表中的有效期。
Hive---拉链表
大呱的博客
03-07 2313
拉链表
hive 拉链表
qq_43192537的博客
08-04 2134
hive 拉链表
hive之拉链表实现过程及剖析
luo981695830的博客
11-09 3214
1、创建拉链表 create external if exists dwd_user_info( `id` string COMMENT '用户id', `name` string COMMENT '姓名', `start_date` string COMMENT '开始日期', `end_date` string COMMENT '结束日期' )COMMENT '用户信息拉链表' 2、初始化拉链表(导入2020-11-08数据) insert overwrite tabl...
Hive的分区表
01-20
Hive分区表 Hive分区表对应的是HDFS上独立的文件夹，该文件夹存放的是该分区的所有数据，其实分区就是分目录。Hive通过分区把一个大的数据集根据业务需要分割成小的数据集，在查询时能够通过where关键词选择指定分区...
Hive分区表
01-07
分区表就是根据指定字段的值进行分类、分组，字段值相同的分为一类然后存储在一个单独的HDFS文件中，多个类就存储在多个文件中。原本存储在一个文件中的数据现在存储在多个文件中，查询数据时只需要知道数据在哪个...
Hive内部表和外部表
01-07
删除表时(drop table)内部表会删除hdfs对应路径，而外部表不会删除hdfs对应的路径, 删除表无论是内部表和外部表都会删除元数据(metastore.TBLS、metastore.COLUMNS_V2) 二：location关键字 用于指定hdfs路径，如果不...
HIVE 自建落地表
01-07
自建落地表为什么要自建落地表HIVE SQL创建内/外部表、分区表insert overwrite 向分区表插入数据 为什么要自建落地表 1、公司的大数据资源非常紧张， 2、导数逻辑很复杂； 3、日常导数要求时效性高； 4、部门数仓的...
Hive 拉链表实现方式(2种)
u014727826的博客
04-19 661
我们首先要知道，拉链表是一个逻辑上的概念。拉链表记录的是增量数据，它通过不断的同步增量数据来构成，不断进行数据清洗。拉链表有数据的开始日期和结束日期，记录着数据的生命周期。
拉链表（hive）
weixin_35995069的博客
10-14 4818
目录 1、什么是拉链表 2、拉链表是怎么存储的 3、Hive中拉链表的存储过程 1、什么是拉链表 拉链表是我们存储数据时为了处理某些情况而设计的一种表结构，是一种存储数据的方法。 在数据仓库的数据模型设计过程中，经常会遇到下面这种表的设计： 1.有一些表的数据量很大，比如一张用户表，大约10亿条记录，50个字段，这种表，即使使用ORC压缩，单张表的存储也会超过100G，...
拉链表实现过程
the_dog_so_happy的博客
06-27 2351
详细整理了一份拉链表的具体操作。
拉链表实现及使用
se7en.shi的专栏
11-13 9074
一、概念 历史拉链表，就是记录一个事务从开始一直到当前状态的所有变化的信息，拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据的一种常见方式。 假设企业拥有1000万的会员信息，每天有20万的会员资料变更，我们需要记录所有会议的历史变化记录，并至少保留两年，该怎么办？ 储存两年就是2x365x1000万=7300000000(70亿)，如果储存...
hive拉链表hql实现
最新发布
10-11
Hive拉链表（又称历史快照表）是一种数据模型，在数据更新时会保留历史版本，以便查询不同时间点的数据。Hive支持使用HQL语句实现拉链表。 下面是一个创建拉链表的HQL示例： ``` CREATE TABLE employee_history ( employee_id INT, name STRING, salary INT, start_date DATE, end_date DATE, version INT ) CLUSTERED BY (employee_id) INTO 4 BUCKETS STORED AS ORC; ``` 在这个示例中，我们创建了一个名为employee_history的表，它包含了每个员工的历史记录，包括工资、开始日期和结束日期。每次更新时，我们会将新的记录插入到表中，并将旧的记录的结束日期设为当前日期。这样，我们就可以在任何时间点查询员工的历史工资信息。 下面是一个插入新记录的HQL示例： ``` INSERT INTO employee_history SELECT employee_id, name, salary, start_date, current_date(), version + 1 FROM employee WHERE employee_id = 123; ``` 这个示例中，我们从employee表中选出特定员工的当前记录，并将其插入到employee_history表中。我们还增加了一个version字段，以便在查询历史记录时进行排序。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值