一、dim层

1、商品信息表

（1）建表语句

drop table if EXISTS dim_sku_info_df;
CREATE TABLE if not EXISTS dim_sku_info_df(
sku_id string comment   '库存单元id',
price  double comment   '价格',
sku_name string comment '库存单元名称',
sku_desc string comment '商品规格描述',
weight double  comment '重量',
is_sale string  comment '是否销售（1：是 0：否）',
spu_id string  comment '商品id',
spu_name string comment '商品名称',
brand_id string comment '品牌id',               
brand_name string comment '品牌名称',           
is_used string  comment '是否有效 1有效 0失效' ,                
banned_time string comment '封禁时间',            
is_domestic string comment '是否国内品牌 1是 0否',             
logo_url string  comment '品牌logo的图片路径',                   
class_name_3 string comment '三级分类名称',
class_id_3 string comment '三级分类ID',
class_name_2 string comment '二级分类名称',
class_id_2 string comment '二级分类ID',
class_name_1 string comment '一级分类名称',
class_id_1 string comment '一级分类ID',
create_time string  comment '创建时间',
update_time string comment '修改时间'
)
comment '商品信息表'
PARTITIONED BY (
  pt string comment '日期'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
STORED AS ORC tblproperties("orc.compress"="SNAPPY")
;

（2）导数语句

with sku AS
(SELECT
 sku_id                  
,spu_id                  
,price                   
,sku_name                
,sku_desc                
,weight                  
,brand_id                
,category3_id             
,sku_img                   
,is_sale                     
,create_time             
,update_time             
,pt                      
from  project.ods_sku_info_full where pt = '2022-01-01'),
spu as 
(select 
spu_id                         
,spu_name                         
from  project.ods_spu_info_full where pt = '2022-01-01'),
brand as 
(select 
 brand_id                
,brand_name              
,is_used                    
,banned_time             
,is_domestic                
,logo_url                  
from project.ods_pub_brand_full where pt = '2022-01-01'),
class as 
(select 
 l3.class_id as class_id_3
,l3.class_name as class_name_3
,l2.class_id as class_id_2
,l2.class_name as class_name_2
,l1.class_id as class_id_1
,l1.class_name as class_name_1
from (select * from project.ods_pub_class_full where pt='2022-01-01' and `level`= '1') l1
left join (select * from project.ods_pub_class_full where pt='2022-01-01' and `level`='2') l2 
 on  l1.class_id = l2.parent_id
left join (select * from project.ods_pub_class_full where pt='2022-01-01' and `level`='3') l3
 on l2.class_id = l3.parent_id )
insert overwrite table project.dim_sku_info_df partition(pt='2022-01-01')
select
sku.sku_id,
sku.price,
sku.sku_name,
sku.sku_desc,
sku.weight,
sku.is_sale,
sku.spu_id,
spu.spu_name,
brand.brand_id,               
brand.brand_name,           
brand.is_used,                
brand.banned_time,            
brand.is_domestic,             
brand.logo_url,                   
class.class_name_3,
class.class_id_3,
class.class_name_2,
class.class_id_2,
class.class_name_1,
class.class_id_1,
sku.create_time,
sku.update_time
from  sku 
left join spu 
 on sku.spu_id = spu.spu_id
left join brand
 on  sku.brand_id = brand.brand_id
left join class 
 on sku.category3_id =  class.class_id_3 ;   

2、商品详细属性表

（1）建表语句

drop table if EXISTS dim_sku_para_info_df;
CREATE TABLE if not EXISTS dim_sku_para_info_df(
 id string comment   'id'
,sku_id string comment   '库存单元id'
,spu_id string comment   '商品id'
,para_id string comment   'sku参数id'
,attr_id string comment   '属性id'
,attr_name  string comment   '属性名称'
,value  string comment   '属性值'
)
comment '商品详细属性表'
PARTITIONED BY (
  pt string comment '日期'
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
STORED AS ORC tblproperties("orc.compress"="SNAPPY")
;

（2）导数语句

with sp AS
(select 
id
,sku_id
,spu_id
,para_id
from project.ods_sku_para_full   where pt = '2022-01-01'),
spi as 
(select 
para_id
,attr_id
,attr_name
,value
from project.ods_sku_para_info_full   where pt = '2022-01-01')
insert overwrite table dim_sku_para_info_df partition(pt='2022-01-01')
select
 sp.id 
,sp.sku_id
,sp.spu_id
,sp.para_id
,spi.attr_id
,spi.attr_name
,spi.value
from sp
left join spi
on sp.para_id = spi.para_id
;

3、活动信息表

（1）建表语句

drop table if exists dim_activity_info_df;
create external table dim_activity_info_df
(   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 '活动描述',
    activity_rules   string comment '活动规则',
    content_amount   double comment '满足的金额',
    reduce_amount      int comment '活动减免的金额',
    content_cnt      double comment '满足的件数',
    reduce_rate      double comment '活动减免的折扣',
    activity_level     string comment '优惠等级',
    start_time         string comment '开始时间',
    end_time           string comment '结束时间',
    create_time        string comment '创建时间',
    update_time string comment '修改时间'
) comment '活动信息表'
partitioned by (pt string)
stored as orc
tblproperties ('orc.compress' = 'snappy')
;

（2）导数语句

with act as 
(
select 
id
,activity_id
,activity_name
,activity_type
,activity_desc
,case activity_type
        when '3101' then concat('满',content_amount,'元减',reduce_amount,'元')
        when '3102' then concat('满',content_cnt,'件打',10*(1-reduce_rate),'折')
  end activity_rules
,content_amount
,reduce_amount
,content_cnt
,reduce_rate
,activity_level
,start_time
,end_time
,create_time
,update_time
from  project.ods_activity_info_full where pt='2022-01-01'
),
code as
(
select 
 code_id
,code_name
from project.ods_pub_code_full where pt='2022-01-01'
)
insert overwrite table dim_activity_info_df partition(pt='2022-01-01')
select 
id
,activity_id
,activity_name
,activity_type
,code.code_name
,activity_desc
,case activity_type
        when '3101' then concat('满',content_amount,'元减',reduce_amount,'元')
        when '3102' then concat('满',content_cnt,'件打',10*(1-reduce_rate),'折')
  end activity_rules
,content_amount
,reduce_amount
,content_cnt
,reduce_rate
,activity_level
,start_time
,end_time
,create_time
,update_time
from  act
left join  code
on act.activity_type = code.code_id
;

4、优惠券信息表

（1）建表语句

drop table if exists dim_coupon_info_df;
create table dim_coupon_info_df
(
 coupon_id        string comment '购物券id'
,coupon_name      string comment '购物券名称'
,coupon_type      string comment '购物券类型'
,coupon_type_name string comment '购物券类型名称'
,content_amount   double comment '满足的金额'
,reduce_amount    double comment '优惠券减免的金额'
,range_type       string comment '优惠券范围类型'
,class_id_3     string  comment '三级分类id'
,start_time       string comment '优惠券使用开始日期'
,end_time         string comment '优惠券使用结束日期'
,create_time      string comment '创建时间'
,update_time     string comment '修改时间'
,etl_load_time string comment 'etl加载时间'
) comment '优惠券信息表'
partitioned by (pt string)
stored as orc
tblproperties ('orc.compress' = 'snappy')
;

（2）导数语句

with coupon as
(
select 
 coupon_id
,coupon_name
,coupon_type
,content_amount
,reduce_amount
,range_type
,category3_id
,start_time
,end_time
,create_time
,update_time
,current_timestamp()
from project.ods_coupon_info_full
where pt='2022-01-01'
)
,
code as
(
select 
 code_id
,code_name
from project.ods_pub_code_full where pt='2022-01-01'
)
insert overwrite table dim_coupon_info_df partition(pt='2022-01-01')
select 
 coupon_id
,coupon_name
,coupon_type
,code_name 
,content_amount
,reduce_amount
,range_type
,category3_id 
,start_time
,end_time
,create_time
,update_time
,current_timestamp()
from coupon
left join code 
on coupon.coupon_type = code.code_id
;

5、区域表

（1）建表语句

drop table if exists dim_pub_area_df;
create  table dim_pub_area_df
(  
 province_id   string comment 'id'
,province_name string comment '省市名称'
,area_code     string comment '地区编码'
,iso_code_old      string comment '旧版地区编码'
,iso_code_new    string comment '新版地区编码'
,region_id     string comment '地区id'
,region_name   string comment '地区名称'
,etl_load_time string comment 'etl加载时间'
) comment '区域表'
partitioned by (pt string)
stored as orc
tblproperties ('orc.compress' = 'snappy')
;

（2）导数语句

insert overwrite table dim_pub_area_df partition(pt='2022-01-01')
select    
 province_id            
,province_name 
,area_code     
,iso_code      
,iso_3166_2    
,region_id     
,region_name   
,current_timestamp()
from project.ods_pub_area_full
where pt='2022-01-01'

6、商家信息表

（1）建表语句

drop table if exists dim_business_info_df;
create table dim_business_info_df
(
 bus_id          string comment '商家id'
,bus_name        string comment '商家名称'
,telephone       string comment '联系电话'
,pic_url         string comment '图片地址URL'
,bus_user_name   double comment '商家联系人'
,province        double comment '省份'
,city            string comment '城市'
,dist            string  comment '区'
,addr            string comment '详细地址'
,zipcode         string comment '邮编'
,is_open         string comment '是否关店 1关店 0未关'
,open_time       string comment '开店时间'
,closed_time     string comment '关店时间'
,create_time     string comment '创建时间'
,update_time     string comment '修改时间'
,etl_load_time   string comment 'etl加载时间'
) comment '优惠券信息表'
partitioned by (pt string)
stored as orc
tblproperties ('orc.compress' = 'snappy')
;

（2）导数语句

insert overwrite table dim_business_info_df partition(pt='2022-01-01')
select 
bus_id
,bus_name
,md5(telephone)
,pic_url
,md5(bus_user_name)
,province
,city
,dist
,addr
,zipcode
,is_open
,open_time
,closed_time
,create_time
,update_time
,current_timestamp()
from project.ods_business_info_full
where pt ='2022-01-01'
;

7、用户信息表（拉链表）

（1）建表语句

DROP TABLE IF EXISTS dim_user_info_his;
CREATE EXTERNAL TABLE dim_user_info_his
(
user_id       STRING COMMENT '用户id'
,login_name   STRING COMMENT '用户名称'
,nick_name    STRING COMMENT '用户昵称'
,name         STRING COMMENT '用户姓名'
,phone_num    STRING COMMENT '手机号码'
,email        STRING COMMENT '邮箱'
,head_img     STRING COMMENT '头像地址'
,user_level   STRING COMMENT '用户等级'
,birthday     STRING COMMENT '生日'
,gender       STRING COMMENT '性别'
,status       STRING COMMENT '状态 1-有效，0-无效，2-黑名单'
,create_time  STRING COMMENT '创建时间'
,update_time  STRING COMMENT '操作时间'
,start_date   STRING COMMENT '开始日期'
,end_date     STRING COMMENT '结束日期'
) COMMENT '用户表'
PARTITIONED BY (pt STRING)
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'snappy');

（2）首日装载语句

insert overwrite table  dim_user_info_his partition(pt='9999-12-31')
select
 user_id
,login_name
,nick_name
,md5(user_name)
,md5(phone_num)
,md5(email)
,head_img
,user_level
,birthday
,gender
,status
,create_time
,update_time
,'2022-01-01' start_date
,'9999-12-31' end_date
from project.ods_user_info_incr_stg

（3）每日装载（x）

set hive.exec.dynamic.partition=true;    
set hive.exec.dynamic.partition.mode=nonstrict;
with tmp as 
(
    select 
      t1.user_id       as user_id1
    , t1.login_name    as login_name1
    , t1.nick_name     as nick_name1
    , t1.name          as name1
    , t1.phone_num     as phone_num1
    , t1.email         as email1
    , t1.head_img      as head_img1
    , t1.user_level    as user_level1
    , t1.birthday      as birthday1
    , t1.gender        as gender1
    , t1.create_time   as create_time1
    , t1.update_time   as update_time1
    , t1.statuss       as statuss1
    , t1.start_date    as start_date1
    , t1.end_date      as end_date1

    , t2.user_id       as user_id2
    , t2.login_name    as login_name2
    , t2.nick_name     as nick_name2 
    , t2.name          as name2
    , t2.phone_num     as phone_num2
    , t2.email         as email2
    , t2.head_img      as head_img2
    , t2.user_level    as user_level2
    , t2.birthday      as birthday2
    , t2.gender        as gender2
    , t2.create_time   as create_time2
    , t2.update_time   as update_time2
    , t2.statuss       as statuss2
    , t2.start_date    as start_date2
    , t2.end_date      as end_date2
    from
    (
        SELECT user_id, login_name, nick_name, name, phone_num, email, head_img, user_level, birthday, gender,  create_time, update_time, `status` as statuss,start_date, end_date
        FROM project.dim_user_info_his
        where pt ='9999-12-31'
    ) t1
    full join
    (
        SELECT user_id, login_name, nick_name,  user_name as name , phone_num, email, head_img, user_level, birthday, gender, create_time, update_time, `status` as statuss,'2022-01-02' as start_date,'9999-12-31' as end_date
        FROM project.ods_user_info_incr_stg
    )t2
    on t1.user_id = t2.user_id
)
insert overwrite table dim_user_info_his partition(pt)
select 
      if(user_id2     is not null,user_id2,user_id1)
    , if(login_name2  is not null,login_name2,login_name1)
    , if(nick_name2   is not null,nick_name2,nick_name1)  
    , if(name2        is not null,name2,name1) 
    , if(phone_num2   is not null,phone_num2,phone_num1)     
    , if(email2       is not null,email2,email1)   
    , if(head_img2    is not null,head_img2,head_img1)    
    , if(user_level2  is not null,user_level2,user_level1)       
    , if(birthday2    is not null,birthday2,birthday1)     
    , if(gender2      is not null,gender2,gender1)   
    , if(create_time2 is not null,create_time2,create_time1)       
    , if(update_time2 is not null,update_time2,update_time1)      
    , if(statuss2     is not null,statuss2,statuss1)       
    , if(start_date2  is not null,start_date2,start_date1)         
    , if(end_date2    is not null,end_date2,end_date1)
    , if(end_date2    is not null,end_date2,end_date1) as pt    
from tmp
union all
select 
      user_id1
    , login_name1
    , nick_name1
    , name1
    , phone_num1
    , email1
    , head_img1
    , user_level1
    , birthday1
    , gender1
    , create_time1
    , update_time1
    , statuss1
    , start_date1
    , '2022-01-01' as end_date1
    , '2022-01-01' as pt
from tmp
where user_id1 is not null and user_id1 is not null

（4）每日装载

set hive.exec.dynamic.partition.mode=true; 
with user_info_tmp as 
(
select 
 dim.user_id     
,dim.login_name 
,dim.nick_name  
,dim.name       
,dim.phone_num  
,dim.email      
,dim.head_img   
,dim.user_level 
,dim.birthday   
,dim.gender     
,dim.status     
,dim.create_time
,dim.update_time
,dim.start_date 
,case when ods.user_id is not null and dim.end_date='9999-12-31' then date_sub('2022-01-02',1) 
 else dim.end_date end end_date
from (select 
      user_id     
      ,login_name 
      ,nick_name  
      ,name       
      ,phone_num  
      ,email      
      ,head_img   
      ,user_level 
      ,birthday   
      ,gender     
      ,status     
      ,create_time
      ,update_time
      ,start_date 
      ,end_date   
      from dim_user_info_his 
      where pt='9999-12-31') dim
left join ods_user_info_incr_stg ods
       on dim.user_id = ods.user_id
union all
select 
 user_id     
,login_name 
,nick_name  
,md5(user_name)
,md5(phone_num)
,md5(email)   
,head_img   
,user_level 
,birthday   
,gender     
,status     
,create_time
,update_time
,'2022-01-02' start_date
,'9999-12-31'  end_date
from ods_user_info_incr_stg
)
insert overwrite table dim_user_info_his partition(pt)
select 
user_id     
,login_name 
,nick_name  
,name       
,phone_num  
,email      
,head_img   
,user_level 
,birthday   
,gender     
,status     
,create_time
,update_time
,start_date
,end_date 
,end_date
from user_info_tmp
;