数据仓库搭建DWT层

最新推荐文章于 2023-03-20 15:59:46 发布
最新推荐文章于 2023-03-20 15:59:46 发布
阅读量941 收藏 4
点赞数 3
分类专栏： 项目 文章标签： 数据仓库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_55906442/article/details/124999575
版权

本篇只是DWT层，其他内容请关注我的博客！在<项目>专栏里！！！

本篇文章参考尚硅谷大数据项目写成！

目录

一、DWT层

1.设备主题宽表

2.会员主题宽表

3.商品主题宽表

4.活动主题宽表

5.地区主题宽表

二、数据导入脚本

一、DWT层

1.设备主题宽表

1）建表语句

create external table dwt_uv_topic
(
    `mid_id` string comment '设备id',
    `brand` string comment '手机品牌',
    `model` string comment '手机型号',
    `login_date_first` string  comment '首次活跃时间',
    `login_date_last` string  comment '末次活跃时间',
    `login_day_count` bigint comment '当日活跃次数',
    `login_count` bigint comment '累积活跃天数'
) COMMENT '设备主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_uv_topic'
tblproperties ("parquet.compression"="lzo");

2）数据导入

insert overwrite table dwt_uv_topic
select
    nvl(new.mid_id,old.mid_id),
    nvl(new.model,old.model),
    nvl(new.brand,old.brand),
    if(old.mid_id is null,'2022-05-20',old.login_date_first),
    if(new.mid_id is not null,'2022-05-20',old.login_date_last),
    if(new.mid_id is not null, new.login_count,0),
    nvl(old.login_count,0)+if(new.login_count>0,1,0)
from
(
    select
        *
    from dwt_uv_topic
)old
full outer join
(
    select
        *
    from dws_uv_detail_daycount
    where dt='2022-05-20'
)new
on old.mid_id=new.mid_id;

3）查询数据

select * from dwt_uv_topic limit 5;

 

2.会员主题宽表

 1）建表语句

create external table dwt_user_topic
(
    user_id string  comment '用户id',
    login_date_first string  comment '首次登录时间',
    login_date_last string  comment '末次登录时间',
    login_count bigint comment '累积登录天数',
    login_last_30d_count bigint comment '最近30日登录天数',
    order_date_first string  comment '首次下单时间',
    order_date_last string  comment '末次下单时间',
    order_count bigint comment '累积下单次数',
    order_amount decimal(16,2) comment '累积下单金额',
    order_last_30d_count bigint comment '最近30日下单次数',
    order_last_30d_amount bigint comment '最近30日下单金额',
    payment_date_first string  comment '首次支付时间',
    payment_date_last string  comment '末次支付时间',
    payment_count decimal(16,2) comment '累积支付次数',
    payment_amount decimal(16,2) comment '累积支付金额',
    payment_last_30d_count decimal(16,2) comment '最近30日支付次数',
    payment_last_30d_amount decimal(16,2) comment '最近30日支付金额'
)COMMENT '会员主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_user_topic/'
tblproperties ("parquet.compression"="lzo");

2）数据导入

insert overwrite table dwt_user_topic
select
    nvl(new.user_id,old.user_id),
    if(old.login_date_first is null and new.login_count>0,'2022-05-20',old.login_date_first),
    if(new.login_count>0,'2022-05-20',old.login_date_last),
    nvl(old.login_count,0)+if(new.login_count>0,1,0),
    nvl(new.login_last_30d_count,0),
    if(old.order_date_first is null and new.order_count>0,'2022-05-20',old.order_date_first),
    if(new.order_count>0,'2022-05-20',old.order_date_last),
    nvl(old.order_count,0)+nvl(new.order_count,0),
    nvl(old.order_amount,0)+nvl(new.order_amount,0),
    nvl(new.order_last_30d_count,0),
    nvl(new.order_last_30d_amount,0),
    if(old.payment_date_first is null and new.payment_count>0,'2022-05-20',old.payment_date_first),
    if(new.payment_count>0,'2022-05-20',old.payment_date_last),
    nvl(old.payment_count,0)+nvl(new.payment_count,0),
    nvl(old.payment_amount,0)+nvl(new.payment_amount,0),
    nvl(new.payment_last_30d_count,0),
    nvl(new.payment_last_30d_amount,0)
from
dwt_user_topic old
full outer join
(
    select
        user_id,
        sum(if(dt='2022-05-20',login_count,0)) login_count,
        sum(if(dt='2022-05-20',order_count,0)) order_count,
        sum(if(dt='2022-05-20',order_amount,0)) order_amount,
        sum(if(dt='2022-05-20',payment_count,0)) payment_count,
        sum(if(dt='2022-05-20',payment_amount,0)) payment_amount,
        sum(if(login_count>0,1,0)) login_last_30d_count,
        sum(order_count) order_last_30d_count,
        sum(order_amount) order_last_30d_amount,
        sum(payment_count) payment_last_30d_count,
        sum(payment_amount) payment_last_30d_amount
    from dws_user_action_daycount
    where dt>=date_add( '2022-05-20',-30)
    group by user_id
)new
on old.user_id=new.user_id;

3）查询数据

select * from dwt_user_topic limit 5;

 

3.商品主题宽表

1）建表语句

create external table dwt_sku_topic
(
    sku_id string comment 'sku_id',
    spu_id string comment 'spu_id',
    order_last_30d_count bigint comment '最近30日被下单次数',
    order_last_30d_num bigint comment '最近30日被下单件数',
    order_last_30d_amount decimal(16,2)  comment '最近30日被下单金额',
    order_count bigint comment '累积被下单次数',
    order_num bigint comment '累积被下单件数',
    order_amount decimal(16,2) comment '累积被下单金额',
    payment_last_30d_count   bigint  comment '最近30日被支付次数',
    payment_last_30d_num bigint comment '最近30日被支付件数',
    payment_last_30d_amount  decimal(16,2) comment '最近30日被支付金额',
    payment_count   bigint  comment '累积被支付次数',
    payment_num bigint comment '累积被支付件数',
    payment_amount  decimal(16,2) comment '累积被支付金额',
    refund_last_30d_count bigint comment '最近三十日退款次数',
    refund_last_30d_num bigint comment '最近三十日退款件数',
    refund_last_30d_amount decimal(16,2) comment '最近三十日退款金额',
    refund_count bigint comment '累积退款次数',
    refund_num bigint comment '累积退款件数',
    refund_amount decimal(16,2) comment '累积退款金额',
    cart_last_30d_count bigint comment '最近30日被加入购物车次数',
    cart_count bigint comment '累积被加入购物车次数',
    favor_last_30d_count bigint comment '最近30日被收藏次数',
    favor_count bigint comment '累积被收藏次数',
    appraise_last_30d_good_count bigint comment '最近30日好评数',
    appraise_last_30d_mid_count bigint comment '最近30日中评数',
    appraise_last_30d_bad_count bigint comment '最近30日差评数',
    appraise_last_30d_default_count bigint comment '最近30日默认评价数',
    appraise_good_count bigint comment '累积好评数',
    appraise_mid_count bigint comment '累积中评数',
    appraise_bad_count bigint comment '累积差评数',
    appraise_default_count bigint comment '累积默认评价数'
 )COMMENT '商品主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_sku_topic/'
tblproperties ("parquet.compression"="lzo");

2）数据导入

insert overwrite table dwt_sku_topic
select 
    nvl(new.sku_id,old.sku_id),
    sku_info.spu_id,
    nvl(new.order_count30,0),
    nvl(new.order_num30,0),
    nvl(new.order_amount30,0),
    nvl(old.order_count,0) + nvl(new.order_count,0),
    nvl(old.order_num,0) + nvl(new.order_num,0),
    nvl(old.order_amount,0) + nvl(new.order_amount,0),
    nvl(new.payment_count30,0),
    nvl(new.payment_num30,0),
    nvl(new.payment_amount30,0),
    nvl(old.payment_count,0) + nvl(new.payment_count,0),
    nvl(old.payment_num,0) + nvl(new.payment_count,0),
    nvl(old.payment_amount,0) + nvl(new.payment_count,0),
    nvl(new.refund_count30,0),
    nvl(new.refund_num30,0),
    nvl(new.refund_amount30,0),
    nvl(old.refund_count,0) + nvl(new.refund_count,0),
    nvl(old.refund_num,0) + nvl(new.refund_num,0),
    nvl(old.refund_amount,0) + nvl(new.refund_amount,0),
    nvl(new.cart_count30,0),
    nvl(old.cart_count,0) + nvl(new.cart_count,0),
    nvl(new.favor_count30,0),
    nvl(old.favor_count,0) + nvl(new.favor_count,0),
    nvl(new.appraise_good_count30,0),
    nvl(new.appraise_mid_count30,0),
    nvl(new.appraise_bad_count30,0),
    nvl(new.appraise_default_count30,0)  ,
    nvl(old.appraise_good_count,0) + nvl(new.appraise_good_count,0),
    nvl(old.appraise_mid_count,0) + nvl(new.appraise_mid_count,0),
    nvl(old.appraise_bad_count,0) + nvl(new.appraise_bad_count,0),
    nvl(old.appraise_default_count,0) + nvl(new.appraise_default_count,0) 
from 
dwt_sku_topic old
full outer join 
(
    select 
        sku_id,
        sum(if(dt='2022-05-20', order_count,0 )) order_count,
        sum(if(dt='2022-05-20',order_num ,0 ))  order_num, 
        sum(if(dt='2022-05-20',order_amount,0 )) order_amount ,
        sum(if(dt='2022-05-20',payment_count,0 )) payment_count,
        sum(if(dt='2022-05-20',payment_num,0 )) payment_num,
        sum(if(dt='2022-05-20',payment_amount,0 )) payment_amount,
        sum(if(dt='2022-05-20',refund_count,0 )) refund_count,
        sum(if(dt='2022-05-20',refund_num,0 )) refund_num,
        sum(if(dt='2022-05-20',refund_amount,0 )) refund_amount,  
        sum(if(dt='2022-05-20',cart_count,0 )) cart_count,
        sum(if(dt='2022-05-20',favor_count,0 )) favor_count,
        sum(if(dt='2022-05-20',appraise_good_count,0 )) appraise_good_count,  
        sum(if(dt='2022-05-20',appraise_mid_count,0 ) ) appraise_mid_count ,
        sum(if(dt='2022-05-20',appraise_bad_count,0 )) appraise_bad_count,  
        sum(if(dt='2022-05-20',appraise_default_count,0 )) appraise_default_count,
        sum(order_count) order_count30 ,
        sum(order_num) order_num30,
        sum(order_amount) order_amount30,
        sum(payment_count) payment_count30,
        sum(payment_num) payment_num30,
        sum(payment_amount) payment_amount30,
        sum(refund_count) refund_count30,
        sum(refund_num) refund_num30,
        sum(refund_amount) refund_amount30,
        sum(cart_count) cart_count30,
        sum(favor_count) favor_count30,
        sum(appraise_good_count) appraise_good_count30,
        sum(appraise_mid_count) appraise_mid_count30,
        sum(appraise_bad_count) appraise_bad_count30,
        sum(appraise_default_count) appraise_default_count30 
    from dws_sku_action_daycount
    where dt >= date_add ('2022-05-20', -30)
    group by sku_id    
)new 
on new.sku_id = old.sku_id
left join 
(select * from dwd_dim_sku_info where dt='2022-05-20') sku_info
on nvl(new.sku_id,old.sku_id)= sku_info.id;

3）查询数据

select * from dwt_sku_topic limit 5;

 

4.活动主题宽表

 1）建表语句

create external table dwt_activity_topic(
    `id` string COMMENT '编号',
    `activity_name` string  COMMENT '活动名称',
    `activity_type` string  COMMENT '活动类型',
    `start_time` string  COMMENT '开始时间',
    `end_time` string  COMMENT '结束时间',
    `create_time` string  COMMENT '创建时间',
    `display_day_count` bigint COMMENT '当日曝光次数',
    `order_day_count` bigint COMMENT '当日下单次数',
    `order_day_amount` decimal(20,2) COMMENT '当日下单金额',
    `payment_day_count` bigint COMMENT '当日支付次数',
    `payment_day_amount` decimal(20,2) COMMENT '当日支付金额',
    `display_count` bigint COMMENT '累积曝光次数',
    `order_count` bigint COMMENT '累积下单次数',
    `order_amount` decimal(20,2) COMMENT '累积下单金额',
    `payment_count` bigint COMMENT '累积支付次数',
    `payment_amount` decimal(20,2) COMMENT '累积支付金额'
) COMMENT '活动主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_activity_topic/'
tblproperties ("parquet.compression"="lzo");

2）数据导入

insert overwrite table dwt_activity_topic
select
    nvl(new.id,old.id),
    nvl(new.activity_name,old.activity_name),
    nvl(new.activity_type,old.activity_type),
    nvl(new.start_time,old.start_time),
    nvl(new.end_time,old.end_time),
    nvl(new.create_time,old.create_time),
    nvl(new.display_count,0),
    nvl(new.order_count,0),
    nvl(new.order_amount,0.0),
    nvl(new.payment_count,0),
    nvl(new.payment_amount,0.0),
    nvl(new.display_count,0)+nvl(old.display_count,0),
    nvl(new.order_count,0)+nvl(old.order_count,0),
    nvl(new.order_amount,0.0)+nvl(old.order_amount,0.0),
    nvl(new.payment_count,0)+nvl(old.payment_count,0),
    nvl(new.payment_amount,0.0)+nvl(old.payment_amount,0.0)
from
(
    select
        *
    from dwt_activity_topic
)old
full outer join
(
    select
        *
    from dws_activity_info_daycount
    where dt='2022-05-20'
)new
on old.id=new.id;

3）查询数据

select * from dwt_activity_topic limit 5;

5.地区主题宽表

1）建表语句

create external table dwt_area_topic(
    `id` bigint COMMENT '编号',
    `province_name` string COMMENT '省份名称',
    `area_code` string COMMENT '地区编码',
    `iso_code` string COMMENT 'iso编码',
    `region_id` string COMMENT '地区ID',
    `region_name` string COMMENT '地区名称',
    `login_day_count` string COMMENT '当天活跃设备数',
    `login_last_30d_count` string COMMENT '最近30天活跃设备数',
    `order_day_count` bigint COMMENT '当天下单次数',
    `order_day_amount` decimal(16,2) COMMENT '当天下单金额',
    `order_last_30d_count` bigint COMMENT '最近30天下单次数',
    `order_last_30d_amount` decimal(16,2) COMMENT '最近30天下单金额',
    `payment_day_count` bigint COMMENT '当天支付次数',
    `payment_day_amount` decimal(16,2) COMMENT '当天支付金额',
    `payment_last_30d_count` bigint COMMENT '最近30天支付次数',
    `payment_last_30d_amount` decimal(16,2) COMMENT '最近30天支付金额'
) COMMENT '地区主题宽表'
stored as parquet
location '/warehouse/gmall/dwt/dwt_area_topic/'
tblproperties ("parquet.compression"="lzo");

2）数据导入

insert overwrite table dwt_area_topic
select
    nvl(old.id,new.id),
    nvl(old.province_name,new.province_name),
    nvl(old.area_code,new.area_code),
    nvl(old.iso_code,new.iso_code),
    nvl(old.region_id,new.region_id),
    nvl(old.region_name,new.region_name),
    nvl(new.login_day_count,0),
    nvl(new.login_last_30d_count,0),
    nvl(new.order_day_count,0),
    nvl(new.order_day_amount,0.0),
    nvl(new.order_last_30d_count,0),
    nvl(new.order_last_30d_amount,0.0),
    nvl(new.payment_day_count,0),
    nvl(new.payment_day_amount,0.0),
    nvl(new.payment_last_30d_count,0),
    nvl(new.payment_last_30d_amount,0.0)
from 
(
    select
        *
    from dwt_area_topic
)old
full outer join
(
    select
        id,
        province_name,
        area_code,
        iso_code,
        region_id,
        region_name,
        sum(if(dt='2022-05-20',login_count,0)) login_day_count,
        sum(if(dt='2022-05-20',order_count,0)) order_day_count,
        sum(if(dt='2022-05-20',order_amount,0.0)) order_day_amount,
        sum(if(dt='2022-05-20',payment_count,0)) payment_day_count,
        sum(if(dt='2022-05-20',payment_amount,0.0)) payment_day_amount,
        sum(login_count) login_last_30d_count,
        sum(order_count) order_last_30d_count,
        sum(order_amount) order_last_30d_amount,
        sum(payment_count) payment_last_30d_count,
        sum(payment_amount) payment_last_30d_amount
    from dws_area_stats_daycount
    where dt>=date_add('2022-05-20',-30)
    group by id,province_name,area_code,iso_code,region_id,region_name
)new
on old.id=new.id;

3）查询数据

select * from dwt_area_topic limit 5;

 

二、数据导入脚本

vim dws_to_dwt.sh

#!/bin/bash

APP=default
hive=/training/hive/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
    do_date=$1
else 
    do_date=`date -d "-1 day" +%F`
fi

sql="
set mapreduce.job.queuename=default;
insert overwrite table ${APP}.dwt_uv_topic
select
    nvl(new.mid_id,old.mid_id),
    nvl(new.model,old.model),
    nvl(new.brand,old.brand),
    if(old.mid_id is null,'$do_date',old.login_date_first),
    if(new.mid_id is not null,'$do_date',old.login_date_last),
    if(new.mid_id is not null, new.login_count,0),
    nvl(old.login_count,0)+if(new.login_count>0,1,0)
from
(
    select
        *
    from ${APP}.dwt_uv_topic
)old
full outer join
(
    select
        *
    from ${APP}.dws_uv_detail_daycount
    where dt='$do_date'
)new
on old.mid_id=new.mid_id;

insert overwrite table ${APP}.dwt_user_topic
select
    nvl(new.user_id,old.user_id),
    if(old.login_date_first is null and new.login_count>0,'$do_date',old.login_date_first),
    if(new.login_count>0,'$do_date',old.login_date_last),
    nvl(old.login_count,0)+if(new.login_count>0,1,0),
    nvl(new.login_last_30d_count,0),
    if(old.order_date_first is null and new.order_count>0,'$do_date',old.order_date_first),
    if(new.order_count>0,'$do_date',old.order_date_last),
    nvl(old.order_count,0)+nvl(new.order_count,0),
    nvl(old.order_amount,0)+nvl(new.order_amount,0),
    nvl(new.order_last_30d_count,0),
    nvl(new.order_last_30d_amount,0),
    if(old.payment_date_first is null and new.payment_count>0,'$do_date',old.payment_date_first),
    if(new.payment_count>0,'$do_date',old.payment_date_last),
    nvl(old.payment_count,0)+nvl(new.payment_count,0),
    nvl(old.payment_amount,0)+nvl(new.payment_amount,0),
    nvl(new.payment_last_30d_count,0),
    nvl(new.payment_last_30d_amount,0)
from
${APP}.dwt_user_topic old
full outer join
(
    select
        user_id,
        sum(if(dt='$do_date',login_count,0)) login_count,
        sum(if(dt='$do_date',order_count,0)) order_count,
        sum(if(dt='$do_date',order_amount,0)) order_amount,
        sum(if(dt='$do_date',payment_count,0)) payment_count,
        sum(if(dt='$do_date',payment_amount,0)) payment_amount,
        sum(if(login_count>0,1,0)) login_last_30d_count,
        sum(order_count) order_last_30d_count,
        sum(order_amount) order_last_30d_amount,
        sum(payment_count) payment_last_30d_count,
        sum(payment_amount) payment_last_30d_amount
    from ${APP}.dws_user_action_daycount
    where dt>=date_add( '$do_date',-30)
    group by user_id
)new
on old.user_id=new.user_id;

insert overwrite table ${APP}.dwt_sku_topic
select 
    nvl(new.sku_id,old.sku_id),
    sku_info.spu_id,
    nvl(new.order_count30,0),
    nvl(new.order_num30,0),
    nvl(new.order_amount30,0),
    nvl(old.order_count,0) + nvl(new.order_count,0),
    nvl(old.order_num,0) + nvl(new.order_num,0),
    nvl(old.order_amount,0) + nvl(new.order_amount,0),
    nvl(new.payment_count30,0),
    nvl(new.payment_num30,0),
    nvl(new.payment_amount30,0),
    nvl(old.payment_count,0) + nvl(new.payment_count,0),
    nvl(old.payment_num,0) + nvl(new.payment_num,0),
    nvl(old.payment_amount,0) + nvl(new.payment_amount,0),
    nvl(new.refund_count30,0),
    nvl(new.refund_num30,0),
    nvl(new.refund_amount30,0),
    nvl(old.refund_count,0) + nvl(new.refund_count,0),
    nvl(old.refund_num,0) + nvl(new.refund_num,0),
    nvl(old.refund_amount,0) + nvl(new.refund_amount,0),
    nvl(new.cart_count30,0),
    nvl(old.cart_count,0) + nvl(new.cart_count,0),
    nvl(new.favor_count30,0),
    nvl(old.favor_count,0) + nvl(new.favor_count,0),
    nvl(new.appraise_good_count30,0),
    nvl(new.appraise_mid_count30,0),
    nvl(new.appraise_bad_count30,0),
    nvl(new.appraise_default_count30,0)  ,
    nvl(old.appraise_good_count,0) + nvl(new.appraise_good_count,0),
    nvl(old.appraise_mid_count,0) + nvl(new.appraise_mid_count,0),
    nvl(old.appraise_bad_count,0) + nvl(new.appraise_bad_count,0),
    nvl(old.appraise_default_count,0) + nvl(new.appraise_default_count,0) 
from 
(
    select
        sku_id,
        spu_id,
        order_last_30d_count,
        order_last_30d_num,
        order_last_30d_amount,
        order_count,
        order_num,
        order_amount  ,
        payment_last_30d_count,
        payment_last_30d_num,
        payment_last_30d_amount,
        payment_count,
        payment_num,
        payment_amount,
        refund_last_30d_count,
        refund_last_30d_num,
        refund_last_30d_amount,
        refund_count,
        refund_num,
        refund_amount,
        cart_last_30d_count,
        cart_count,
        favor_last_30d_count,
        favor_count,
        appraise_last_30d_good_count,
        appraise_last_30d_mid_count,
        appraise_last_30d_bad_count,
        appraise_last_30d_default_count,
        appraise_good_count,
        appraise_mid_count,
        appraise_bad_count,
        appraise_default_count 
    from ${APP}.dwt_sku_topic
)old
full outer join 
(
    select 
        sku_id,
        sum(if(dt='$do_date', order_count,0 )) order_count,
        sum(if(dt='$do_date',order_num ,0 ))  order_num, 
        sum(if(dt='$do_date',order_amount,0 )) order_amount ,
        sum(if(dt='$do_date',payment_count,0 )) payment_count,
        sum(if(dt='$do_date',payment_num,0 )) payment_num,
        sum(if(dt='$do_date',payment_amount,0 )) payment_amount,
        sum(if(dt='$do_date',refund_count,0 )) refund_count,
        sum(if(dt='$do_date',refund_num,0 )) refund_num,
        sum(if(dt='$do_date',refund_amount,0 )) refund_amount,  
        sum(if(dt='$do_date',cart_count,0 )) cart_count,
        sum(if(dt='$do_date',favor_count,0 )) favor_count,
        sum(if(dt='$do_date',appraise_good_count,0 )) appraise_good_count,  
        sum(if(dt='$do_date',appraise_mid_count,0 ) ) appraise_mid_count ,
        sum(if(dt='$do_date',appraise_bad_count,0 )) appraise_bad_count,  
        sum(if(dt='$do_date',appraise_default_count,0 )) appraise_default_count,
        sum(order_count) order_count30 ,
        sum(order_num) order_num30,
        sum(order_amount) order_amount30,
        sum(payment_count) payment_count30,
        sum(payment_num) payment_num30,
        sum(payment_amount) payment_amount30,
        sum(refund_count) refund_count30,
        sum(refund_num) refund_num30,
        sum(refund_amount) refund_amount30,
        sum(cart_count) cart_count30,
        sum(favor_count) favor_count30,
        sum(appraise_good_count) appraise_good_count30,
        sum(appraise_mid_count) appraise_mid_count30,
        sum(appraise_bad_count) appraise_bad_count30,
        sum(appraise_default_count) appraise_default_count30 
    from ${APP}.dws_sku_action_daycount
    where dt >= date_add ('$do_date', -30)
    group by sku_id    
)new 
on new.sku_id = old.sku_id
left join 
(select * from ${APP}.dwd_dim_sku_info where dt='$do_date') sku_info
on nvl(new.sku_id,old.sku_id)= sku_info.id;

insert overwrite table ${APP}.dwt_activity_topic
select
    nvl(new.id,old.id),
    nvl(new.activity_name,old.activity_name),
    nvl(new.activity_type,old.activity_type),
    nvl(new.start_time,old.start_time),
    nvl(new.end_time,old.end_time),
    nvl(new.create_time,old.create_time),
    nvl(new.display_count,0),
    nvl(new.order_count,0),
    nvl(new.order_amount,0.0),
    nvl(new.payment_count,0),
    nvl(new.payment_amount,0.0),
    nvl(new.display_count,0)+nvl(old.display_count,0),
    nvl(new.order_count,0)+nvl(old.order_count,0),
    nvl(new.order_amount,0.0)+nvl(old.order_amount,0.0),
    nvl(new.payment_count,0)+nvl(old.payment_count,0),
    nvl(new.payment_amount,0.0)+nvl(old.payment_amount,0.0)
from
(
    select
        *
    from ${APP}.dwt_activity_topic
)old
full outer join
(
    select
        *
    from ${APP}.dws_activity_info_daycount
    where dt='$do_date'
)new
on old.id=new.id;

insert overwrite table ${APP}.dwt_area_topic
select
    nvl(old.id,new.id),
    nvl(old.province_name,new.province_name),
    nvl(old.area_code,new.area_code),
    nvl(old.iso_code,new.iso_code),
    nvl(old.region_id,new.region_id),
    nvl(old.region_name,new.region_name),
    nvl(new.login_day_count,0),
    nvl(new.login_last_30d_count,0),
    nvl(new.order_day_count,0),
    nvl(new.order_day_amount,0.0),
    nvl(new.order_last_30d_count,0),
    nvl(new.order_last_30d_amount,0.0),
    nvl(new.payment_day_count,0),
    nvl(new.payment_day_amount,0.0),
    nvl(new.payment_last_30d_count,0),
    nvl(new.payment_last_30d_amount,0.0)
from 
(
    select
        *
    from ${APP}.dwt_area_topic
)old
full outer join
(
    select
        id,
        province_name,
        area_code,
        iso_code,
        region_id,
        region_name,
        sum(if(dt='$do_date',login_count,0)) login_day_count,
        sum(if(dt='$do_date',order_count,0)) order_day_count,
        sum(if(dt='$do_date',order_amount,0.0)) order_day_amount,
        sum(if(dt='$do_date',payment_count,0)) payment_day_count,
        sum(if(dt='$do_date',payment_amount,0.0)) payment_day_amount,
        sum(login_count) login_last_30d_count,
        sum(order_count) order_last_30d_count,
        sum(order_amount) order_last_30d_amount,
        sum(payment_count) payment_last_30d_count,
        sum(payment_amount) payment_last_30d_amount
    from ${APP}.dws_area_stats_daycount
    where dt>=date_add('$do_date',-30)
    group by id,province_name,area_code,iso_code,region_id,region_name
)new
on old.id=new.id;
"

$hive -e "$sql"

2）增加脚本执行权限：chmod 777 dws_to_dwt.sh

3）执行脚本导入数据： dws_to_dwt.sh 2022-05-21

4）查看导入数据

select * from dwt_uv_topic limit 5;
select * from dwt_user_topic limit 5;
select * from dwt_sku_topic limit 5;
select * from dwt_activity_topic limit 5;
select * from dwt_area_topic limit 5;

 

作者水平低，如有错误，恳请指正！谢谢！！！！！

本篇文章参考尚硅谷大数据项目写成！！！

一个人的牛牛
关注
  • 3
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据仓库分层
别这么骄傲
11-02 576
图文结合，方便理解数仓
数仓搭建-DWT层
qq_43827516的博客
11-10 401
6.1 设备主题宽表 累积型表：1、找old (在当前层找，也就是当前要插入数据的表) new(当前层的下一层找) 2、full outer join 1）建表语句 hive (gmall)> drop table if exists dwt_uv_topic; create external table dwt_uv_topic ( `mid_id` string comment '设备id', `brand` string comment '手机品牌', `model`
（23）DWS 层与DWT 层
song_quan_的博客
07-11 2619
1.设计 DWS 层和DWT 层统称宽表层，这两层的设计思想大致相同，通过以下案例进行阐述。 1）问题引出：两个需求，统计每个省份订单的个数、统计每个省份订单的总金额 2）处理办法：都是将省份表和订单表进行join，group by 省份，然后计算。同样数据被计算了两次，实际上类似的场景还会更多。 那怎么设计能避免重复计算呢？ 针对上述场景，可以设计一张地区宽表，其主键为地区ID，字段包含为：下单次数、下单金额、支付次数、支付金额等。上述所有指标都统一进行计算，并将结果保存在该宽表中，这样就能有效
数仓分层（ODS、DWD、DWS、DWT、ADS）和数仓建模
热门推荐
wjt199866的博客
03-24 4万+
文章目录一、数仓分层数仓概念ODS（原始数据层）做了哪些事DWD（明细数据层）做了哪些事DWS（服务数据层）做了哪些事DWT（主题数据层）做了哪些事ADS（应用数据层）做了哪些事二、数仓建模常用的建模工具ODS 层DWD 层DWS 层DWT 层ADS 层 一、数仓分层 数仓概念 什么是数仓：数据仓库是为企业所有决策制定过程，提供所有系统数据支持的战略集合。通过对数据仓库中数据的分析，可以帮助企业改进业务流程、控制成本、提高产品质量等。数据仓库并不是数据的最终目的地，而是为数据最终的目的地做好准备。这些准
数仓及其维度(分层)建模(ODS DWD DWS DWT ADS)
weixin_45532984的博客
05-12 1万+
一. 数仓及其维度 1. 什么是数仓？ 数据仓库，简称数仓,（ Data Warehouse ）。从逻辑上理解，数据库和数仓没有区别，都是通过数据库软件实现存放数据的地方，只不过从数据量来说，数据仓库要比数据库更庞大。 数仓主要是为企业制定决策，提供数据支持的。当业务简单，可以用数据库来存储，分析，制表。但当数据量几何式增长，需要跨机器整合时，数仓就是非常必要的了。 2. 数仓的特点 （1）集成性 数仓中存储的数据来源于多个数据源，原始数据在不同数据源中的存储方式.........
大数据项目离线数仓（全 ）二（数仓系统）
qq_55906442的博客
05-28 2865
一、数据仓库系统 1.1基础概念 3.1.1数据分层的好处 1.把复杂问题简单化，将复杂的任务分解成多层来完成，每一层只处理简单任务，方便定位问题。 2.减少重复开发，规范数据分层，通过中间层数据，能够减少极大的重复计算，增加一次计算结果的复用性。 3.隔离原始数据：不论是数据的异常还是数据的敏感性，使真实数据与统计数据解耦开。 3.1.2数据分层 ODS：原始数据层,存放原始数据,直接加载原始日志、数据,数据保持原貌不做处理。 DWD：对ODS层数据进行清洗(去除空值,脏数据,超过极限范围
数据仓库学习笔记
07-13
离散小波变换（DWT）是一种线性信号处理技术，当用于数据向量 D 时，将它转换成不同的数值向量小波系数 D’。两个向量具有相同的长度。离散小波变换可以用于数据压缩、去噪、特征提取等。 离散富里叶变换（DFT）是...
数据仓库知识(数仓建模以及分层).pdf
最新发布
08-29
- DWS层：汇总数据层，对DWD层数据进行聚合，减少数据量，优化查询性能。 - DWT层：宽表层或主题层，进一步融合数据，满足特定业务场景需求。 - ADS层：分析服务层，面向最终用户，提供易于理解和使用的分析结果。 ...
数据仓库学习.docx
05-01
分层通常分为ODS（原始数据层）、DWD（明细事实层）、DWS（数据仓库服务层）、DWT（主题域仓库层）和ADS（应用数据服务层）。每层都有特定的功能，例如ODS保留原始数据，DWD用于数据清洗和规范化，DWS则提供汇总数据...
4层DWT离散小波变换
03-29
总的来说，4层DWT结合97小波是一种强大的图像处理工具，它能够在保持图像质量和信息完整性的同时，有效地提取和压缩数据。这种技术对于图像分析、压缩、传输以及噪声去除等场景有着显著的实用价值。在实际应用中，...
大数据项目之电商数仓（2用户行为数据仓库）.docx
08-03
此数仓项目文档详细介绍从项目需求分析到数据生成模块、数据采集模块、数仓各分层搭建再到业务实现的过程，其中各种环境的配置很细致，很有价值学习一下。
3D图像DWT变换MATLAB源码
12-24
2. **分解**：使用`wavedec3`进行3D DWT，该函数需要输入3D图像矩阵、小波基名和分解层数。分解结果会得到一系列的系数，表示不同方向和尺度的细节信息。 3. **操作**：对分解得到的系数进行操作，如阈值处理（用于...
电商数据仓库理论
Intelia的博客
06-23 618
电商数据仓库理论数据仓库分层为什么要分层数据集市与数据仓库概念数仓命名规范表命名脚本命名规范表字段类型数仓理论范式理论范式概念函数依赖三范式区分关系建模和维度建模关系建模维度建模维度表和事实表数据仓库建模数仓建模数仓建模 数据仓库分层 为什么要分层 ODS（Operation Data Store） 原始数据层。存放原始数据，直接加载原始日志、数据，数据保持原貌不作处理 DWD（Data Warehouse Detail） 明细数据层。对ODS层数据进行清洗（去除空值，脏数据，超过极限范围的数据）、
五、数据仓库电商项目——DWT层、可与DWS合并一层
.
05-14 1230
DWT层 DWT层将DWS层每日聚合的数据进行积累，DWT层不是分区表，是一个累积型全量表，并且数据来源与DWS层。 累积性全量表：查询要改动的旧数据，查询新增和变化的新数据，新旧关联，以新换旧，导入覆盖。 DWT设备主题宽表 用户主题宽表 drop table if exists dwt_user_topic; create external table dwt_user_topic ( user_id string comment '用户id', login_d..
[转]科普 | 什么是宽表？
Toby的博客
03-20 1744
宽表
数仓建模，什么是宽表？如何设计？好处与不足
lz_N_one的博客
07-27 6616
数仓建模，什么是宽表？如何设计？好处与不足，如何运用宽表
离线数仓搭建_14_DWT数据构建
勇敢牛牛，不怕困难！
09-28 485
（1）在/home/atguigu/bin目录下创建脚本dws_to_dwt_init.sh。（1）在/home/atguigu/bin目录下创建脚本dws_to_dwt.sh。（2）查看数据是否导入成功。（2）增加脚本执行权限。在脚本中填写如下内容。在脚本中填写如下内容。
数仓实战06：数仓搭建-DWT层
qq_28286027的博客
08-19 1138
1.设备主题宽表 1）建表语句 hive (gmall) > DROP TABLE IF EXISTS dwt_uv_topic; CREATE external TABLE dwt_uv_topic ( `mid_id` string COMMENT '设备唯一标识', `user_id` string COMMENT '用户标识', `version_code` string COMMENT '程序版本号', `version_name` string COMMENT '程序版本名',
数仓搭建——DWT层
zhangzhagn_的博客
07-28 218
数仓搭建笔记——DWT层

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值