首页 > 其他分享 >大数据-246 离线数仓 - 电商分析 拉链表的分析与构建与回滚

大数据-246 离线数仓 - 电商分析 拉链表的分析与构建与回滚

时间：2024-12-10 10:01:18浏览次数：6  
标签：数仓 06 userid 离线 test 2020 date end 电商

点一下关注吧！！！非常感谢！！持续更新！！！

Java篇开始了！

目前开始更新 MyBatis，一起深入浅出！

目前已经更新到了：

  • Hadoop（已更完）
  • HDFS（已更完）
  • MapReduce（已更完）
  • Hive（已更完）
  • Flume（已更完）
  • Sqoop（已更完）
  • Zookeeper（已更完）
  • HBase（已更完）
  • Redis （已更完）
  • Kafka（已更完）
  • Spark（已更完）
  • Flink（已更完）
  • ClickHouse（已更完）
  • Kudu（已更完）
  • Druid（已更完）
  • Kylin（已更完）
  • Elasticsearch（已更完）
  • DataX（已更完）
  • Tez（已更完）
  • 数据挖掘（已更完）
  • Prometheus（已更完）
  • Grafana（已更完）
  • 离线数仓（正在更新…）

章节内容

上节我们完成了如下的内容：

  • 电商分析 缓慢变化维
  • 拉链表 SCD Slowly Changing Dimensions

在这里插入图片描述

拉链表的实现

userinfo（分区表） => userid、mobile、regdate => 每日变更的数据（修改的+新增的）/ 历史数据（第一天）
userhis（拉链表）=> 多个两个字段 start_date / end_date

拉链表（Zipper Table）

拉链表是一种数据库设计模式，用于跟踪数据随时间的变化，同时保持高效的查询性能。这种模式广泛应用于数据仓库和数据分析场景，因为它能够很好地记录历史数据的变化情况。

拉链表的基本概念

拉链表的核心思想是将每条记录的有效时间范围存储起来，通过“拉链”方式记录版本变化。每一条记录都包含以下关键信息：

  • 开始时间（Start Date/Effective Date）：表示这条记录的生效时间。
  • 结束时间（End Date/Expiration Date）：表示这条记录的失效时间。
  • 是否当前有效（Is Current）：表示这条记录是否为最新版本（通常通过标志位存储，如1表示当前记录，0表示历史记录）。

工作原理

  • 新增数据：当有新数据插入时，系统创建一条新记录，设置其开始时间为当前时间，结束时间为一个默认的最大时间（如9999-12-31），同时将is_current字段设为1。
  • 更新数据：首先将现有的有效记录的结束时间更新为当前时间，表示它的有效期结束，同时将is_current标志设为0。
  • 然后插入一条新的记录，表示更新后的版本，开始时间为当前时间，结束时间为默认最大时间，is_current标志为1。
  • 删除数据：一般通过逻辑删除方式（更新结束时间和is_current字段）实现，而不是直接物理删除。

准备数据

这里的数据刚才已经全部都写入进去了

-- 1、userinfo初始化（2020-06-20）。获取历史数据
001,13551111111,2020-03-01,2020-06-20
002,13561111111,2020-04-01,2020-06-20
003,13571111111,2020-05-01,2020-06-20
004,13581111111,2020-06-01,2020-06-20

初始化拉链表

将2020-06-20的数据写入到表中

 -- 2、初始化拉链表（2020-06-20）。userinfo => userhis
INSERT OVERWRITE TABLE test.userhis
SELECT 
    userid, 
    mobile, 
    regdate, 
    dt AS start_date, 
    '9999-12-31' AS end_date
FROM 
    test.userinfo
WHERE 
    dt = '2020-06-20';

执行结果如下所示：

在这里插入图片描述

继续准备数据

这批数据也已经写入了：

-- 3、次日新增数据（2020-06-21）；获取新增数据
002,13562222222,2020-04-01,2020-06-21
004,13582222222,2020-06-01,2020-06-21
005,13552222222,2020-06-21,2020-06-21

构建拉链表

-- 4、构建拉链表(userhis)（2020-06-21）【核心】 userinfo(2020-06-21) + userhis => userhis
-- userinfo: 新增数据
-- userhis：历史数据
-- 第一步：处理新增数据【userinfo】（处理逻辑与加载历史数据类似）
SELECT 
    userid, 
    mobile, 
    regdate, 
    dt AS start_date, 
    '9999-12-31' AS end_date
FROM 
    test.userinfo
WHERE 
    dt = '2020-06-21';

-- 第二步：处理历史数据【userhis】（历史包括两部分：变化的、未变化的）
-- 变化的：start_date:不变；end_date：传入日期-1
-- 未变化的：不做处理

-- 观察数据
SELECT 
    A.userid, 
    B.userid, 
    B.mobile, 
    B.regdate, 
    B.start_date, 
    B.end_date
FROM 
    (SELECT * 
     FROM test.userinfo 
     WHERE dt = '2020-06-21') A
RIGHT JOIN 
    test.userhis B 
ON 
    A.userid = B.userid;

-- 编写SQL，处理历史数据
SELECT 
    B.userid,
    B.mobile,
    B.regdate,
    B.start_Date,
    CASE 
        WHEN B.end_date = '9999-12-31' AND A.userid IS NOT NULL 
        THEN DATE_ADD('2020-06-21', INTERVAL -1 DAY)
        ELSE B.end_date
    END AS end_date
FROM 
    (SELECT * FROM test.userinfo WHERE dt = '2020-06-21') A
RIGHT JOIN 
    test.userhis B
ON 
    A.userid = B.userid;

-- 最终的处理（新增+历史数据）
INSERT OVERWRITE TABLE test.userhis
SELECT 
    userid, 
    mobile, 
    regdate, 
    dt AS start_date, 
    '9999-12-31' AS end_date
FROM 
    test.userinfo
WHERE 
    dt = '2020-06-21'
UNION ALL
SELECT 
    B.userid,
    B.mobile,
    B.regdate,
    B.start_date,
    CASE 
        WHEN B.end_date = '9999-12-31' AND A.userid IS NOT NULL 
        THEN date_add('2020-06-21', -1) 
        ELSE B.end_date 
    END AS end_date
FROM 
    (SELECT * FROM test.userinfo WHERE dt = '2020-06-21') A
RIGHT JOIN 
    test.userhis B
ON 
    A.userid = B.userid;

执行过程如下图所示：
在这里插入图片描述

拉链表测试脚本

vim test_zipper.sh

写入的内容如下所示：

#!/bin/bash
# 加载环境变量
source /etc/profile

# 判断是否传入日期参数，如果没有则使用前一天的日期
if [ -n "$1" ]; then
    do_date=$1
else
    do_date=$(date -d "-1 day" +%F)
fi

# SQL 语句
sql="
INSERT OVERWRITE TABLE test.userhis
SELECT
    userid,
    mobile,
    regdate,
    dt AS start_date,
    '9999-12-31' AS end_date
FROM
    test.userinfo
WHERE
    dt = '$do_date'
UNION ALL
SELECT
    B.userid,
    B.mobile,
    B.regdate,
    B.start_date,
    CASE
        WHEN B.end_date = '9999-12-31' AND A.userid IS NOT NULL THEN date_add('$do_date', -1)
        ELSE B.end_date
    END AS end_date
FROM
    (SELECT * FROM test.userinfo WHERE dt = '$do_date') A
RIGHT JOIN
    test.userhis B
ON
    A.userid = B.userid;
"

# 执行 Hive SQL
hive -e "$sql"

拉链表的回滚

由于种种原因需要将拉链表恢复到rollback_date那一天的数据，此时有：

  • end_date < rollback_date，即结束日期<回滚日期，表示该行数据在roll_back_date之前产生，这些数据需要原样保留
  • start_date <= rollback_date <= end_date，即开始日期 <= 回滚日期 <= 结束日期，这些数据是回滚日期之后产生的，但是需要修改，将end_date改为9999-12-31
  • 其他数据不用管

在这里插入图片描述
按照上述方案进行编码：
处理end_date < rollback_date 的数据

SELECT 
    userid, 
    mobile, 
    regdate, 
    start_date, 
    end_date, 
    '1' AS tag
FROM 
    test.userhis
WHERE 
    end_date < '2020-06-22';

处理start_date <= rollback_date <= end_date 的数据，设置 end_date=9999-12-31

SELECT 
    userid, 
    mobile, 
    regdate, 
    start_date, 
    '9999-12-31' AS end_date, 
    '2' AS tag
FROM 
    test.userhis
WHERE 
    start_date <= '2020-06-22' 
    AND end_date >= '2020-06-22';

将前面两步的数据写入临时表tmp（拉链表）

-- 删除临时表
DROP TABLE IF EXISTS test.tmp;

-- 创建临时表
CREATE TABLE test.tmp AS
SELECT 
    userid, 
    mobile, 
    regdate, 
    start_date, 
    end_date, 
    '1' AS tag
FROM 
    test.userhis
WHERE 
    end_date < '2020-06-22'

UNION ALL

SELECT 
    userid, 
    mobile, 
    regdate, 
    start_date, 
    '9999-12-31' AS end_date, 
    '2' AS tag
FROM 
    test.userhis
WHERE 
    start_date <= '2020-06-22' 
    AND end_date >= '2020-06-22';

-- 查询结果并按照 userid 和 start_date 进行聚集
SELECT * 
FROM test.tmp 
CLUSTER BY userid, start_date;

模拟脚本：

zippertmp.sh

写入的内容如下所示：

#!/bin/bash

# 加载环境变量
source /etc/profile

# 判断是否传递日期参数，如果没有则使用前一天的日期
if [ -n "$1" ]; then
  do_date=$1
else
  do_date=$(date -d "-1 day" +%F)
fi

# 定义SQL查询语句
sql="
  DROP TABLE IF EXISTS test.tmp;

  CREATE TABLE test.tmp AS
  SELECT userid, mobile, regdate, start_date, end_date, '1' AS tag
  FROM test.userhis
  WHERE end_date < '${do_date}'
  
  UNION ALL
  
  SELECT userid, mobile, regdate, start_date, '9999-12-31' AS end_date, '2' AS tag
  FROM test.userhis
  WHERE start_date <= '${do_date}' 
  AND end_date >= '${do_date}';
"

# 执行Hive查询
hive -e "$sql"

逐天回滚，检查数据

标签：数仓,06,userid,离线,test,2020,date,end,电商
From： https://blog.csdn.net/w776341482/article/details/144362921

相关文章

  • 跨境电商年终：抓住最后的节假日营销流量！
    黑五网一已经告一段落，但圣诞节和新年即将到来。电商卖家要根据节假日活动继续调整营销策略，因为还有不少消费者存在节假日和相关活动的消费需求。而且，面向不同的国家和地区的市场时，还要把当地节日考虑在内，这也增加了营销机会和流量。要抓住一年中最后的节假日流量，可以参考以下......
  • 跨境电商年终：抓住最后的节假日营销流量！
    黑五网一已经告一段落，但圣诞节和新年即将到来。电商卖家要根据节假日活动继续调整营销策略，因为还有不少消费者存在节假日和相关活动的消费需求。而且，面向不同的国家和地区的市场时，还要把当地节日考虑在内，这也增加了营销机会和流量。要抓住一年中最后的节假日流量，可以参考以下......
  • 基于Hadoop的电商用户行为分析
    一、课题背景及研究意义1.1课题背景随着电子商务的迅猛发展，电商平台积累了大量的用户数据，包括用户的浏览、搜索、购买等行为。这些数据蕴含着极为丰富的市场洞察，能够为电商企业提供有关用户偏好、消费趋势、营销策略等方面的关键信息。如何从海量的数据中提取有价值的信息，......
  • 国标GB28181网页直播平台LiteGBS国标GB28181软件关于设备离线如何处理？
    GB28181协议作为国家标准，规定了公共安全视频监控联网系统的互联结构、传输、交换、控制的基本要求和安全性要求。这有助于实现不同设备和系统之间的互联互通和信息共享，提高公共安全视频监控的效率和质量。LiteGBS国标GB28181软件平台，作为视频监控领域的核心管理系统，其稳定性和可......
  • 软件测试|电商类项目业务测试点汇总
    前言在数字化浪潮的推动下，电商项目已成为商业领域的重要支柱。从用户下单、支付到物流配送，每一个环节都关乎着用户的满意度和企业的生死存亡。在这样的背景下，电商项目的业务测试显得尤为重要。下面给大家介绍一下电商项目业务相关的测试点。01登陆功能测试功能测试：①输入......
  • 1分钟搞定电商产品背景图，小白福音，StartAI让你瞬间变高手！
    电商界的小伙伴们，注意啦！ 想要让你的产品图在众多竞争对手中脱颖而出？StartAI的背景移除功能与文生图Controlnet功能强强联手，助你轻松打造专业级、吸引眼球的电商产品图！插件功能优势： 一键背景移除，聚焦产品核心 ——使用StartAI的背景移除功能，繁琐的抠图工作瞬间变得简单。......
  • 大数据-245 离线数仓 - 电商分析 缓慢变化维 与 拉链表 SCD Slowly Changing Dimensio
    点一下关注吧！！！非常感谢！！持续更新！！！Java篇开始了！目前开始更新MyBatis，一起深入浅出！目前已经更新到了：Hadoop（已更完）HDFS（已更完）MapReduce（已更完）Hive（已更完）Flume（已更完）Sqoop（已更完）Zookeeper（已更完）HBase（已更完）Redis（已更完）Kafka（已更完）Spark（已更完）Flink（已更完）ClickHouse（已更完）Kudu（......
  • 电商行业团队协作，这 6 款软件到底有多神奇？
    在电商行业中，高效的团队协作至关重要。全J人电商行业团队以其严谨的规划和高效的执行力而著称，而合适的可视化团队协作办公软件则能够进一步提升团队的工作效率和协同能力。以下将为大家盘点六款适用于电商团队的协作软件，其中包括板栗看板以及五款国外小众冷门软件，着重介绍板栗看......
  • Qt/C++离线读取全国任意经纬度高程海拔值/无任何依赖/纯原创代码解析
    一、前言说明做地图开发会遇到一个常规需求，就是获取当前经纬度对应的海拔高度，也叫做高程值，很遗憾各大地图厂商都未提供接口获取，可能是有明文规定，不能地图中提供对应的海拔高度值，于是需要另想他法，尽管谷歌地图在线的api接口是提供了海拔高度值，但是懂得都懂，国内哪里还能用谷歌地图？......
  • winserver离线部署Python服务（vitualenv）
    python项目开发完成后，经常遇到服务器无法访问互联网，所以导致部署时无法在线安装依赖包，本篇记录了离线部署python环境的步骤。1、下载Python安装包前往Python官网下载地址下载对应版本的Python安装包，如python-3.9.12-amd64.exe。 2、在项目中创建requirements.txt，记录依赖名称......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99