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

离线数仓（七）【DIM 层开发】（1）https://developer.aliyun.com/article/1532403

4.1.2、装载语句

这张表的装载我们不能再用简单的 load 语句了，因为我们现在不再是通过文件映射到表格了。我们得通过 insert + select 来进行装载，select 的当然就是我们的 ODS 层的原始数据表了，而且 insert 时需要指定分区。

这就需要我们从 ODS 层中和我们商品相关的所有表（主维表和相关维表）中获取了，而且是拿到这些表当天分区的数据，然后写入到当天的维度表中。

-- 数据装载
-- 2020-06-14
-- sku_info 中的数据
select
    id,
    spu_id,
    price,
    sku_name,
    sku_desc,
    weight,
    tm_id,
    category3_id,
    is_sale,
    create_time
from ods_sku_info_full
where dt='2020-06-14';
 
-- spu_info 中的数据
select
    id,
    spu_name
from ods_spu_info_full
where dt='2020-06-14';
 
-- base_category3 中的数据,通过category2_id和base_category2产生关联
select
    id,
    name,
    category2_id
from ods_base_category3_full
where dt='2020-06-14';
 
-- base_category2 中的数据,通过category1_id和base_category1产生关联
select
    id,
    name,
    category1_id
from ods_base_category2_full
where dt='2020-06-14';
 
select
    id,
    name
from ods_base_category1_full
where dt='2020-06-14';
 
select
    id,
    tm_name
from ods_base_trademark_full
where dt='2020-06-14';
 
select
    sku_id,
collect_set(named_struct("attr_id",attr_id,"value_id",value_id,"attr_name",attr_name,"value_name",value_name))
from ods_sku_attr_value_full
where dt='2020-06-14'
group by sku_id;
 
select
        sku_id,
collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs
    from ods_sku_sale_attr_value_full
    where dt='2020-06-14'
    group by sku_id;

对于平台属性名和平台属性值，我们要把同一个 sku_id 的平台属性名和平台属性值放到同一个结构体数组中：

select
    sku_id,
    collect_set(named_struct("attr_id",attr_id,"value_id",value_id,"attr_name",attr_name,"value_name",value_name))
from ods_sku_attr_value_full
where dt='2020-06-14'
group by sku_id;

销售属性名和销售属性值也是一样；最后我们一共从这 8 张和商品信息相关的表中来获取我们需要的字段，但是我们总不能就这么 join 吧，那可读性太差了！我们学习一种新的语法（CTE：common table expression）：

with
sku as
(
    select
        id,
        price,
        sku_name,
        sku_desc,
        weight,
        is_sale,
        spu_id,
        category3_id,
        tm_id,
        create_time
    from ods_sku_info_full
    where dt='2020-06-14'
),
spu as
(
    select
        id,
        spu_name
    from ods_spu_info_full
    where dt='2020-06-14'
),
c3 as
(
    select
        id,
        name,
        category2_id
    from ods_base_category3_full
    where dt='2020-06-14'
),
c2 as
(
    select
        id,
        name,
        category1_id
    from ods_base_category2_full
    where dt='2020-06-14'
),
c1 as
(
    select
        id,
        name
    from ods_base_category1_full
    where dt='2020-06-14'
),
tm as
(
    select
        id,
        tm_name
    from ods_base_trademark_full
    where dt='2020-06-14'
),
attr as
(
    select
        sku_id,
        collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs
    from ods_sku_attr_value_full
    where dt='2020-06-14'
    group by sku_id
),
sale_attr as
(
    select
        sku_id,
        collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs
    from ods_sku_sale_attr_value_full
    where dt='2020-06-14'
    group by sku_id
)
insert overwrite table dim_sku_full partition(dt='2020-06-14')
select
    sku.id,
    sku.price,
    sku.sku_name,
    sku.sku_desc,
    sku.weight,
    sku.is_sale,
    sku.spu_id,
    spu.spu_name,
    sku.category3_id,
    c3.name,
    c3.category2_id,
    c2.name,
    c2.category1_id,
    c1.name,
    sku.tm_id,
    tm.tm_name,
    attr.attrs,
    sale_attr.sale_attrs,
    sku.create_time
from sku
left join spu on sku.spu_id=spu.id
left join c3 on sku.category3_id=c3.id
left join c2 on c3.category2_id=c2.id
left join c1 on c2.category1_id=c1.id
left join tm on sku.tm_id=tm.id
left join attr on sku.id=attr.sku_id
left join sale_attr on sku.id=sale_attr.sku_id;

可以看到，我们在 join 这 8 张表的时候，先从我们的主维表（sku）去查询然后再和其它维表去 left join 来保证结果中包含所有的 sku 信息。而且我们可以看到每张表都能直接的（通过和 sku 表中的字段）或者间接的（比如 1级category和2级category产生关联，2级category再和3级category产生关联，最后3级category直接和sku通过公共字段产生关联）和我们的主维表产生关联。

而且我们这里用的是 insert overwrite，为的是保持任务的幂等性，比如说，我们用 insert overwrite 执行这个任务多少次它的结果都是一样的，也就不用担心执行过程失败重新执行时数据重复的问题；但是如果我们用 insert into 的话，我们这个分区的数据就可能会发生重复。

1.5、优惠券维度表设计

1.5.1、确认维度（表）

确认维度在设计事实表的时候也有，但事实表的主要目标是存储业务事实的度量值，所以它只会留一个维度外键；而维度表则更侧重于描述业务事实并提供丰富的维度属性以支持分析。这里我们确认维度就是确认这张优惠券维度表的维度属性。

在确认维度时，我们需要思考哪些属性对于理解业务事实是有价值的，并且这些属性是否能够帮助我们回答可能的分析问题。这通常涉及到对业务领域的深入了解和对数据需求的仔细分析。

1.5.2、确认主维表和相关维表

和优惠券相关的维度表只有一张：ods_coupon_info_full ，所以我们的优惠券维度表的大部分维度属性也都将来自这里，所以我们直接进入下一步，确认维度属性：

1.5.3、确认优惠券维度属性

确认维度属性这一步要做的无非就是确认从维度表中拿哪些字段，要不要加工。

我们这里要做的就是对这张表中的字典编码字段进行加工（使用明确的文字说明或者编码和文字共存），还有对满减规则做一个加工（尽量沉淀出通用的维度属性）

1.6、优惠券维度表实现

1.6.1、建表语句

DROP TABLE IF EXISTS dim_coupon_full;
CREATE EXTERNAL TABLE dim_coupon_full
(
    `id`               STRING COMMENT '购物券编号',
    `coupon_name`      STRING COMMENT '购物券名称',
    `coupon_type_code` STRING COMMENT '购物券类型编码',
    `coupon_type_name` STRING COMMENT '购物券类型名称',
    `condition_amount` DECIMAL(16, 2) COMMENT '满额数',
    `condition_num`    BIGINT COMMENT '满件数',
    `activity_id`      STRING COMMENT '活动编号',
    `benefit_amount`   DECIMAL(16, 2) COMMENT '减金额',
    `benefit_discount` DECIMAL(16, 2) COMMENT '折扣',
    `benefit_rule`     STRING COMMENT '优惠规则:满元*减*元，满*件打*折',
    `create_time`      STRING COMMENT '创建时间',
    `range_type_code`  STRING COMMENT '优惠范围类型编码',
    `range_type_name`  STRING COMMENT '优惠范围类型名称',
    `limit_num`        BIGINT COMMENT '最多领取次数',
    `taken_count`      BIGINT COMMENT '已领取次数',
    `start_time`       STRING COMMENT '可以领取的开始日期',
    `end_time`         STRING COMMENT '可以领取的结束日期',
    `operate_time`     STRING COMMENT '修改时间',
    `expire_time`      STRING COMMENT '过期时间'
) COMMENT '优惠券维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_coupon_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

可以看到我们在主维表的基础上对一些字段进行了加工和沉淀，同样根据 dt 字段进行分区。

1.6.2、加载语句

insert overwrite table dim_coupon_full partition(dt='2020-06-14')
select
    id,
    coupon_name,
    coupon_type,
    coupon_dic.dic_name,
    condition_amount,
    condition_num,
    activity_id,
    benefit_amount,
    benefit_discount,
    case coupon_type
        when '3201' then concat('满',condition_amount,'元减',benefit_amount,'元')
        when '3202' then concat('满',condition_num,'件打',10*(1-benefit_discount),'折')
        when '3203' then concat('减',benefit_amount,'元')
    end benefit_rule,
    create_time,
    range_type,
    range_dic.dic_name,
    limit_num,
    taken_count,
    start_time,
    end_time,
    operate_time,
    expire_time
from
(
    select
        id,
        coupon_name,
        coupon_type,
        condition_amount,
        condition_num,
        activity_id,
        benefit_amount,
        benefit_discount,
        create_time,
        range_type,
        limit_num,
        taken_count,
        start_time,
        end_time,
        operate_time,
        expire_time
    from ods_coupon_info_full
    where dt='2020-06-14'
)ci
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-14'
    and parent_code='32'
)coupon_dic
on ci.coupon_type=coupon_dic.dic_code
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-14'
    and parent_code='33'
)range_dic
on ci.range_type=range_dic.dic_code;

1.7、活动维度表

1.7.1、确认维度（表）

这里其实就是理解业务系统，知道我们需要哪些维度信息。

1.7.2、查找主维表和相关维表

和活动相关的表一共有两张：ods_activity_info_full 和 ods_activity_rule_full ，这里我们选择粒度更小的 ods_activity_rule 作为主维表，ods_activity_info_full 为相关维表。

1.7.3、确认活动维度属性

和优惠券维度属性一样，我们这里也要对一些字段进行沉淀，比如活动规则，还有 activity_type 现在它是一个字典编码类型，我们也要给它进行加工（编码和文字共存）

1.7.4、建表语句

我们表的字段主要来自于主维表和相关维表，但是我们还添加了 activity_type_name 来对字典编码进行文字说明，还有benifit_rule 来对优惠规则进行沉淀。

DROP TABLE IF EXISTS dim_activity_full;
CREATE EXTERNAL TABLE dim_activity_full
(
    `activity_rule_id`   STRING COMMENT '活动规则ID',
    `activity_id`        STRING COMMENT '活动ID',
    `activity_name`      STRING COMMENT '活动名称',
    `activity_type_code` STRING COMMENT '活动类型编码',
    `activity_type_name` STRING COMMENT '活动类型名称',
    `activity_desc`      STRING COMMENT '活动描述',
    `start_time`         STRING COMMENT '开始时间',
    `end_time`           STRING COMMENT '结束时间',
    `create_time`        STRING COMMENT '创建时间',
    `condition_amount`   DECIMAL(16, 2) COMMENT '满减金额',
    `condition_num`      BIGINT COMMENT '满减件数',
    `benefit_amount`     DECIMAL(16, 2) COMMENT '优惠金额',
    `benefit_discount`   DECIMAL(16, 2) COMMENT '优惠折扣',
    `benefit_rule`       STRING COMMENT '优惠规则',
    `benefit_level`      STRING COMMENT '优惠级别'
) COMMENT '活动信息表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_activity_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

1.7.5、装载语句

同样，这张表依然是每天从这张表相关的两张维表拿到分区数据进行处理后直接写进我们这张维度表的分区。

insert overwrite table dim_activity_full partition(dt='2020-06-14')
select
    rule.id,
    info.id,
    activity_name,
    rule.activity_type,
    dic.dic_name,
    activity_desc,
    start_time,
    end_time,
    create_time,
    condition_amount,
    condition_num,
    benefit_amount,
    benefit_discount,
    case rule.activity_type
        when '3101' then concat('满',condition_amount,'元减',benefit_amount,'元')
        when '3102' then concat('满',condition_num,'件打',10*(1-benefit_discount),'折')
        when '3103' then concat('打',10*(1-benefit_discount),'折')
    end benefit_rule,
    benefit_level
from
(
    select
        id,
        activity_id,
        activity_type,
        condition_amount,
        condition_num,
        benefit_amount,
        benefit_discount,
        benefit_level
    from ods_activity_rule_full
    where dt='2020-06-14'
)rule
left join
(
    select
        id,
        activity_name,
        activity_type,
        activity_desc,
        start_time,
        end_time,
        create_time
    from ods_activity_info_full
    where dt='2020-06-14'
)info
on rule.activity_id=info.id
left join
(
    select
        dic_code,
        dic_name
    from ods_base_dic_full
    where dt='2020-06-14'
    and parent_code='31'
)dic
on rule.activity_type=dic.dic_code;

1.8、地区维度表

之后我们的维表设计直接两步：查找主维表和相关维表、确定维度属性。至于确认维度这一步因为我们已经熟悉了业务过程，比如这里地区表相关的业务过程我们能够想到，肯定和交易域、用户域有关，所以直接跳过。

1.8.1、查找主维表和相关维表

和地区相关的表同样只有两张：ods_base_province_full 和 ods_base_region_full ，所以主维表当然选择粒度更细的 ods_base_province_full 。

1.8.2、确认维度属性

这里的维度属性主要以主维表为主，然后我们需要对主维表当中的 region_id 字段和 ods_base_region_full 进行一个 left join 操作使得这个字段的意义更加完整。

1.8.3、建表语句

尽管我们的地区信息十分稳定，所以我们既可以选择每天全量同步，反正这个数据量也没多大，但也可以只保存一份。

DROP TABLE IF EXISTS dim_province_full;
CREATE EXTERNAL TABLE dim_province_full
(
    `id`            STRING COMMENT 'id',
    `province_name` STRING COMMENT '省市名称',
    `area_code`     STRING COMMENT '地区编码',
    `iso_code`      STRING COMMENT '旧版ISO-3166-2编码，供可视化使用',
    `iso_3166_2`    STRING COMMENT '新版IOS-3166-2编码，供可视化使用',
    `region_id`     STRING COMMENT '地区id',
    `region_name`   STRING COMMENT '地区名称'
) COMMENT '地区维度表'
    PARTITIONED BY (`dt` STRING)
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_province_full/'
    TBLPROPERTIES ('orc.compress' = 'snappy');

1.8.4、加载语句

insert overwrite table dim_province_full partition(dt='2020-06-14')
select
    province.id,
    province.name,
    province.area_code,
    province.iso_code,
    province.iso_3166_2,
    region_id,
    region_name
from
(
    select
        id,
        name,
        region_id,
        area_code,
        iso_code,
        iso_3166_2
    from ods_base_province_full
    where dt='2020-06-14'
)province
left join
(
    select
        id,
        region_name
    from ods_base_region_full
    where dt='2020-06-14'
)region
on province.region_id=region.id;

离线数仓（七）【DIM 层开发】（3）https://developer.aliyun.com/article/1532406

离线数仓（七）【DIM 层开发】（2）

4.1.2、装载语句

1.5、优惠券维度表设计

1.5.1、确认维度（表）

1.5.2、确认主维表和相关维表

1.5.3、确认优惠券维度属性

1.6、优惠券维度表实现

1.6.1、建表语句

1.6.2、加载语句

1.7、活动维度表

1.7.1、确认维度（表）

1.7.2、查找主维表和相关维表

1.7.3、确认活动维度属性

1.7.4、建表语句

1.7.5、装载语句

1.8、地区维度表

1.8.1、查找主维表和相关维表

1.8.2、确认维度属性

1.8.3、建表语句

1.8.4、加载语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

离线数仓（七）【DIM 层开发】（2）

4.1.2、装载语句

1.5、优惠券维度表设计

1.5.1、确认维度（表）

1.5.2、确认主维表和相关维表

1.5.3、确认优惠券维度属性

1.6、优惠券维度表实现

1.6.1、建表语句

1.6.2、加载语句

1.7、活动维度表

1.7.1、确认维度（表）

1.7.2、查找主维表和相关维表

1.7.3、确认活动维度属性

1.7.4、建表语句

1.7.5、装载语句

1.8、地区维度表

1.8.1、查找主维表和相关维表

1.8.2、确认维度属性

1.8.3、建表语句

1.8.4、加载语句

热门文章

最新文章

相关课程

相关电子书

相关实验场景