10.数据仓库搭建之DWD层搭建

Mmj666

已于 2023-02-20 17:36:27 修改

阅读量3k

点赞数 2

分类专栏：东软睿购跨境电商数仓项目文章标签：数据仓库大数据

于 2022-07-14 15:53:58 首次发布

本文链接：https://blog.csdn.net/Mmj666/article/details/125786261

版权

本文详细介绍了数据仓库DWD层的构建，涵盖交易、流量、工具和互动四大域的多个事务型事实表设计，包括加购物车、下单、支付等业务过程，涉及数据粒度、维度、事实确定、建表和数据装载等关键步骤。

摘要由CSDN通过智能技术生成

数据仓库搭建之DWD层搭建

我们在设计项目中DWD层时，需要注意以下几点：

1）DWD层的设计依据维度建模理论，该层存储维度模型当中的事实表。

2）DWD层的数据存储格式为ORC列式存储结合snappy压缩。

3）DWD层表名的命名规范为dwd _ 数据域 _ 表名 _ 单分区增量全量标识（inc/full）。

1.通过业务总线矩阵确定业务

我们根据之前构建的业务总线矩阵，来确定我们当前需要构建的事实表。

在这里插入图片描述

我们可以看到，我们在交易域当中所涉及到的业务过程有加购物车、下单、取消订单、支付成功、退单、退款成功六个业务过程，这六个业务过程对应着六张事务型事实表；流量域中所涉及到的业务过程有页面浏览、动作、曝光、启动应用以及错误五个业务过程，这五个业务过程对应着五张事务型事实表；用户域当中涉及到的业务过程有登录和注册，共两个业务过程，对应着两张事务型事实表；工具域当中有领取优惠券、使用优惠券（下单）和使用优惠券（支付）三个业务过程；在互动域当中有收藏商品和评价两个业务过程。

2.事实表设计

2.1交易域加购物车事务型事实表设计

2.1.1选择业务过程

业务过程为加购物车。

2.1.2声明粒度

该表的粒度为每一行数据代表某个用户将某个商品加购物车的一次操作。

2.1.3确定维度

加购物车的操作所涉及到的维度有时间维度、用户维度和商品维度。（维度的确定由业务系统当中的逻辑决定）

2.1.4确定事实

加购物车的事实（度量值）为商品的件数。

2.1.5建表语句

DROP TABLE IF EXISTS dwd_trade_cart_add_inc;
CREATE EXTERNAL TABLE dwd_trade_cart_add_inc
(
    `id`               STRING COMMENT '编号',
    `user_id`          STRING COMMENT '用户id',
    `sku_id`           STRING COMMENT '商品id',
    `date_id`          STRING COMMENT '时间id',
    `create_time`      STRING COMMENT '加购时间',
    `source_id`        STRING COMMENT '来源类型ID',
    `source_type_code` STRING COMMENT '来源类型编码',
    `source_type_name` STRING COMMENT '来源类型名称',
    `sku_num`          BIGINT COMMENT '加购物车件数'
) COMMENT '交易域加购物车事务事实表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_add_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

2.1.6数据装载

（1）数据的流向

（2）首日数据加载

我们进行首日装载时，由于需要将数据加载到不同日期的分区内，所以需要开启非严格模式。

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_cart_add_inc partition (dt)
select
    id,
    user_id,
    sku_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    source_id,
    source_type,
    dic.dic_name,
    sku_num,
    date_format(create_time, 'yyyy-MM-dd')
from
(
    select
        data.id,
        data.user_id,
        data.sku_id,
        data.create_time,
        data.source_id,
        data.source_type,
        data.sku_num
    from ods_cart_info_inc
    where dt = '2022-05-01'
    and type = 'bootstrap-insert'
)cart
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2022-05-01'
    and parent_code='24'
)dic
on cart.source_type=dic.dic_code;

（3）每日数据加载

insert overwrite table dwd_trade_cart_add_inc partition(dt='2022-05-02')
select
    id,
    user_id,
    sku_id,
    date_id,
    create_time,
    source_id,
    source_type_code,
    source_type_name,
    sku_num
from
(
    select
        data.id,
        data.user_id,
        data.sku_id,
        date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd') date_id,
        date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd HH:mm:ss') create_time,
        data.source_id,
        data.source_type source_type_code,
        if(type='insert',data.sku_num,data.sku_num-old['sku_num']) sku_num
    from ods_cart_info_inc
    where dt='2022-05-02'
    and (type='insert'
    or(type='update' and old['sku_num'] is not null and data.sku_num>cast(old['sku_num'] as int)))
)cart
left join
(
    select
        dic_code,
        dic_name source_type_name
    from ods_base_dic_full
    where dt='2022-05-02'
    and parent_code='24'
)dic
on cart.source_type_code=dic.dic_code;

2.1.7关于from_unixtime(ts,format)和from_utc_timestamp(ts,timezone)

如何我们想将ts时间戳格式化，使用from_unixtime时，会将时区设置为UTC时区，这明显是错误的，因为我们的时间是北京时间。因此，我们需要使用from_utc_timestamp(ts,‘GMT+8’)将时间戳转换成北京时间（其中ts为整数的话，则认为它是毫秒）。

2.2交易域下单事务型事实表设计

2.2.1选择业务过程

业务过程为下单。

2.1.2声明粒度

该表的粒度为每一行数据代表一个订单中的一个商品项。

2.2.3确定维度

下单的操作所涉及到的维度有时间维度、用户维度、商品维度、地区维度、活动维度和优惠券维度。

2.2.4确定事实

下单的事实（度量值）有下单的件数、下单的原始金额、下单的最终金额、活动优惠金额和优惠券优惠金额。

2.2.5建表语句

DROP TABLE IF EXISTS dwd_trade_order_detail_inc;
CREATE EXTERNAL TABLE dwd_trade_order_detail_inc
(
    `id`                    STRING COMMENT '编号',
    `order_id`              STRING COMMENT '订单id',
    `user_id`               STRING COMMENT '用户id',
    `sku_id`                STRING COMMENT '商品id',
    `province_id`           STRING COMMENT '省份id',
    `activity_id`           STRING COMMENT '参与活动规则id',
    `activity_rule_id`      STRING COMMENT '参与活动规则id',
    `coupon_id`             STRING COMMENT '使用优惠券id',
    `date_id`               STRING COMMENT '下单日期id',
    `create_time`           STRING COMMENT '下单时间',
    `source_id`             STRING COMMENT '来源编号',
    `source_type_code`      STRING COMMENT '来源类型编码',
    `source_type_name`      STRING COMMENT '来源类型名称',
    `sku_num`               BIGINT COMMENT '商品数量',
    `split_original_amount` DECIMAL(16, 2) COMMENT '原始价格',
    `split_activity_amount` DECIMAL(16, 2) COMMENT '活动优惠分摊',
    `split_coupon_amount`   DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
    `split_total_amount`    DECIMAL(16, 2) COMMENT '最终价格分摊'
) COMMENT '交易域下单明细事务事实表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_order_detail_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

2.1.6数据装载

（1）数据的流向

（2）首日数据加载

涉及到的业务表有订单明细表、订单信息表、订单明细活动表、订单明细优惠券表以及字典表。

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_order_detail_inc partition (dt)
select
    od.id,
    order_id,
    user_id,
    sku_id,
    province_id,
    activity_id,
    activity_rule_id,
    coupon_id,
    date_format(create_time, 'yyyy-MM-dd') date_id,
    create_time,
    source_id,
    source_type,
    dic_name,
    sku_num,
    split_original_amount,
    split_activity_amount,
    split_coupon_amount,
    split_total_amount,
    date_format(create_time,'yyyy-MM-dd')
from
(
    select
        data.id,
        data.order_id,
        data.sku_id,
        data.create_time,
        data.source_id,
        data.source_type,
        data.sku_num,
        data.sku_num * data.order_price split_original_amount,
        data.split_total_amount,
        data.split_activity_amount,
        data.split_coupon_amount
    from ods_order_detail_inc
    where dt = '2022-05-01'
    and type = 'bootstrap-insert'
) od
left join
(
    select
        data.id,
        data.user_id,
        data.province_id
    from ods_order_info_inc
    where dt = '2022-05-01'
    and type = 'bootstrap-insert'
) oi
on od.order_id = oi.id
left join
(
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where dt = '2022-05-01'
    and type = 'bootstrap-insert'
) act
on od.id = act.order_detail_id
left join
(
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where dt = '2022-05-01'
    and type = 'bootstrap-insert'
) cou
on od.id = cou.order_detail_id
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2022-05-01'
    and parent_code='24'
)dic
on od.source_type=dic.dic_code;

（3）每日数据加载

insert overwrite table dwd_trade_order_detail_inc partition (dt='2022-05-02')
select
    od.id,
    order_id,
    user_id,
    sku_id,
    province_id,
    activity_id,
    activity_rule_id,
    coupon_id,
    date_id,
    create_time,
    source_id,
    source_type,
    dic_name,
    sku_num,
    split_original_amount,
    split_activity_amount,
    split_coupon_amount,
    split_total_amount
from
(
    select
        data.id,
        data.order_id,
        data.sku_id,
        date_format(data.create_time, 'yyyy-MM-dd') date_id,
        data.create_time,
        data.source_id,
        data.source_type,
        data.sku_num,
        data.sku_num * data.order_price split_original_amount,
        data.split_total_amount,
        data.split_activity_amount,
        data.split_coupon_amount
    from ods_order_detail_inc
    where dt = '2022-05-02'
    and type = 'insert'
) od
left join
(
    select
        data.id,
        data.user_id,
        data.province_id
    from ods_order_info_inc
    where dt = '2022-05-02'
    and type = 'insert'
) oi
on od.order_id = oi.id
left join
(
    select
        data.order_detail_id,
        data.activity_id,
        data.activity_rule_id
    from ods_order_detail_activity_inc
    where dt = '2022-05-02'
    and type = 'insert'
) act
on od.id = act.order_detail_id
left join
(
    select
        data.order_detail_id,
        data.coupon_id
    from ods_order_detail_coupon_inc
    where dt = '2022-05-02'
    and type = 'insert'
) cou
on od.id = cou.order_detail_id
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2022-05-02'
    and parent_code='24'
)dic
on od.source_type=dic.dic_code;

2.3交易域取消订单事务型事实表设计

2.3.1选择业务过程

业务过程为取消订单。

2.3.2声明粒度

该表的粒度为每一行数据代表一次取消订单操作，具体为哪一个用户在什么时间对哪一个商品取消了订单，粒度精细到了取消订单中的每一种类的商品。

2.3.3确定维度

取消订单的操作所涉及到的维度有时间维度、用户维度、商品维度、地区维度、活动维度和优惠券维度。

2.3.4确定事实

取消订单的事实（度量值）有下单的件数、下单的原始金额、下单的最终金额、活动优惠金额和优惠券优惠金额。

2.3.5建表语句

DROP TABLE IF EXISTS dwd_trade_cancel_detail_inc;
CREATE EXTERNAL TABLE dwd_trade_cancel_detail_inc
(
    `id`                    STRING COMMENT '编号',
    `order_id`              STRING COMMENT '订单id',
    `user_id`               STRING COMMENT '用户id',
    `sku_id`                STRING COMMENT '商品id',
    `province_id`           STRING COMMENT '省份id',
    `activity_id`           STRING COMMENT '参与活动规则id',
    `activity_rule_id`      STRING COMMENT '参与活动规则id',
    `coupon_id`             STRING COMMENT '使用优惠券id',
    `date_id`               STRING COMMENT '取消订单日期id',
    `cancel_time`           STRING COMMENT '取消订单时间',
    `source_id`             STRING COMMENT '来源编号',
    `source_type_code`      STRING COMMENT '来源类型编码',
    `source_type_name`      STRING COMMENT '来源类型名称',
    `sku_num`               BIGINT COMMENT '商品数量',
    `split_original_amount` DECIMAL(16, 2) COMMENT '原始价格',
    `split_activity_amount` DECIMAL(16, 2) COMMENT '活动优惠分摊',
    `split_coupon_amount`   DECIMAL(16, 2) COMMENT '优惠券优惠分摊',
    `split_total_amount`    DECIMAL(16, 2) COMMENT '最终价格分摊'
) COMMENT '交易域取消订单明细事务事实表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    STORED AS ORC
    LOCATION '/warehouse/gmall/dwd/dwd_trade_cancel_detail_inc/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

2.3.6数据装载

（1）数据的流向

（2）首日数据加载

涉及到的业务表有订单明细表、订单信息表、订单明细活动表、订单明细优惠券表以及字典表。

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_cancel_detail_inc partition (dt)
select
    od.id,
    order_id,
    user_id,
    sku_id,
    province_id,
    activity_id,
    activity_rule_id,
    coupon_id,
    date_format(canel_time,'yyyy-MM-dd') date_id,
    canel_time,
    source_id,
    source_type,
    dic_name,
    sku_num,
    split_original_amount,
    split_activity_amount,
    split_coupon_amount,
    split_total_amount,
    date_format(canel_time,'yyyy-MM-dd')
from
(
    select
        data.id,
        data.order_id,
        data.sku_id,
        data.source_id,
        data.source_type,
        data.sku_num,
        data.sku_num * data.order_price split_original_amount,
        data.split_total_amount,
        data.split_activity_amount,
        data.split_coupon_amount
    from ods_order_detail_inc
    where dt = '2022-05-01'
    and type = 'bootstrap-insert'
) od
join
(
    select
        data.id,
        data.user_id,
        data.province_id,
        data.operate_time canel_time
    from ods_order_info_inc
    where dt = '2022-05-01'
    and type = 'bootstrap-insert'
    and data.order_status='1003'
) oi
on od.order_id = oi.id
left join
(