【数据仓库】Hive 拉链表实践

背景

        拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的；顾名思义，所谓拉链表，就是记录历史。记录一个事务从开始一直到当前状态的所有变化的信息。

        拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。

应用场景

        现假设有如下场景：一个企业拥有5000万会员信息，每天有20万会员资料变更，需要在数仓中记录会员表的历史变化以备分析使用，即每天都要保留一个快照供查询，反映历史数据的情况。

        在此场景中，需要反映5000万会员的历史变化，如果保留快照，存储两年就需要2X365X5000W条数据存储空间，数据量为365亿，如果存储更长时间，则无法估计需要的存储空间。而利用拉链算法存储，每日只向历史表中添加新增和变化的数据，每日不过20万条，存储4年也只需要3亿存储空间。

实现步骤

        在拉链表中，每一条数据都有一个生效日期(effective_date)和失效日期(expire_date)。假设在一个用户表中，在2019年11月8日新增了两个用户，如下表所示，则这两条记录的生效时间为当天，由于到2019年11月8日为止,这两条就还没有被修改过，所以失效时间为一个给定的比较大的值，比如：3000-12-31  

member_idphonenocreate_timeupdate_time
10001133000000012019-11-083000-12-31
10002135000000022019-11-083000-12-31

        第二天(2019-11-09)，用户10001被删除了，用户10002的电话号码被修改成13600000002.为了保留历史状态，用户10001的失效时间被修改为2019-11-09，用户10002则变成了两条记录，如下表所示： 

member_idphonenocreate_timeupdate_time
10001133000000012019-11-082019-11-09
10002135000000022019-11-082019-11-09
10002136000000022019-11-093000-12-31

        第三天(2019-11-10),又新增了用户10003，则用户表数据如小表所示： 

member_idphonenocreate_timeupdate_time
10001133000000012019-11-082019-11-09
10002135000000022019-11-082019-11-09
10002136000000022019-11-093000-12-31
10003133000000062019-11-103000-12-31

        如果要查询最新的数据，那么只要查询失效时间为3000-12-31的数据即可，如果要查11月8号的历史数据，则筛选生效时间<= 2019-11-08并且失效时间>2019-11-08的数据即可。如果查询11月9号的数据，那么筛选条件则是生效时间<=2019-11-09并且失效时间>2019-11-09

表结构

  • MySQL源member表

CREATE TABLE member(
            member_id VARCHAR ( 64 ),
            phoneno VARCHAR ( 20 ),
            create_time datetime,
            update_time datetime );

  • ODS层增量表member_delta,每天一个分区

CREATE TABLE member_delta
          (member_id string,
            phoneno string,
            create_time string,
            update_time string)
PARTITIONED BY (DAY string);

  • 临时表

CREATE TABLE member_his_tmp
          (member_id string,
            phoneno string,
            effective_date date,
            expire_date date
            );

  • DW层历史拉链表

CREATE TABLE member_his
          (member_id string,
            phoneno string,
            effective_date date,
            expire_date date);

Demo数据准备

2019-11-08的数据为： 

member_idphonenocreate_timeupdate_time
10001135000000012019-11-08 14:47:552019-11-08 14:47:55
10002135000000022019-11-08 14:48:332019-11-08 14:48:33
10003135000000032019-11-08 14:48:532019-11-08 14:48:53
10004135000000042019-11-08 14:49:022019-11-08 14:49:02

2019-11-09的数据为：其中蓝色代表新增数据，红色代表修改的数据

member_idphonenocreate_timeupdate_time
10001135000000012019-11-08 14:47:552019-11-08 14:47:55
10002136000000022019-11-08 14:48:332019-11-09 14:48:33
10003135000000032019-11-08 14:48:532019-11-08 14:48:53
10004135000000042019-11-08 14:49:022019-11-08 14:49:02
10005135000000052019-11-09 08:54:032019-11-09 08:54:03
10006135000000062019-11-09 09:54:252019-11-09 09:54:25

2019-11-10的数据：其中蓝色代表新增数据，红色代表修改的数据  

member_idphonenocreate_timeupdate_time
10001135000000012019-11-08 14:47:552019-11-08 14:47:55
10002136000000022019-11-08 14:48:332019-11-09 14:48:33
10003135000000032019-11-08 14:48:532019-11-08 14:48:53
10004136000000042019-11-08 14:49:022019-11-10 14:49:02
10005135000000052019-11-09 08:54:032019-11-09 08:54:03
10006135000000062019-11-09 09:54:252019-11-09 09:54:25
10007135000000072019-11-10 17:41:492019-11-10 17:41:49

全量初始装载

        在启用拉链表时，先对其进行初始装载，比如以2019-11-08为开始时间，那么将MySQL源表全量抽取到ODS层member_delta表的2018-11-08的分区中，然后初始装载DW层的拉链表member_his

INSERT overwrite TABLE member_his
SELECT
  member_id,
  phoneno,
  to_date ( create_time ) AS effective_date,
 '3000-12-31'
FROM
member_delta
WHERE
DAY = '2019-11-08'

        查询初始的历史拉链表数据

图片

增量抽取数据

        每天，从源系统member表中，将前一天的增量数据抽取到ODS层的增量数据表member_delta对应的分区中。这里的增量需要通过member表中的创建时间和修改时间来确定，或者使用sqoop job监控update时间来进行增联抽取。比如，本案例中2019-11-09和2019-11-10为两个分区，分别存储了2019-11-09和2019-11-10日的增量数据。2019-11-09分区的数据为:

图片

        2019-11-10分区的数据为：

图片

增量刷新历史拉链数据

  • 2019-11-09增量刷新历史拉链表将数据放进临时表

INSERT overwrite TABLE member_his_tmp
SELECT *
FROM
  (
-- 2019-11-09增量数据，代表最新的状态，该数据的生效时间是2019-11-09，过期时间为3000-12-31
-- 这些增量的数据需要被全部加载到历史拉链表中
SELECT member_id,
       phoneno,
       '2019-11-09' effective_date,
                    '3000-12-31' expire_date
   FROM member_delta
   WHERE DAY='2019-11-09'
   UNION ALL 
-- 用当前为生效状态的拉链数据，去left join 增量数据，
-- 如果匹配得上，则表示该数据已发生了更新，
-- 此时，需要将发生更新的数据的过期时间更改为当前时间.
-- 如果匹配不上，则表明该数据没有发生更新，此时过期时间不变
SELECT a.member_id,
       a.phoneno,
       a.effective_date,
       if(b.member_id IS NULL, to_date(a.expire_date), to_date(b.day)) expire_date
   FROM
     (SELECT *
      FROM member_his
      WHERE expire_date='3000-12-31') a
   LEFT JOIN
     (SELECT *
      FROM member_delta
      WHERE DAY='2019-11-09') b ON a.member_id=b.member_id)his

        将数据覆盖到历史拉链表

INSERT overwrite TABLE member_his
SELECT *
FROM member_his_tmp

        查看历史拉链表

图片

  • 2019-11-10增量刷新历史拉链表

                将数据放进临时表

INSERT overwrite TABLE member_his_tmp
SELECT *
FROM
(
-- 2019-11-10增量数据，代表最新的状态，该数据的生效时间是2019-11-10，过期时间为3000-12-31
-- 这些增量的数据需要被全部加载到历史拉链表中
SELECT member_id,
      phoneno,
      '2019-11-10' effective_date,
                   '3000-12-31' expire_date
  FROM member_delta
  WHERE DAY='2019-11-10'
  UNION ALL
-- 用当前为生效状态的拉链数据，去left join 增量数据，
-- 如果匹配得上，则表示该数据已发生了更新，
-- 此时，需要将发生更新的数据的过期时间更改为当前时间.
-- 如果匹配不上，则表明该数据没有发生更新，此时过期时间不变
SELECT a.member_id,
      a.phoneno,
      a.effective_date,
      if(b.member_id IS NULL, to_date(a.expire_date), to_date(b.day)) expire_date
  FROM
    (SELECT *
    FROM member_his
    WHERE expire_date='3000-12-31') a
  LEFT JOIN
    (SELECT *
    FROM member_delta
    WHERE DAY='2019-11-10') b ON a.member_id=b.member_id)his

查看历史拉链表

图片

将以上脚本封装成shell调度的脚本

#!/bin/bash

#如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="

INSERT overwrite TABLE member_his_tmp
SELECT *
FROM
  (
-- 2019-11-10增量数据，代表最新的状态，该数据的生效时间是2019-11-10，过期时间为3000-12-31
-- 这些增量的数据需要被全部加载到历史拉链表中
SELECT member_id,
       phoneno,
       '$do_date' effective_date,
       '3000-12-31' expire_date
   FROM member_delta
   WHERE DAY='$do_date'
   UNION ALL
-- 用当前为生效状态的拉链数据，去left join 增量数据，
-- 如果匹配得上，则表示该数据已发生了更新，
-- 此时，需要将发生更新的数据的过期时间更改为当前时间.
-- 如果匹配不上，则表明该数据没有发生更新，此时过期时间不变
SELECT a.member_id,
       a.phoneno,
       a.effective_date,
       if(b.member_id IS NULL, to_date(a.expire_date), to_date(b.day)) expire_date
   FROM
     (SELECT *
      FROM member_his
      WHERE expire_date='3000-12-31') a
   LEFT JOIN
     (SELECT *
      FROM member_delta
      WHERE DAY='$do_date') b ON a.member_id=b.member_id)his;
"

$hive -e "$sql"

如需获取更多资料，您可以下载知识星球app，并搜索加入‘数据要素X’。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/909494.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

UnityAssetsBundle字体优化解决方案

UnityAssetsBundle字体优化解决方案

Unity开发某个项目&#xff0c;打包后的apk包体已经高达1.25G了&#xff0c;这是非常离谱的。为了不影响用户体验&#xff0c;需要将apk包体缩小。因为项目本身不包含很多模型以及其他大型资源&#xff0c;排除法将AB包删除&#xff0c;发现app本身就100多M。 由此可以锁定是AB…
阅读更多...
高通Quick板上安装编译Ros1 noetic,LeGO_LOAM,FAR_Planner和rslidar_sdk

高通Quick板上安装编译Ros1 noetic,LeGO_LOAM,FAR_Planner和rslidar_sdk

环境要求&#xff1a; 这里quick板上安装的是Ubuntu20.04版本 Ros Noeti安装&#xff1a; 1.设置软件源&#xff1a; 官方提供的软件源&#xff1a; sudo sh -c echo "deb http://packages.ros.org/ros/ubuntu $(lsb_release -sc) main" > /etc/apt/sources.list.…
阅读更多...
Qt多边形填充/不填充绘制

Qt多边形填充/不填充绘制

1 填充多边形绘制形式 void GraphicsPolygonItem::paint(QPainter *painter, const QStyleOptionGraphicsItem *option, QWidget *widget) {Q_UNUSED(option);Q_UNUSED(widget);//painter->setPen(pen()); // 设置默认画笔//painter->setBrush(brush()); // 设置默…
阅读更多...
零基础学习Spring AI Java AI使用向量数据库postgresql 检索增强生成 RAG

零基础学习Spring AI Java AI使用向量数据库postgresql 检索增强生成 RAG

零基础学习Spring AI Java AI使用向量数据库postgresql 检索增强生成 RAG 向量数据库是一种特殊类型的数据库&#xff0c;在人工智能应用中发挥着至关重要的作用。 在向量数据库中&#xff0c;查询与传统的关系数据库不同。它们不是进行精确匹配&#xff0c;而是执行相似性搜…
阅读更多...
如何在 uniapp 中实现图形验证码

如何在 uniapp 中实现图形验证码

全篇大概2000 字&#xff08;含代码&#xff09;&#xff0c;建议阅读时间10分钟。 什么是图形验证码&#xff1f; 图形验证码&#xff08;也称为图片验证码或验证码图像&#xff09;通常用于防止机器人自动提交表单&#xff0c;确保用户是人工操作。 一、需求 我们希望在一个…
阅读更多...
mysql error：1449权限问题 及 用户授权

mysql error：1449权限问题 及 用户授权

一、权限问题 Got error: 1449: The user specified as a definer (skip-grants userskip-grants host) does not exist when using LOCK TABLES 在迁移数据库时&#xff0c;定义的definer&#xff0c;在两个数据库之间不同步时&#xff0c;要将不存在的definer改成数据库中已…
阅读更多...
uniapp+vue加油服务系统 微信小程序

uniapp+vue加油服务系统 微信小程序

文章目录 项目介绍具体实现截图技术介绍mvc设计模式小程序框架以及目录结构介绍错误处理和异常处理java类核心代码部分展示详细视频演示源码获取 项目介绍 基于微信小程序的加油服务系统设计为微信小程序和后台管理两个服务端&#xff0c;并对此设计相应的功能模块如下&#x…
阅读更多...
【MFC编程（一）】MFC概述

【MFC编程（一）】MFC概述

文章目录 MFC概述MFC组成MFC对比Windows APIMFC类库基类CObject命令发送类CCmdTarget应用程序结构类应用程序线程支持类CWinThread/CWinApp文档类CDocument文档模板类CDocTemplate 窗口类窗口基类CWnd边框窗口类CFrameWnd视图类CView MFC概述 MFC&#xff08;Microsoft Founda…
阅读更多...
如何解决传统能源企业后备人才不足、人才规划缺失问题

如何解决传统能源企业后备人才不足、人才规划缺失问题

如何解决传统能源企业后备人才不足、人才规划缺失问题 很多传统能源企业都面临着老员工逐渐退休&#xff0c;新员工还没有培养起来的问题&#xff0c;缺乏提前对人力资源规划的意识&#xff0c;导致当企业要开展新业务时或者老员工离职的时候&#xff0c;缺乏合适的人选。特别…
阅读更多...
服装品牌零售业态融合中的创新发展：以开源 AI 智能名片 S2B2C 商城小程序为视角

服装品牌零售业态融合中的创新发展：以开源 AI 智能名片 S2B2C 商城小程序为视角

摘要&#xff1a;本文以服装品牌零售业态融合为背景&#xff0c;探讨信息流优化和资金流创新的重要作用&#xff0c;并结合开源 AI 智能名片 S2B2C 商城小程序&#xff0c;分析其如何进一步推动服装品牌在零售领域的发展&#xff0c;提高运营效率和用户体验&#xff0c;实现商业…
阅读更多...
C#编程：VSTO在Excel工作表中输出List数据

C#编程：VSTO在Excel工作表中输出List数据

标题 C#编程&#xff1a;VSTO在Excel工作表中输出List数据 正文 一、问题&#xff1a; 我想把C#中的List<T>输出到Excel工作表中 二、思路方法&#xff1a; &#xff08;1&#xff09;用程序创建一个List<T> &#xff08;2&#xff09;输出到当前工作表 三、代码&a…
阅读更多...
【算法】递归+深搜：106.从中序与后序遍历序列构造二叉树（medium）

【算法】递归+深搜：106.从中序与后序遍历序列构造二叉树（medium）

目录 1、题目链接 相似题目&#xff1a; 2、题目 3、解法 函数头-----找出重复子问题 函数体---解决子问题 4、代码 1、题目链接 106.从中序与后序遍历序列构造二叉树&#xff08;LeetCode&#xff09; 相似题目&#xff1a; 105.从前序与中序遍历序列构造二叉树 889.根…
阅读更多...
【Postman深入测试接口的详细指南】保姆级

【Postman深入测试接口的详细指南】保姆级

Postman深入测试接口的详细操作步骤 一、创建测试集合二、使用环境变量三、编写请求四、编写测试脚本五、数据驱动测试六、模拟请求&#xff08;Mocking&#xff09;1. 创建Mock Server2. 定义响应3. 使用Mock Server进行请求 七、API监控1. 创建监控2. 运行监控 一、创建测试集…
阅读更多...
Memento 备忘录模式

Memento 备忘录模式

备忘录模式 意图结构适用性实例Java Web开发中的简单示例Originator 类Memento 类Caretaker 类 文本编辑器示例1. Originator (发起人) - TextEditor2. Memento (备忘录) - TextMemento3. Caretaker (负责人) - History4. 使用示例输出 备忘录模式&#xff08;Memento Pattern&…
阅读更多...
HTMLCSS：3D 旋转卡片的炫酷动画

HTMLCSS：3D 旋转卡片的炫酷动画

效果演示 这段代码是一个HTML和CSS的组合&#xff0c;用于创建一个具有3D效果的动画卡片。 HTML <div class"obj"><div class"objchild"><span class"inn6"><h3 class"text">我是谁&#xff1f;我在那<…
阅读更多...
为什么越来越多人开始用云电脑？网友道出了真相

为什么越来越多人开始用云电脑？网友道出了真相

近期&#xff0c;3A游戏大作《黑神话&#xff1a;悟空》的横空出世&#xff0c;成功激起大多数人对国产游戏的兴趣。然而&#xff0c;没有一台高配置的电脑&#xff0c;就无法在《黑神话&#xff1a;悟空》中获得震撼的游戏体验。想要配齐处理器、显卡、内存等硬件&#xff0c;…
阅读更多...
https服务器访问http资源报Mixed Content混合内容错误

https服务器访问http资源报Mixed Content混合内容错误

1 报错内容 Mixed Content: The page at ‘https://xxx’ was loaded over HTTPS, but requested an insecure XMLHttpRequest endpoint ‘http://xxx’. This request has been blocked; the content must be served over HTTPS. 2 报错原因 页面通过 HTTPS 加载&#xff…
阅读更多...
vue3项目中实现el-table分批渲染表格

vue3项目中实现el-table分批渲染表格

开篇 因最近工作中遇到了无分页情景下页面因大数据量卡顿的问题&#xff0c;在分别考虑并尝试了懒加载、虚拟滚动、分批渲染等各个方法后&#xff0c;最后决定使用分批渲染来解决该问题。 代码实现 表格代码 <el-table :data"currTableData"borderstyle"wi…
阅读更多...
多模态PaliGemma——Google推出的基于SigLIP和Gemma的视觉语言模型

多模态PaliGemma——Google推出的基于SigLIP和Gemma的视觉语言模型

前言 本文怎么来的呢&#xff1f;其实很简单&#xff0c;源于上一篇文章《π0——用于通用机器人控制的流匹配VLA模型&#xff1a;一套框架控制7种机械臂(改造了PaliGemma和ACT的3B模型)》中的π0用到了PaliGemma 故本文便来解读下这个PaliGemma 第一部分 PaliGemma 1.1 Pal…
阅读更多...
基于vue框架的的楼盘销售管理系统6n60a（程序+源码+数据库+调试部署+开发环境）系统界面在最后面。

基于vue框架的的楼盘销售管理系统6n60a（程序+源码+数据库+调试部署+开发环境）系统界面在最后面。

系统程序文件列表 用户,房源类型,员工,房源信息,购房预订,购房合同 开题报告内容 基于Vue框架的楼盘销售管理系统开题报告 一、研究背景 随着房地产市场的蓬勃发展&#xff0c;楼盘销售行业的竞争日益激烈。传统的销售管理方式依赖于人工记录和纸质文档&#xff0c;效率低下…
阅读更多...
最新文章

Copyright @ 2022~2023