程序地带

Hive离线分析项目(部分)



文章目录
hive离线分析项目:1、项目分析:2、项目实施步骤1)、项目准备①、存储路径准备②、数据准备(模拟产生日志)a、书写脚本cp_mv_data.shb、设置cp_mv_data.sh脚本定时器
2)、加载数据①、创建hive项目库a、创project库b、创movie_vv表
②、向表格中加载数据a、书写脚本up_mv_data.shb、设置up_mv_data.sh脚本定时器
3)、项目需求①、每日vv时长用户数分析sql语句②、其他需求sql
4)、项目计算①、每日vv时长用户数分析a、书写文件vv.hqlb、书写脚本fenxiu_analysis.shc、设置vv.hql定时器
3、项目成果(截图)4、项目补充1)、定时器关系2)、sql的补充
5、附录1)、pv,uv,ip,vv,cv说明2)、项目说明


hive离线分析项目:
1、项目分析:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qs94Y0Sf-1610721426703)(C:UsersxiaoyoupeiAppDataRoamingTypora	ypora-user-imagesimage-20210115193949694.png)]


2、项目实施步骤
1)、项目准备
①、存储路径准备

在linux文件系统中创建项目文件夹(project),在文件内创建数据文件夹(data)、脚本文件夹(scripts)、输出结果文件夹(result)(此目录下存储脚本输出的每小时视频文件)


②、数据准备(模拟产生日志)
a、书写脚本cp_mv_data.sh

用来创建数据,脚本内容如下:


#!/bin/bash
datetime=`date "+%Y%m%d%H"`
cp /usr/local/soft/hive-1.2.1/project/data/movie_vv.txt /usr/local/soft/hive-1.2.1/project/result/movie_vv_$datetime
//增加执行权限
[root@master scripts]# chmod a+x cp_mv_data.sh

补充:#!/bin/bash表示是一个脚本文件,其次bash是指用哪个解释器,chmod a+x增加所有用户权限


b、设置cp_mv_data.sh脚本定时器
//打开定时器
[root@master scripts]# crontab -e
//设置每小时第一分钟开始执行
* */1 * * * /usr/local/soft/hive-1.2.1/project/scripts/cp_mv_data.sh

补充:这里对定时器基本补充* * * * *,分别表示分钟、小时、天数、月份、星期几


2)、加载数据
①、创建hive项目库
a、创project库
create database if not exists project;
b、创movie_vv表
create table if not exists movie_vv(
stat_date string,
userid string,
uid string,
version string,
country string,
province string,
movie_tryvv int,
movie_sucvv int,
movie_ptime int
)
PARTITIONED BY (
dt string)
clustered by (userid) into 3000 buckets
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS textfile;
②、向表格中加载数据
a、书写脚本up_mv_data.sh

用来加载到hive表里的数据,脚本内容如下:


#!/bin/bash
datetime=`date "+%Y%m%d%H"`
source /etc/profile
hive -e "load data local inpath '/usr/local/soft/hive-1.2.1/project/result/movie_vv_${datetime}' into table project.movie_vv partition(dt=$datetime)";
//增加执行权限
[root@master scripts]# chmod a+x up_mv_data.sh

补充:这里对脚本中书写source /etc/profile进行解释,如果不加这一句,运行时默认环境变量是用户的环境变量,而hive的环境变量配置在全局变量/etc/profile中,会在系统运行的时候加载(启动),而计时器(crontab)运行的时候不会加载该环境变量,只会加载用户的环境变量,其实用户的目录(~)下的bashrc、bash_profile也可以配环境变量,所以一般可以将环境变量配下用户下,但是这里同一放在了全局变量下,所以必须加source /etc/profile


b、设置up_mv_data.sh脚本定时器
//设置每小时第3分钟开始执行,有个缓冲的过程
3 */1 * * * nohup sh /usr/local/soft/hive-1.2.1/project/scripts/up_movie_data.sh >> /usr/local/soft/hive-1.2.1/project/log.log 2>&1 &

补充:sh xxx表示执行脚本,xxx >> yyy表示把xxx的输出放到yyy中,加上nohup &表示后台运行,2>&1表示将前面文件的三类输出中的第二类定在一内(输出类型2的日志合并到输出类型1中)


3)、项目需求
①、每日vv时长用户数分析sql语句
select stat_date
,country
,province
,count(distinct userid) as sucvv_user_cnt
,count(distinct if(movie_ptime>=1440 or movie_ptime<0,userid,null)) as time_excep_user
,count(distinct if(movie_ptime>0 and movie_ptime<1,userid,null)) as time_0to1_user
,count(distinct if(movie_ptime>=1 and movie_ptime<3,userid,null)) as time_1to3_user
,count(distinct if(movie_ptime>=3 and movie_ptime<6,userid,null)) as time_3to6_user
,count(distinct if(movie_ptime>=6 and movie_ptime<10,userid,null)) as time_6to10_user
,count(distinct if(movie_ptime>=10 and movie_ptime<30,userid,null)) as time_10to30_user
,count(distinct if(movie_ptime>=30 and movie_ptime<60,userid,null)) as time_30to60_user
,count(distinct if(movie_ptime>=60 and movie_ptime<90,userid,null)) as time_60to90_user
,count(distinct if(movie_ptime>=90 and movie_ptime<120,userid,null)) as time_90to120_user
,count(distinct if(movie_ptime>=120 and movie_ptime<180,userid,null)) as time_120to180_user
,count(distinct if(movie_ptime>=180 and movie_ptime<240,userid,null)) as time_180to240_user
,count(distinct if(movie_ptime>=240 and movie_ptime<360,userid,null)) as time_240to360_user
,count(distinct if(movie_ptime>=360 and movie_ptime<1440,userid,null)) as time_360up_user
from project.movie_vv
where userid is not null
and length(userid) > 10
and length(userid) < 30
and movie_sucvv>0
group by stat_date,country,province;
②、其他需求sql
--项目中的sql
select stat_date,
'merge-1082559571' as country,
'merge-1082559571' as province,
'merge-1082559571' as groupid,
'96673' as version,
'96673' as vvplatform,
'96673' as movietype,
'96673' as usertype,
count(distinct userid) as sucvv_user_cnt,
count(distinct if(movie_ptime>=1440 or movie_ptime<0,userid,null)) as time_excep_user,
count(distinct if(movie_ptime>=0 and movie_ptime<1,userid,null)) as time_0to1_user,
count(distinct if(movie_ptime>=1 and movie_ptime<3,userid,null)) as time_1to3_user,
count(distinct if(movie_ptime>=3 and movie_ptime<6,userid,null)) as time_3to6_user,
count(distinct if(movie_ptime>=6 and movie_ptime<10,userid,null)) as time_6to10_user,
count(distinct if(movie_ptime>=10 and movie_ptime<30,userid,null)) as time_10to30_user,
count(distinct if(movie_ptime>=30 and movie_ptime<60,userid,null)) as time_30to60_user,
count(distinct if(movie_ptime>=60 and movie_ptime<90,userid,null)) as time_60to90_user,
count(distinct if(movie_ptime>=90 and movie_ptime<120,userid,null)) as time_90to120_user,
count(distinct if(movie_ptime>=120 and movie_ptime<180,userid,null)) as time_120to180_user,
count(distinct if(movie_ptime>=180 and movie_ptime<240,userid,null)) as time_180to240_user,
count(distinct if(movie_ptime>=240 and movie_ptime<360,userid,null)) as time_240to360_user,
count(distinct if(movie_ptime>=360 and movie_ptime<1440,userid,null)) as time_360up_user
from (
select u.*
,if(p.vip_state is not null ,p.vip_state,'-1') usertype --此处判断vip,null用-1代替,游客
from (
select stat_date
,if(userid is not null and length(userid) > 10 and length(userid)<30,userid,uid) userid
,sum(movie_ptime)movie_ptime --判断userid长度,不符合的用uid代替
from userplatform.day_vv
where dt=%(date)s and movie_type!='ALL' and sucvv>0 --过滤出所有影片类型对应数据和分区数据
group by stat_date ,if(userid is not null and length(userid) > 10 and length(userid)<30,userid,uid)--注意 遇到group by字段是表达式的时候,group by后必须跟具体表达式,不能是别名
)u left outer join --关联vip表,拿出vip对应的状态
(select *
from userplatform.purchase_info_vip
where dt=%(date)s
) p on u.userid=p.userid)a group by stat_date;
/*其他sql学习
7日留存用户数
原理:第一天存在观影行为的用户(userid)到第7天依然有观影行为。*/
select addtime as stat_date ,
'merge-1082559571' as country,
'merge-1082559571' as province,
'merge-1082559571' as groupid,
'96673' as version,
'tryvv' as vvtype,
source as vvplatform,
count(distinct if(day_next_try>0,user_id,null))nextday_user,
count(distinct if(day_3_try>0,user_id,null)) threeday_user,
count(distinct if(day_7_try>0,user_id,null)) lastof7day_user,
count(distinct if(movie_tryday>0 and day_7_try>0,user_id,null)) movie_user,
count(distinct if(tv_tryday>0 and day_7_try>0,user_id,null)) tv_user,
count(distinct if(comic_tryday>0 and day_7_try>0,user_id,null)) comic_user,
count(distinct if(show_tryday>0 and day_7_try>0,user_id,null)) show_user,
count(distinct if(doc_tryday>0 and day_7_try>0,user_id,null))documentary_user,
count(distinct if(edu_tryday>0 and day_7_try>0,user_id,null)) education_user,
count(distinct if(olong_tryday>0 and day_7_try>0,user_id,null))other_long_video_user,
count(distinct if(short_tryday>0 and day_7_try>0,user_id,null)) short_video_user
from (select lg.user_id,lg.addtime,lg.source,u.*
from(select user_id,addtime,source,
time from ucenter.all_user_login_info where
addtime>=20160612 and addtime<=20160613 and source='storm5')
lg inner join (select *,regexp_replace(date_sub(from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd'),7),'-','')dtt
from userplatform.day_7_vv where dt >=20160619 and dt <=20160620) u on lg.user_id=u.userid and lg.time=u.dtt)a
group by addtime,source,'tryvv';
4)、项目计算
①、每日vv时长用户数分析
a、书写文件vv.hql

在scripts目录下创建vv.hql文件将项目需求中的每日vv时长用户数分析sql写入该文件中(不要忘记加上数据库的名称)


b、书写脚本fenxiu_analysis.sh
#!/bin/bash
datetime=`date "+%Y%m%d%H"`
source /etc/profile
hive -f /usr/local/soft/hive-1.2.1/project/scripts/vv.hql

补充:不要忘记执行权限


c、设置vv.hql定时器
6 */1 * * * nohup sh /usr/local/soft/hive-1.2.1/project/scripts/fenxiu_ana
lysis.sh >> /usr/local/soft/hive-1.2.1/project/log2.log 2>&1 &
3、项目成果(截图)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nTKI68eM-1610721426707)(C:UsersxiaoyoupeiAppDataRoamingTypora	ypora-user-imagesimage-20210115210242421.png)]


4、项目补充
1)、定时器关系

目前我们的项目使用时间去约束定时器的先后执行顺序,必须要去判断上一条件是否执行成功才可以执行下一步骤,实际桑应该要用调度工具去约束,这只是一个初始的项目。


2)、sql的补充

每日vv时长用户数分析的sql语句中缺少分区裁剪,后续可以补上。


5、附录
1)、pv,uv,ip,vv,cv说明
PV(page view,页面浏览量)
用户每打开1个网站页面,记录1个PV。用户多次打开同一页面,PV值累计多次。主要用来衡量网站用户访问的网页数量。是评价网站流量最常用的指标之一。
UV( unique visitor,网站独立访客)
通过互联网访问、流量网站的自然人。1天内相同访客多次访问网站,只计算为1个独立访客。该概念的引入,是从用户个体的角度对访问数据进行划分。
IP(独立IP)
1天之内(00:00~24:00),访问网站的不重复的IP数。拥有特定唯一IP的计算机访问网站的次数,因为这种统计方式比较容易实现,具有较高的真实性。
VV(video view,视频播放量)
一个统计周期内,视频被打开的次数。
CV(Content Views,内容播放数)
一个统计周期内,视频被打开,且视频正片内容(除广告)被成功播放的次数。
这些概念都是在数据分析中使用,用于数据统计和用户分析。VV和CV属于播放类指标,PV、UV、IP属于浏览类指标。
针对不同行业和不同产品形态,都可以参考以上概念进行特定行业内数据设定和统计,对数据统计和用户分析具有重要意义。
2)、项目说明
/*风秀直播平台这个项目是离线项目,一天算一次,定时脚本,夜间执行。第二天会算昨天的日志。 T+1
一般都是分区表,一个分区存一天的数据。
pv,是指页面点击量,是指总点击量。*/
select count(*)from table where pt=20180929
/*uv,是指用户访问量:
需要对用户id去重:*/
select count(distinct userid)from table where pt=20180929
/*每日活跃:是指每天有浏览页面行为记录的人有多少人。
实际上每点击一次页面就会生成一条数据。
pv:算下多少条数据就是总访问量。
uv:算下去重的用户id。
SQL相关,当然实际sql还会结合实际业务,做一些其他处理,比如where过滤,日期转换等等:
留存率:指当天访问的用户,过3天或7天后还有浏览记录的用户剩下多少。相关SQL可以了解一下:*/
select addtime as stat_date ,
'merge-1082559571' as country,
'merge-1082559571' as province,
'merge-1082559571' as groupid,
'96673' as version,
'tryvv' as vvtype,
source as vvplatform,
count(distinct if(day_next_try>0,user_id,null))nextday_user,
count(distinct if(day_3_try>0,user_id,null)) threeday_user,
count(distinct if(day_7_try>0,user_id,null)) lastof7day_user,
count(distinct if(movie_tryday>0 and day_7_try>0,user_id,null)) movie_user,
count(distinct if(tv_tryday>0 and day_7_try>0,user_id,null)) tv_user,
count(distinct if(comic_tryday>0 and day_7_try>0,user_id,null)) comic_user,
count(distinct if(show_tryday>0 and day_7_try>0,user_id,null)) show_user,
count(distinct if(doc_tryday>0 and day_7_try>0,user_id,null))documentary_user,
count(distinct if(edu_tryday>0 and day_7_try>0,user_id,null)) education_user,
count(distinct if(olong_tryday>0 and day_7_try>0,user_id,null))other_long_video_user,
count(distinct if(short_tryday>0 and day_7_try>0,user_id,null)) short_video_user
from (select lg.user_id,lg.addtime,lg.source,u.*
from(select user_id,addtime,source,time from ucenter.all_user_login_info
where addtime = '<!subdate(date,7)!>' and time = '<!subdate(date,7)!>' and source='storm5')
lg inner join (select *,regexp_replace(date_sub(from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd'),7),'-','')dtt
from userplatform.day_7_vv where dt='%(date)s') u
on lg.user_id=u.userid and lg.time=u.dtt)a group by addtime,source,'tryvv'

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/xiaoyoupei/article/details/112689266

随机推荐

东北大学应用数理统计考试提纲

东北大学应用数理统计考试提纲抽样分布:介绍如何收集数据。主要抽样方法,样本容量的确定,抽样误差,敏感问题等。参数估计:如何根据数据...

笛语星落——一只在编程路上不断爬起的小白 阅读(507)

WxPython 实现TextCtrl部分文本颜色控制

WxPython 实现TextCtrl部分文本颜色控制

功能需求:在TextCtrl中输出一段话,其中一部分为红色,一部分为绿色,一部分为黄色。功能实现:如果你是用的wxFormBuil...

永远的小白虾 阅读(874)

3-1

#include<stdio.h>intmain(){intmm,dd,yy;printf("Enteradate(mm/dd/yy): ");scanf("%d...

weixin_47538252 阅读(445)

Day-6 剪绳子(浮点数二分)

Day-6 剪绳子(浮点数二分)

考点:浮点数二分这道题正常想很难找到突破口,如果我们利用逆向思维,把它转化为一个判定问题,就会变得容易。试想,如果把已知的几段绳子...

嘛也不会_ 阅读(253)

定位插件_OD插件编写

定位插件_OD插件编写

OD的bugOD的异常bug当程序产生了异常后,操作系统在派发异常时,首先会将异常给调试器,但是在调试过程中,OD并不能成功收到异常࿰...

Pinxian Li 阅读(748)