大数据——Hive分析项目案例

Hive分析项目案例梳理

商业网站中经常统计的数据有哪些：

1. UV：独立访客

   同一个用户访问多次会产生多个记录，但是这些记录会在运算的时候合并为1个

   语法：count(distinct guid)

2. PV：页面浏览量

   同一个用户访问多次，产生多条记录，每一条记录都是一次PV

   语法：count(url)

3. 登陆人数：

   登陆网站访问的人数[普通会员，VIP，SVIP]

   endUserId标识会员

4. 游客人数：

   在没有登陆的情况下访问的客户。

   endUserId为空 --> “”/null

5. 平均访问时长

   用户在网站停留的时间

   trackTime --> max - min

6. 二跳率：

   在同一个网站中平均浏览了2个以上的页面的用户

   计算方法：(pv > 2) / 用户总数

7. 独立IP：

   客户的公网IP

   正常情况下，分析一天内的数据，同一个用户的IP是不会变的

   语法：cunt(distinct ip)

8. 分析结果表的结构

    键名				   描述				对应track_log字段
    session_id			会话id				sessionId
    guid				访客id				guid
    trackerU			访问去掉id，获取	trackerU
    					会话中的第一个			
    landing_url			着陆URL				url
    landing_url_ref		着落之前的URL		referer
    user_id				会员id				endUserId
    pv
    uv
    stay_time			停留时间			trackTime
    min_trackTime		最小时间
    ip
    provinceId			省份id				provinceId
   

分析步骤：

1.创建会话信息表
create table if not exists db_web_ana.session_info(
	session_id string,
	guid string,
	tracker_u string,
	landing_url string,
	landing_url_ref string,
	user_id string,
	pv string,
	uv string,
	stay_time string,
	min_trackTime string,
	ip string,
	province_id string
)
partitioned by (date string,hour string)
row format delimited fields terminated by '\t';
2.创建临时表1
create table if not exists db_web_ana.session_info_temp1(
	session_id string,
	guid string,
	user_id string,
	pv string,
	stay_time string,
	min_trackTime string,
	ip string,
	province_id string
)
row format delimited fields terminated by '\t';
3.分析数据并向临时表1中插入数据   
insert overwrite table db_web_ana.session_info_temp1
select 
sessionId,
guid,
endUserId,
url,
max(unix_timestamp(trackTime))-min(unix_timestamp(trackTime)),
min(from_unixtime(unix_timestamp(trackTime))),
ip,
provinceId,
from db_web_data.track_log where date='20150828'
group by 
sessionId;
4.创建临时表2
create table db_web_ana.session_info_temp2(
session_id string,
tracktime string,
tracker_u string,
landing_url string,
landing_url_ref string
)
row format delimited fields terminated by '\t';
5.分析数据并向临时表2中导入数据
insert overwrite table db_web_ana.session_info_temp2
select
sessionId,
trackTime,
tracker_u,
url,
referer
from db_web_data.track_log where date='20150828';
6.通过sessionId和trackTime将两张临时表join在一起后插入到会话信息表中：db_web_ana.session_info
insert overwrite table db_web_ana.session_info partition(date='20150828')
select
p1.session_id,
p1.guid,
p2.tracker_u,
p2.landing_url,
p2.landing_url_ref,
p1.user_id,
p1.pv,
p1.stay_time,
p1.min_tracktime,
p1.ip,
p1.province_id
from db_web_ana.session_info_temp1 p1 join db_web_ana.session_info_temp2 p2
on p1.session_id=p2.session_id and p1.min_tracktime=p2.tracktime;

执行一系列操作
$ bin/hive -f hql/web_ana/create_session_info.hql ;
bin/hive -f hql/web_ana/create_session_info_temp1.hql ;
bin/hive -f hql/web_ana/create_session_info_temp2.hql ;
bin/hive -f hql/web_ana/insert_session_info_temp1.hql ;
bin/hive -f hql/web_ana/insert_session_info_temp2.hql ;
bin/hive -f hql/web_ana/insert_join_session_info.hql 
7.创建结果表
日期	UV       	PV 		登录人数 		游客人数 	平均访问时长 	二跳率 	独立IP
create table db_web_ana.result_info(
date string,
uv string,
pv string,
login_users string,
visit_users string,
avg_time string,
sec_hop string,
ip_count string
)
row format delimited fields terminated by '\t';
8.为结果信息导入数据：
insert overwrite table db_web_ana.result_info
select
date,
count(distinct guid),
sum(pv),
count(case when user_id != '' then user_id else null end),
count(case when user_id = '' then user_id else null end),
avg(stay_time),
count(distinct (case when pv >= 2 then guid else null end))/count(distinct guid),
count(distinct ip)
from db_web_ana.session_info where date='20150828'
group by
date;
9.验证结果

日期	       UV        PV 	    登录人数 	游客人数 		平均访问时长 		      二跳率 		    独立IP
20150828	38985	131668.0	  18548		 21902		 750.7895179233622	0.5166089521610876	29668