电商数据仓库（八）

欢迎

你好！这是我历经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章 完结

第18章 ADS层搭建

18.1 GMV成交总额（重点）

1. 建表

create external table ads_gmv_sum_day(
    `dt` string COMMENT '统计日期',
    `gmv_count`  bigint COMMENT '当日gmv订单个数',
    `gmv_amount`  decimal(16,2) COMMENT '当日gmv订单总金额',
    `gmv_payment`  decimal(16,2) COMMENT '当日支付金额'
) COMMENT 'GMV'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_gmv_sum_day/';

2. 分析

相关表
dws_user_action

思路
什么是GMV？？？ 一定时间端内的成交总额
统计每天的成交总额

3. sql

insert into table ads_gmv_sum_day
select 
    '2021-02-24',
    sum(order_count) gmv_count,
    sum(order_amount) gmv_amount,
    sum(payment_amount) gmv_payment
from dws_user_action
where dt='2021-02-24'

4. 写成脚本

18.2 各用户等级对应的复购率前十的商品排行

1. 建表

create  table ads_ul_rep_ratio(   
    user_level string comment '用户等级' ,
    sku_id string comment '商品id',
buy_count bigint  comment '购买总人数',
buy_twice_count bigint comment  '两次购买总数',
    buy_twice_rate decimal(10,2)  comment  '二次复购率', 
rank string comment  '排名' ,
    state_date string comment '统计日期'
)   COMMENT '复购率统计'
row format delimited  fields terminated by '\t' 
location '/warehouse/gmall/ads/ads_ul_rep_ratio/';

2. 分析

相关表
dws_sale_detail_daycount

思路
统计今天之前的所有的数据：
①每个用户每个商品的购买次数
②再按照用户等级和商品进行分组，统计每个等级下对商品的复够率

3. sql

INSERT INTO TABLE ads_ul_rep_ratio
SELECT
     user_level,sku_id,buy_count,buy_twice_count,buy_twice_rate,rn,'2021-02-24'
from 
(select
     user_level,sku_id,buy_count,buy_twice_count,buy_twice_rate,
     rank() over(PARTITION by user_level order by buy_twice_rate desc)rn
from 
(select 
    user_level,sku_id,
    sum(if(total_buy_count_per_person>=1,1,0)) buy_count,
    sum(if(total_buy_count_per_person>=2,1,0)) buy_twice_count,
    cast(sum(if(total_buy_count_per_person>=2,1,0))/sum(if(total_buy_count_per_person>=1,1,0))*100 as decimal(10,2)) buy_twice_rate 
from 
(select 
    user_level,sku_id,user_id,count(*)  total_buy_count_per_person
from dws_sale_detail_daycount
where dt<='2021-02-24'
group by user_level,sku_id,user_id) tmp
group by user_level,sku_id) tmp2
where buy_twice_rate>0) tmp3
where rn<=10

4. 写成脚本

18.3 新付费用户数

1. 建表

create external table ads_pay_user_count(   
    dt string COMMENT '统计日期',
    pay_count   bigint  COMMENT '付费用户数'
) COMMENT '付费用户表'
stored as parquet
location '/warehouse/gmall/dws/ads_pay_user_count/';

2. 分析

相关表
dws_pay_user_detail

3. sql

insert into table ads_pay_user_count
select 
    '2021-02-24',count(*)
from dws_pay_user_detail
where dt='2021-02-24'

4. 写成脚本

18.4 每个用户最近一次购买时间

1. 建表

create external table ads_user_last_pay(
    user_id   string  comment '用户id',
    pay_date string comment '最近一次购买时间'
) COMMENT '用户最近一次购买时间表'
stored as parquet
location '/warehouse/gmall/dws/ads_user_last_pay/';

2. 分析

相关表
dws_user_action： 每个用户每天的下单，支付明细

3. sql

insert overwrite TABLE ads_user_last_pay
select 
    user_id,
    max(dt)
from dws_user_action
where payment_count>0
GROUP by user_id

4. 写成脚本

18.5 商品每日下单排行Top10

1. 建表

create external table ads_goods_order_count_day(   
    dt string comment '统计日期',
    sku_id   string  comment '商品id',
    order_count bigint comment '下单次数'
) COMMENT '商品下单top10'
stored as parquet
location '/warehouse/gmall/dws/ads_goods_order_count_day/';

2. 分析

相关表
dws_sale_detail_daycount: 每个用户每天的购买的商品明细

思路
求今日的销售明细，按商品分组，统计数量，排序取前十

3. sql

insert into TABLE ads_goods_order_count_day
select 
    '2021-02-24',sku_id,count(*) order_count
from dws_sale_detail_daycount
where dt='2021-02-24'
group by sku_id
order by order_count desc
limit 10

4. 写成脚本

18.6 统计每个月订单付款率

1. 建表

create external  table ads_order2pay_mn (
    `dt` string COMMENT '统计日期',
    `order_u_count` bigint     COMMENT '下单人数',
    `payment_u_count` bigint     COMMENT '支付人数',
    `order2payment_convert_ratio` decimal(10,2) COMMENT '下单到支付的转化率'
 ) COMMENT ''
row format delimited  fields terminated by '\t'
location '/warehouse/gmall/ads/ ads_order2pay_mn /';

2. 分析

相关表
dws_user_action： 每个用户每天的下单数和支付数

思路
取一个月，所有用户的下单数累加，和所有用户的支付数累加

3. sql

insert into TABLE ads_order2pay_mn
SELECT
    '2021-02-24',
    sum(order_count) order_u_count, 
    sum(payment_count) payment_u_count,
   cast(sum(payment_count)/ sum(order_count) * 100 as  decimal(10,2)) order2payment_convert_ratio
from dws_user_action
where date_format(dt,'yyyy-MM')=date_format('2021-02-24','yyyy-MM')

4. 写成脚本

18.7 用户行为漏斗分析（重点）

1. 建表

create external  table ads_user_action_convert_day(
    `dt` string COMMENT '统计日期',
    `total_visitor_m_count`  bigint COMMENT '总访问人数',
    `order_u_count` bigint     COMMENT '下单人数',
    `visitor2order_convert_ratio`  decimal(10,2) COMMENT '访问到下单转化率',
    `payment_u_count` bigint     COMMENT '支付人数',
    `order2payment_convert_ratio` decimal(10,2) COMMENT '下单到支付的转化率'
 ) COMMENT '用户行为漏斗分析'
row format delimited  fields terminated by '\t'
location '/warehouse/gmall/ads/ads_user_action_convert_day/';

2. 分析

相关表
dwd_start_log： 取当天的总访问人数
dws_user_action： 取当天的下单人数和支付人数

需求
总访问人数： uv 根据用户身份去重后的数据！
总访问人次： pv
同一个人可以多次访问！ 如果讲访问的人次数据，根据用户去重后，得到人数！

思路
total_visitor_m_countbigint COMMENT '总访问人数': 从启动日志中找 order_u_count bigint COMMENT ‘下单人数’： 从dws_user_action中找
payment_u_count bigint COMMENT ‘支付人数’：从dws_user_action中找

3. sql

insert into table ads_user_action_convert_day
select
    '2021-02-24',
    total_visitor_m_count,
    order_u_count,
    cast(order_u_count/total_visitor_m_count*100 as decimal(10,2)) visitor2order_convert_ratio,
    payment_u_count,
     cast(payment_u_count/order_u_count*100 as decimal(10,2)) order2payment_convert_ratio
from 
(select
    count(*) total_visitor_m_count
from
(select 
    user_id
from dwd_start_log
where dt='2021-02-24'
group by user_id) t1) t3
join
(select 
    sum(if(order_count>0,1,0)) order_u_count,
    sum(if(payment_count>0,1,0)) payment_u_count
from dws_user_action
where dt='2021-02-24') t2

4. 写成脚本

18.8 统计某一个品牌的月复够率（重点）

1. 建表

create external table ads_sale_tm_category1_stat_mn
(   
    tm_id string comment '品牌id',
    category1_id string comment '1级品类id ',
    category1_name string comment '1级品类名称 ',
    buycount   bigint comment  '购买人数',
    buy_twice_last bigint  comment '两次以上购买人数',
    buy_twice_last_ratio decimal(10,2)  comment  '单次复购率',
    buy_3times_last   bigint comment   '三次以上购买人数',
    buy_3times_last_ratio decimal(10,2)  comment  '多次复购率',
    stat_mn string comment '统计月份',
    stat_date string comment '统计日期' 
)   COMMENT '复购率统计'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_sale_tm_category1_stat_mn/';

2. 分析

相关表
dws_sale_detail_daycount: 统计了用户每一天购买的每一件商品的明细

需求
复购率： 重复购买的概率！
同一件商品 购买过的人数有 10 人
购买同一件商品 二次的人数有 8 人
购买同一件商品 三次的人数有 6人
此商品，单次复购率：8/10
多(至少购买3次)次复购率： 6/10

思路
1.选取dws_sale_detail_daycount一个月范围的数据：
统计这个月中，购买了这个品牌下的商品一次以上的有多少人；
统计这个月中，购买了这个品牌下的商品二次以上的有多少人；
统计这个月中，购买了这个品牌下的商品三次以上的有多少人。
2.选取dws_sale_detail_daycount一个月范围的数据，按照user_id和tm_id分组，累加购买了这个品牌的商品下了多少单(多少次)

3. sql

INSERT into TABLE ads_sale_tm_category1_stat_mn
select
    sku_tm_id, sku_category1_id,sku_category1_name,
    sum(if(order_count_per_mn>=1,1,0)) buycount,
    sum(if(order_count_per_mn>=2,1,0)) buy_twice_last,
    cast(sum(if(order_count_per_mn>=2,1,0))/sum(if(order_count_per_mn>=1,1,0))*100 as decimal(10,2)) buy_twice_last_ratio,
    sum(if(order_count_per_mn>=3,1,0)) buy_3times_last,
    cast(sum(if(order_count_per_mn>=3,1,0)) / sum(if(order_count_per_mn>=1,1,0)) * 100 as decimal(10,2)) buy_3times_last_ratio,
    date_format('2021-02-24','yyyy-MM') stat_mn,
    '2021-02-24'
from 
(select 
    user_id,sku_tm_id,sum(order_count) order_count_per_mn,sku_category1_id,sku_category1_name
from  dws_sale_detail_daycount
where date_format(dt,'yyyy-MM')=date_format('2021-02-24','yyyy-MM')
group by sku_tm_id,user_id,sku_category1_id,sku_category1_name ) tmp
group by sku_tm_id,sku_category1_id,sku_category1_name

4. 写成脚本

第19章 Azkaban调度器

19.1 安装Azkaban

1. 下载地址

http://azkaban.github.io/downloads.html
下载2.5.0版本，下载不了可以私聊我，我提供给您！

2. 安装前的所有tar包

查看mysql安装好
a) azkaban-web-server-2.5.0.tar.gz
b) azkaban-executor-server-2.5.0.tar.gz
c) azkaban-sql-script-2.5.0.tar.gz

3. 安装

• (1).在/opt/module/目录下创建azkaban目录

• (2).解压

• (3).对解压后的文件重新命名

	mv azkaban-executor-2.5.0/ executor
	mv azkaban-web-2.5.0/ server

• (4).azkaban脚本导入

	进入mysql
		mysql -uroot -p123456
	创建azkaban数据库
		create database azkaban;
	并将解压的脚本导入到azkaban数据库
		use azkaban;
		source /opt/module/azkaban/azkaban-2.5.0/create-all-sql-2.5.0.sql

5. 生成密钥对和证书

• (1).azkaban/server/生成 keystore的密码及相应信息的密钥库

keytool -keystore keystore -alias jetty -genkey -keyalg RSA

输入密钥库口令(123456)

其他回车

是否正确? y

输入 <jetty> 的密钥口令

回车

• (2).查看私钥和公钥

keytool -keystore keystore -list

6. 时间同步

7. 配置文件

• (1).Web服务器配置

	azkaban/server/conf/azkaban.properties


下面这些都需要逐一改

web.resource.dir=/opt/module/azkaban/server/web/
#默认时区,已改为亚洲/上海 默认为美国
default.timezone.id=Asia/Shanghai


#用户权限管理默认类（绝对路径）
user.manager.xml.file=/opt/module/azkaban/server/conf/azkaban-users.xml
#global配置文件所在位置（绝对路径）
executor.global.properties=/opt/module/azkaban/executor/conf/global.properties


#数据库连接IP
mysql.host=hadoop103
#数据库用户名
mysql.user=root
#数据库密码
mysql.password=123456



#SSL文件名（绝对路径）
jetty.keystore=/opt/module/azkaban/server/keystore
#SSL文件密码
jetty.password=123456
#Jetty主密码与keystore文件相同
jetty.keypassword=123456
#SSL文件名（绝对路径）
jetty.truststore=/opt/module/azkaban/server/keystore
#SSL文件密码
jetty.trustpassword=123456

	azkaban/server/conf/azkaban-users.xml
	
在中间添加admin管理员
	<user username="admin" password="admin" roles="admin,metrics"/>

• (2).执行服务器配置

	azkaban/executor/conf/azkaban.properties
	
#时区
default.timezone.id=Asia/Shanghai



executor.global.properties=/opt/module/azkaban/executor/conf/global.properties


mysql.host=hadoop103
mysql.database=azkaban
mysql.user=root
mysql.password=123456

8. 启动

• (1).启动Executor服务器

    azkaban/executor

bin/azkaban-executor-start.sh

先执行executor，再执行web，避免Web Server会因为找不到执行器启动失败

• (2).启动Web服务器

   azkaban/server
   
bin/azkaban-web-start.sh

• (3).web网址

https://hadoop103:8443
第一次执行一定要带https

19.2 GMV指标获取的全调度流程

这里用GMV作为个例子进行调度
重新2021-02-24的数据

编写Azkaban程序运行job

1. gmv-import.job

type=command
command=/home/atguigu/bin/sqoop_import.sh all ${do_date}

2. gmv-ods.job

type=command
dependencies=gmv_import
command=ods_db.sh ${do_date}

3. gmv-dwd.job

type=command
dependencies=gmv_ods
command=dwd_db.sh ${do_date}

4. gmv-dws.job

type=command
dependencies=gmv_dwd
command=dws_db_wide.sh ${do_date}

5. gmv-ads.job

type=command
dependencies=gmv_dws
command=ads_db_gmv.sh ${do_date}

6. gmv-export.job

type=command
dependencies=gmv_ads
command=sqoop_export.sh ads_gmv_sum_day ${do_date}

7. 打成zip包

gmv-job.zip

8. 执行zip包

https://hadoop102:8443
执行gmv-job.zip

第20章 即席查询：Presto

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