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

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

2024-06-07 160

版权

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

本文涉及的产品

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

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

前言

上一个 DWD 层用了半个月时间，但是慢有慢的好处；今天开始 DWS 层的学习，目标是 4 月初把项目完成，完了赶紧从头回顾一遍项目。

今天操场跑了 20 分钟，顺便在这里记录一下，现在每周只有没早八的时候能跑一下了，近一年没有好好跑步了，这个习惯应该找回来了。

1、DWS 层开发

DWS 也叫数据汇总层，它的职责就是把一些计算耗时的公共中间结果提前保存起来（比如提前 join 两张表），用空间换时间！所以 DWS 层是依托于需求的（需求驱动），没有需求我们是不知道需要保存哪些中间结果的。

1.0、DWS 层设计

1.0.1、设计要点：

（1）DWS层的设计参考指标体系（原子、派生、衍生指标等）。一般能通过一张明细事实表聚合得到的，那就是派生指标；得不到就是衍生指标。

（2）DWS层的数据存储格式为ORC列式存储 + snappy压缩。

（3）DWS层表名的命名规范为dws_数据域_统计粒度_业务过程_统计周期（1d/nd/td），这里的统计粒度可以是多个

注：1d表示最近1日，nd表示最近n日，td表示历史至今。

我们接下来的汇总表的设计并不会把 1d、nd、td 这三个字段都放到一张表里，比如对于页面浏览这个业务过程，如果我们把三个字段都放到一张表里，那么当装载数据的时候，它需要从 DWD 的当天分区、前n天分区、历史分区中拿数据，这样的代价太大了，而如果我们把三个字段分开，一天的汇总结果放到一天的分区，当够了n天的时候，我们直接从 DWS 同层的 1d 中拿7天的就够了，而且 1d 的表已经帮我们聚合好了。尽管我们在学习 DWD 层的时候希望同层的表不要有依赖关系，但是在这里是可以的。

而且我们并不会按照统计周期去创建n张表，而是只创建两张表：统计周期为1的和统计周期为n的。

1.0.2、构建指标体系

下面我们根据 ADS 层商品域的交易主题下的需求来进行指标分析：

这里的度量值为 1 ，意思是我们并没有专门的维度供我们计算（度量值和维度的界限并不是那么清晰，并不是说度量值就非得是什么金额、下单件数等），我们的一行数据就代表一个度量值（这里指一个订单）。

1.0.3、抽取派生指标

我们需要列出所有需求的指标类型，然后从一堆需求中找到其中业务过程、统计粒度、统计周期相同的派生指标放到一张汇总表里。一般我们会把所有需求对应的派生指标都放到一张表里去分析：

比如上面的表格是我们把所有交易域的需求对应的派生指标放到了一起，可以看到，我们需要创建4张汇总表，但是考虑到不同的统计周期，我们这里需要创建 8 张汇总表。

首先至少得 3 个字段：品牌id，下单次数、下单人数。考虑到 ADS 层需要展示更详细的数据比如品牌名称，如果我们这里不加的话到时候还得用 DWS 层的表去和 DIM 层的维度表去 join 才能得到。所以我们这里尽量也做一个维度退化。毕竟我们分层的时候，DIM 层是同时跨越 DWD 和 DWS 层的：

关于分区，对于 1d 的汇总结果，它的当日分区放的就是当日的明细汇总结果。

1.0.4、最近1d和nd的汇总表

我们的下单次数和下单人数这两个派生指标是可以放到一张汇总表的：

CREATE EXTERNAL TABLE dws_trade_brand_order_1d
(
    `brand_id`                   STRING COMMENT '品牌id',
    `brand_name`                 STRING COMMENT '品牌名称',
    `order_count`                STRING COMMENT '下单次数',
    `order_user_count`                STRING COMMENT '下单人数'
) COMMENT '交易域品牌粒度订单最近1日汇总事实表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dws/dws_trade_brand_order_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

这张表显然十分简单，但是考虑到我们数仓的需求并不是一成不变的，比如现在增加两个需求：统计最近1/7/30日各品牌的下单件数、下单金额。显然这两个指标也都是派生指标，而且它们的业务过程（下单）、统计粒度（品牌）和统计周期（1/7/30）和我们这张表都是一样的，所以我们就需要修改这张表结构，但是修改表就意味着我们需要处理这张表原本的历史数据，显然十分麻烦。所以我们在最初建表的时候就应该尽可能避免将来的改表操作，为此，我们提供了两种方案：

1. 之前我们把所有业务过程、统计粒度和统计周期相同的派生指标会都放到一个汇总表里，现在我们可以为每个派生指标建一张汇总表，这样即使有新的派生指标加进来也不用担心对旧表的影响，但是这样我们的 DWS 层会出现大量的表，所以我们一般并不采用。

2. 我们可以“前瞻性”地把这张事实表（比如下单的事实表就是 order_detail）的度量值去预测将来可能用到的派生指标：

CREATE EXTERNAL TABLE dws_trade_brand_order_1d
(
    `brand_id`                   STRING COMMENT '品牌id',
    `brand_name`                 STRING COMMENT '品牌名称',
    `order_count`                BIGINT COMMENT '下单次数',
    `order_user_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');

这张表的数据装载也很简单：

insert overwrite table dws_trade_brand_order_1d partition (dt='2020-06-14')
select tm_id,
       tm_name,
       count(*),
       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;

关于下单的 1d 的汇总表已经设计完毕了，解下来就是 nd 汇总表的设计了。对于 nd ，它每天的分区里放的就是这一天最近 n 天的数据。

CREATE EXTERNAL TABLE dws_trade_brand_order_nd
(
    `brand_id`                   STRING COMMENT '品牌id',
    `brand_name`                 STRING COMMENT '品牌名称',
    `order_count_7d`                BIGINT COMMENT '下单次数',
    `order_user_count_7d`           BIGINT COMMENT '下单人数',
    `order_num_7d`                  BIGINT COMMENT '下单件数',
    `order_amount_7d`               DECIMAL(16,2) COMMENT '最近7天的下单金额',
    `order_count_30d`                BIGINT COMMENT '下单次数',
    `order_user_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');

关于 nd 表的数据从哪取？我们一般都是直接从它的 1d 表去拿的，如果实在不行（比如我们的需求中并没有 1d 的需求）那我们只能去 DWD 去拿了。

数据装载：我们只需要分别查询出 7d 和 30d 的数据直接 join 即可：

insert overwrite table dws_trade_brand_order_nd partition (dt='2020-06-14')
select xxxx
from
(
-- 7d
select
    brand_id,
    brand_name,
    sum(order_count),
    sum(order_user_count),
    sum(order_num),
    sum(order_amount)
from dws_trade_brand_order_1d
where dt>=date_sub('2020-06-14',6)
group by brand_id,brand_name
) d7
join
(
--30d
select
    brand_id,
    brand_name,
    sum(order_count),
    sum(order_user_count),
    sum(order_num),
    sum(order_amount)
from dws_trade_brand_order_1d
where dt>=date_sub('2020-06-14',29)
group by brand_id,brand_name
)d30
on d7.sku_id=d30.sku_id

这里，因为前 30 天的数据包含了前 7 天的数据，所以我们可以对这个 SQL 进行一个优化：

insert overwrite table dws_trade_brand_order_nd partition (dt='2020-06-14')
select
    brand_id,
    brand_name,
    sum(`if`(dt>=date_sub('2020-06-14',6),order_count,0)),
    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_amount,0)),
    sum(order_count),
    sum(order_user_count),
    sum(order_num),
    sum(order_amount)
from dws_trade_brand_order_1d
where dt>=date_sub('2020-06-14',29)
group by brand_id,brand_name;

这样我们就不用专门去查询近 7 天的数据，而且还少 join 了一次。但是这张表存在一个问题，我们之前在 1d 表中计算下单人数的时候对用户是去了重的，因为一个用户一天可能下单多次；按道理 7d 30d 同样需要对相同的用户进行去重，因为一个用户可能在多天中下单，所以我们这里需要解决这个同一用户重复计算的问题。

V2.0

我们可以通过改变这张表的粒度（改为用户品牌和用户品类粒度）来解决这个问题：

既然粒度已经是用户品牌粒度，那指标下单人数就不需要了，只需要在查询的时候直接去重即可。

CREATE EXTERNAL TABLE dws_trade_user_brand_order_1d
(
    `user_id`                    STRING COMMENT '用户id',
    `brand_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_user_brand_order_1d'
    TBLPROPERTIES ('orc.compress' = 'snappy');

装载语句只需要稍微改改即可：

insert overwrite table dws_trade_user_brand_order_1d partition (dt='2020-06-14')
select
       user_id,
       tm_id,
       tm_name,
       count(*),
       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;

修改 nd 表的建表语句：

CREATE EXTERNAL TABLE dws_trade_user_brand_order_nd
(
    `user_id`                    STRING COMMENT '用户id',
    `brand_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');

修改 nd 表的装载语句：

insert overwrite table dws_trade_user_brand_order_nd partition (dt='2020-06-14')
select
    user_id,
    brand_id,
    brand_name,
    sum(`if`(dt>=date_sub('2020-06-14',6),order_count,0)),
    sum(`if`(dt>=date_sub('2020-06-14',6),order_num,0)),
    sum(`if`(dt>=date_sub('2020-06-14',6),order_amount,0)),
    sum(order_count),
    sum(order_num),
    sum(order_amount)
from dws_trade_user_brand_order_nd
where dt>=date_sub('2020-06-14',29)
group by brand_id,brand_name;

现在，我们的指标（各品牌的下单件数和下单人数）就变成了：

最终我们的派生指标根据统计粒度分为两类：

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

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

前言

1、DWS 层开发

1.0、DWS 层设计

1.0.1、设计要点：

1.0.2、构建指标体系

1.0.3、抽取派生指标

1.0.4、最近1d和nd的汇总表

V2.0

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

前言

1、DWS 层开发

1.0、DWS 层设计

1.0.1、设计要点：

1.0.2、构建指标体系

1.0.3、抽取派生指标

1.0.4、最近1d和nd的汇总表

V2.0

热门文章

最新文章

相关课程

相关电子书

相关实验场景