4.离线数仓—DWD层设计开发

自学大数据的菜鸡

已于 2022-03-07 20:22:58 修改

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

分类专栏：离线数仓文章标签： hive sql 数据仓库

于 2022-02-24 16:18:33 首次发布

本文链接：https://blog.youkuaiyun.com/yunweijie_cn/article/details/123098157

本文详细介绍了数据仓库DWD层的设计和实现，主要涉及交易域的加购、下单、取消订单、支付成功和退单事务事实表的分区规划、建表语句以及数据装载流程。DWD层依据维度建模理论，采用orc存储格式，通过增量分区策略进行数据组织。各事实表根据业务场景和数据流向进行设计，确保数据准确反映业务行为。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

前面完成了DIM层维度表的设计和开发，下面进行额DWD层事实表的设计和开发。

一、DWD层设计分析

1.设计要点

1.1 设计依据

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

1.2 存储格式

DWD层的数据存储格式为orc列式存储+snappy压缩

1.3 存储格式

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

二、DWD层实现

根据业务总线矩阵确定每个表里的字段。
在这里插入图片描述

1.交易域加购事务事实表

1.1 分区规划分析

事实表一般情况下都是增量表，所以采用一天一个增量的分区规划。
在这里插入图片描述

1.2 建表语句

在这里插入图片描述
加购物车这个业务过程是一个用户一次把一种商品加入到购物车，所以它的维度有时间、用户、商品，还有度量值商品件数（隐藏度量值加购次数，一行相当于一次），建表语句如下：

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');

1.4 数据流向分析

在这里插入图片描述
其实这个加购表一行数据并不不代表是一次加购操作，因为如果商品件数不为1的时候，可能是用户加购了多次该商品。这在平日的增量同步中可以很好的判断出来；但是在数仓首日时，如果没有初始的binlog记录，无法判断是几次，我们只能把一行数据代表是一次加购操作。
在首日时，业务数据库中可能有多日的数据，我们根据创建时间这一列获取加购的时间，把对应的数据放到日期对应的分区。
在平时时，当天的增量数据获取到的加购操作直接就放到当日日期的文件夹中即可。

1.5 首日数据装载

首日时，表中可能存在多天的业务数据，我们要根据create_time将对应日期的数据放到对应的分区。

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 = '2020-06-14'
    and type = 'bootstrap-insert'
)ci
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-14'
    and parent_code='24'
)dic
on ci.source_type=dic.dic_code;

注意：开启非严格模式；

1.6 每日数据装载

insert overwrite table dwd_trade_cart_add_inc partition(dt='2020-06-15')
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='2020-06-15'
    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='2020-06-15'
    and parent_code='24'
)dic
on cart.source_type_code=dic.dic_code;

需要注意的地方：
1）每日数据中有insert和update两种类型的数据，我们要找到所有insert的数据，以及update中修改了sku_num且修改后数量增加的数据，其中old是一个Map<STRING,STRING>类型的数据，所以要转化为int类型

type='insert'or(type='update' and old['sku_num'] is not null and data.sku_num>cast(old['sku_num'] as int))

2）时间问题。对于insert类型的数据，create_time就是加购的时间，同时它的operate_time为null；而对于update类型的数据，create_time是第一次加购的时间，而不是本次加购的时间，它的operate_time才是本次加购的时间，因此同时获取两种数据的时候，没办法使用create_time或operate_time来确定加购时间。
获取加购时间的方式：第一种是使用ods_cart_info_inc里的ts字段，这个字段就是发生变化的时间，但是这是以秒为单位的，在转换时要注意。

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,

第二种是用if判断如果是insert，就取它的create_time字段；如果是update就取它的operate_time字段（在子查询里要把create_time和operate_time都选到）

if(type='insert',date_format(create_time,'yyyy-MM-dd'),date_format(operate_time,'yyyy-MM-dd')) date_id

if(type='insert',create_time,operate_time) create_time

3）sku_num问题。对于insert数据，sku_num就是该次加购的商品件数，对于update数据，data里的sku_num减去old里的sku_num才是该次加购商品件数（int减string类型数据可以不转换）。

if(type='insert',data.sku_num,data.sku_num-old['sku_num']) sku_num

2.交易域下单事务事实表

2.1 分区规划分析

分区规划跟加购事实表一样，一天一个增量分区

2.2 建表语句

在这里插入图片描述
下单这个业务过程的粒度是一个订单中的一个商品项，它的维度有时间、用户、商品、地区、活动、优惠券，度量值有商品件数、商品原始金额、商品最终金额、活动优惠金额、优惠券优惠金额。
建表语句如下：

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.3 首日装载

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_am