【电商数仓】数仓搭建之DIM维度层(商品、优惠券、活动、地区、时间维度表)
文章目录
- 零 DIM层最终建模结果
- 一 商品维度表(全量)
- 1 商品维度表
- 2 建模过程分析
- 3 建表语句
- 4 装载数据
- (1)逐步分析
- (2)完整装载sql
- 二 优惠券维度表(全量)
- 1 建表语句
- 2 数据装载
- 三 活动维度表(全量)
- 1 建表语句
- 2 数据装载
- 四 地区维度表(特殊)
- 1 建表语句
- 2 数据装载
- 五 时间维度表(特殊)
- 1 建表语句
- 2 数据装载
- (1)创建临时表格
- (2)上传到HDFS
- (3)导入
- (4)校验
零 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<struct<attr_id, attr_name, value_id, value_name>>,这张表重新以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<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_info/'
TBLPROPERTIES ("orc.compress"="snappy");
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'
),
说明:将这张表作为一张临时表格,后面调用sku指的就是上面()中的内容,共十列。
join spu表
spu as
(
select
id,
spu_name
from ods_spu_info
where dt='2020-06-14'
),
category3
c3 as
(
select
id,
name,
category2_id
from ods_base_category3
where dt='2020-06-14'
),
连接category2
c2 as
(
select
id,
name,
category1_id
from ods_base_category2
where dt='2020-06-14'
),
join category1
c1 as
(
select
id,
name
from ods_base_category1
where dt='2020-06-14'
),
连接tm
tm as
(
select
id,
tm_name
from ods_base_trademark
where dt='2020-06-14'
),
连接平台属性,此表粒度比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
),
连接销售属性
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;
(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;
二 优惠券维度表(全量)
优惠券维度表分区:
直接每日将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");
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';
三 活动维度表(全量)
来源于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");
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;
以上三张维度表,每天数据进来后,直接插入就可以,数据之间彼此没有干扰。
四 地区维度表(特殊)
导入一次即可。
数据来源为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");
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;
五 时间维度表(特殊)
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");
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/'
(2)上传到HDFS
将数据文件<data_info.txt>上传到HFDS上临时表指定路径/warehouse/gmall/tmp/tmp_dim_date_info/
(3)导入
执行以下语句将其导入时间维度表
insert overwrite table dim_date_info select * from tmp_dim_date_info;
(4)校验
检查数据是否导入成功
select * from dim_date_info;