前言
今天开始 DIM 层的开发，说开发好像有点不配，还只是学习阶段，离开发还有很长的路要走。
一个人想象自己不懂得的事很容易浪漫。 --《沉默的大多数》王小波
1、DIM 层开发
DIM层设计要点：
- DIM层的设计依据是维度建模理论，该层存储维度模型的维度表。
- DIM层的数据存储格式为orc列式存储+snappy压缩。
- DIM层表名的命名规范为dim_表名_全量表或者拉链表标识（full/zip）
我们在前面 ODS 层的开发时数据压缩用的是 gz ，为的是最大的压缩存储。但是这里，我们的 DIM 、DWD、DWS 层是之后用的最频繁的三层，所以我们必须保证它的读取和解压缩更快，所以我们选用 orc + snappy 压缩。
维度表我们大致可以分为两类：每日全量快照表和拉链表
DIM 层就是创建维度表的过程，所以我们需要先回顾一下维度建模的知识：
1.1、维度建模回顾
前面我们学习数据仓库构建流程的时候说 DWD 和 DIM 层是业务驱动的，因为我们的事实表（存在 DWD 层）取决于业务系统中业务过程，而我们的维度表（存在 DIM 层）取决于业务系统中的环境，它俩都和我们的指标没啥关系。
在设计维度模型之前我们需要构建业务总线矩阵，矩阵的行是一个个业务过程（具体说就是每行代表一个业务事实表），矩阵的列是一个个的维度（代表一个维度表），行列的交点表示业务过程与维度存在关联关系。
按照事务型事实表的设计流程我们就可以得到业务总线矩阵，因为我们的维度模型是以事实表为核心，而事实表主要就是事务型事实表。
这里只对红色框起来的部分维度进行建表，而蓝色框的维度我们会做一个维度退化。回顾一下维度退化的定义：
如果某些维度表的维度属性很少（比如支付方式表没有必要去单独创建一个维度表，因为它就一个支付方式字段），则可不创建该维度表，而把该表的维度属性直接增加到与之相关的事实表中，这个操作称为维度退化。
也就是说，我们直接把支付方式这个维度属性作为一个字段加到支付成功和退款成功这两个事实表当中，把退单类型作为一个字段做到退款成功这个事实表当中...
还有渠道、设备这两个维度属性也要做维度退化，尽管它俩和多张事实表有关系，但是与这两个维度属性相关联的业务过程基本都是日志当中的业务过程。
CREATE EXTERNAL TABLE ods_log_inc
(
`common` STRUCT<ar :STRING,ba :STRING,ch :STRING,is_new :STRING,md :STRING,mid :STRING,os :STRING,uid :STRING,vc
:STRING> COMMENT '公共信息',
`page` STRUCT<during_time :STRING,item :STRING,item_type :STRING,last_page_id :STRING,page_id
:STRING,source_type :STRING> COMMENT '页面信息',
`actions` ARRAY<STRUCT<action_id:STRING,item:STRING,item_type:STRING,ts:BIGINT>> COMMENT '动作信息',
`displays` ARRAY<STRUCT<display_type :STRING,item :STRING,item_type :STRING,`order` :STRING,pos_id
:STRING>> COMMENT '曝光信息',
`start` STRUCT<entry :STRING,loading_time :BIGINT,open_ad_id :BIGINT,open_ad_ms :BIGINT,open_ad_skip_ms
:BIGINT> COMMENT '启动信息',
`err` STRUCT<error_code:BIGINT,msg:STRING> COMMENT '错误信息',
`ts` BIGINT COMMENT '时间戳'
) COMMENT '活动信息表'
PARTITIONED BY (`dt` STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
LOCATION '/warehouse/gmall/ods/ods_log_inc/';我们在创建日志表的时候已经把业务过程和维度属性给放到一起了，没有必要再把维度属性 "渠道"、"设备" 再拆出来单独建个维度表。
1.2、维度表设计步骤
具体建模步骤看前面的博客：数据仓库（五）【数据仓库建模】
1.2.1、确认维度
也就确认创建哪些维度表，哪些维度属性不需要创建单独的维度表而是做维度退化，我们上面都分析清楚了。
1.2.2、确定主维表和相关维表
主维表和相关维表指的都是和维度相关的业务系统当中的表，然后通常情况下粒度最细的是主维表（比如商品这个维度，我们要去业务系统中找到和它相关的表，发现有购物车表、订单表、商品信息表、退单表... ），如果我们以订单表作为主维表，那一个订单中可能有多个订单，那就导致粒度太大，我们无法确定商品的多个属性。所以我们尽可能选择粒度最细的，比如商品信息表，从商品信息表中我们可以得到更多和商品这个维度相关的属性。
1.2.3、确定维度属性
确定维度属性也就是确定维度表字段。维度属性可直接从主维表或相关维表中选择，也可通过进一步加工得到。
1.3、商品维度表设计
1.3.1、确认维度（表）
确认维度这一步在设计事实表和维度表时都有，我的理解是设计维度表时提供的维度更加丰富，而事实表主要关注如何准确记录度量值并确保其粒度的一致性。比如设计事实表时对时间维度它只会留一个时间维度外键，并没有更多的时间维度信息。但是设计维度表的时候就必须尽可能丰富时间这个维度属性，比如提供年、月、日、时、分等详细的维度属性。
1.3.2、确定主维表和相关维表
我们需要去业务系统寻找和商品相关的每张表：
我们的 base_attr_info 存储的是平台属性名：品牌、分类、内存、材质、CPU型号等都是平台属性名，而 base_attr_value 存储的三星、6.4寸、8G内存、玻璃材质、骁龙888 等都是平台属性值。
一个平台属性值可能对应多个商品，同样一个商品也有很多个平台属性值，它们是多对多的关系，平台属性和商品信息的关系存储在 sku_attr_value 这个中间表中。
上面都是我们和平台属性有关的表，我们现在需要知道从应该这些表中获取哪些和商品相关的信息。其实我们主要希望得到的就是每个商品对应的平台属性名（attr_id 和 value_name）和平台属性值（value_id 和 value_name），而我们需要的这些字段都正好被存在了 sku_attr_value 这张中间表，这张表并没有按照三范式去设计，所以我们都不需要去通过 attr_id 和 value_id 去关联另外两张表去获取平台属性名和平台属性值。所以我们之前在做数据采集的时候其实也并没有对这两张表（base_attr_value 和 base_attr_info ）进行同步。
和平台属性一样，一个商品属性值可能对应多个商品，同样一个商品也有很多个销售属性值，它们同样是多对多的关系，商品属性和商品信息的关系存储在 sku_sale_attr_value 这个中间表中。而且它同样对 spu_sale_attr 和 spu_sale_attr_value 中的销售属性和销售属性值的字段进行了冗余，我们采集的时候也没有进行同步，而且在设计维度表的时候直接参 sku_sale_attr_value 这个中间表即可。
这样，我们的主维表和相关维表就确定好了，sku_info 是最细粒度的表，所以 sku_info 就是主维表，其它和商品相关的表都是相关维表。
1.3.3、确定商品维度的属性
确定商品维度属性就是确定商品维度表的字段，商品主维表（sku_info）具有最细粒度的属性，所以主维表的字段我们可以直接作为商品维度表的维度属性，而其它的相关维表只需要取出它的 id 作为维度属性即可。
1.4、商品维度表的实现
1.4.1、建表语句
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');可以看到，大部分的维度属性是来自于我们的主维表：
剩下的字段来自我们的其它维表，比如 SKU 相关的平台属性和销售属性：
`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 '销售属性'其中，sku_attr_values 存的是平台属性名和平台属性值；sku_sale_attr_values 存的是销售属性名和销售属性值。这是我们前面维度表设计中提到的一个设计要点——多值属性，对于多值属性通常有两种存储方案：1. 多个属性值放到一个字段，这也是我们这里用到的。2. 将多值属性放到多个字段，这里我们没有用，因为我们不能确定多值属性的属性个数，所以就无法怎么去设计这个结构体。
至于这张全量表的分区，我们选择用日期进行分区，每天把前一天的数据都得导入到一个日期目录下。
这里我们选择 store as orc 来指定存储格式为 orc ，使用 tblproperties 来指定压缩格式为 snappy。
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;1.9、日期维度表
前面的四张维度表都是每日全量快照表而且来自于业务系统，但时间维度表的数据并不是来自于业务系统，而是手动写入，并且由于时间维度表数据的可预见性，无须每日导入，一般可一次性导入一年的数据（主要是因为我们国家每年的节假日由国务院一年公布一次）。所以设计日期维度表也就没有选择主维表和相关维表这一步了，而确认维度属性取决于我们数仓的计算周期，并尽可能使得维度属性更加丰富。
日期维度表中每一行代表什么取决于我们数仓的计算周期，通常以天为单位。
1.9.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');1.9.2、加载语句
日期维度表的数据不是来自于业务系统，而是来自于我们自己上传。为了批量导入一年的数据，我们总不能一天一天的 insert ，而是先生成一个一年的日期文件，每行代表一天，然后把它 load 到我们的日期维度表当中，但是不能直接 load ，因为我们的日期维度表是一个 ORC 文件（STORE AS ORC 而不是 STORE AS TEXTFILE），所以我们还需要借助一个中间表（表结构应该和日期维度表一致，除了 STORE AS 字段）进行 insert + select 来导入到日期维度表当中。
（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/';注意：对于分区表的数据 ，我们不能直接把数据所在文件放到 location 字段所指定的路径来实现数据插入，因为分区表的元数据中存储着分区目录文件的信息，我们自己偷摸放到里面是没有用的。但是对于非分区表是 OK 的。所以我们这里直接上传到临时表的 location 即可。
（2）将数据文件上传到HFDS上临时表路径
/warehouse/gmall/tmp/tmp_dim_date_info
（3）执行以下语句将其导入时间维度表
insert overwrite table dim_date select * from tmp_dim_date_info;1.10、用户维度表
1.10.1、回顾拉链表
之前我们在学习数仓建模的维度表的设计时讲到，对于维度属性变化的表，我们保存维度的策略有两种：全量快照表和拉链表，对于这里的用户信息，它不适合每天全量快照因为它内容很多，但是变化很小，所以我们一般把它做成拉链表，因为拉链表它不会保存那些不变化的数据，减少数据冗余。
那拉链表的每行代表什么？是一个维度对象的一个状态，可能是历史状态，也可能是最新的 状态（结束日期是极大值 9999-12-31）；列是我们用户维度的一些常规属性（比如姓名、手机号等），特殊的是拉链表需要多创建两个字段：开始日期和结束日期，它们形容的是我们这个维度状态的有效期范围。
如何使用拉链表？
拉链表同样是维度表，所以我们依然是用事实表和它去做关联，关联的时候的规则同样是哪一天发生的事实去 join 哪天的维度状态。现在的问题是我们如何通过拉链表获得那一天的全量状态。
对于最新数据，我们可以直接查询满足状态结束日期为 9999-12-31 的数据。对于某天的数据，比如 2023-12-1 我们只要要求 状态开始日期 <= 2023-12-1 <= 状态结束日期 即可。
1.10.2、设计用户维度表
拉链表和前面的全量快照表一样都是维度表，那么我们同样在设计拉链表的时候需要参考业务系统来确定选择哪些字段。
主维表和相关维表
业务系统当中和我们用户表相关的表只有 user_info ，所以我们将来用户维度表的维度基本上就都是从该表中的字段选取过来的。此外，我们需要遵守拉链表的规定，额外创建两个字段（start_date 和 end_date ）带表示该行维度状态的有效时间。
分区
拉链表怎么分区？我们分区的意义当然是希望能够快速的查到我们需要的信息，而对于前面的全量快照表，我们可以直接通过分区查询到具体到哪一天的数据；而对于这里的增量表，我们的查询场景往往就是：1. 获取最新的用户状态信息 2.查询用户历史状态信息 。而实际开发中对于查询最新状态信息肯定是最频繁的，所以我们拉链表的分区设计应该尽可能为查询最新维度状态数据做考虑。
这里，我们按照用户状态过期的日期做分区，也就是说，对于用户维度表，每天我们创建的分区都是当日过期的用户状态。同时对于用户最新状态（结束日期字段为极大值）单独放到一个分区。这里的细节有很多：比如我们 HDFS 的数据是只读的，那如果用户的状态发生改变那我存放在最新状态分区（dt=9999-12-31）中的数据不就不是最新的了吗，那怎么去修改？这个问题我们放到拉链表的装载过程去回答。
DROP TABLE IF EXISTS dim_user_zip;
CREATE EXTERNAL TABLE dim_user_zip
(
`id` STRING COMMENT '用户id',
`login_name` STRING COMMENT '用户名称',
`nick_name` STRING COMMENT '用户昵称',
`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 '开始日期',
`end_date` STRING COMMENT '结束日期'
) COMMENT '用户表'
PARTITIONED BY (`dt` STRING)
STORED AS ORC
LOCATION '/warehouse/gmall/dim/dim_user_zip/'
TBLPROPERTIES ('orc.compress' = 'snappy');1.10.2、装载拉链表
可以看到，拉链表首日装载和之后的装载逻辑是不一样的：首日装载直接从我们业务系统中全量同步过来然后增加两个字段：start_date = 首日装载时间，end_date =日期极大值；之后的装载是那当天的新增和变化值和我们昨天的拉链表进行合并，然后需要修改各自的 start_date 和 end_date 字段。
数据流向
首日全量数据：
首日全量的数据是都放到最新状态分区的（也就是 dt=9999-12-31）
对于之后的数据，我们首先需要从用户的新增和变化数据中过滤出用户的最后一个状态，然后合并到最新用户状态分区，那么原本最新用户状态分区中就存在数据过期，就需要我们拿出来放到当天的分区中。
所以对于之后的数据都采用这种方式来分区，那么我们在装载的时候就需要写两条 SQL 语句：首日装载语句和每日装载语句。
1.10.2.1、首日装载
insert overwrite table dim_user_zip partition (dt='9999-12-31')
select
data.id,
data.login_name,
data.nick_name,
md5(data.name),
md5(data.phone_num),
md5(data.email),
data.user_level,
data.birthday,
data.gender,
data.create_time,
data.operate_time,
'2020-06-14' start_date,
'9999-12-31' end_date
from ods_user_info_inc
where dt='2020-06-14'
and type='bootstrap-insert';1.10.2.2、每日装载语句
我们首先要清楚数据来源和目的地，对于每日装载，我们需要通过 Maxwell 从业务系统重获得用户变更操作日志（Maxwell 会把这部分日志每日增量同步到 ods_user_info_inc ），然后把这部分数据中的新增及变化数据对我们的 9999-12-31 的分区的数据进行修改合并（把过期的数据移动到当日的过期分区）。
所以我们的数据源和目的地各有两个：数据源：9999-12-31 分区和当日的增量数据 ods_user_info_inc，目的地：9999-12-31 分区和当日的过期数据 dim_user_zip 。
我们现在要做的就是把截止前一天的全量最新数据中的过时数据放到今日过期分区，把新增及变化的数据放到 9999-12-31 分区中。
具体来说，我们可以对截止前一天的全量最新数据（9999-12-31分区）和 ODS 层今日新增及变化数据（ods_user_info_inc 的 dt=今天）的数据通过 id 进行 full join，上图中蓝色的部分就是两表 join 之后匹配不上的部分，它们的单元格全部为 null。这样，我们的数据可以分为三份：1. 没有修改的数据，2. 被修改的数据，3. 新增的数据
这里我们把左边的截止昨天最新数据表叫做 a，今日新增及变化表叫做 b。对于没有修改的数据，我们直接在 SQL 中判断 if (a.id !=null and b.id=null) 这样的数据就是没有修改的数据。对于修改了的数据，我们只需判断（if(b.id !=null),b.xxx,a.xxx）。对于新增数据我们只要判断 if（a.id =null and b.id !=null ）即可。
截止昨日全量最新数据（9999-12-31）
-- 截止昨天全量最新数据
select id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time,
start_date,
end_date,
dt
from dim_user_zip
where dt='9999-12-31';今日新增及变化（ods_user_info_inc）
这里我们需要得到的是每个用户的最后一个状态，因为用户可能一天修改了多次状态。
-- 今天的新增及变化数据 需要注意这里得到的所有的状态数据，比如一个用户一天修改了10次用户名。我们只要他的最后状态
select
id,
login_name,
nick_name,
md5(name),
md5(phone_num),
md5(email),
user_level,
birthday,
gender,
create_time,
operate_time,
'2020-06-15' start_date,
'9999-12-31' end_date
from
(
select
data.id,
data.login_name,
data.nick_name,
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) rk
from ods_user_info_inc
where dt='2020-06-15'
)t1
where rk=1;full join
with
tmp as
(
select
old.id old_id,
old.login_name old_login_name,
old.nick_name old_nick_name,
old.name old_name,
old.phone_num old_phone_num,
old.email old_email,
old.user_level old_user_level,
old.birthday old_birthday,
old.gender old_gender,
old.create_time old_create_time,
old.operate_time old_operate_time,
old.start_date old_start_date,
old.end_date old_end_date,
new.id new_id,
new.login_name new_login_name,
new.nick_name new_nick_name,
new.name new_name,
new.phone_num new_phone_num,
new.email new_email,
new.user_level new_user_level,
new.birthday new_birthday,
new.gender new_gender,
new.create_time new_create_time,
new.operate_time new_operate_time,
new.start_date new_start_date,
new.end_date new_end_date
from
(
select
id,
login_name,
nick_name,
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'
)old
full outer join
(
select
id,
login_name,
nick_name,
md5(name) name,
md5(phone_num) phone_num,
md5(email) email,
user_level,
birthday,
gender,
create_time,
operate_time,
'2020-06-15' start_date,
'9999-12-31' end_date
from
(
select
data.id,
data.login_name,
data.nick_name,
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) rn
from ods_user_info_inc
where dt='2020-06-15'
)t1
where rn=1
)new
on old.id=new.id
)
insert overwrite table dim_user_zip partition(dt)
-- 更新后的全量最新数据
select
if(new_id is not null,new_id,old_id),
if(new_id is not null,new_login_name,old_login_name),
if(new_id is not null,new_nick_name,old_nick_name),
if(new_id is not null,new_name,old_name),
if(new_id is not null,new_phone_num,old_phone_num),
if(new_id is not null,new_email,old_email),
if(new_id is not null,new_user_level,old_user_level),
if(new_id is not null,new_birthday,old_birthday),
if(new_id is not null,new_gender,old_gender),
if(new_id is not null,new_create_time,old_create_time),
if(new_id is not null,new_operate_time,old_operate_time),
if(new_id is not null,new_start_date,old_start_date),
if(new_id is not null,new_end_date,old_end_date),
if(new_id is not null,new_end_date,old_end_date) dt
from tmp
union all
-- 过期数据
select
old_id,
old_login_name,
old_nick_name,
old_name,
old_phone_num,
old_email,
old_user_level,
old_birthday,
old_gender,
old_create_time,
old_operate_time,
old_start_date,
cast(date_add('2020-06-15',-1) as string) old_end_date,
cast(date_add('2020-06-15',-1) as string) dt
from tmp
where old_id is not null and new_id is not null;除了上面通过 full join 来实现之外，还可以通过 union all 来达到同样的效果：
思路：通过 union all 来将两张表合并成一张长表，然后通过 row_number 进行开窗（按照 id 进行分区、按照 start_date 进行降序排序）这样对于那些修改了的数据它就会有两条记录分别 rank 为 1（新状态） 和 2（旧状态）。然后我们只需要查询 rk=1 的记录就可以得到所有最新的记录放入最新状态分区。查询所有 rk=2 的数据并修改 end_date 为当天-1 放入当天的过期分区。
insert overwrite table dim_user_zip partition (dt)
select
id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time,
start_date,
`if`(rk=2,date_sub('2020-06-15',1),end_date) end_date,
`if`(rk=1,'9999-12-31',date_sub('2020-06-15',1))
from
(
select
id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time,
start_date,
end_date,
rank() over (partition by id order by start_date desc ) rk
from
(
select id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time,
start_date,
end_date,
dt
from dim_user_zip
where dt='9999-12-31'
union all
select
id,
login_name,
nick_name,
md5(name),
md5(phone_num),
md5(email),
user_level,
birthday,
gender,
create_time,
operate_time,
'2020-06-15' start_date,
'9999-12-31' end_date
from
(
select
data.id,
data.login_name,
data.nick_name,
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) rk
from ods_user_info_inc
where dt='2020-06-15'
)t1
where rk=1
)t2
)t31.11、数据装载
1.11.1、首日装载脚本
首日装载脚本只需要数仓上线第一天执行一次即可，除了用户维度之外（用户维度表是用拉链表），别的维度表都是每日全量快照表，所以首日和每日SQL都一样，还有日期维度表也不需要再去装载了（因为我们一年才装载一次）。
#!/bin/bash
APP=gmall
if [ -n "$2" ] ;then
do_date=$2
else
echo "请传入日期参数"
exit
fi
dim_user_zip="
insert overwrite table ${APP}.dim_user_zip partition (dt='9999-12-31')
select
data.id,
data.login_name,
data.nick_name,
md5(data.name),
md5(data.phone_num),
md5(data.email),
data.user_level,
data.birthday,
data.gender,
data.create_time,
data.operate_time,
'$do_date' start_date,
'9999-12-31' end_date
from ${APP}.ods_user_info_inc
where dt='$do_date'
and type='bootstrap-insert';
"
dim_sku_full="
with
sku as
(
select
id,
price,
sku_name,
sku_desc,
weight,
is_sale,
spu_id,
category3_id,
tm_id,
create_time
from ${APP}.ods_sku_info_full
where dt='$do_date'
),
spu as
(
select
id,
spu_name
from ${APP}.ods_spu_info_full
where dt='$do_date'
),
c3 as
(
select
id,
name,
category2_id
from ${APP}.ods_base_category3_full
where dt='$do_date'
),
c2 as
(
select
id,
name,
category1_id
from ${APP}.ods_base_category2_full
where dt='$do_date'
),
c1 as
(
select
id,
name
from ${APP}.ods_base_category1_full
where dt='$do_date'
),
tm as
(
select
id,
tm_name
from ${APP}.ods_base_trademark_full
where dt='$do_date'
),
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 ${APP}.ods_sku_attr_value_full
where dt='$do_date'
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 ${APP}.ods_sku_sale_attr_value_full
where dt='$do_date'
group by sku_id
)
insert overwrite table ${APP}.dim_sku_full partition(dt='$do_date')
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;
"
dim_province_full="
insert overwrite table ${APP}.dim_province_full partition(dt='$do_date')
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 ${APP}.ods_base_province_full
where dt='$do_date'
)province
left join
(
select
id,
region_name
from ${APP}.ods_base_region_full
where dt='$do_date'
)region
on province.region_id=region.id;
"
dim_coupon_full="
insert overwrite table ${APP}.dim_coupon_full partition(dt='$do_date')
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 ${APP}.ods_coupon_info_full
where dt='$do_date'
)ci
left join
(
select
dic_code,
dic_name
from ${APP}.ods_base_dic_full
where dt='$do_date'
and parent_code='32'
)coupon_dic
on ci.coupon_type=coupon_dic.dic_code
left join
(
select
dic_code,
dic_name
from ${APP}.ods_base_dic_full
where dt='$do_date'
and parent_code='33'
)range_dic
on ci.range_type=range_dic.dic_code;
"
dim_activity_full="
insert overwrite table ${APP}.dim_activity_full partition(dt='$do_date')
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 ${APP}.ods_activity_rule_full
where dt='$do_date'
)rule
left join
(
select
id,
activity_name,
activity_type,
activity_desc,
start_time,
end_time,
create_time
from ${APP}.ods_activity_info_full
where dt='$do_date'
)info
on rule.activity_id=info.id
left join
(
select
dic_code,
dic_name
from ${APP}.ods_base_dic_full
where dt='$do_date'
and parent_code='31'
)dic
on rule.activity_type=dic.dic_code;
"
case $1 in
"dim_user_zip")
hive -e "$dim_user_zip"
;;
"dim_sku_full")
hive -e "$dim_sku_full"
;;
"dim_province_full")
hive -e "$dim_province_full"
;;
"dim_coupon_full")
hive -e "$dim_coupon_full"
;;
"dim_activity_full")
hive -e "$dim_activity_full"
;;
"all")
hive -e "$dim_user_zip$dim_sku_full$dim_province_full$dim_coupon_full$dim_activity_full"
;;
esac1.11.2、每日装载脚本
#!/bin/bash
APP=gmall
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
do_date=$2
else
do_date=`date -d "-1 day" +%F`
fi
dim_user_zip="
set hive.exec.dynamic.partition.mode=nonstrict;
with
tmp as
(
select
old.id old_id,
old.login_name old_login_name,
old.nick_name old_nick_name,
old.name old_name,
old.phone_num old_phone_num,
old.email old_email,
old.user_level old_user_level,
old.birthday old_birthday,
old.gender old_gender,
old.create_time old_create_time,
old.operate_time old_operate_time,
old.start_date old_start_date,
old.end_date old_end_date,
new.id new_id,
new.login_name new_login_name,
new.nick_name new_nick_name,
new.name new_name,
new.phone_num new_phone_num,
new.email new_email,
new.user_level new_user_level,
new.birthday new_birthday,
new.gender new_gender,
new.create_time new_create_time,
new.operate_time new_operate_time,
new.start_date new_start_date,
new.end_date new_end_date
from
(
select
id,
login_name,
nick_name,
name,
phone_num,
email,
user_level,
birthday,
gender,
create_time,
operate_time,
start_date,
end_date
from ${APP}.dim_user_zip
where dt='9999-12-31'
)old
full outer join
(
select
id,
login_name,
nick_name,
md5(name) name,
md5(phone_num) phone_num,
md5(email) email,
user_level,
birthday,
gender,
create_time,
operate_time,
'$do_date' start_date,
'9999-12-31' end_date
from
(
select
data.id,
data.login_name,
data.nick_name,
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) rn
from ${APP}.ods_user_info_inc
where dt='$do_date'
)t1
where rn=1
)new
on old.id=new.id
)
insert overwrite table ${APP}.dim_user_zip partition(dt)
select
if(new_id is not null,new_id,old_id),
if(new_id is not null,new_login_name,old_login_name),
if(new_id is not null,new_nick_name,old_nick_name),
if(new_id is not null,new_name,old_name),
if(new_id is not null,new_phone_num,old_phone_num),
if(new_id is not null,new_email,old_email),
if(new_id is not null,new_user_level,old_user_level),
if(new_id is not null,new_birthday,old_birthday),
if(new_id is not null,new_gender,old_gender),
if(new_id is not null,new_create_time,old_create_time),
if(new_id is not null,new_operate_time,old_operate_time),
if(new_id is not null,new_start_date,old_start_date),
if(new_id is not null,new_end_date,old_end_date),
if(new_id is not null,new_end_date,old_end_date) dt
from tmp
union all
select
old_id,
old_login_name,
old_nick_name,
old_name,
old_phone_num,
old_email,
old_user_level,
old_birthday,
old_gender,
old_create_time,
old_operate_time,
old_start_date,
cast(date_add('$do_date',-1) as string) old_end_date,
cast(date_add('$do_date',-1) as string) dt
from tmp
where old_id is not null
and new_id is not null;
"
dim_sku_full="
with
sku as
(
select
id,
price,
sku_name,
sku_desc,
weight,
is_sale,
spu_id,
category3_id,
tm_id,
create_time
from ${APP}.ods_sku_info_full
where dt='$do_date'
),
spu as
(
select
id,
spu_name
from ${APP}.ods_spu_info_full
where dt='$do_date'
),
c3 as
(
select
id,
name,
category2_id
from ${APP}.ods_base_category3_full
where dt='$do_date'
),
c2 as
(
select
id,
name,
category1_id
from ${APP}.ods_base_category2_full
where dt='$do_date'
),
c1 as
(
select
id,
name
from ${APP}.ods_base_category1_full
where dt='$do_date'
),
tm as
(
select
id,
tm_name
from ${APP}.ods_base_trademark_full
where dt='$do_date'
),
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 ${APP}.ods_sku_attr_value_full
where dt='$do_date'
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 ${APP}.ods_sku_sale_attr_value_full
where dt='$do_date'
group by sku_id
)
insert overwrite table ${APP}.dim_sku_full partition(dt='$do_date')
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;
"
dim_province_full="
insert overwrite table ${APP}.dim_province_full partition(dt='$do_date')
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 ${APP}.ods_base_province_full
where dt='$do_date'
)province
left join
(
select
id,
region_name
from ${APP}.ods_base_region_full
where dt='$do_date'
)region
on province.region_id=region.id;
"
dim_coupon_full="
insert overwrite table ${APP}.dim_coupon_full partition(dt='$do_date')
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 ${APP}.ods_coupon_info_full
where dt='$do_date'
)ci
left join
(
select
dic_code,
dic_name
from ${APP}.ods_base_dic_full
where dt='$do_date'
and parent_code='32'
)coupon_dic
on ci.coupon_type=coupon_dic.dic_code
left join
(
select
dic_code,
dic_name
from ${APP}.ods_base_dic_full
where dt='$do_date'
and parent_code='33'
)range_dic
on ci.range_type=range_dic.dic_code;
"
dim_activity_full="
insert overwrite table ${APP}.dim_activity_full partition(dt='$do_date')
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 ${APP}.ods_activity_rule_full
where dt='$do_date'
)rule
left join
(
select
id,
activity_name,
activity_type,
activity_desc,
start_time,
end_time,
create_time
from ${APP}.ods_activity_info_full
where dt='$do_date'
)info
on rule.activity_id=info.id
left join
(
select
dic_code,
dic_name
from ${APP}.ods_base_dic_full
where dt='$do_date'
and parent_code='31'
)dic
on rule.activity_type=dic.dic_code;
"
case $1 in
"dim_user_zip")
hive -e "$dim_user_zip"
;;
"dim_sku_full")
hive -e "$dim_sku_full"
;;
"dim_province_full")
hive -e "$dim_province_full"
;;
"dim_coupon_full")
hive -e "$dim_coupon_full"
;;
"dim_activity_full")
hive -e "$dim_activity_full"
;;
"all")
hive -e "$dim_user_zip$dim_sku_full$dim_province_full$dim_coupon_full$dim_activity_full"
;;
esac执行脚本：
./ods_to_dim_init.sh all 2020-06-14总结
至此，DIM 层开发完毕，学得慢有慢的好处。要学的东西还是很多的，不能心急。
修改元数据库字符集
Hive 元数据字符集默认是 Latin1，所以不支持中文字符，而我们的建表语句中存在中文注释，会出现乱码，所以我们只需要修改元数据中字段注释和标注释的编码。
1）修改 Hive 源数据库中存储注释的字段字符集
（1）字段注释
alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;（2）表注释
alter table TABLE_PARAMS modify column PARAM_VALUE mediumtext character set utf8;2）修改 hive-site,xml 中的 JDBC URL
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://hadoop102:3306/metastore?useSSL=false&useUnicode=true&characterEncoding=UTF-8</value>
</property>3）修复分区信息
修改编码后我们已经创建的表的字段字符编码依然还是乱码，因为我们之前是用 latin1 编码的，现在它无法从 latin1 转为 utf8 ，除非我们重建这张表。
删除重建 Hive 表之后执行下面的语句：
msck repair table ods_log_inc;注意：因为我们创建的是外部表，所以删除表格数据也不会丢，只是重建之后它不知道分区信息所以没有数据，所以需要我们修复表的分区。
2025
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
