离线数仓—DIM层设计开发
前言
前面完成了ODS层的设计开发,下面进行DIM层的设计和开发。
一、DIM层设计分析
1.设计要点
1.1 设计依据
DIM层的设计依据是维度建模理论,该层存储维度模型的维度表。
1.2 压缩类型
DIM层的数据存储格式为orc列式存储,优点是方便取数据。例如要取某个表的某几列,如果采用行式存储,要隔几个数据才取一个数,若采用列式存储,一列的数据都放在一块,可以很快的取出一列的数据。
DIM层的压缩类型选用snappy压缩,解压和压缩的速率都很快。
1.3 命名规范
DIM层表名的命名规范是dim_表名_全量表或者拉链表标识(full/zip)
2.业务总线矩阵
根据业务总线矩阵可以得知,总共有时间、用户、商品、地区等等共计11个维度。
注意的点:
1)其中某些维度与多个业务过程都有联系,例如时间维度,像这种与多种业务过程都有联系的维度只需要设计一个表即可。
2)某些维度只有一两个字段,这就没有必要单独建一个表,可以进行维度退化,将对应的字段添加到事实表中
3)上图中的支付方式、退单类型、退单原因类型、渠道、设备中的字段都很少,进行了维度退化,不单独为这些维度建表
二、DIM层设计实现
需要设计的维度表总共有6张,这里以商品维度表为例,具体说明建表过程。
1.商品维度表
1.1主维度和其他维度
在建商品维度表前,要先确定业务数据库中与商品相关的表有哪些,如下图:
我们在找到了所有跟商品有关的表之后,要确定一个主维表,让主维表作为主体,将其他维表的属性添加进去。这是商品维度,毫无疑问,SKU商品信息表是主维表,其他的表是其他维表。
但是不一定所有与商品有关的表都要用到,例如下图:
SKU平台属性表(是商品和平台属性的关系表)中包含了一些冗余字段,只需要SKU平台属性表这一个表即可满足要求,剩下两个表无需用到。同理右上角的部分也只需要一个SKU销售属性表即可。最终,商品维度表需要的表如下(黑色框部分):
1.2 确定维度表字段
确定字段需要依次从需要的业务数据库表中拿字段。
首先,从SKU信息表拿需要的字段:
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信息表拿需要的字段:
select
id,
spu_name
from ods_spu_info_full
where dt='2020-06-14'
从三级分类表、二级分类表、一级分类表拿需要的字段:
select
id,
name,
category2_id
from ods_base_category3_full
where dt='2020-06-14'
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'
从SKU销售属性表拿需要的数据:
从SKU平台属性表拿需要的数据:
上面所有表拿到的数据就组成了商品维度表的字段。
需要注意的是:平台属性和销售属性都是多值属性
按上图,方式二没办法使用,因为多值属性个数不固定,只能采用方式一。
方法一:首先STRUCT没办法满足需求,因为STRUCT也是需要确定个数的;
方法二:采用MAP,但是有一个问题是MAP的每个key的类型都是固定的,每个value的类型也是固定的
方法三:采用ARRAY[STRUCT<>]的方式,可以很好的解决问题,一个结构体对应一个属性,用这种方式还有另外一个好处就是能够存储更多的数据,除了可以存储name,还可以额外存储id
1.3 建表语句
根据上面确定的字段,确定对应的字段类型,写建表语句如下:
DROP TABLE IF EXISTS dim_sku_full;
CREATE EXTERNAL TABLE dim_sku_full
(
`id` STRING COMMENT 'sku_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<STRUCT<attr_id :STRING,value_id :STRING,attr_name :STRING,value_name:STRING>> COMMENT '平台属性',
`sku_sale_attr_values` ARRAY<STRUCT<sale_attr_id :STRING,sale_attr_value_id :STRING,sale_attr_name :STRING,sale_attr_value_name:STRING>> COMMENT '销售属性',
`create_time` STRING COMMENT '创建时间'
) COMMENT '商品维度表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dim/dim_sku_full/'
TBLPROPERTIES ('orc.compress' = 'snappy');
1.4 数据装载
数据流向:从ods中的全量表中读取当天的分区的数据,写入到该表当天的分区下
该表中大部分数据都可以直接从表里获得,但是有两个字段sku_attr_values和sku_sale_attr_values是ARRAY[STRUCT]类型,需要自己去构造。
最终的数据装载语句:
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;
注意:insert语句中的overwrite是因为可能装载数据时出错,如果重跑该装载语句,可能原先的表中已存在数据,所以要先清空再导入。
2.优惠券维度表
首先,我们从业务系统找到与优惠券相关的表,有上图这两张表,毫无疑问,优惠券信息表是主维表,其他是相关维表。
我们需要的字段:
2.1建表语句
其中购物券类型还需要一个coupon_type_name,范围类型还需要一个range_type_name,还需要一个优惠规则的字段,因此建表语句如下:
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');
2.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;
3.活动维度表
活动维度表相关的表:
3.1 建表语句
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');
3.2 数据装载
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;
4.地区维度表
很明显,省份表是主维表
4.1 建表语句
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');
4.2 数据装载
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;
5.日期维度表
日期维度表在业务系统中没有单独的表,需要自己去建
5.1 建表语句
DROP TABLE IF EXISTS dim_date;
CREATE EXTERNAL TABLE dim_date
(
`date_id` STRING COMMENT '日期ID',
`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/'
TBLPROPERTIES ('orc.compress' = 'snappy');
5.2 数据装载
因为日期维度表是手动创建的,它一般情况下是固定的,可以一次性导入几年的数据,因为时间一般情况下不会改变。
而且我们不可能一条一条手动往表中添加数据,一般将数据放到文件里,然后load到表中。
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/';
2)将数据文件data_info.txt上传到临时表/warehouse/gmall/tmp/tmp_dim_date_info中
3)执行sql语句将数据导入到时间维度表:insert overwrite table dim_date select * from tmp_dim_date_info;
注意:不能够直接将文件上传到日期维度表,这是因为日期维度表的格式是orc格式,无法正确识别txt文件里的数据,而表与表之间是可以相互转换的,因此使用了一个中间表进行转换。