数仓(九)从0到1简单搭建加载数仓DWD层(业务数据解析)

2023-10-09 00:08

本文主要是介绍数仓(九)从0到1简单搭建加载数仓DWD层(业务数据解析),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

数仓(一)简介数仓,OLTP和OLAP

数仓(二)关系建模和维度建模

数仓(三)简析阿里、美团、网易、恒丰银行、马蜂窝5家数仓分层架构

数仓(四)数据仓库分层

数仓 (五) 元数据管理系统解析

数仓(六)从0到1简单搭建数仓ODS层(埋点日志 + 业务数据)

数仓(七)从0到1简单搭建加载数仓DIM层以及拉链表处理

数仓(八)从0到1简单搭建加载数仓DWD层(用户行为日志数据解析)

上一节我们讲解了数仓DWD层(用户行为日志数据)的搭建、解析、加载。并且讲解了通过编写java代码来实现UDTF功能。

这节详解数仓DWD层(关于用户交易等业务数据)的搭建、解析加载。

一、DWD层结构

前面一节已经说过了,DWD层是对用户的日志行为进行解析,以及对交易业务数据采用维度模型的方式重新建模(即维度退化)。

1、回顾DWD层概念

我们在来回顾一下数仓(四)数据仓库分层对DWD层(Data Warehouse Detail)的定义:“明细粒度事实层:是以业务过程来作为建模驱动,基于每个具体的业务过程特点,构建最细粒度的明细层事实表(注意是最细粒度)。需要结合企业的数据使用特点,将明细事实表的某些重要维度属性字段做适当冗余,即宽表化处理。明细粒度事实层的表通常也被称为逻辑事实表。”

2、DWD层建模4步骤

DWD层是事实建模层,这层建模主要做的4个步骤:

4bf2c6ea8d2a16014e1c4fc343d8dede.png

我们目前已经完成了:

2.1、选择业务过程

选择了事实表,比如:订单事实表、支付事实表等;

2.2、声明粒度

即确认每一行数据是什么,要保证事实表的最小粒度。

2.3、确认维度

在前面两节中我们确定了6个维度;比如时间、用户、地点、商品、优惠券、活动这6个维度。数仓(十二)从0到1简单搭建加载数仓DIM层以及拉链表处理思路是其他ODS层表的维度需要向这6个维度进行退化到DIM层,这样做的母的是减少后期的大量表之间的join操作。

9b096bddd96aac516cd417636dd1a492.png

6个维度表的退化操作其实我们在前面的第十二章节已经做了即DIM层。除了第3张表即商品维度表是5个表退化到1张表上,其他都是1-2张表退化到1张表上,相对比较简单。

2.4、确认事实

就是确认事实表的每张事实表的度量值。

76c430bcbd239a735274656a4b2015b6.png

下面我们根据事实表的加载方式来选择几个实战操作一下。

二、DWD层-事务型事实表

关于事实表分类,我们在数仓(三)关系建模和维度建模,里面说过,分为6类事实表。

1、事务型事实表的概念

适用于不会发生变化的业务。业务表的同步策略是增量同步。以每个事务或事件为单位,例如一个销售订单记录,一笔支付记录等,作为事实表里的一行数据。一旦事务被提交,事实表数据被插入,数据就不再进行更改,其更新方式为增量更新。

8张表里面包含:支付事实表、评价事实表、退款事实表、订单明细(详情)事实表

2、解析思路

根据事实表(行),选择不同的维度(列)来建表。

811e2912c71004c0dfc63f85f7adfadd.png

3、支付事实表(事务型事实表)

需要时间、用户、地区三个维度,查看ODS层表ods_payment_info,发现没有地区维度字段。所以通过ods_order_info表关联做join获取该字段。

3.1、建表语句

drop table if exists dwd_fact_payment_info;
create external table dwd_fact_payment_info (`id` string COMMENT 'id',`out_trade_no` string COMMENT '对外业务编号',`order_id` string COMMENT '订单编号',`user_id` string COMMENT '用户编号',`alipay_trade_no` string COMMENT '支付宝交易流水编号',`payment_amount`    decimal(16,2) COMMENT '支付金额',`subject`         string COMMENT '交易内容',`payment_type` string COMMENT '支付类型',`payment_time` string COMMENT '支付时间',`province_id` string COMMENT '省份ID'
) COMMENT '支付事实表表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_payment_info/'
tblproperties ("parquet.compression"="lzo");

3.2、装载语句

province_id省份ID这个字段通过 ods_order_info表做join获取

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_payment_info partition(dt='2021-05-03')
selectpi.id,pi.out_trade_no,pi.order_id,pi.user_id,pi.alipay_trade_no,pi.total_amount,pi.subject,pi.payment_type,pi.payment_time,oi.province_id
from
(select * from ods_payment_info where dt='2021-05-03'
)pi
join
(select id, province_id from ods_order_info where dt='2021-05-03'
)oi
on pi.order_id = oi.id;

4、退款事实表(事务型事实表)

需要时间、用户、商品三个维度,查看ODS层表ods_order_refund_info,所有字段都有,那么直接取数装载。

4.1、创建表

drop table if exists dwd_fact_order_refund_info;
create external table dwd_fact_order_refund_info(`id` string COMMENT '编号',`user_id` string COMMENT '用户ID',`order_id` string COMMENT '订单ID',`sku_id` string COMMENT '商品ID',`refund_type` string COMMENT '退款类型',`refund_num` bigint COMMENT '退款件数',`refund_amount` decimal(16,2) COMMENT '退款金额',`refund_reason_type` string COMMENT '退款原因类型',`create_time` string COMMENT '退款时间'
) COMMENT '退款事实表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_order_refund_info/'
tblproperties ("parquet.compression"="lzo");

4.2、装载时间

直接从ODS层查到数据后装载。

insert overwrite table dwd_fact_order_refund_info partition(dt='2021-05-03')
selectid,user_id,order_id,sku_id,refund_type,refund_num,refund_amount,refund_reason_type,create_time
from ods_order_refund_info
where dt='2021-05-03';

5、评价事实表、订单明细事实表(事务型事实表)

都和上面“退款事实表”处理方法一样,并且所有字段均从ODS层ods_comment_info直接获取。你是否可以自己创建呢?

三、DW层-周期型快照事实表

1、周期型快照事实表的概念

周期型快照事实表,表中不会保留所有数据,只保留固定时间间隔的数据,例如每天或者每月的销售额或每月的账户余额等。例如购物车,有加减商品,随时都有可能变化,但是我们更关心每天结束时这里面有多少商品,方便我们后期统计分析。相当于每天一个全量快照,业务表的同步策略是全量同步。

2、解析思路

每天做一次快照,导入的数据是全量,区别于事务型事实表是每天导入新增。

存储的数据比较讲究时效性,时间太久了的意义不大,可以删除以前的数据。

4f943aec8fbf0eaea7f327a3118be5cb.png

3、加购事实表(周期型快照事实表)

3.1、创建表结构

所有字段ODS层,fact_cart_info表都有。

drop table if exists dwd_fact_cart_info;
create external table dwd_fact_cart_info(`id` string COMMENT '编号',`user_id` string  COMMENT '用户id',`sku_id` string  COMMENT 'skuid',`cart_price` string  COMMENT '放入购物车时价格',`sku_num` string  COMMENT '数量',`sku_name` string  COMMENT 'sku名称 (冗余)',`create_time` string  COMMENT '创建时间',`operate_time` string COMMENT '修改时间',`is_ordered` string COMMENT '是否已经下单。1为已下单;0为未下单',`order_time` string  COMMENT '下单时间',`source_type` string COMMENT '来源类型',`srouce_id` string COMMENT '来源编号'
) COMMENT '加购事实表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_cart_info/'
tblproperties ("parquet.compression"="lzo");

3.2、装载数据

insert overwrite table dwd_fact_cart_info partition(dt='2021-05-03')
selectid,user_id,sku_id,cart_price,sku_num,sku_name,create_time,operate_time,is_ordered,order_time,source_type,source_id
from ods_cart_info
where dt='2020-06-14';

4、收藏事实表

收藏事实表的操作和加购事实表一样,从时间、商品、用户三个维度来创建表。

四、DWD层-累积型快照事实表

1、累积型快照事实表的概念

累积型快照事实表,用于周期性发生变化的业务,即需要周期性的跟踪业务事实的变化。例如:数据仓库中可能需要累积或者存储订单从下订单开始,到订单商品被打包、运输、和签收的各个业务阶段的时间点数据来跟踪订单声明周期的进展情况。当这个业务过程进行时,事实表的记录也要不断更新。

业务表的同步策略是新增以及变化同步。

2、解析思路

我们以优惠券领用事实表为例。首先要了解优惠卷的生命周期:领取优惠卷——>用优惠卷下单——>优惠卷参与支付

累积型快照事实表使用:统计优惠卷领取次数、优惠卷下单次数、优惠卷参与支付次数。

90a21d1ab72dac42bcd18b6a6fe6f61a.png

3、优惠券领用事实表(累积型快照事实表)

3.1、创建表结构

drop table if exists dwd_fact_coupon_use;
create external table dwd_fact_coupon_use(`id` string COMMENT '编号',`coupon_id` string  COMMENT '优惠券ID',`user_id` string  COMMENT 'userid',`order_id` string  COMMENT '订单id',`coupon_status` string  COMMENT '优惠券状态',`get_time` string  COMMENT '领取时间',`using_time` string  COMMENT '使用时间(下单)',`used_time` string  COMMENT '使用时间(支付)'
) COMMENT '优惠券领用事实表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_fact_coupon_use/'
tblproperties ("parquet.compression"="lzo");

注意:这里dt是按照优惠卷领用时间get_time做为分区

`get_time` string  COMMENT '领取时间',
`using_time` string  COMMENT '使用时间(下单)',
`used_time` string  COMMENT '使用时间(支付)'

3.2装载数据

首日装载分析

16de14d80e8dfe18ac155a7ee100d59b.png

首日装载SQL代码,注意是动态分区。

insert overwrite table dwd_coupon_use partition(dt)
selectid,coupon_id,user_id,order_id,coupon_status,get_time,using_time,used_time,expire_time,coalesce(date_format(used_time,'yyyy-MM-dd'),date_format(expire_time,'yyyy-MM-dd'),'9999-99-99')
from ods_coupon_use
where dt='2021-05-03';

每日装载思路分析

591147e4db529245774cac40331a236f.png

SQL代码

set hive.exec.dynamic.partition.mode=nonstrict;
set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_coupon_use partition(dt)
selectif(new.id is null,old.id,new.id),if(new.coupon_id is null,old.coupon_id,new.coupon_id),if(new.user_id is null,old.user_id,new.user_id),if(new.order_id is null,old.order_id,new.order_id),if(new.coupon_status is null,old.coupon_status,new.coupon_status),if(new.get_time is null,old.get_time,new.get_time),if(new.using_time is null,old.using_time,new.using_time),if(new.used_time is null,old.used_time,new.used_time),date_format(if(new.get_time is null,old.get_time,new.get_time),'yyyy-MM-dd')
from
(selectid,coupon_id,user_id,order_id,coupon_status,get_time,using_time,used_timefrom dwd_fact_coupon_usewhere dt in(selectdate_format(get_time,'yyyy-MM-dd')from ods_coupon_usewhere dt='2021-05-04')
)old
full outer join
(selectid,coupon_id,user_id,order_id,coupon_status,get_time,using_time,used_timefrom ods_coupon_usewhere dt='2021-05-04'
)new
on old.id=new.id;

其他类似的累积型事实表也是这个操作思路。

这样我们就完成了DWD层业务数据的建模和设计、搭建和使用包括简要的SQL代码的编写。

现在我们来总结一下:

DWD层是对事实表的处理,代表的是业务的最小粒度层。任何数据的记录都可以从这一层获取,为后续的DWS和DWT层做准备。DWD层是站在选择好事实表的基础上,对维度建模的视角,这层维度建模主要做的4个步骤:选择业务过程、声明粒度、确认维度、确认事实。

数仓(一)简介数仓,OLTP和OLAP

数仓(二)关系建模和维度建模

数仓(三)简析阿里、美团、网易、恒丰银行、马蜂窝5家数仓分层架构

数仓(四)数据仓库分层

数仓 (五) 元数据管理系统解析

数仓(六)从0到1简单搭建数仓ODS层(埋点日志 + 业务数据)

数仓(七)从0到1简单搭建加载数仓DIM层以及拉链表处理

数仓(八)从0到1简单搭建加载数仓DWD层(用户行为日志数据解析)

3c4f35c4109df2e18c256c2271b04b2f.png

这篇关于数仓(九)从0到1简单搭建加载数仓DWD层(业务数据解析)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/168902

相关文章

python处理带有时区的日期和时间数据

《python处理带有时区的日期和时间数据》这篇文章主要为大家详细介绍了如何在Python中使用pytz库处理时区信息,包括获取当前UTC时间,转换为特定时区等,有需要的小伙伴可以参考一下... 目录时区基本信息python datetime使用timezonepandas处理时区数据知识延展时区基本信息

Qt实现网络数据解析的方法总结

《Qt实现网络数据解析的方法总结》在Qt中解析网络数据通常涉及接收原始字节流,并将其转换为有意义的应用层数据,这篇文章为大家介绍了详细步骤和示例,感兴趣的小伙伴可以了解下... 目录1. 网络数据接收2. 缓冲区管理(处理粘包/拆包)3. 常见数据格式解析3.1 jsON解析3.2 XML解析3.3 自定义

SpringMVC 通过ajax 前后端数据交互的实现方法

《SpringMVC通过ajax前后端数据交互的实现方法》:本文主要介绍SpringMVC通过ajax前后端数据交互的实现方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价... 在前端的开发过程中,经常在html页面通过AJAX进行前后端数据的交互,SpringMVC的controll

SpringBoot中配置文件的加载顺序解读

《SpringBoot中配置文件的加载顺序解读》:本文主要介绍SpringBoot中配置文件的加载顺序,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录SpringBoot配置文件的加载顺序1、命令⾏参数2、Java系统属性3、操作系统环境变量5、项目【外部】的ap

Golang HashMap实现原理解析

《GolangHashMap实现原理解析》HashMap是一种基于哈希表实现的键值对存储结构,它通过哈希函数将键映射到数组的索引位置,支持高效的插入、查找和删除操作,:本文主要介绍GolangH... 目录HashMap是一种基于哈希表实现的键值对存储结构,它通过哈希函数将键映射到数组的索引位置,支持

Pandas统计每行数据中的空值的方法示例

《Pandas统计每行数据中的空值的方法示例》处理缺失数据(NaN值)是一个非常常见的问题,本文主要介绍了Pandas统计每行数据中的空值的方法示例,具有一定的参考价值,感兴趣的可以了解一下... 目录什么是空值?为什么要统计空值?准备工作创建示例数据统计每行空值数量进一步分析www.chinasem.cn处

如何使用 Python 读取 Excel 数据

《如何使用Python读取Excel数据》:本文主要介绍使用Python读取Excel数据的详细教程,通过pandas和openpyxl,你可以轻松读取Excel文件,并进行各种数据处理操... 目录使用 python 读取 Excel 数据的详细教程1. 安装必要的依赖2. 读取 Excel 文件3. 读

Spring 请求之传递 JSON 数据的操作方法

《Spring请求之传递JSON数据的操作方法》JSON就是一种数据格式,有自己的格式和语法,使用文本表示一个对象或数组的信息,因此JSON本质是字符串,主要负责在不同的语言中数据传递和交换,这... 目录jsON 概念JSON 语法JSON 的语法JSON 的两种结构JSON 字符串和 Java 对象互转

Python使用getopt处理命令行参数示例解析(最佳实践)

《Python使用getopt处理命令行参数示例解析(最佳实践)》getopt模块是Python标准库中一个简单但强大的命令行参数处理工具,它特别适合那些需要快速实现基本命令行参数解析的场景,或者需要... 目录为什么需要处理命令行参数?getopt模块基础实际应用示例与其他参数处理方式的比较常见问http

Python利用ElementTree实现快速解析XML文件

《Python利用ElementTree实现快速解析XML文件》ElementTree是Python标准库的一部分,而且是Python标准库中用于解析和操作XML数据的模块,下面小编就来和大家详细讲讲... 目录一、XML文件解析到底有多重要二、ElementTree快速入门1. 加载XML的两种方式2.