hive 的数据案例 统计网站的数据信息

• 根据hive的案例一增加需求

一： 增加案例需求：

统计 pv , uv , 登录人数 , 游客人数 , 平均访问时长 , 二跳率 , 独立IP 用一张表去处理

1.1 查看track_log的分区

show partitions track_log ;

1.2 建立一张会话信息表（session）：

create table session_info(session_id string,guid string,trackerU string,landing_url string,landing_url_ref string,user_id string,pv string,stay_time string,min_trackTime string,ip string,provinceId string)partitioned by (date string)row format delimited fields terminated by ‘\t‘ ;

1.3 ?创建临时表 session_info_tmp1

create table session_info_tmp1 as select sessionId session_id,max(guid) guid,--trackerU string,--landing_url string,--landing_url_ref string,max(endUserId) user_id,count(url) pv,(max(unix_timestamp(trackTime)) - min(unix_timestamp(trackTime))) ?stay_time,min(trackTime) min_trackTime ,max(ip) ip,max(provinceId) provinceIdfrom track_log where date=‘20150828‘group by sessionId ;

1.4 创建临时表session_info_tmp2

create table session_info_tmp2 as selectsessionId session_id,trackTime trackTime,trackeru trackerU,url landing_url,referer landing_url_reffrom track_log where date=‘20150828‘ ;

1.5 导入数据处理：

insert overwrite table session_info partition (date=‘20150828‘)selecta.session_id,a.guid,b.trackerU,b.landing_url,b.landing_url_ref,a.user_id,a.pv,a.stay_time,a.min_trackTime,a.ip,a.provinceIdfrom session_info_tmp1 a ?join session_info_tmp2 bon a.session_id=b.session_id and a.min_trackTime=b.trackTime ;

1.6 生成最后所需的表：

create table vistor_users_info asselectdate,count(distinct guid) UV,sum(pv) PV,count(case when user_id != ‘‘ then user_id else null end) login_users,count(case when user_id = ‘‘ then user_id else null end) vistor_users,avg(stay_time) avg_stay_time,count(case when pv>=2 then session_id else null end)/count(session_id) sec_ratio,count(distinct ip) ipfrom session_info where date=‘20150828‘group by date ;

1.7 查询结果：

select * from vistor_users_info;

hive 的数据案例 统计网站的数据信息

原文地址：http://blog.51cto.com/flyfish225/2097296