数仓(九)从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获取中国节假日数据记录入JSON文件

《Python获取中国节假日数据记录入JSON文件》项目系统内置的日历应用为了提升用户体验,特别设置了在调休日期显示“休”的UI图标功能,那么问题是这些调休数据从哪里来呢?我尝试一种更为智能的方法:P... 目录节假日数据获取存入jsON文件节假日数据读取封装完整代码项目系统内置的日历应用为了提升用户体验,

使用Jackson进行JSON生成与解析的新手指南

《使用Jackson进行JSON生成与解析的新手指南》这篇文章主要为大家详细介绍了如何使用Jackson进行JSON生成与解析处理,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1. 核心依赖2. 基础用法2.1 对象转 jsON(序列化)2.2 JSON 转对象(反序列化)3.

Springboot @Autowired和@Resource的区别解析

《Springboot@Autowired和@Resource的区别解析》@Resource是JDK提供的注解,只是Spring在实现上提供了这个注解的功能支持,本文给大家介绍Springboot@... 目录【一】定义【1】@Autowired【2】@Resource【二】区别【1】包含的属性不同【2】@

使用Python实现快速搭建本地HTTP服务器

《使用Python实现快速搭建本地HTTP服务器》:本文主要介绍如何使用Python快速搭建本地HTTP服务器,轻松实现一键HTTP文件共享,同时结合二维码技术,让访问更简单,感兴趣的小伙伴可以了... 目录1. 概述2. 快速搭建 HTTP 文件共享服务2.1 核心思路2.2 代码实现2.3 代码解读3.

MySQL双主搭建+keepalived高可用的实现

《MySQL双主搭建+keepalived高可用的实现》本文主要介绍了MySQL双主搭建+keepalived高可用的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,... 目录一、测试环境准备二、主从搭建1.创建复制用户2.创建复制关系3.开启复制,确认复制是否成功4.同

SpringCloud动态配置注解@RefreshScope与@Component的深度解析

《SpringCloud动态配置注解@RefreshScope与@Component的深度解析》在现代微服务架构中,动态配置管理是一个关键需求,本文将为大家介绍SpringCloud中相关的注解@Re... 目录引言1. @RefreshScope 的作用与原理1.1 什么是 @RefreshScope1.

Java并发编程必备之Synchronized关键字深入解析

《Java并发编程必备之Synchronized关键字深入解析》本文我们深入探索了Java中的Synchronized关键字,包括其互斥性和可重入性的特性,文章详细介绍了Synchronized的三种... 目录一、前言二、Synchronized关键字2.1 Synchronized的特性1. 互斥2.

Spring Boot 配置文件之类型、加载顺序与最佳实践记录

《SpringBoot配置文件之类型、加载顺序与最佳实践记录》SpringBoot的配置文件是灵活且强大的工具,通过合理的配置管理,可以让应用开发和部署更加高效,无论是简单的属性配置,还是复杂... 目录Spring Boot 配置文件详解一、Spring Boot 配置文件类型1.1 applicatio

Mysql表的简单操作(基本技能)

《Mysql表的简单操作(基本技能)》在数据库中,表的操作主要包括表的创建、查看、修改、删除等,了解如何操作这些表是数据库管理和开发的基本技能,本文给大家介绍Mysql表的简单操作,感兴趣的朋友一起看... 目录3.1 创建表 3.2 查看表结构3.3 修改表3.4 实践案例:修改表在数据库中,表的操作主要

Java利用JSONPath操作JSON数据的技术指南

《Java利用JSONPath操作JSON数据的技术指南》JSONPath是一种强大的工具,用于查询和操作JSON数据,类似于SQL的语法,它为处理复杂的JSON数据结构提供了简单且高效... 目录1、简述2、什么是 jsONPath?3、Java 示例3.1 基本查询3.2 过滤查询3.3 递归搜索3.4