离线数仓（八）【DWD 层开发】（1）-阿里云开发者社区

前言

1、DWD 层开发

DWD层设计要点：

（1）DWD层的设计依据是维度建模理论（主体是事务型事实表（选择业务过程 -> 声明粒度 -> 确定维度 -> 确定事实），另外两种周期型快照事实表和累积型事务事实表按需求选择），该层存储维度模型的事实表。

（2）DWD层的数据存储格式为orc列式存储+snappy压缩（和DIM层、DWS层都是一样的）。

（3）DWD层表名的命名规范为dwd_数据域_表名_单分区增量全量标识（inc/full）（划分数据域的目的是为了通过对数据分类使得从业务系统中可以快速的找到我们希望得到的数据；划分数据域的标准是按照业务过程，将若干个业务过程划分到一个数据域里面，其实所谓的划分数据域就是在划分事实表，因为一个业务过程对应一个事实表）。

1.1、交易域加购事务事实表

加购指的是加入购物车这个业务过程，我们按照设计事务事实表的过程来设计它的表结构：

1.1.1、选择业务过程

就是加购物车这个行为

1.1.2、声明粒度

要求尽可能选择最细粒度，声明粒度将来指代的是将来这张表的每一行所代表的内容。这里我们声明的粒度就是：谁+在什么时候+把什么商品加到了购物车

1.1.3、确认维度

我们首先能想到的就是用户、时间和商品，至于其它维度我们在设计事实表的时候尽可能多的考虑到，避免后期一些指标无法分析。

确认了维度，我们就确认了我们这张事务事实表的维度外键。

1.1.4、确认事实

确认事实就是确认事实表的度量值，对于这里的加购操作度量值主要就是加购的商品件数。

1.1.5、建表语句

这里我们的表名由几部分组成：dwd 代表这张表是 dwd 层的事实表；trade 代表数据域是交易域；cart_add 代表这张事实表的业务过程是加购操作；inc 因为这张表是事务型事实表所以我们一般都是增量表。

再看字段：其中 id 选取的是我们业务系统中 cart_info 的 id；user_id sku_id date_id 是我们的维度外键；sku_num 是我们的度量值。剩下的 create_time 是具体的加购时间，精确到秒，它区别于维度外键 date_id，date_id 是日期，只能精确到哪一天；source_id source_type_code source_type_name 这些字段都是来自我们业务系统的，我们之前说 DIM 层和 DWD 层的维度表和事实表都是业务驱动的，所以它们各自表的设计字段的选择来源于我们的业务系统中表，这里的加购事务事实表就是对应我们业务系统当中的 cart_info：

通过查询字典表可以看到：

不同的 source_type_code 代表不同类型的加购操作（用户通过哪种来源类型来加购的，比如用户通过广告来加购的，那通过哪个广告呢，所以这里我们还指定了 source_id），这些字段都算是维度，但是我们并没有对这些属性做一个维度表，而是直接放到事实表里（也就是维度退化，而维度退化要求不能只有编码，一般都是编码和文字描述共存的；所以这里我们保留了来源类型编码和来源类型名称）。

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.1.6、数据流向

现在我们表设计好了下一步就是数据源从哪来（从 ods 层的哪些表来），以及我们怎么向这张表进行装载（insert + select），这就需要我们了解一下这张表的不同维度的数据应该来自于哪些表。

我们首先需要了解业务系统中 order_info 这张表：

其实，对于加购这个过程无非只有两种情况：1、原本购物车没有这个商品然后进行加购，这是对数据库来说是一个 insert 操作；2、原本购物车就有这个商品然后加购，这对数据库来说是一个 update 操作。显式的变化就是 sku_num 的值的变化。

这样我们就搞清楚了加购这个业务过程，会对 order_info（或者可以说是 ods_order_info_inc）产生影响，产生的影响就是 sku_num 字段的值发生变化。

现在我们不仅要知道数据从张表来，还得知道数据从具体哪个分区流向哪个分区：

首日数据：

所有的增量表都要在首日做一个全量同步，这里我们的加购表当前存储的就是首日的全量数据，我们需要对字段 create_time 做一个动态分区来把不同时间的加购信息放到不同的分区：

每日数据：

解决了首日全量数据的分区问题，我们之后 ods 层每天的分区中存储的就是 Maxwell 监听的每日的增量数据，所以这些都是加购信息，我们直接存储当当日分区即可：

1.1.7、首日装载语句

-- 动态分区需要设置非严格模式
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.1.8、每日装载语句

我们比如要装载 06-15 这一天的数据，首先我们需要保证 type 为 insert 或者 update 类型的操作，并且加购后的 sku_num 的值要大于旧的 sku_num 值。

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;

这里在判断是否修改了 order_info 中 sku_num 值的时候我们还可以通过下面的方式来确认修改之前包含该字段：

type='update' and array_contains(map_keys(old),'sku_num')

这里我们不能使用 create_time 作为加购时间，因为 create_time 是我们创建这个购物车的时间，而不是真正的加购时间，所以我们应该用 ods_cart_info_inc 中的 ts 自动，因为它代表的是订单的变动时间：

对于不同的操作类型（insert 或 update）sku_num 的值也是不一样的，对于 insert 操作，这个商品是第一次加入购物车，所以 sku_num 就是加购的件数；但是对于 update，sku_num 指的是加购后的件数，所以实际加购的数量=old['sku_num']-data.sku_num 。

所以只要我们清楚了加购这个业务过程对订单表的影响，装载这张表就很简单了。

1.2、交易域下单事务事实表

同样分两步：设计表结构（4步），编写装载语句

1.2.1、设计表结构

选择业务过程：我们选择的是下单这个业务过程
声明粒度：要求尽可能最细粒度，所以这里的粒度应该是一个订单中的一个商品项而不是一整个订单，所以我们也就可以想到这张事实表对应业务系统中的 order_detail 表。
确认维度：关于下单操作，我们能想到的维度比如：时间、用户、商品、地区、活动和优惠券。以及下单方式，我们之前说过：如果某些维度表的维度属性很少（比如支付方式表没有必要去单独创建一个维度表，因为它就一个支付方式字段），则可不创建该维度表，而把该表的维度属性直接增加到与之相关的事实表中对于维度退化。确认维度是个灵活的过程，它并不取决于我们要分析什么指标，而是取决于我们业务系统能提供什么信息，比如这里的 order_detail 中包含了下单地区、该订单参与的活动以及使用优惠券的信息才能支持我们确认这样的维度。
确认事实：也就是确认度量值，这里的度量值可以是：下单件数、下单原始金额、下单的最终金额、活动优惠金额和优惠券优惠金额等。

1.2.2、建表语句

同样，这里的维度可以分为四部分：

从业务系统表当中直接拿过来的字段（比如 id、order_id 、create_time、source_id、source_type）
用作关联维度表的维度外键（user_id、sku_id、province_id、activity_id（算是退化字段，因为它和activity_rule_id都在同一张表，但是activity_rule_id的粒度更细）、activity_rule_id 、coupo_id、date_id等）
度量指标字段（比如 sku_num、split_xxx ...）
维度退化字段（比如 activity_id、source_type_code、source_id、source_type_name 等）

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

1.2.3、数据流向

在数据装载之前我们同样需要了解数据的流向，下单这个业务过程会对哪些表产生影响我们就从哪个表去获取数据。

这里的下单这个操作会影响到 order_info（会插入一条订单数据）、order_detail（会插入多条数据，取决于订单的中的商品数量）、order_detail_coupon（用户使用优惠券就会在这张表中插入数据）和 order_detail_activity（用户参与活动就会往这张表插入数据）。

相比较上面的加购操作，这里的下单操作虽然涉及到的表更多，但是它都是插入（insert）操作，所以事实上装载的逻辑要比加购简单。同时我们依然要区分首日和每日的数据。

1.2.4、首日数据装载

逻辑还是比较简单的，就是把 order_detail 作为主表不断进行 left join ：

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 = '2020-06-14'
    and type = 'bootstrap-insert'
) od
left join
(
    select
        data.id,
        data.user_id,
        data.province_id
    from ods_order_info_inc
    where dt = '2020-06-14'
    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 = '2020-06-14'
    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 = '2020-06-14'
    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='2020-06-14'
    and parent_code='24'
)dic
on od.source_type=dic.dic_code;

1.2.5、每日装载语句

对于之后的每日装载，我们只需要确保每张表的 type 为 insert、dt = 当天即可。

insert overwrite table dwd_trade_order_detail_inc partition (dt='2020-06-15')
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 = '2020-06-15'
    and type = 'insert'
) od
left join
(
    select
        data.id,
        data.user_id,
        data.province_id
    from ods_order_info_inc
    where dt = '2020-06-15'
    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 = '2020-06-15'
    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 = '2020-06-15'
    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='2020-06-15'
    and parent_code='24'
)dic
on od.source_type=dic.dic_code;

离线数仓（八）【DWD 层开发】（2）https://developer.aliyun.com/article/1532415

离线数仓（八）【DWD 层开发】（1）

前言

1、DWD 层开发

1.1、交易域加购事务事实表

1.1.1、选择业务过程

1.1.2、声明粒度

1.1.3、确认维度

1.1.4、确认事实

1.1.5、建表语句

1.1.6、数据流向

1.1.7、首日装载语句

1.1.8、每日装载语句

1.2、交易域下单事务事实表

1.2.1、设计表结构

1.2.2、建表语句

1.2.3、数据流向

1.2.4、首日数据装载

1.2.5、每日装载语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

离线数仓（八）【DWD 层开发】（1）

前言

1、DWD 层开发

1.1、交易域加购事务事实表

1.1.1、选择业务过程

1.1.2、声明粒度

1.1.3、确认维度

1.1.4、确认事实

1.1.5、建表语句

1.1.6、数据流向

1.1.7、首日装载语句

1.1.8、每日装载语句

1.2、交易域下单事务事实表

1.2.1、设计表结构

1.2.2、建表语句

1.2.3、数据流向

1.2.4、首日数据装载

1.2.5、每日装载语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景