• 【电商数仓】数仓搭建之DIM维度层（商品、优惠券、活动、地区、时间维度表）

    文章目录

    零 DIM层最终建模结果

    时间用户地区商品优惠券活动度量值
    订单√√√运费/优惠金额/原始金额/最终金额
    订单详情√√√√√√件数/优惠金额/原始金额/最终金额
    支付√√√支付金额
    加购√√√件数/金额
    收藏√√√次数
    评价√√√次数
    退单√√√√件数/金额
    退款√√√√件数/金额
    优惠券领用√√√次数

    一 商品维度表（全量）

    1 商品维度表

    商品维度表分区：

    在这里插入图片描述

    2 建模过程分析

    在这里插入图片描述

    需要将以上正方形圈起来的八张表建模成一张商品维度表，主键为sku_id。圆形圈起来的几张表可选，此项目不考虑这几张表。

    以ods_sku_info（id, spu_id, price, sku_name, sku_desc, weight, tm_id, category3_id, is_sale, create_time, dt）表为基础去join其他表【表一】

    根据ods_sku_info表中spu_id列可以获得ods_spu_info表中的内容（spu_name, category3_id,tm_id）【表二】

    根据ods_sku_info表中category3_id可以获得ods_base_category3表中的信息（name[cat3_name], category2_id）【表三】

    根据category2_id可以获取ods_base_category2表中的内容（name[cat2_name], category1_id）【表四】

    根据category1_id可以获取ods_base_category1表中的内容（name[cat1_name]）【表五】

    根据ods_sku_info表中tm_id列可以获得ods_base_trademark表中的内容（tm_name）【表六】

    目前已经整合了六张表，此外还需要整合销售属性和平台属性，以上六张表没有平台属性和销售属性的信息，所以分别去找平台属性和销售属性对应的表格（ods_sku_attr_value，ods_sku_sale_attr_value）。

    ods_sku_attr_value表中的内容（id, attr_id, value_id, sku_id, attr_name, value_name ），根据sku_id扩展开，这时粒度不再是sku_id，而是自己的id，一个属性一行，一个sku_id对应多个平台属性，所以sku_id不再是主键。从sku_id的角度去看平台属性，应该是将多行整合成一行，最终表现形式，array<平台属性>，即一个sku_id对应多个平台属性，正规写法为array>，这张表重新以sku_id为粒度。

    ods_sku_sale_attr_value表中的内容（id, sku_id, spu_id, sale_attr_value_id, sale_attr_id, sale_attr_name, sale_attr_value_name），同平台表，一个sku_id对应多个销售属性，即array<销售属性>

    以上就是商品维度表中的所有列，根据需求，其中不需要的列可以去除，再次进行精简。

    建模过程：找到最关键的八张表，将这八张表的信息以sku_id为粒度将它们整合在一起，商品维度表就应该以商品的id为粒度，所以主键就是sku_id，数据来源于原来关系建模表中的数据–ods层。

    3 建表语句

    DROP TABLE IF EXISTS dim_sku_info;
CREATE EXTERNAL TABLE dim_sku_info (
    `id` STRING COMMENT '商品id',
    `price` DECIMAL(16,2) COMMENT '商品价格',
    `sku_name` STRING COMMENT '商品名称',
    `sku_desc` STRING COMMENT '商品描述',
    `weight` DECIMAL(16,2) COMMENT '重量',
    `is_sale` BOOLEAN COMMENT '是否在售',
    `spu_id` STRING COMMENT 'spu编号',
    `spu_name` STRING COMMENT 'spu名称',
    `category3_id` STRING COMMENT '三级分类id',
    `category3_name` STRING COMMENT '三级分类名称',
    `category2_id` STRING COMMENT '二级分类id',
    `category2_name` STRING COMMENT '二级分类名称',
    `category1_id` STRING COMMENT '一级分类id',
    `category1_name` STRING COMMENT '一级分类名称',
    `tm_id` STRING COMMENT '品牌id',
    `tm_name` STRING COMMENT '品牌名称',
    `sku_attr_values` ARRAY> COMMENT '平台属性',
    `sku_sale_attr_values` ARRAY> COMMENT '销售属性',
    `create_time` STRING COMMENT '创建时间'
) COMMENT '商品维度表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dim/dim_sku_info/'
TBLPROPERTIES ("orc.compress"="snappy");

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    4 装载数据

    数据来源为ods层，ods层每天都会导入一些数据，需要去查看每天导入的数据是些什么样的数据

    在这里插入图片描述

    这八张表全部都是全量导入，每天库中都是全量信息，将每天的全量信息join到一起就是当天的全量信息，所以这八张表数据的导入方式就是将信息进行join和整理以后，直接每天导入到维度表中，维度表按照日期分区，所以维度表每天都是最新的维度信息。

    （1）逐步分析

    首先需要从ods表格中将当天所有信息查询出来，如插入06-14的数据，代码如下

    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
    where dt='2020-06-14'
),

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17

    说明：将这张表作为一张临时表格，后面调用sku指的就是上面（）中的内容，共十列。

    join spu表

    spu as
(
    select
        id,
        spu_name
    from ods_spu_info
    where dt='2020-06-14'
),

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    category3

    c3 as
(
    select
        id,
        name,
        category2_id
    from ods_base_category3
    where dt='2020-06-14'
),

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    连接category2

    c2 as
(
    select
        id,
        name,
        category1_id
    from ods_base_category2
    where dt='2020-06-14'
),

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    join category1

    c1 as
(
    select
        id,
        name
    from ods_base_category1
    where dt='2020-06-14'
),

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    连接tm

    tm as
(
    select
        id,
        tm_name
    from ods_base_trademark
    where dt='2020-06-14'
),

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    连接平台属性，此表粒度比sku小，所以需要聚合，将四列合并成一个结构体，最终呈现结果就是一列数据，使用named_struct()函数完成，再将sku合并成一个数组，使用collect_set()函数。

    核心思想：以sku为基础进行聚合。

    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
    where dt='2020-06-14'
    group by sku_id
),

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    连接销售属性

    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
    where dt='2020-06-14'
    group by sku_id
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    目前八张临时表格全部准备就绪，接下来以这八张表格为基础，进行如下操作

    insert overwrite table dim_sku_info 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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29

    （2）完整装载sql

    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
    where dt='2020-06-14'
),
spu as
(
    select
        id,
        spu_name
    from ods_spu_info
    where dt='2020-06-14'
),
c3 as
(
    select
        id,
        name,
        category2_id
    from ods_base_category3
    where dt='2020-06-14'
),
c2 as
(
    select
        id,
        name,
        category1_id
    from ods_base_category2
    where dt='2020-06-14'
),
c1 as
(
    select
        id,
        name
    from ods_base_category1
    where dt='2020-06-14'
),
tm as
(
    select
        id,
        tm_name
    from ods_base_trademark
    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
    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
    where dt='2020-06-14'
    group by sku_id
)
insert overwrite table dim_sku_info 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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106

    二 优惠券维度表（全量）

    在这里插入图片描述

    优惠券维度表分区：

    在这里插入图片描述

    直接每日将ods_coupon_info表中的数据直接导入到DIM层即可

    1 建表语句

    DROP TABLE IF EXISTS dim_coupon_info;
CREATE EXTERNAL TABLE dim_coupon_info(
    `id` STRING COMMENT '购物券编号',
    `coupon_name` STRING COMMENT '购物券名称',
    `coupon_type` STRING COMMENT '购物券类型 1 现金券 2 折扣券 3 满减券 4 满件打折券',
    `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 '折扣',
    `create_time` STRING COMMENT '创建时间',
    `range_type` STRING COMMENT '范围类型 1、商品 2、品类 3、品牌',
    `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_info/'
TBLPROPERTIES ("orc.compress"="snappy");

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    2 数据装载

    在这里插入图片描述

    insert overwrite table dim_coupon_info partition(dt='2020-06-14')
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
where dt='2020-06-14';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    三 活动维度表（全量）

    来源于ods_activity_info 和 ods_activity_rule 这两张表，同一个活动可能有多个活动规则，所以以rule的id为粒度来join，即创建activity_rule每条规则的维度表，里面再嵌套activity id

    活动维度表分区：

    在这里插入图片描述

    1 建表语句

    DROP TABLE IF EXISTS dim_activity_rule_info;
CREATE EXTERNAL TABLE dim_activity_rule_info(
    `activity_rule_id` STRING COMMENT '活动规则ID',
    `activity_id` STRING COMMENT '活动ID',
    `activity_name` STRING  COMMENT '活动名称',
    `activity_type` 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_level` STRING COMMENT '优惠级别'
) COMMENT '活动信息表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dim/dim_activity_rule_info/'
TBLPROPERTIES ("orc.compress"="snappy");

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    2 数据装载

    用activity_rule activity_id 去 join activity_info 的 id

    在这里插入图片描述

    insert overwrite table dim_activity_rule_info partition(dt='2020-06-14')
select
    ar.id,
    ar.activity_id,
    ai.activity_name,
    ar.activity_type,
    ai.start_time,
    ai.end_time,
    ai.create_time,
    ar.condition_amount,
    ar.condition_num,
    ar.benefit_amount,
    ar.benefit_discount,
    ar.benefit_level
from
(
    select
        id,
        activity_id,
        activity_type,
        condition_amount,
        condition_num,
        benefit_amount,
        benefit_discount,
        benefit_level
    from ods_activity_rule
    where dt='2020-06-14'
)ar
left join
(
    select
        id,
        activity_name,
        start_time,
        end_time,
        create_time
    from ods_activity_info
    where dt='2020-06-14'
)ai
on ar.activity_id=ai.id;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40

    以上三张维度表，每天数据进来后，直接插入就可以，数据之间彼此没有干扰。

    四 地区维度表（特殊）

    导入一次即可。

    数据来源为ods_base_province 和 ods_base_region 两张表。

    用ods_base_province 的region_id join ods_base_region 的id以后，将refion_name整合进来就可以了。

    地区维度表分区：

    在这里插入图片描述

    1 建表语句

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    2 数据装载

    insert overwrite table dim_base_province
select
    bp.id,
    bp.name,
    bp.area_code,
    bp.iso_code,
    bp.iso_3166_2,
    bp.region_id,
    br.region_name
from ods_base_province bp
join ods_base_region br on bp.region_id = br.id;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    五 时间维度表（特殊）

    ods层中没有时间维度表，时间维度表作用为某日是工作日还是节假日，如果工作日是星期几，节假日是什么节日，是假期的第几天等等诸如此类的信息。这些信息不需要看业务，看日历即可，所以这种表，在建库的时候，通常通过程序生成数据，一次性导入几年的数据。

    1 建表语句

    DROP TABLE IF EXISTS dim_date_info;
CREATE EXTERNAL TABLE dim_date_info(
    `date_id` STRING COMMENT '日',
    `week_id` STRING COMMENT '周ID',
    `week_day` STRING COMMENT '周几',
    `day` STRING COMMENT '每月的第几天',
    `month` STRING COMMENT '第几月',
    `quarter` STRING COMMENT '第几季度',
    `year` STRING COMMENT '年',
    `is_workday` STRING COMMENT '是否是工作日',
    `holiday_id` STRING COMMENT '节假日'
) COMMENT '时间维度表'
STORED AS ORC
LOCATION '/warehouse/gmall/dim/dim_date_info/'
TBLPROPERTIES ("orc.compress"="snappy");

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    2 数据装载

    （1）创建临时表格

    DROP TABLE IF EXISTS tmp_dim_date_info;
CREATE EXTERNAL TABLE tmp_dim_date_info (
    `date_id` STRING COMMENT '日',
    `week_id` STRING COMMENT '周ID',
    `week_day` STRING COMMENT '周几',
    `day` STRING COMMENT '每月的第几天',
    `month` STRING COMMENT '第几月',
    `quarter` STRING COMMENT '第几季度',
    `year` STRING COMMENT '年',
    `is_workday` STRING COMMENT '是否是工作日',
    `holiday_id` STRING COMMENT '节假日'
) COMMENT '时间维度表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/tmp/tmp_dim_date_info/'

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    （2）上传到HDFS

    将数据文件上传到HFDS上临时表指定路径/warehouse/gmall/tmp/tmp_dim_date_info/

    （3）导入

    执行以下语句将其导入时间维度表

    insert overwrite table dim_date_info select * from tmp_dim_date_info;

    • 1

    （4）校验

    检查数据是否导入成功

    select * from dim_date_info;

    • 1
  • 相关阅读:
    【开源三方库】crypto-js加密算法库的使用方法
    aspose-slides-22.5-jdk16
    数据结构之二叉树
    内存学习（4）：内存分类与常用概念3（ROM）
    逐字稿 | ViT论文逐段精读【论文精读】
    5.4 Windows驱动开发：内核通过PEB取进程参数
    Vue引入Echarts图表的使用
    Tensorflow2 环境搭建
    ubuntu 16.04成功安装meteor
    如何选择合适的汽车芯片ERP系统?
  • 原文地址：https://blog.csdn.net/weixin_43923463/article/details/127078748