简介
DIM 层主要是对相关状态数据的总结，我们主要关键点是对字段（维度）的确定
关联方式：
join 方式需要有关联条件
A B C D E 表示数据，空白表示空的数据，C是两张表的关联条件
Left Join红色数据 黄色数据
Right Join红色数据 绿色数据
Full Join 红色数据 黄色数据 绿色数据
Join红色数据
- Union方式需要两个表列数相同，具有相近属性的列
UnionUnion All
应用场景：
Left Join
用法：当你想要从左表（A）获取所有记录，并且希望包含与右表（B）匹配的记录，即使右表没有匹配时也会返回左表的记录，右表则显示空
Right Join
用法：与 Left Join 相似，但从右表获取所有记录，并连接左表中的匹配记录。
Full Join
用法：获取两个表中的所有记录，无论是否有匹配。
Union
用法：当你需要从两个查询中获取所有唯一的结果。
Union All
用法：类似于 Union，但包括重复行。
营销坑位维度表
原始业务数据库中只有promotion_pos表与营销坑位维度相关，从ods_promotion_pos_full表中筛选2022-06-08分区的数据，选择所须字段写入dim_promotion_pos_full表的2022-06-08分区即可
insert overwrite table dim_promotion_pos_full partition (dt = '2022-06-08')
select
`id` , -- STRING COMMENT '营销坑位ID',
`pos_location` , -- STRING COMMENT '营销坑位位置',
`pos_type` , -- STRING COMMENT '营销坑位类型 ',
`promotion_type` , -- STRING COMMENT '营销类型',
`create_time` , -- STRING COMMENT '创建时间',
`operate_time` -- STRING COMMENT '修改时间'
from ods_promotion_pos_full
where dt = '2022-06-08';营销渠道维度表
原始业务数据库中只有promotion_refer表与营销渠道维度相关，从ods_promotion_refer_full表中筛选2022-06-08分区的数据，选取所须字段写入dim_promotion_refer_full表的2022-06-08分区即可
insert overwrite table dim_promotion_refer_full partition (dt = '2022-06-08')
select
`id` , -- STRING COMMENT '营销坑位ID',
`refer_name` , -- STRING COMMENT '营销渠道名称',
`create_time` , -- STRING COMMENT '创建时间',
`operate_time` -- STRING COMMENT '修改时间'
from ods_promotion_refer_full
where dt = '2022-06-08';地区维度表
原始业务数据库中与地区相关的表有base_province和base_region，二者通过region_id产生联系。下单等业务过程相关的表中都通过province_id字段与地区维度产生关联，显然base_province应为主维表
insert overwrite table dim_province_full partition (dt = '2022-06-08')
select
prv.`id` , -- STRING COMMENT '省份ID',
`province_name` , -- STRING COMMENT '省份名称',
`area_code` , -- STRING COMMENT '地区编码',
`iso_code` , -- STRING COMMENT '旧版国际标准地区编码，供可视化使用',
`iso_3166_2` , -- STRING COMMENT '新版国际标准地区编码，供可视化使用',
`region_id` , -- STRING COMMENT '地区ID',
`region_name` -- STRING COMMENT '地区名称'
from (
select
`id` ,
name `province_name` ,
`area_code` ,
`iso_code` ,
`iso_3166_2` ,
`region_id`
from ods_base_province_full
where dt = '2022-06-08'
) prv
left join (
select
id,
region_name
from ods_base_region_full
where dt = '2022-06-08'
) area on prv.region_id = area.id;日期维度表
时间维度表的数据并不是来自于业务系统，而是手动写入，并且由于时间维度表数据的可预见性，无须每日导入，一般可一次性导入一年的数据
代码语言：shell复制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');
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/';
insert overwrite table dim_date select * from tmp_dim_date_info;商品维度表
商品维度相关的业务表有八张：
sku_info，spu_info，base_trademark，base_category1_info，base_category2_info，base_category3_info，sku_attr_value，sku_sale_attr_value
这些表都做了全量采集，在ODS层有相应的原始表与之对应。基于维度建模理论，我们要确定主维表，将商品维度相关的原始表关联起来。维度表的粒度与主维表保持一致，后者的主键就是维度表的唯一标识。
insert overwrite table dim_sku_full partition (dt = '2022-06-08')
select
sku.`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` , -- COMMENT '平台属性',
`sku_sale_attr_values` , --COMMENT '销售属性',
`create_time` -- STRING COMMENT '创建时间'
from (
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 = '2022-06-08'
) sku
left join (
select
id,
spu_name
from ods_spu_info_full
where dt = '2022-06-08'
) spu on sku.spu_id = spu.id
left join (
select
id,
tm_name
from ods_base_trademark_full
where dt = '2022-06-08'
) tm on sku.tm_id = tm.id
left join (
select
id,
name category3_name,
category2_id
from ods_base_category3_full
where dt = '2022-06-08'
) c3 on sku.category3_id = c3.id
left join (
select
id,
name category2_name,
category1_id
from ods_base_category2_full
where dt = '2022-06-08'
) c2 on c3.category2_id = c2.id
left join (
select
id,
name category1_name
from ods_base_category1_full
where dt = '2022-06-08'
) c1 on c2.category1_id = c1.id
left join (
select
sku_id,
collect_list(named_struct('attr_id' , attr_id ,'value_id' , value_id,'attr_name' , attr_name ,'value_name' , value_name)) sku_attr_values
from ods_sku_attr_value_full
where dt = '2022-06-08'
group by sku_id
) sav on sku.id = sav.sku_id
left join (
select
sku_id,
collect_list(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)) sku_sale_attr_values
from ods_sku_sale_attr_value_full
where dt = '2022-06-08'
group by sku_id
) ssav on sku.id = ssav.sku_id;活动维度表
活动相关的原始业务表有activity_rule、activity_info，此外，为了获取活动类型名称，还需要关联字典表。activity_rule中记录了活动的规则描述，activity_info记录了活动描述，用户下单时，每条明细记录都可能参与活动，order_detail_activity（订单活动关联表）记录了这些信息，该表中记录的是每个SKU具体参与了那次活动，满足了该活动的哪条规则，因此，要让事实表与活动维度进行关联，活动维度表的粒度应细化至活动规则粒度。综上，以activity_rule作为主表
insert overwrite table dim_activity_full partition (dt = '2022-06-08')
select
`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 '优惠级别'
from (
select
id `activity_rule_id` ,
`activity_id` ,
activity_type `activity_type_code` ,
`create_time` ,
`condition_amount` ,
`condition_num` ,
`benefit_amount` ,
`benefit_discount` ,
case `activity_type`
when '3101' then concat('满' , condition_amount , '元减' , benefit_amount ,'元')
when '3102' then concat('满' , condition_num , '件打' , benefit_discount ,'折')
when '3103' then concat('打' , benefit_discount , '折')
end `benefit_rule` ,
`benefit_level`
from ods_activity_rule_full
where dt = '2022-06-08'
) rule
left join (
select
`id`,
`activity_name`,
`activity_desc` ,
`start_time` ,
`end_time`
from ods_activity_info_full
where dt = '2022-06-08'
) info on rule.activity_id = info.id
left join (
select
dic_code,
dic_name activity_type_name
from ods_base_dic_full
where dt = '2022-06-08' and parent_code = '31'
) dic on rule.activity_type_code = dic.dic_code;优惠券维度表
我们只会用到字典表的编码和名称两个字段，单独建表意义不大，应做维度退化。优惠券相关的原始业务表只有coupon_info，不需要确定主维表和相关维表。因此，只须关联ods_coupon_info_full与ods_base_dic_full。此时ods_base_dic_full表需要当成两种场合来用（ dic_name coupon_type_name，dic_name range_type_name），则不可直接使用where条件一次判断（where是一行一行判断，条件不符时直接进行下一行的判断）。于是我们可以使用把该表当成两张表使用，进行两次连接
insert overwrite table dim_coupon_full partition (dt = '2022-06-28')
select
`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 '过期时间'
from (
select
`id` ,
`coupon_name` ,
coupon_type `coupon_type_code` ,
`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 , '件打' , benefit_discount ,'折')
when '3203' then concat('减' , benefit_amount , '元')
end `benefit_rule`,
`create_time` ,
range_type`range_type_code` ,
`limit_num` ,
`taken_count` ,
`start_time` ,
`end_time` ,
`operate_time` ,
`expire_time`
from ods_coupon_info_full
where dt = '2022-06-08'
) cp
left join (
select
dic_code,
dic_name coupon_type_name
from ods_base_dic_full
where dt = '2022-06-08' and parent_code = '32'
) dic1 on cp.coupon_type_code = dic1.dic_code
left join (
select
dic_code,
dic_name range_type_name
from ods_base_dic_full
where dt = '2022-06-08' and parent_code = '33'
) dic2 on cp.range_type_code = dic2.dic_code;用户维度表
由于一般电商网站的用户的基数过大，不适合每日全量进行数据的存储，这里我们采用拉链的思维（将变化的数据记录下来，保存每个用户有效期内最后的状态）进行数据的存储
拉链表的分区有两类：9999-12-31分区和普通日期分区。前者保存最新的维度数据，后者保存有效期截至分区日期的数据。
首日
业务数据库的user_info表中记录了全量最新的用户数据，全部进入9999-12-31分区。
每日
从数仓上线次日开始，采集user_info的变更数据，通常业务数据库的数据不会被删除，因而用户信息的变更只有新增和修改两类。新增的用户信息进入9999-12-31分区，修改的用户信息覆盖9999-12-31分区的记录，并将历史数据写入前一分区（如某用户信息在2022-06-09发生变化，当日的历史用户信息有效期截至2022-06-08，进入2022-06-08分区）。
数据装载
首日数据装载：筛选所须字段，对敏感信息加密脱敏，写入9999-12-31分区即可。
每日数据装载较为复杂。首先要考虑到，用户的数据可能在一天内多次变化，而拉链表中对于同一个用户每天至多只会维护一条数据，因此只须保留同一用户每天的最后一次更改。此处不需要区分新增和修改操作，我们只要获取同一用户当天最晚的一次操作就可以获取其最新状态。
接下来，要将当日发生变更的用户信息与历史所有用户的最新信息（拉链表9999-12-31分区的数据）合并起来。最后，在9999-12-31分区保留每个用户最新的状态，并将过期数据写入当日分区。
以下图片来自尚硅谷教学资料
# 首日装载
insert overwrite table dim_user_zip partition (dt = '9999-12-31')
select data.id,
concat(substr(data.name, 1, 1), '*') name,
if(data.phone_num regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\d{8}$',
concat(substr(data.phone_num, 1, 3), '*'), null) phone_num,
if(data.email regexp '^[a-zA-Z0-9_-] @[a-zA-Z0-9_-] (\.[a-zA-Z0-9_-] ) $',
concat('*@', split(data.email, '@')[1]), null) email,
data.user_level,
data.birthday,
data.gender,
data.create_time,
data.operate_time,
'2022-06-08' start_date,
'9999-12-31' end_date
from ods_user_info_inc
where dt = '2022-06-08' and type = 'bootstrap-insert';
# 每日装载
set hive.exec.dynamic.partition.mode=nonstrict
insert overwrite table dim_user_zip partition (dt)
select
`id` , -- STRING COMMENT '用户ID',
`name` , -- STRING COMMENT '用户姓名',
`phone_num` , -- STRING COMMENT '手机号码',
`email` , -- STRING COMMENT '邮箱',
`user_level` , -- STRING COMMENT '用户等级',
`birthday` , -- STRING COMMENT '生日',
`gender` , -- STRING COMMENT '性别',
`create_time` , -- STRING COMMENT '创建时间',
`operate_time` , -- STRING COMMENT '操作时间',
`start_date` , -- STRING COMMENT '开始日期',
`if`(rn == 2 , date_sub('2022-06-09' , 1) , '9999-12-31') `end_date`,
`if`(rn == 2 , date_sub('2022-06-09' , 1) , '9999-12-31')
from (
select
`id` ,
`name` ,
`phone_num` ,
`email` ,
`user_level` ,
`birthday` ,
`gender` ,
`create_time` ,
`operate_time` ,
`start_date` ,
`end_date` ,
row_number() over (partition by id order by start_date desc) rn
from (
select
`id` ,
`name` ,
`phone_num` ,
`email` ,
`user_level` ,
`birthday` ,
`gender` ,
`create_time` ,
`operate_time` ,
`start_date` ,
`end_date`
from dim_user_zip
where dt = '9999-12-31'
union all
select
`id` ,
`name` ,
`phone_num` ,
`email` ,
`user_level` ,
`birthday` ,
`gender` ,
`create_time` ,
`operate_time` ,
'2022-06-09' ,
'9999-12-31'
from (
select
data.`id` ,
data.`name` ,
data.`phone_num` ,
data.`email` ,
data.`user_level` ,
data.`birthday` ,
data.`gender` ,
data.`create_time` ,
data.`operate_time` ,
row_number() over (partition by data.id order by ts desc) num
from ods_user_info_inc
where dt = '2022-06-09' and type in ('insert' , 'updata')
) a where num = 1
) t
) t1;
