电商数据仓库（七）

欢迎

你好！这是我历经1个半月的学习（Apache和CDH），做完的一个项目，本次和你们分享一下Apache版。
感谢您的阅读！

第1章~第4章在电商数据仓库（一）
第5章~第5章在电商数据仓库（二）
第6章~第8章在电商数据仓库（三)
第8章~第9章在电商数据仓库（四）
第10章~第12章在电商数据仓库（五）
第13章~第15章在电商数据仓库（六）
第16章~第17章在电商数据仓库（七）
第18章~第19章在电商数据仓库（八）
第20章~第21章在电商数据仓库（九）
第22章~第24章在电商数据仓库（十）
第24章 完结

第16章 DWD层搭建

16.1 建表

16.1.1 订单表

1. 建表

create external table dwd_order_info (
    `id` string COMMENT '',
    `total_amount` decimal(10,2) COMMENT '',
    `order_status` string COMMENT ' 1 2 3 4 5',
    `user_id` string COMMENT 'id',
    `payment_way` string COMMENT '',
    `out_trade_no` string COMMENT '',
    `create_time` string COMMENT '',
    `operate_time` string COMMENT ''
) 
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_order_info/'
tblproperties ("parquet.compression"="snappy");

2. sql

insert overwrite table  dwd_order_info partition(dt)
select * from ods_order_info 
where dt='2021-02-24' and id is not null;

16.1.2 订单详情表

1. 建表

create external table dwd_order_detail( 
    `id` string COMMENT '',
    `order_id` decimal(10,2) COMMENT '', 
    `user_id` string COMMENT 'id',
    `sku_id` string COMMENT 'id',
    `sku_name` string COMMENT '',
    `order_price` string COMMENT '',
    `sku_num` string COMMENT '',
    `create_time` string COMMENT ''
)
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_order_detail/'
tblproperties ("parquet.compression"="snappy");

2. sql

insert overwrite table dwd_order_detail partition(dt)
select * from ods_order_detail 
where dt='2021-02-24'   and id is not null;

16.1.3 用户表

1. 建表

create external table dwd_user_info( 
    `id` string COMMENT 'id',
    `name` string COMMENT '', 
    `birthday` string COMMENT '',
    `gender` string COMMENT '',
    `email` string COMMENT '',
    `user_level` string COMMENT '',
    `create_time` string COMMENT ''
) 
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_user_info/'
tblproperties ("parquet.compression"="snappy");

2. sql

insert overwrite table dwd_user_info partition(dt)
select * from ods_user_info
where dt='2021-02-24' and id is not null;

16.1.4 支付流水表

1. 建表

create external table dwd_payment_info(
    `id`   bigint COMMENT '',
    `out_trade_no`    string COMMENT '',
    `order_id`        string COMMENT '',
    `user_id`         string COMMENT '',
    `alipay_trade_no` string COMMENT '',
    `total_amount`    decimal(16,2) COMMENT '',
    `subject`         string COMMENT '',
    `payment_tpe`    string COMMENT '',
    `payment_time`    string COMMENT ''
   )  
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_payment_info/'
tblproperties ("parquet.compression"="snappy");

2. sql

insert overwrite table dwd_payment_info partition(dt)
select * from ods_payment_info
where dt='2021-02-24' and id is not null;

16.1.5 商品表

1. 建表

create external table dwd_sku_info(
    `id` string COMMENT 'skuId',
    `spu_id` string COMMENT 'spuid',
    `price` decimal(10,2) COMMENT '',
    `sku_name` string COMMENT '',
    `sku_desc` string COMMENT '',
    `weight` string COMMENT '',
    `tm_id` string COMMENT 'id',
    `category3_id` string COMMENT '1id',
    `category2_id` string COMMENT '2id',
    `category1_id` string COMMENT '3id',
    `category3_name` string COMMENT '3',
    `category2_name` string COMMENT '2',
    `category1_name` string COMMENT '1',
    `create_time` string COMMENT ''
) 
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_sku_info/'
tblproperties ("parquet.compression"="snappy");

2. 分析

相关表
ods_ods_sku_info
ods_base_category1
ods_base_category2
ods_base_category3

思路
通过四个表先过滤要导入日期的数据，再关联，将和商品相关的1，2，3级分类信息汇总，插入到dwd_sku_info

3. sql

insert overwrite table dwd_sku_info partition(dt)
select  
    sku.id,
    sku.spu_id,
    sku.price,
    sku.sku_name,
    sku.sku_desc,
    sku.weight,
    sku.tm_id,
    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.dt
from
    ods_sku_info sku
join ods_base_category3 c3 on sku.category3_id=c3.id 
    join ods_base_category2 c2 on c3.category2_id=c2.id 
    join ods_base_category1 c1 on c2.category1_id=c1.id 
where sku.dt='2021-02-24'  and c2.dt='2021-02-24'
and c3.dt='2021-02-24' and c1.dt='2021-02-24'
and sku.id is not null;

16.2 写成脚本

把这5个表的sql写成脚本

第17章 DWS层搭建

17.1 用户行为宽表

1. 建表

create external table dws_user_action 
(   
    user_id          string      comment '用户 id',
    order_count     bigint      comment '下单次数 ',
    order_amount    decimal(16,2)  comment '下单金额 ',
    payment_count   bigint      comment '支付次数',
    payment_amount  decimal(16,2) comment '支付金额 ',
    comment_count   bigint      comment '评论次数'
) COMMENT '每日用户行为宽表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dws/dws_user_action/';

2. 分析

需求
创建用户在电商业务中的行为宽表，统计每个用户每天的下单总数，支付总数，下单总金额，支付总金额和评论次数

相关表
dwd_order_info： 取user_id(用户)，total_amount(当前订单金额)
dwd_payment_info： 取user_id(用户)，total_amount(当前支付金额)
dwd_comment_log： 取user_id(用户)，统计评论次数

full join和union all
最后拼接结果集时，不能使用join!因为三个结果集中，可能存在差异（只存在在t3，不在t2,t1中）
不使用join，使用union all
如果基于用户在收货成功后才能评论的业务逻辑，可以使用Join!
union all 要注意拼接结果集的字段个数，类型，顺序必须一致！

3. sql

insert overwrite TABLE dws_user_action PARTITION(dt='2021-02-24')
select
    user_id,sum(order_count),sum(order_amount),
    sum(payment_count),sum(payment_amount),
    sum(comment_count)
from
(select 
    user_id,count(*) order_count,sum(total_amount) order_amount,
    0 payment_count,0 payment_amount,0 comment_count
from dwd_order_info
where dt='2021-02-24'
GROUP by user_id
union all
select 
    user_id,0 order_count,0 order_amount,
    count(*) payment_count,sum(total_amount) payment_amount,
    0 comment_count
from dwd_payment_info
where dt='2021-02-24'
GROUP by user_id
union all
select 
    user_id,0 order_count,0 order_amount,
    0 payment_count,0 payment_amount,
    count(*) comment_count
from dwd_comment_log
where dt='2021-02-24'
GROUP by user_id) tmp
GROUP by user_id

4. 生成脚本

17.2 用户购买商品明细表

1. 建表

create external table dws_sale_detail_daycount
(   
    user_id   string  comment '用户 id',
    sku_id    string comment '商品 Id',
    user_gender  string comment '用户性别',
    user_age string  comment '用户年龄',
    user_level string comment '用户等级',
    order_price decimal(10,2) comment '商品价格',
    sku_name string   comment '商品名称',
    sku_tm_id string   comment '品牌id',
    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',
    sku_num  int comment '购买个数',
    order_count string comment '当日下单单数',
    order_amount string comment '当日下单金额'
) COMMENT '用户购买商品明细表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dws/dws_user_sale_detail_daycount/'
tblproperties ("parquet.compression"="snappy");

2. 分析

需求
用户购买商品明细表： 用户分别购买的商品明细是什么

相关表
dwd_user_info： 取出用户的相关信息
dwd_sku_info： 取出和商品相关的信息
ods_order_detail： 订单详情表
ods_payment_info： 支付流水表

思路
sku_num int comment ‘购买个数’：只有支付了才算购买，下单不算购买！要从ods_order_detail和ods_payment_info的交集中取
根据birthday求年龄（1999-9-9） ：ceil(datediff(‘2021-02-24’,‘1999-9-9’)/365)

3. sql

with t1 as
(select 
    id user_id,gender user_gender,
    ceil(months_between('2021-02-24',birthday)/12) user_age,
    user_level
from  dwd_user_info
where dt='2021-02-24'),
t2 as 
(select 
    id sku_id,price order_price,sku_name,tm_id sku_tm_id,
    category3_id sku_category3_id,
    category2_id sku_category2_id,
    category1_id sku_category1_id,
    category3_name sku_category3_name, 
    category2_name sku_category2_name, 
    category1_name sku_category1_name,
    spu_id spu_id
from  dwd_sku_info
where  dt='2021-02-24'),
t3 as 
(select 
    orderdatail.sku_num,orderdatail.sku_id,orderdatail.user_id
from ods_order_detail orderdatail join ods_payment_info payment
on orderdatail.order_id = payment.order_id
),
t4 as 
(select 
    orderdatail.sku_id,orderdatail.user_id,
    count(*) order_count,
    sum(orderdatail.order_price*orderdatail.sku_num) order_amount
from ods_order_detail orderdatail join ods_payment_info payment
on orderdatail.order_id = payment.order_id
group by orderdatail.sku_id,orderdatail.user_id)
insert overwrite TABLE dws_sale_detail_daycount PARTITION(dt='2021-02-24')
select 
    t1.user_id,t2.sku_id,t1.user_gender,t1.user_age,t1.user_level,
    t2.order_price,t2.sku_name,t2.sku_tm_id,t2.sku_category3_id,
    t2.sku_category2_id,t2.sku_category1_id,
    t2.sku_category3_name,t2.sku_category2_name,t2.sku_category1_name,
    t2.spu_id,t3.sku_num,t4.order_count,t4.order_amount
from  t4 join t3 
on t4.sku_id=t3.sku_id and t4.user_id=t3.user_id
join t1  on t1.user_id=t3.user_id
join t2 on t3.sku_id=t2.sku_id

4. 生成脚本

17.3 新付费用户数

1. 建表

create external table dws_pay_user_detail(   
    `user_id` string comment '付费用户id',
    `name` string comment '付费用户姓名',
    `birthday` string COMMENT '',
    `gender` string COMMENT '',
    `email` string COMMENT '',
    `user_level` string COMMENT ''
) COMMENT '付费用户表'
PARTITIONED BY (`dt` string)
stored as parquet
location '/warehouse/gmall/dws/dws_pay_user_detail/';

2. 分析

需求
新付费用户数： 判断今天掏钱支付的用户中，哪些是新用户，统计其数量
取今天支付的所有用户-历史新付费用户(dws_pay_user_detail)

相关表
dws_pay_user_detail: 每天统计今天的新付费用户有哪些
截至到今天，表中已经有了今天之前所有付费的用户(付费的老用户)
dws_sale_detail_daycount： 取所有掏钱的用户
dwd_user_info: 用户信息表

思路
从dws_sale_detail_daycount取今天所有用户的购买明细，和dws_pay_user_detail做差集，统计出哪些是新用户，去重后，和用户信息表拼接：

3. sql

insert overwrite table dws_pay_user_detail PARTITION(dt='2021-02-24')
SELECT
   ui.id,ui.name,ui.birthday,ui.gender,ui.email,ui.user_level
from 
(select 
    distinct t1.user_id
from 
(select 
    user_id
from dws_sale_detail_daycount
where dt='2021-02-24') t1
left join dws_pay_user_detail pu
on t1.user_id=pu.user_id
where pu.user_id is null) t2 join 
(select * from dwd_user_info where dt='2021-02-24') ui
on t2.user_id=ui.id

4. 生成脚本

第18章 ADS层搭建

请移步至基于Apache的电商数据仓库（八）