离线数仓（九）【DWS 层开发】（2）-阿里云开发者社区

离线数仓（九）【DWS 层开发】（2）

2024-06-07 27

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生数据仓库AnalyticDB MySQL版，基础版 8ACU 100GB 1个月

简介： 离线数仓（九）【DWS 层开发】

离线数仓（九）【DWS 层开发】（1）https://developer.aliyun.com/article/1532432

V3.0

其实，我们还可以进一步进行优化（把两个粒度统一为一个粒度：用户-商品粒度），因为我们 ADS 最终要求的粒度是品牌和品类粒度，所以我们的数据从 DWD 到 DWS 层同样的数据需要聚合两次得到统计粒度分别为用户-品牌和用户-品类的这么两类数据，然后 ADS 层再对两张表分别进行过滤。

我们这里可以做一个优化：把用户-品牌和用户-品类这两个粒度统一为用户-商品粒度（粒度变得更细），等 ADS 层需要调用的时候再分别过滤，这样我们在 DWD 层到 DWS 层的过程就可以省去大量的计算开销。

CREATE EXTERNAL TABLE dws_trade_user_sku_order_1d
(
    `user_id`                    STRING COMMENT '用户id',
    `sku_id`                   STRING COMMENT '商品id',
    `brand_name`                 STRING COMMENT '品牌名称',
    `order_count`                BIGINT COMMENT '下单次数',
    `order_num`                  BIGINT COMMENT '下单件数',
    `order_amount`               DECIMAL(16,2) COMMENT '下单金额'
) COMMENT '交易域用户商品粒度订单最近1日汇总事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_brand_order_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');
 
CREATE EXTERNAL TABLE dws_trade_user_brand_order_nd
(
    `user_id`                    STRING COMMENT '用户id',
    `sku_id`                   STRING COMMENT '商品id',
    `brand_name`                 STRING COMMENT '品牌名称',
    `order_count_7d`                BIGINT COMMENT '下单次数',
    `order_num_7d`                  BIGINT COMMENT '下单件数',
    `order_amount_7d`               DECIMAL(16,2) COMMENT '最近7天的下单金额',
    `order_count_30d`                BIGINT COMMENT '下单次数',
    `order_num_30d`                  BIGINT COMMENT '下单件数',
    `order_amount_30d`               DECIMAL(16,2) COMMENT '最近30天的下单金额'
) COMMENT '交易域品牌粒度订单最近7/30日汇总事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_brand_order_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

装载语句这里就省略了，只不过就是 group by 的字段改成了 user_id和sku_id。

这样，我们最终的派生指标分类就可以分为两类了，最终根据统计周期只需要建 4 张表即可：

1.0.5、历史至今的汇总表

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`                BIGINT COMMENT '下单次数',
    `order_num`                  BIGINT COMMENT '下单件数',
    `order_total_amount`         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');

装载数据（区分首日和每日）

-- 数据装载
-- 2020-06-14
insert overwrite table dws_trade_user_order_td partition(dt='2020-06-14')
select
    user_id,
       min(date_id),
       max(date_id),
       count(distinct (order_id)),
       sum(sku_num),
       sum(split_total_amount)
from dwd_trade_order_detail_inc
group by user_id;
-- 2020-06-15
insert overwrite table dws_trade_user_order_td partition(dt='2020-06-15')
select
    user_id,
       min(order_date_first),
       max(order_date_last),
       sum(order_count),
       sum(order_num),
       sum(order_total_amount)
from
(
        select
        user_id,
           order_date_first,
           order_date_last,
           order_count,
           order_num,
           order_total_amount
    from dws_trade_user_order_td
    where dt=date_sub('2020-06-15',1)
    union all
    select
            user_id,
           '2020-06-15',
           '2020-06-15',
           count(distinct(order_id)) order_count_1d,
           sum(sku_num) order_num_1d,
           sum(split_total_amount) order_total_amount_1d
    from dwd_trade_order_detail_inc
    where dt='2020-06-15'
    group by user_id
)t1
group by user_id;

1.0.6、DWS 层设计总结

（1）1d 表

表结构：行由派生指标的统计粒度决定，列由统计粒度id和派生指标决定。

分区：按天分区，每天存放的是当天明细的汇总结果。

数据装载：找到 DWD 层与之对应的明细表，从明细表拿一个分区的数据进行汇总，汇总之后放到汇总表的当天分区。

（2）nd 表

表结构：行和列和 1d 表基本一致，无非就是把派生指标分为 7d 和 30d。

分区：按天分区，每天存放的是截止当前最近n天的汇总结果。

数据装载：优先去 DWS 层的 1d 表中去取数据，直接拿30个分区的数据进行聚合（聚合时用sum（if）把7d的数据和30d的数据区分开来，聚合之后放到汇总表的当天分区。

（3）td 表

表结构：行和列还是分别根据粒度和派生指标决定。

分区：按天分区，每天存放的是历史截止当前的汇总结果。

数据装载：首日需要从 DWS 层的 1d 表中或者 DWD 中的明细表中获取数据。第二天之后从 td 表中拿到前一个分区的数据，然后再从 1d 表或者 DWD 中的明细表找到今天的汇总结果，然后把这两部分数据进行 union all 得到截止今天的汇总结果。

1.1、最近1日汇总表

1.1.1、交易域用户商品粒度订单最近1日汇总表

建表语句

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

这张表可以大概分为两部分：维度字段和度量字段。其中度量字段中的统计粒度是必须的，退化字段是为了方便我们后期使用添加的（也可以不添加），而度量字段除了 ADS 层要求的，我们这里也“前瞻性”地添加了一些度量指标，防止后期该表麻烦，而且添加几个字对计算开销没什么影响，毕竟一张表里的聚合函数只会取一次数据。

分区我们按天分区，每天存储的内容都是当天的汇总结果。

数据装载（区分首日/每日装载）

因为我们在首日装载的时候有大量的历史数据（从属于不同的分区），所以我们要动态分区来装载。

首日装载：

我们在建表的时候退化了很多商品维度的字段，所以我们在装载数据的时候就应该把这部分数据通过 sku_id join 到一起，而我们 dwd 中存在大量历史的数据，所以用 14 号的商品信息去关联历史上的下单记录很可能对应不上，所以我们需要判断 null。

我们在搭建数仓的时候，历史的订单数据（事实）是有的，但是历史的商品信息（维度）是没有的，所以我们这里只能用首日的商品信息去关联历史的下单信息。

set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_user_sku_order_1d partition(dt)
select
    user_id,
    id,
    sku_name,
    category1_id,
    category1_name,
    category2_id,
    category2_name,
    category3_id,
    category3_name,
    tm_id,
    tm_name,
    order_count_1d,
    order_num_1d,
    order_original_amount_1d,
    activity_reduce_amount_1d,
    coupon_reduce_amount_1d,
    order_total_amount_1d,
    dt
from
(
    select
        dt,
        user_id,
        sku_id,
        count(*) order_count_1d,
        sum(sku_num) order_num_1d,
        sum(split_original_amount) order_original_amount_1d,
        sum(nvl(split_activity_amount,0.0)) activity_reduce_amount_1d,
        sum(nvl(split_coupon_amount,0.0)) coupon_reduce_amount_1d,
        sum(split_total_amount) order_total_amount_1d
    from dwd_trade_order_detail_inc
    group by dt,user_id,sku_id
)od
left join
(
    select
        id,
        sku_name,
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        tm_id,
        tm_name
    from dim_sku_full
    where dt='2020-06-14'
)sku
on od.sku_id=sku.id;

每日装载：

insert overwrite table dws_trade_user_sku_order_1d partition(dt='2020-06-15')
select
    user_id,
    id,
    sku_name,
    category1_id,
    category1_name,
    category2_id,
    category2_name,
    category3_id,
    category3_name,
    tm_id,
    tm_name,
    order_count,
    order_num,
    order_original_amount,
    activity_reduce_amount,
    coupon_reduce_amount,
    order_total_amount
from
(
    select
        user_id,
        sku_id,
        count(*) order_count,
        sum(sku_num) order_num,
        sum(split_original_amount) order_original_amount,
        sum(nvl(split_activity_amount,0)) activity_reduce_amount,
        sum(nvl(split_coupon_amount,0)) coupon_reduce_amount,
        sum(split_total_amount) order_total_amount
    from dwd_trade_order_detail_inc
    where dt='2020-06-15'
    group by user_id,sku_id
)od
left join
(
    select
        id,
        sku_name,
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        tm_id,
        tm_name
    from dim_sku_full
    where dt='2020-06-15'
)sku
on od.sku_id=sku.id;

离线数仓（九）【DWS 层开发】（3）https://developer.aliyun.com/article/1532437

离线数仓（九）【DWS 层开发】（2）

V3.0

1.0.5、历史至今的汇总表

1.0.6、DWS 层设计总结

（1）1d 表

（2）nd 表

（3）td 表

1.1、最近1日汇总表

1.1.1、交易域用户商品粒度订单最近1日汇总表

建表语句

数据装载（区分首日/每日装载）

热门文章

最新文章

相关课程

相关电子书

相关实验场景

离线数仓（九）【DWS 层开发】（2）

V3.0

1.0.5、历史至今的汇总表

1.0.6、DWS 层设计总结

（1）1d 表

（2）nd 表

（3）td 表

1.1、最近1日汇总表

1.1.1、 交易域用户商品粒度订单最近1日汇总表

建表语句

数据装载（区分首日/每日装载）

热门文章

最新文章

相关课程

相关电子书

相关实验场景

1.1.1、交易域用户商品粒度订单最近1日汇总表