从0到1搭建数仓DWD层案例实践
浪尖聊大数据
共 7498字,需浏览 15分钟
· 2022-01-08
一、DWD层结构
6个维度表的退化操作其实我们在前面的第十二章节已经做了即DIM层。除了第3张表即商品维度表是5个表退化到1张表上,其他都是1-2张表退化到1张表上,相对比较简单。
2.4、确认事实
就是确认事实表的每张事实表的度量值。
二、DWD层-事务型事实表
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");
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_fact_payment_info partition(dt='2021-05-03')
select
pi.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;
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");
直接从ODS层查到数据后装载。
insert overwrite table dwd_fact_order_refund_info partition(dt='2021-05-03')
select
id,
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';
三、DW层-周期型快照事实表
1、周期型快照事实表的概念
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')
select
id,
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层-累积型快照事实表
我们以优惠券领用事实表为例。首先要了解优惠卷的生命周期:领取优惠卷——>用优惠卷下单——>优惠卷参与支付
累积型快照事实表使用:统计优惠卷领取次数、优惠卷下单次数、优惠卷参与支付次数。
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 '使用时间(支付)'
insert overwrite table dwd_coupon_use partition(dt)
select
id,
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';
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)
select
if(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
(
select
id,
coupon_id,
user_id,
order_id,
coupon_status,
get_time,
using_time,
used_time
from dwd_fact_coupon_use
where dt in
(
select
date_format(get_time,'yyyy-MM-dd')
from ods_coupon_use
where dt='2021-05-04'
)
)old
full outer join
(
select
id,
coupon_id,
user_id,
order_id,
coupon_status,
get_time,
using_time,
used_time
from ods_coupon_use
where dt='2021-05-04'
)new
on old.id=new.id;
其他类似的累积型事实表也是这个操作思路。
这样我们就完成了DWD层业务数据的建模和设计、搭建和使用包括简要的SQL代码的编写。
现在我们来总结一下:
DWD层是对事实表的处理,代表的是业务的最小粒度层。任何数据的记录都可以从这一层获取,为后续的DWS和DWT层做准备。DWD层是站在选择好事实表的基础上,对维度建模的视角,这层维度建模主要做的4个步骤:选择业务过程、声明粒度、确认维度、确认事实。
数据仓库第4版 数据仓库工具箱 DAMA数据管理知识体系指南 华为数据之道
评论
了解加密货币到加密货币的互换
1、什么是加密货币互换?加密货币到加密货币的互换是指以现行市场汇率将一种加密货币直接兑换为另一种加密货币。与需要法定货币存款和较长流程的传统交易所不同,加密货币到加密货币的互换可以无缝地促进交换。掉期在提高加密货币的流动性和效率方面发挥着重要作用。该功能使用户能够将他们的加密货币与钱包中的其他代币进
区块链头条
0
盘点一个使用超级鹰识别验证码并自动登录的案例
点击上方“Python共享之家”,进行关注回复“资源”即可获赠Python学习资料今日鸡汤江上几人在,天涯孤棹还。大家好,我是皮皮。一、前言前几天在Python钻石交流群【静惜】问了一个Python实现识别验证码并自动登录的问题,提问截图如下:验证码的截图如下所示:二、实现过程这里大家激烈的探讨,【
IT共享之家
0
零售业降本增效的新举措:从卖场的能耗入手
导语:对于超市企业来讲,通过节能灯具减少40%以上照明电费,是切实可行的节流方案之一。商超节能,势在必行!零售企业的降本增效从何入手?它们更需要怎样的创新产品与照明解决方案?广东富兴商超照明有限公司总经理唐京飘在第二十届全国连锁商业发展战略论坛上,与大家分享了“如何用科技创新推动超市节能”的主题演讲
联商网资讯
0
某程序员吐槽:公司最近招了一批35左右的,这帮人习惯天天卷到八点多,导致现在我们也要八点才下班
架构师大咖
架构师大咖,打造有价值的架构师交流平台。分享架构师干货、教程、课程、资讯。架构师大咖,每日推送。
公众号该公众号已被封禁某位程序员的吐槽引发了广泛的思考和共鸣。他抱怨公司
源码共读
0
乐普心安宝及心电图机,助力安康市搭建“心电一张网”,打通全域“生命线”!
为持续推动胸痛中心建设,助力全民健康,全面提升心血管疾病等急危重症救治能力水平。4月20日,由安康市卫健委主办、安康市中医医院承办的“第七届心血管汉江学术会议暨安康市胸痛中心大会”在高新国际会议中心顺利举行。市人大常委会主任王彪、市政协副主席唐纹、市政府党组成员刘英华等领导亲临现场,受邀参会的中国科
乐普医疗AI
0
江苏省人民医院外科党总支专家团队到盱眙县人民医院开展惠民医疗活动
党建引领聚合力,共建交流惠民生。4月20日,江苏省人民医院外科党总支专家团队走进盱眙县人民医院开展党建主题活动。江苏省人民医院外科党总支书记、大外科副主任杨力,大外科主任吴延虎,盱眙县人民医院院长干文武出席座谈交流会并讲话,盱眙县人民医院党委委员、副院长刘新亮主持了活动。杨力说,江苏省人民医院和盱眙
盱眙老妹
0
日常搬砖,Service层接口,就是多此一举!
今天我们要探讨的问题是:Service层需要接口?现在结合我参与的项目以及阅读的一些项目源码来看。如果「项目中使用了像Spring这样的依赖注入框架,那可以不用接口」!先来说说为什么使用了依赖注入框架以后,可以不使用接口!不需要接口的理由我整理了支持Service层和Dao层需要加上接口的理由,总结
java金融
0
惹到雷军,智己惨了
作为上汽集团品牌向上的“双翼战略”,智己汽车与飞凡汽车的价格接连下探,被迫与小米汽车同台竞争。可在热度与销量上,三者相形见绌。2023年,智己和飞凡销量累计约6万台;2024年3月28日,小米汽车上市27分钟大定破5万。老实巴交的车二代眼馋营销高手小米的热度,于是做了一场照猫画虎的“致敬”。不成想却
亿欧网
0