电商数仓项目----笔记七(数仓DIM层)

2023-12-24 18:36
文章标签 项目 笔记 数仓 dim 商数

本文主要是介绍电商数仓项目----笔记七(数仓DIM层),希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

所谓的维度层其实就是分析数据的角度,维度层保存的表其实是分析数据的角度,比如:

        --性别,年龄,品牌,品类

这层的表主要用于统计分析,因此DIM层的数据存储格式为orc列式存储+snappy压缩(时间短)

orc列式存储的好处:

  1. 查询的时候不需要扫描全部的数据,而只需要读取每次查询涉及的列,这样可以将I/O消耗降低N倍,另外可以保存每一列的统计信息(min、max、sum等),实现部分的谓词下推。
  2. 由于每一列的成员都是同构的,可以针对不同的数据类型使用更高效的数据压缩算法,进一步减小I/O。
  3. 由于每一列的成员的同构性,可以使用更加适合CPU pipeline的编码方式,减小CPU的缓存失效。

维度表的设计

        一个维度就是一张表,从实践的角度来讲,不同的维度就是这张表的字段,可以达到解耦的目的。如果维度特别简单,可以不用创建表,可以在事实表直接使用。

        字段:只要能用来分析的维度,都是字段;

        数据(字段)来源:参考业务数据的表字段:

                -- 主维表:业务数据库主要用于分析维度字段的表;

                -- 相关维表:业务数据库相关用于分析维度字段的表;

        维度字段的确定:

                尽可能生成丰富的维度属性:字段越多越好;

                编码和文字共存(0男/1女);

                计算通用的维度属性;

下面举几个例子:

优惠券维度表 

从主维表和相关维表分析:

        主维表:coupon_info,相关维表:coupon_range,coupon_use,但是coupon_use算是一种行为概念,并不属于状态,状态才是用来做分析的。但是在coupon_info里面也有range相关字段,因此发生了冗余,只需关注coupon_info即可。

coupon_info长这样:

我们这样设计:

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 ORCLOCATION '/warehouse/gmall/dim/dim_coupon_full/'TBLPROPERTIES ('orc.compress' = 'snappy');

        其中不一样的地方有我们将ODS层原表的coupon_type分解为了coupon_type_code和coupon_type_name。将range_type分解为了range_type_code,range_type_name,并且增加了benefit_rule字段(优惠规则)。这样做符合我们上面说的编码和文字共存规则。

数据装载

        我们的表主要从coupon_info和base_dic(字典表)中取得:

        记住这里的主维表是coupon_info,因此我们先select coupon_info这张表,select里面的字段依照我们建表语句里面的字段先写好,当然其中肯定会有几个字段会报红,没关系我们后面还要join 操作,其中coupon_type_code,coupon_type_name,range_type_code,range_type_name字段是找不到的,因此需要join操作。我们join base_dic字典表:

join base_dic两次分别得到coupon_type_code,coupon_type_name字段和range_type_code,range_type_name字段;

        接下来是benefit_rule字段,这里需要我们自行拼接。拼接逻辑如下:

case coupon_typewhen '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,

 完整是这样:

selectid,coupon_name,coupon_type,coupon_dic.dic_name,condition_amount,condition_num,activity_id,benefit_amount,benefit_discount,case coupon_typewhen '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
(selectid,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_timefrom ods_coupon_info_fullwhere dt='2020-06-14'
)ci
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code='32'
)coupon_dic
on ci.coupon_type=coupon_dic.dic_code
left join
(selectdic_code,dic_namefrom ods_base_dic_fullwhere dt='2020-06-14'and parent_code='33'
)range_dic
on ci.range_type=range_dic.dic_code;

数据装载我们只需要前面加上下面这一句即可:

insert overwrite table dim_coupon_full partition(dt='2020-06-14')

我们的Dim层优惠券维度表就设计完啦。

活动维度表

        同样的,找到主维表和相关维表。

        activity_info ,activity_rule,activity_sku:我们分析的更多的是活动规则,而不是活动本身,所以主维表是activity_rule,相关维表是activity_info。

我们这样设计:

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 ORCLOCATION '/warehouse/gmall/dim/dim_activity_full/'TBLPROPERTIES ('orc.compress' = 'snappy');

数据装载:

insert overwrite table dim_activity_full partition(dt='2020-06-14')
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(selectid `activity_rule_id`   ,--STRING COMMENT '活动规则ID',`activity_id`        ,--STRING COMMENT '活动ID',--`activity_name`      ,--STRING COMMENT '活动名称',activity_type `activity_type_code` ,--STRING COMMENT '活动类型编码',--`activity_type_name` ,--STRING COMMENT '活动类型名称',--`activity_desc`      ,--STRING COMMENT '活动描述',--`start_time`         ,--STRING COMMENT '开始时间',--`end_time`           ,--STRING COMMENT '结束时间',dt 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 '优惠折扣',case activity_typewhen '3101' then concat('满',condition_amount,'元减',benefit_amount,'元')when '3102' then concat('满',condition_num,'件打',benefit_discount,'折')when '3103' then concat('打',benefit_discount,'折')end `benefit_rule`       ,--STRING COMMENT '优惠规则',`benefit_level`      --STRING COMMENT '优惠级别'from ods_activity_rule_fullwhere dt='2020-06-14')rule
left join(selectid,activity_name,activity_desc,start_time,end_timefrom ods_activity_info_fullwhere dt='2020-06-14') info
on rule.activity_id=info.id
left join (selectdic_code,dic_name activity_type_namefrom ods_base_dic_fullwhere dt='2020-06-14' and parent_code='31')dic on rule.activity_type_code=dic.dic_code

        整体思路就是先将create表中的字段复制到select 主维表的语句中,爆红的字段我们一一给他们join出来,或在join的那张表中给他们查询出来,这里就不详细分析了。

日期维度表

建表语句

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 ORCLOCATION '/warehouse/gmall/dim/dim_date/'TBLPROPERTIES ('orc.compress' = 'snappy');

数据装载

        通常情况下,时间维度表的数据并不是来自于业务系统,而是手动写入,并且由于时间维度表数据的可预见性,无须每日导入,一般可一次性导入一年的数据。

(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/';

将数据文件上传到HFDS上临时表路径/warehouse/gmall/tmp/tmp_dim_date_info 

(3)执行以下语句将其导入时间维度表

insert overwrite table dim_date select * from tmp_dim_date_info;

 

用户维度表

        用户维度表这里我们使用拉链表,来记录用户姓名的变更或者用户的增加减少。

(1)数据装载过程

(2)数据流向 

首日装载 

我们的用户数据在进行首日装载和后续的变更都是insert overwrite到9999-12-31的分区,首日装载如下:

insert overwrite table dim_user_zip partition (dt='9999-12-31')
selectdata.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';

每日装载

        装载思路:

         装载语句:

with
tmp as
(selectold.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_datefrom(selectid,login_name,nick_name,name,phone_num,email,user_level,birthday,gender,create_time,operate_time,start_date,end_datefrom dim_user_zipwhere dt='9999-12-31')oldfull outer join(selectid,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_datefrom(selectdata.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) rnfrom ods_user_info_incwhere dt='2020-06-15')t1where rn=1)newon old.id=new.id
)
insert overwrite table dim_user_zip partition(dt)
selectif(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
selectold_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;

 

数据装载脚本

首日装载脚本

#!/bin/bashAPP=gmallif [ -n "$2" ] ;thendo_date=$2
else echo "请传入日期参数"exit
fi dim_user_zip="
insert overwrite table ${APP}.dim_user_zip partition (dt='9999-12-31')
selectdata.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
(selectid,price,sku_name,sku_desc,weight,is_sale,spu_id,category3_id,tm_id,create_timefrom ${APP}.ods_sku_info_fullwhere dt='$do_date'
),
spu as
(selectid,spu_namefrom ${APP}.ods_spu_info_fullwhere dt='$do_date'
),
c3 as
(selectid,name,category2_idfrom ${APP}.ods_base_category3_fullwhere dt='$do_date'
),
c2 as
(selectid,name,category1_idfrom ${APP}.ods_base_category2_fullwhere dt='$do_date'
),
c1 as
(selectid,namefrom ${APP}.ods_base_category1_fullwhere dt='$do_date'
),
tm as
(selectid,tm_namefrom ${APP}.ods_base_trademark_fullwhere dt='$do_date'
),
attr as
(selectsku_id,collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrsfrom ${APP}.ods_sku_attr_value_fullwhere dt='$do_date'group by sku_id
),
sale_attr as
(selectsku_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_attrsfrom ${APP}.ods_sku_sale_attr_value_fullwhere dt='$do_date'group by sku_id
)
insert overwrite table ${APP}.dim_sku_full partition(dt='$do_date')
selectsku.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')
selectprovince.id,province.name,province.area_code,province.iso_code,province.iso_3166_2,region_id,region_name
from
(selectid,name,region_id,area_code,iso_code,iso_3166_2from ${APP}.ods_base_province_fullwhere dt='$do_date'
)province
left join
(selectid,region_namefrom ${APP}.ods_base_region_fullwhere dt='$do_date'
)region
on province.region_id=region.id;
"dim_coupon_full="
insert overwrite table ${APP}.dim_coupon_full partition(dt='$do_date')
selectid,coupon_name,coupon_type,coupon_dic.dic_name,condition_amount,condition_num,activity_id,benefit_amount,benefit_discount,case coupon_typewhen '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
(selectid,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_timefrom ${APP}.ods_coupon_info_fullwhere dt='$do_date'
)ci
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='32'
)coupon_dic
on ci.coupon_type=coupon_dic.dic_code
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere 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')
selectrule.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_typewhen '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
(selectid,activity_id,activity_type,condition_amount,condition_num,benefit_amount,benefit_discount,benefit_levelfrom ${APP}.ods_activity_rule_fullwhere dt='$do_date'
)rule
left join
(selectid,activity_name,activity_type,activity_desc,start_time,end_time,create_timefrom ${APP}.ods_activity_info_fullwhere dt='$do_date'
)info
on rule.activity_id=info.id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere 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

每日装载脚本

#!/bin/bashAPP=gmall# 如果是输入的日期按照取输入日期;如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;thendo_date=$2
else do_date=`date -d "-1 day" +%F`
fidim_user_zip="
set hive.exec.dynamic.partition.mode=nonstrict;
with
tmp as
(selectold.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_datefrom(selectid,login_name,nick_name,name,phone_num,email,user_level,birthday,gender,create_time,operate_time,start_date,end_datefrom ${APP}.dim_user_zipwhere dt='9999-12-31')oldfull outer join(selectid,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_datefrom(selectdata.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) rnfrom ${APP}.ods_user_info_incwhere dt='$do_date')t1where rn=1)newon old.id=new.id
)
insert overwrite table ${APP}.dim_user_zip partition(dt)
selectif(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
selectold_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
(selectid,price,sku_name,sku_desc,weight,is_sale,spu_id,category3_id,tm_id,create_timefrom ${APP}.ods_sku_info_fullwhere dt='$do_date'
),
spu as
(selectid,spu_namefrom ${APP}.ods_spu_info_fullwhere dt='$do_date'
),
c3 as
(selectid,name,category2_idfrom ${APP}.ods_base_category3_fullwhere dt='$do_date'
),
c2 as
(selectid,name,category1_idfrom ${APP}.ods_base_category2_fullwhere dt='$do_date'
),
c1 as
(selectid,namefrom ${APP}.ods_base_category1_fullwhere dt='$do_date'
),
tm as
(selectid,tm_namefrom ${APP}.ods_base_trademark_fullwhere dt='$do_date'
),
attr as
(selectsku_id,collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrsfrom ${APP}.ods_sku_attr_value_fullwhere dt='$do_date'group by sku_id
),
sale_attr as
(selectsku_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_attrsfrom ${APP}.ods_sku_sale_attr_value_fullwhere dt='$do_date'group by sku_id
)
insert overwrite table ${APP}.dim_sku_full partition(dt='$do_date')
selectsku.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')
selectprovince.id,province.name,province.area_code,province.iso_code,province.iso_3166_2,region_id,region_name
from
(selectid,name,region_id,area_code,iso_code,iso_3166_2from ${APP}.ods_base_province_fullwhere dt='$do_date'
)province
left join
(selectid,region_namefrom ${APP}.ods_base_region_fullwhere dt='$do_date'
)region
on province.region_id=region.id;
"dim_coupon_full="
insert overwrite table ${APP}.dim_coupon_full partition(dt='$do_date')
selectid,coupon_name,coupon_type,coupon_dic.dic_name,condition_amount,condition_num,activity_id,benefit_amount,benefit_discount,case coupon_typewhen '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
(selectid,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_timefrom ${APP}.ods_coupon_info_fullwhere dt='$do_date'
)ci
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere dt='$do_date'and parent_code='32'
)coupon_dic
on ci.coupon_type=coupon_dic.dic_code
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere 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')
selectrule.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_typewhen '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
(selectid,activity_id,activity_type,condition_amount,condition_num,benefit_amount,benefit_discount,benefit_levelfrom ${APP}.ods_activity_rule_fullwhere dt='$do_date'
)rule
left join
(selectid,activity_name,activity_type,activity_desc,start_time,end_time,create_timefrom ${APP}.ods_activity_info_fullwhere dt='$do_date'
)info
on rule.activity_id=info.id
left join
(selectdic_code,dic_namefrom ${APP}.ods_base_dic_fullwhere 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

这篇关于电商数仓项目----笔记七(数仓DIM层)的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/532748

相关文章

这15个Vue指令,让你的项目开发爽到爆

1. V-Hotkey 仓库地址: github.com/Dafrok/v-ho… Demo: 戳这里 https://dafrok.github.io/v-hotkey 安装: npm install --save v-hotkey 这个指令可以给组件绑定一个或多个快捷键。你想要通过按下 Escape 键后隐藏某个组件,按住 Control 和回车键再显示它吗?小菜一碟: <template

如何用Docker运行Django项目

本章教程,介绍如何用Docker创建一个Django,并运行能够访问。 一、拉取镜像 这里我们使用python3.11版本的docker镜像 docker pull python:3.11 二、运行容器 这里我们将容器内部的8080端口,映射到宿主机的80端口上。 docker run -itd --name python311 -p

在cscode中通过maven创建java项目

在cscode中创建java项目 可以通过博客完成maven的导入 建立maven项目 使用快捷键 Ctrl + Shift + P 建立一个 Maven 项目 1 Ctrl + Shift + P 打开输入框2 输入 "> java create"3 选择 maven4 选择 No Archetype5 输入 域名6 输入项目名称7 建立一个文件目录存放项目,文件名一般为项目名8 确定

【学习笔记】 陈强-机器学习-Python-Ch15 人工神经网络(1)sklearn

系列文章目录 监督学习:参数方法 【学习笔记】 陈强-机器学习-Python-Ch4 线性回归 【学习笔记】 陈强-机器学习-Python-Ch5 逻辑回归 【课后题练习】 陈强-机器学习-Python-Ch5 逻辑回归(SAheart.csv) 【学习笔记】 陈强-机器学习-Python-Ch6 多项逻辑回归 【学习笔记 及 课后题练习】 陈强-机器学习-Python-Ch7 判别分析 【学

系统架构师考试学习笔记第三篇——架构设计高级知识(20)通信系统架构设计理论与实践

本章知识考点:         第20课时主要学习通信系统架构设计的理论和工作中的实践。根据新版考试大纲,本课时知识点会涉及案例分析题(25分),而在历年考试中,案例题对该部分内容的考查并不多,虽在综合知识选择题目中经常考查,但分值也不高。本课时内容侧重于对知识点的记忆和理解,按照以往的出题规律,通信系统架构设计基础知识点多来源于教材内的基础网络设备、网络架构和教材外最新时事热点技术。本课时知识

Vue3项目开发——新闻发布管理系统(六)

文章目录 八、首页设计开发1、页面设计2、登录访问拦截实现3、用户基本信息显示①封装用户基本信息获取接口②用户基本信息存储③用户基本信息调用④用户基本信息动态渲染 4、退出功能实现①注册点击事件②添加退出功能③数据清理 5、代码下载 八、首页设计开发 登录成功后,系统就进入了首页。接下来,也就进行首页的开发了。 1、页面设计 系统页面主要分为三部分,左侧为系统的菜单栏,右侧

SpringBoot项目是如何启动

启动步骤 概念 运行main方法,初始化SpringApplication 从spring.factories读取listener ApplicationContentInitializer运行run方法读取环境变量,配置信息创建SpringApplication上下文预初始化上下文,将启动类作为配置类进行读取调用 refresh 加载 IOC容器,加载所有的自动配置类,创建容器在这个过程

Maven创建项目中的groupId, artifactId, 和 version的意思

文章目录 groupIdartifactIdversionname groupId 定义:groupId 是 Maven 项目坐标的第一个部分,它通常表示项目的组织或公司的域名反转写法。例如,如果你为公司 example.com 开发软件,groupId 可能是 com.example。作用:groupId 被用来组织和分组相关的 Maven artifacts,这样可以避免

论文阅读笔记: Segment Anything

文章目录 Segment Anything摘要引言任务模型数据引擎数据集负责任的人工智能 Segment Anything Model图像编码器提示编码器mask解码器解决歧义损失和训练 Segment Anything 论文地址: https://arxiv.org/abs/2304.02643 代码地址:https://github.com/facebookresear

2. 下载rknn-toolkit2项目

官网链接: https://github.com/airockchip/rknn-toolkit2 安装好git:[[1. Git的安装]] 下载项目: git clone https://github.com/airockchip/rknn-toolkit2.git 或者直接去github下载压缩文件,解压即可。