离线数仓-9-数据仓库开发DWS层设计要点-1d/nd/td表设计
离线数仓-9-数据仓库开发DWS层设计要点-1d/nd/td表设计一、DWS层设计要点二、DWS层设计分析 - 1d/nd1.DWS层设计一:不考虑用户维度2.DWS层设计二:考虑用户维度2.DWS层设计三 :考虑用户+商品维度,形成DWS层汇总表最终建表方案:
三、DWS层设计分析 - td 历史至今1.以新增下单人数的需求为例
三、DWS层设计分析 - 总结
离线数仓-9-数据仓库开发DWS层设计要点-1d/nd/td表设计
一、DWS层设计要点
DWS层计算是依托于业务层面的需求来实现的,是需求驱动的。设计要点:
1.DWS层的设计参考指标体系;
具体参考之前文档笔记:https://blog.csdn.net/weixin_38136584/article/details/129167647?spm=1001.2014.3001.5501从0-1搭建dws层步骤:
1.分析现有的每个业务需求,这一步最难,每个业务需求怎样计算,然后分析每个业务需求依赖哪些指标,每个指标依赖的派生指标有哪些,构建出业务需求指标的分析视图。2.从分析出来的业务需求视图里面,可以提炼到表格汇总,然后寻找共用的派生指标有哪些3.根据派生指标,来进行设计DWS层表格,在DWS层创建表格和派生指标关系?一对多;哪些派生指标共用公共派生指标表格呢?
将业务过程相同、统计周期相同、统计粒度相同的派生指标汇总到一个派生指标对应的表格中,这样DWS层表格就会减少很多。
业务过程相同:来自于同一张事实表统计周期相同:在进行过滤的时候,过滤的分区也相同。
不同周期的可以放在一个表格中,但是这样存储的话,在进行数据装载的时候,如果牵涉到历史至今的周期和最近一天的周期,那么会全量扫描dwd层全表,但是最新一天的数据,仅仅跟前一日的分区有关系,数据装载的效率降低。建议将不同周期的存放在不同的表格中,即便是业务过程和统计粒度相同。 统计粒度相同: 统计粒度相同的话,派生指标计算完毕的数据都是一个值,对应到的都是统计粒度维度,这些计算完的指标,新增一个字段,将结果存放进去即可。 2.DWS层的数据存储格式为ORC列式存储 + snappy压缩。3.DWS层表名的命名规范为:dws_数据域_统计粒度_业务过程_统计周期(1d/nd/td) 注:1d表示最近1日,nd表示最近n日,td表示历史至今。
二、DWS层设计分析 - 1d/nd
1.DWS层设计一:不考虑用户维度
1.首先需要对ADS层业务需求进行明确,需求如下:
1.各品牌商品交易统计2.各品类商品交易统计
统计周期统计粒度指标最近1、7、30日品牌订单数最近1、7、30日品牌订单人数最近1、7、30日品牌退单数最近1、7、30日品牌退单人数
统计周期统计粒度指标最近1、7、30日品类订单数最近1、7、30日品类订单人数最近1、7、30日品类退单数最近1、7、30日品类退单人数
2.构建指标体系,对于需求进行指标分析,分析出每个需求对应什么类型指标
各品牌的指标体系分析 各品类的指标体系分析 3.抽取派生指标,将刚刚思维导图中汇总的指标体系,梳理到表格中,抽取共用的派生指标 4.设计DWS层汇总表,根据刚刚梳理的指标体系表格,梳理出DWS层需要创建哪些表格。 DWS层表名的命名规范为:dws_数据域_统计粒度_业务过程_统计周期(1d/nd/td)
针对表格第一行和第二行,设计表格名:dwd_trade_tm_order_1d行信息规划:每行代表此品牌最近1天下单总数量列字段规划:品牌id,聚合后的值(下单次数),聚合后的值(下单人数),品牌的名称(可以加,可以不加,直接关联维度表即可)分区规划:每天计算,最近一日的汇总结果。按天创建分区,每天分区里面存放当天的汇总结果 5.创建dwd_trade_tm_order_1d表格的DDL语句
数仓表格设计的时候,尽量避免后期改表操作,怎么避免?
在DWS添加指标信息的时候,尽量考虑全面,参考维度是:DWD层的相关的表格的度量值。 最近1天指标DDL语句如下:
create external table dws_trade_tm_order_1d
(
tm_id string comment '品牌id',
tm_name string comment '品牌名称',
order_count bigint comment '最近1日下单次数',
order_user_count bigint comment '最近1日下单人数',
order_num bigint comment '最近1日下单件数',
order_total_amount decimal(16,2) comment '最近1日下单金额'
) comment '交易域品牌粒度订单最近1日汇总事实表'
partition by (dt string)
stored as orc
location '/warehouse/gmall/dws/dws_trade_tm_order_1d'
tblproperties('orc.compress'='snappy')
6.对于dwd_trade_tm_order_1d表格进行数据装载
装载数据SQL如下:
insert overwrite table dws_trade_tm_order_1d partition(dt='2020-06-14'
SELECT
tm_id,
tm_name,
COUNT(1),
count(DISTINCT (user_id)),
sum(sku_num),
sum(split_total_amount)
from
(
SELECT
sku_id, user_id, sku_num, split_total_amount
from
dwd_trade_order_detail_inc
where
dt = '2020-06-14' )od
left JOIN (
select
id, tm_id, tm_name
FROM
dim_sku_full
where
dt = '2020-06-14' )sku on
od.sku_id = sku.id
GROUP by
tm_id,
tm_name;
7.nd表的创建表格DDL语句:
create external table dws_trade_tm_order_nd
(
tm_id string comment '品牌id',
tm_name string comment '品牌名称',
order_count_7d bigint comment '最近7日下单次数',
order_user_count_7d bigint comment '最近7日下单人数',
order_num_7d bigint comment '最近7日下单件数',
order_total_amount_7d decimal(16,2) comment '最近7日下单金额',
order_count_30d bigint comment '最近30日下单次数',
order_user_count_30d bigint comment '最近30日下单人数',
order_num_30d bigint comment '最近30日下单件数',
order_total_amount_30d decimal(16,2) comment '最近30日下单金额'
) comment '交易域品牌粒度订单最近7日和30日汇总事实表'
partition by (dt string)
stored as orc
location '/warehouse/gmall/dws/dws_trade_tm_order_nd'
tblproperties('orc.compress'='snappy')
8.nd表的数据装载
存在1d表的数据,优先从1d表中获取,否则直接从dwd层事实表中获取。
insert overwrite table dws_trade_tm_order_nd partition(dt='2020-06-14')
select
tm_id,
tm_name,
sum(if(dt>=date_sub('2020-06-14',6),order_count,0)), //计算最近7天的数据
sum(if(dt>=date_sub('2020-06-14',6),order_user_count,0)),
sum(if(dt>=date_sub('2020-06-14',6),order_num,0)),
sum(if(dt>=date_sub('2020-06-14',6),order_total_amount,0)),
sum(order_count),
sum(order_user_count),
sum(order_num),
sum(order_total_amount),
from dws_trade_tm_order_1d
where dt >= date_sub('2020-06-14',29)
group by tm_id,tm_name;
9.nd表的装载语句存在重复计算的问题,一个下单用户 可能在30个分区都有,但是仅仅是一个下单用户,但在计算的时候,每个分区都有此下单用户,没有进行去重操作,那么相加完毕后,全部一个用户就变成了30个用户了,数据不准确了。
怎样解决?
1.从dwd层获取原始数据,最近7天,最近30天,dws层直接去重汇总即可。2.降低1d表的维度,之前表格不体现用户维度,现在表格体现用户维度,要重新修改1d表格。
2.DWS层设计二:考虑用户维度
1.重新创建1d表 DDL如下:
create external table dws_trade_user_tm_order_1d
(
user_id string comment '用户id',
tm_id string comment '品牌id',
tm_name string comment '品牌名称',
order_count bigint comment '最近1日下单次数',
order_num bigint comment '最近1日下单件数',
order_total_amount decimal(16,2) comment '最近1日下单金额'
) comment '交易域用户品牌粒度订单最近1日汇总事实表'
partition by (dt string)
stored as orc
location '/warehouse/gmall/dws/dws_trade_tm_order_1d'
tblproperties('orc.compress'='snappy')
2.1d表数据装载语句
insert overwrite table dws_trade_user_tm_order_1d partition(dt='2020-06-14'
SELECT
user_id,
tm_id,
tm_name,
COUNT(1),
sum(sku_num),
sum(split_total_amount)
from
(
SELECT
sku_id, user_id, sku_num, split_total_amount
from
dwd_trade_order_detail_inc
where
dt = '2020-06-14' )od
left JOIN (
select
id, tm_id, tm_name
FROM
dim_sku_full
where
dt = '2020-06-14' )sku on
od.sku_id = sku.id
GROUP by
user_id,
tm_id,
tm_name;
3.nd表的创建表格DDL语句:
create external table dws_trade_user_tm_order_nd
(
user_id string comment '用户id',
tm_id string comment '品牌id',
tm_name string comment '品牌名称',
order_count_7d bigint comment '最近7日下单次数',
order_num_7d bigint comment '最近7日下单件数',
order_total_amount_7d decimal(16,2) comment '最近7日下单金额',
order_count_30d bigint comment '最近30日下单次数',
order_num_30d bigint comment '最近30日下单件数',
order_total_amount_30d decimal(16,2) comment '最近30日下单金额'
) comment '交易域用户品牌粒度订单最近7日和30日汇总事实表'
partition by (dt string)
stored as orc
location '/warehouse/gmall/dws/dws_trade_tm_order_nd'
tblproperties('orc.compress'='snappy')
4.指标体系调整:其他指标也需要跟着调整 5.对应需求矩阵 6.nd表的数据装载
存在1d表的数据,优先从1d表中获取,否则直接从dwd层事实表中获取。
insert overwrite table dws_trade_user_tm_order_nd partition(dt='2020-06-14')
select
user_id ,
tm_id,
tm_name,
sum(if(dt>=date_sub('2020-06-14',6),order_count,0)), //计算最近7天的数据
sum(if(dt>=date_sub('2020-06-14',6),order_num,0)),
sum(if(dt>=date_sub('2020-06-14',6),order_total_amount,0)),
sum(order_count),
sum(order_num),
sum(order_total_amount),
from dws_trade_tm_order_1d
where dt >= date_sub('2020-06-14',29)
group by user_id ,tm_id,tm_name;
2.DWS层设计三 :考虑用户+商品维度,形成DWS层汇总表
直接考虑用户+商品维度,创建的表格,就不需要去考虑建表的时候的品牌维度和品类维度了。只创建一张用户+商品维度的DWS层表格,就可以直接提供给 品牌类需求使用,也可以提供给 品类需求使用,这样比较一下,上面几个设计在处理表的时候,较本设计模式增加了不少工作量;
本设计模式,可以只提供一张表,然后供给品牌和品类需求使用。创建的DWS层表格粒度越小,将来服务的表格会越多。
最终建表方案:
1.1d建表语句:用户+商品维度建表DDL语句:
DROP TABLE IF EXISTS dws_trade_user_sku_order_1d;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_1d
(
`user_id` STRING COMMENT '用户id',
`sku_id` STRING COMMENT 'sku_id',
`sku_name` STRING COMMENT 'sku名称',
`category1_id` STRING COMMENT '一级分类id',
`category1_name` STRING COMMENT '一级分类名称',
`category2_id` STRING COMMENT '一级分类id',
`category2_name` STRING COMMENT '一级分类名称',
`category3_id` STRING COMMENT '一级分类id',
`category3_name` STRING COMMENT '一级分类名称',
`tm_id` STRING COMMENT '品牌id',
`tm_name` STRING COMMENT '品牌名称',
`order_count_1d` BIGINT COMMENT '最近1日下单次数',
`order_num_1d` BIGINT COMMENT '最近1日下单件数',
`order_original_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单原始金额',
`activity_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日活动优惠金额',
`coupon_reduce_amount_1d` DECIMAL(16, 2) COMMENT '最近1日优惠券优惠金额',
`order_total_amount_1d` DECIMAL(16, 2) COMMENT '最近1日下单最终金额'
) COMMENT '交易域用户商品粒度订单最近1日汇总事实表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_1d'
TBLPROPERTIES ('orc.compress' = 'snappy');
2.nd建表语句:DDL语句
DROP TABLE IF EXISTS dws_trade_user_sku_order_nd;
CREATE EXTERNAL TABLE dws_trade_user_sku_order_nd
(
`user_id` STRING COMMENT '用户id',
`sku_id` STRING COMMENT 'sku_id',
`sku_name` STRING COMMENT 'sku名称',
`category1_id` STRING COMMENT '一级分类id',
`category1_name` STRING COMMENT '一级分类名称',
`category2_id` STRING COMMENT '一级分类id',
`category2_name` STRING COMMENT '一级分类名称',
`category3_id` STRING COMMENT '一级分类id',
`category3_name` STRING COMMENT '一级分类名称',
`tm_id` STRING COMMENT '品牌id',
`tm_name` STRING COMMENT '品牌名称',
`order_count_7d` STRING COMMENT '最近7日下单次数',
`order_num_7d` BIGINT COMMENT '最近7日下单件数',
`order_original_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单原始金额',
`activity_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日活动优惠金额',
`coupon_reduce_amount_7d` DECIMAL(16, 2) COMMENT '最近7日优惠券优惠金额',
`order_total_amount_7d` DECIMAL(16, 2) COMMENT '最近7日下单最终金额',
`order_count_30d` BIGINT COMMENT '最近30日下单次数',
`order_num_30d` BIGINT COMMENT '最近30日下单件数',
`order_original_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单原始金额',
`activity_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日活动优惠金额',
`coupon_reduce_amount_30d` DECIMAL(16, 2) COMMENT '最近30日优惠券优惠金额',
`order_total_amount_30d` DECIMAL(16, 2) COMMENT '最近30日下单最终金额'
) COMMENT '交易域用户商品粒度订单最近n日汇总事实表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dws/dws_trade_user_sku_order_nd'
TBLPROPERTIES ('orc.compress' = 'snappy');
3.指标体系和需求矩阵调整后结果
三、DWS层设计分析 - td 历史至今
1.以新增下单人数的需求为例
需求如下: 如果根据需求指标来直接处理的话,可能计算量以及sql复杂程度很高,可以换一种思路来解决此类需求;维护一张表格,首次该用户下单的信息维护到这张表格里面,怎样整合这张表,需要根据dwd层全表数据进行汇总,然后将全量数据,根据需求进行逻辑计算,将所需数据维护到dws层的汇总表中,历史全量数据在首次导入的时候,进行sql处理后录入,每日新增数据只需要判断此表格有没有该用户记录即可,没有该用户直接插入表格。创建表格:表格命名:dws_trade_user_order_td
每行代表信息:某用户首次下单信息,以及用户维度其他信息每列信息:与用户相关的维度信息都可以汇总到此表格中分区:按天创建分区,每天存放的数据都是历史至今的最新数据。具体表格创建语句如下:
DROP TABLE IF EXISTS dws_trade_user_order_td;
CREATE EXTERNAL TABLE dws_trade_user_order_td
(
`user_id` STRING COMMENT '用户id',
`order_date_first` STRING COMMENT '首次下单日期',
`order_date_last` STRING COMMENT '末次下单日期',
`order_count_td` BIGINT COMMENT '下单次数',
`order_num_td` BIGINT COMMENT '购买商品件数',
`original_amount_td` DECIMAL(16, 2) COMMENT '原始金额',
`activity_reduce_amount_td` DECIMAL(16, 2) COMMENT '活动优惠金额',
`coupon_reduce_amount_td` DECIMAL(16, 2) COMMENT '优惠券优惠金额',
`total_amount_td` DECIMAL(16, 2) COMMENT '最终金额'
) COMMENT '交易域用户粒度订单历史至今汇总事实表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dws/dws_trade_user_order_td'
TBLPROPERTIES ('orc.compress' = 'snappy');
装载数据:
如果每次都求dwd层表格中全量数据的话, 太损耗计算资源,可以基于前一日计算结果进行计算。这样比较高效解决数据问题。 首日全量装载sql:
insert overwrite table dws_trade_user_order_td partition(dt='2020-06-14')
select
user_id,
min(dt) login_date_first,
max(dt) login_date_last,
sum(order_count_1d) order_count,
sum(order_num_1d) order_num,
sum(order_original_amount_1d) original_amount,
sum(activity_reduce_amount_1d) activity_reduce_amount,
sum(coupon_reduce_amount_1d) coupon_reduce_amount,
sum(order_total_amount_1d) total_amount
from dws_trade_user_order_1d
group by user_id;
每日增量装载sql:方案一:之间使用full outer join ,然后获取的数据进行判断即可
insert overwrite table dws_trade_user_order_td partition(dt='2020-06-15')
select
nvl(old.user_id,new.user_id),
if(new.user_id is not null and old.user_id is null,'2020-06-15',old.order_date_first),
if(new.user_id is not null,'2020-06-15',old.order_date_last),
nvl(old.order_count_td,0)+nvl(new.order_count_1d,0),
nvl(old.order_num_td,0)+nvl(new.order_num_1d,0),
nvl(old.original_amount_td,0)+nvl(new.order_original_amount_1d,0),
nvl(old.activity_reduce_amount_td,0)+nvl(new.activity_reduce_amount_1d,0),
nvl(old.coupon_reduce_amount_td,0)+nvl(new.coupon_reduce_amount_1d,0),
nvl(old.total_amount_td,0)+nvl(new.order_total_amount_1d,0)
from
(
select
user_id,
order_date_first,
order_date_last,
order_count_td,
order_num_td,
original_amount_td,
activity_reduce_amount_td,
coupon_reduce_amount_td,
total_amount_td
from dws_trade_user_order_td
where dt=date_add('2020-06-15',-1)
)old
full outer join
(
select
user_id,
order_count_1d,
order_num_1d,
order_original_amount_1d,
activity_reduce_amount_1d,
coupon_reduce_amount_1d,
order_total_amount_1d
from dws_trade_user_order_1d
where dt='2020-06-15'
)new
on old.user_id=new.user_id;
方案二:两部分子查询之间使用union all进行关联
SELECT
user_id,
min(order_date_first) ,
max(order_date_last),
sum(order_count_td),
sum(order_num_td),
sum(original_amount_td),
sum(activity_reduce_amount_td),
sum(coupon_reduce_amount_td),
sum(total_amount_td)
from
(
select
user_id, order_date_first, order_date_last, order_count_td, order_num_td, original_amount_td, activity_reduce_amount_td, coupon_reduce_amount_td, total_amount_td
from
dws_trade_user_order_td
where
dt = date_add('2020-06-15',-1)
UNION ALL
select
user_id, '2020-06-15', '2020-06-15', order_count_1d, order_num_1d, order_original_amount_1d, activity_reduce_amount_1d, coupon_reduce_amount_1d, order_total_amount_1d
from
dws_trade_user_order_1d
where
dt = '2020-06-15'
GROUP by
user_id ) t1
group by
user_id ;
hive 中sql语法:
nvl(字段1,字段2) :取两个字段中不为空的那个字段,如果都不为空,取前一个,两个都为空,就不取。 开窗和分组
开窗:在原有表格列的基础上,添加一列开窗列,可以基于此列进行分析数据分组:改变原有表格的粒度, 结果的粒度跟原来表格的粒度不一样了。
三、DWS层设计分析 - 总结
dws层设计要点:1d表表结构设计,nd表表结构设计,td表表结构设计,以及对应的数据装载。
1d表表结构:
行设计:派生指标的粒度决定的列设计:粒度的id,派生指标决定,设计时需要有一定的前瞻性,参考与之对应的dwd层的事实表的度量值分区设计: 按天分区,每天分区放的是当天明细的汇总结果,跟明细表分区对应数据装载:dwd层与之对应的明细表,从明细表中获取一个分区的数据,之后进行汇总,汇总完成后放到汇总表一天的分区里面。 nd表表结构:
行设计:派生指标的粒度决定的列设计:跟1d表相比,多n维度字段,7d的指标,30d的指标分区设计:按天分区,截止当天的,最近N天的汇总数据数据装载:优先从1d表里面取数,如果没有1d表,那就去dwd层明细表取数。
直接拿30天的求和,然后在sql里面加个判断,获取最近7天的数据,这样就可以通过一个sql实现不同周期的计算。 td表表结构:
行设计:派生指标的粒度决定的列设计:派生指标决定分区设计:按天分区,每个分区里面存放历史截止当天的汇总数据。数据装载:
首日: 从dws层的1d或者dwd层明细表获取全表数据,优先1d表。每日:首先拿前一天的分区结果,然后拿1d表或者明细表里今天的结果,做加法运算,join或者union。
精彩链接
发表评论