8. DWD层数据汇总
1. 手动导入表
1. 对ODS层数据加工
如：
ODS地区+ODS省份=>DWD省份地区
ODS 商品信息 + ODS 品牌 + ODS 商品一级分类 + ODS 商品二级分类 + ODS 商品三级分类=>DWD商品信息
2. DWD层表结构
3. 创建表
CREATE TABLE `dwd_order_info` (
`id` string COMMENT '订单 id',
`total_amount` double COMMENT '订单总额',
`order_status` string COMMENT ' 1 未支付 2 已支付 3 已发货 4 已收货 5 完成',
`user_id` string COMMENT '用户 id',
`payment_way` string COMMENT '付款方式',
`out_trade_no` string COMMENT '订单流失号',
`province_id` string COMMENT '省市 id',
`create_time` string COMMENT '创建时间',
`operate_time` string COMMENT '修改时间'
)
COMMENT '订单表'
PARTITIONED BY (ds string);
CREATE TABLE `dwd_order_detail` (
`id` string COMMENT '明细 id',
`order_id` string COMMENT '订单 id',
`user_id` string COMMENT '用户 id',
`sku_id` string COMMENT '商品 id',
`sku_name` string COMMENT '商品名称',
`order_price` string COMMENT '购买价格',
`sku_num` string COMMENT '购物数量',
`province_id` string COMMENT '省市 id',
`create_time` string COMMENT '创建时间'
)
COMMENT '订单明细'
PARTITIONED BY (ds string);
CREATE TABLE `dim_sku_info_df` (
`id` string COMMENT '商品 id',
`spu_id` string COMMENT 'spuid',
`price` double COMMENT '商品价格',
`sku_name` string COMMENT '商品名称',
`sku_desc` string COMMENT '商品描述',
`weight` double COMMENT '重量',
`tm_id` string COMMENT '品牌 id',
`tm_name` string COMMENT '品牌名称',
`category3_id` string COMMENT '三级分类 id',
`category2_id` string COMMENT '二级分类 id',
`category1_id` string COMMENT '一级分类 id',
`category3_name` string COMMENT '三级分类名称',
`category2_name` string COMMENT '二级分类名称',
`category1_name` string COMMENT '一级分类名称',
`create_time` string COMMENT '创建时间'
)
COMMENT '商品表信息'
PARTITIONED BY (ds string);
CREATE TABLE `dim_user_info_df` (
`id` string COMMENT 'id',
`name` string COMMENT '用户名称',
`birthday` string COMMENT '生日',
`gender` string COMMENT '性别',
`email` string COMMENT '邮箱',
`user_level` string COMMENT '等级',
`create_time` string COMMENT '注册时间'
)
COMMENT '用户信息表'
PARTITIONED BY (ds string);
CREATE TABLE `dim_base_province_df` (
`id` string COMMENT 'id',
`province_name` string COMMENT '省市名称',
`region_id` string COMMENT '地区 id',
`region_name` string COMMENT '地区名称'
)
COMMENT '地区省市表'
PARTITIONED BY (ds string);
4. 手动将数据导入DWD层
Insert overwrite table dwd_order_info_di partition(ds)
select id,
total_amount,
order_status,
user_id,
payment_way,
out_trade_no,
province_id,
create_time,
operate_time,
ds
from ods_order_info_di
where ds='20220518' and id is not null;
insert overwrite table dwd_order_detail_di partition(ds)
select od.id,
order_id,
oi.user_id,
sku_id,
sku_name,
order_price,
sku_num,
oi.province_id,
od.create_time,
od.ds
from ods_order_detail_di od join ods_order_info_di oi
on od.order_id = oi.id and oi.ds = '20220518'
and od.ds = '20220518' and od.id is not null;
insert overwrite table dim_sku_info_df partition(ds)
select
sku.id,
sku.spu_id,
sku.price,
sku.sku_name,
sku.sku_desc,
sku.weight,
sku.tm_id,
tm.tm_name,
sku.category3_id,
c2.id category2_id ,
c1.id category1_id,
c3.name category3_name,
c2.name category2_name,
c1.name category1_name,
sku.create_time,
sku.ds
from
(
select *
from ods_sku_info_df
where ds='20220518' and id is not null
) sku
join ods_base_category3_df c3 on sku.category3_id = c3.id and
c3.ds = '20220518'
join ods_base_category2_df c2 on c3.category2_id = c2.id and
c2.ds = '20220518'
join ods_base_category1_df c1 on c2.category1_id = c1.id and
c1.ds = '20220518'
join ods_base_trademark_df tm on tm.tm_id = sku.tm_id and tm.ds
= '20220518';
insert overwrite table dim_user_info_df partition(ds)
select id,
name ,
birthday,
gender,
email,
user_level,
create_time,
ds from ods_user_info_df
where ds='20220518' and id is not null;
insert overwrite table dim_base_province_df PARTITION (ds)
select
p.id,
p.name,
p.region_id,
r.region_name,
p.ds
from ods_base_province_df p join ods_base_region_df r
on p.region_id = r.id and p.ds='20220518' and r.ds =
'20220518';
5.手动查询
select * from dwd_order_info where ds='20220521';
select * from dwd_order_detail where ds='20220521';
select * from dim_sku_info_df where ds='20220518';
select * from dim_user_info_df where ds='20220518';
select * from dim_base_province_df where ds='20220518';
2. DWD层数据导入脚本
1. 表dwd_order_info
Insert overwrite table dwd_order_info partition(ds)
Select id,
total_amount,
order_status,
user_id,
payment_way,
out_trade_no,
province_id,
create_time,
operate_time,
ds
from ods_order_info_di
where ds='${bizdate}' and id is not null;
2. 表dwd_order_detail
insert overwrite table dwd_order_detail partition(ds)
select od.id,
order_id,
oi.user_id,
sku_id,
sku_name,
order_price,
sku_num,
oi.province_id,
od.create_time,
od.ds
from ods_order_detail_di od join ods_order_info_di oi
on od.order_id = oi.id and oi.ds = '${bizdate}'
and od.ds = '${bizdate}' and od.id is not null;
3. 表dim_sku_info_df_sql
insert overwrite table dim_sku_info_df partition(ds)
select
sku.id,
sku.spu_id,
sku.price,
sku.sku_name,
sku.sku_desc,
sku.weight,
sku.tm_id,
tm.tm_name,
sku.category3_id,
c2.id category2_id ,
c1.id category1_id,
c3.name category3_name,
c2.name category2_name,
c1.name category1_name,
sku.create_time,
sku.ds
from
(
select *
from ods_sku_info_df
where ds='${bizdate}' and id is not null
) sku
join ods_base_category3_df c3 on sku.category3_id = c3.id and
c3.ds = '${bizdate}'
join ods_base_category2_df c2 on c3.category2_id = c2.id and
c2.ds = '${bizdate}'
join ods_base_category1_df c1 on c2.category1_id = c1.id and
c1.ds = '${bizdate}'
join ods_base_trademark_df tm on tm.tm_id = sku.tm_id and tm.ds
= '${bizdate}';
4. 表dim_user_info_df
insert overwrite table dim_user_info_df partition(ds)
select id,
name ,
birthday,
gender,
email,
user_level,
create_time,
ds from ods_user_info_df
where ds='${bizdate}' and id is not null;
5. 表dim_base_province_df
insert overwrite table dim_base_province_df PARTITION (ds)
select
p.id,
p.name,
p.region_id,
r.region_name,
p.ds
from ods_base_province_df p join ods_base_region_df r
on p.region_id = r.id and p.ds='${bizdate}' and r.ds =
'${bizdate}';
2. dws层
1. 建表语句
CREATE TABLE `dws_trade_detail` (
`user_id` string COMMENT '用户 id',
`sku_id` string COMMENT '商品 Id',
`user_gender` string COMMENT '用户性别',
`user_age` string COMMENT '用户年龄',
`user_level` string COMMENT '用户等级',
`sku_price` double COMMENT '商品当日价格',
`sku_name` string COMMENT '商品名称',
`sku_category3_id` string COMMENT '商品三级品类 id',
`sku_category2_id` string COMMENT '商品二级品类 id',
`sku_category1_id` string COMMENT '商品一级品类 id',
`sku_category3_name` string COMMENT '商品三级品类名称',
`sku_category2_name` string COMMENT '商品二级品类名称',
`sku_category1_name` string COMMENT '商品一级品类名称',
`spu_id` string COMMENT '商品 spu',
`tm_id` string COMMENT '品牌 id',
`tm_name` string COMMENT '品牌名称',
`province_id` string COMMENT '省市 id',
`province_name` string COMMENT '省市名称',
`region_id` string COMMENT '地区 id',
`region_name` string COMMENT '地区名称',
`sku_num` double COMMENT '购买个数',
`order_count` bigint COMMENT '当日下单单数',
`order_amount` double COMMENT '当日下单金额'
)
COMMENT '用户单日交易行为宽表'
PARTITIONED BY (ds string);
2. 插入DWS表
with tmp_trade AS
(
select
od.user_id,od.sku_id,od.province_id,
sum(sku_num) sku_num,
count(*) order_count,
sum(od.order_price*sku_num) order_amount
from dwd_order_detail od
where od.ds='20220521'
group by od.user_id, od.sku_id, od.province_id
)
insert OVERWRITE TABLE dws_trade_detail PARTITION(ds='20220521')
select
tmp_trade.user_id,
tmp_trade.sku_id,
u.gender,
months_between(to_char(to_date('20220521','yyyymmdd'),'yyyy-mm-dd'), u.birthday)/12 age,
u.user_level,
price,
sku_name,
category3_id,
category2_id,
category1_id,
category3_name,
category2_name,
category1_name,
spu_id,
tm_id,
tm_name,
p.id,
p.province_name,
p.region_id,
p.region_name,
tmp_trade.sku_num,
tmp_trade.order_count,
tmp_trade.order_amount
from tmp_trade
left join dim_user_info_df u on u.id = tmp_trade.user_id and
u.ds = '20220521'
left join dim_sku_info_df s on tmp_trade.sku_id = s.id and s.ds
= '20220521'
left join dim_base_province_df p on tmp_trade.province_id = p.id
and p.ds='20220521';
3. 查询dws_trade_detail表
select *
from dws_trade_detail
where ds = '20220521';
4. trade_detail脚本导入
with tmp_trade AS
(
select
od.user_id,od.sku_id,od.province_id,
sum(sku_num) sku_num,
count(*) order_count,
sum(od.order_price*sku_num) order_amount
from dwd_order_detail od
where od.ds='${bizdate}'
group by od.user_id, od.sku_id, od.province_id
)
insert OVERWRITE TABLE dws_trade_detail PARTITION (ds='${bizdate}')
select
tmp_trade.user_id,
tmp_trade.sku_id,
u.gender,
months_between('${bizdate}', u.birthday)/12 age,
u.user_level,
price,
sku_name,
category3_id,
category2_id,
category1_id,
category3_name,
category2_name,
category1_name,
spu_id,
tm_id,
tm_name,
p.id,
p.province_name,
p.region_id,
p.region_name,
tmp_trade.sku_num,
tmp_trade.order_count,
tmp_trade.order_amount
from tmp_trade
left join dim_user_info_df u on u.id = tmp_trade.user_id and
u.ds = '${bizdate}'
left join dim_sku_info_df s on tmp_trade.sku_id = s.id and s.ds
= '${bizdate}'
left join dim_base_province_df p on tmp_trade.province_id = p.id
and p.ds='${bizdate}';
本文来自博客园，作者：jsqup，转载请注明原文链接：https://www.cnblogs.com/jsqup/p/16298578.html
