hive数据仓库框架之日志分析

日志统计的需求:

日志的统计项，包括关键词搜索量 pv ，类别访问量，每秒访问量 tps,hive就是需要将这些数据储存,并提供查询分析等功能。

首先要了解hive中的哪些关键字段:

1)UV：Unique Visited--- count(distinct guid)

访问您网站的一台电脑客户端为一个访客。00:00-24:00内相同的客户端只被计算一次。
2) PV：Page View--- count(url)
即页面浏览量或点击量，用户每次刷新即被计算一次。
3) 登录人数：
登录网站访问的人数[会员]，endUserId有值的数量
4) 访客人数：
没有登录访问的人数，endUserId为空的数量
5) 平均访问时长：
访客平均在网站停留的时间
trackTime  --> max - min
6) 二跳率： pv>1的访问量/总访问量  
平均浏览2个页面及以上(pv>1)的用户数 /  用户总数(discont guid) 点击1次
二跳率的概念是当网站页面展开后，用户在页面上产生的首次点击被称为“二跳”，二跳的次数即为“二跳量”。二跳量与浏览量的比值称为页面的二跳率。


count(url) group by guid >1 / discont (guid)

7) 独立IP：---count(distinct ip)
独立IP表示，拥有特定唯一IP地址的计算机访问您的网站的次数，因为这种统计方式比较容易实现，具有较高的真实性，所以成为大多数机构衡量网站流量的重要指标。比如你是ADSL拨号上网的，你拨一次号都自动分配一个ip，这样你进入了本站，那就算一个ip，当你断线了而没清理cookies，之后又拨 了一次号，又自动分配到一个ip，你再进来了本站，那么又统计到一个ip，但是UV（独立访客)没有变，因为2次都是你进入了本站。



数据样本:数据样本连接
思路分析：
第一步：创建临时表1：session_info_tmp1
可根据sessionid和guid分组（同一个sessionid也肯定是同一个guid），获取以下字段信息：
sessionid：用户session号
guid：用户全局唯一id号
endUserId：会员id（登陆则有，不登录则是空）
使用max（endUserId）可以获取非空的字段值（非空字符串>空字符串）
pv：通过count（url）获取
stay_time：通过最大的tracktime-最小 的tracktime获得
min_trackTime：通过分组中最小tracktime获取
ip：同一个sessionId都应该有同一个ip，通过max（ip）获取
provinceId：同一个sessionId都应该有同一个provinceId，通过max（provinceId）获取
注：由于使用sessionId分组，trackerU、landing_url、landing_url_ref这三个字段都不一定是一个，所以在临时表1中不加入此三字段，然而这三个字段是会话信息表中要求实现的，所以创建临时表2
第二步：创建临时表2：session_info_tmp2
通过track_log获取所有sessionId、tracktime、trackerU、landing_url、landing_url_ref这五个字段，sessionId、tracktime这两个字段都是用来join的时候使用。
trackerU：渠道id
landing_url：首次登陆的url
landing_url_ref：首次登陆的渠道url（从哪个url而来）
第三步：join临时表1和临时表2，得到会话跟踪表session_info
由于在一次会话期间可能产生多个trackerU、landing_url、landing_url_ref的值，而我们实际需要的是这个session一开始的渠道id，在临时表1中按sessionId分组保留每个分组中最小的tracktime，关联临时表2中的tracktime，从而取出表2中每个分组中初始tracktime对应的trackerU、landing_url、landing_url_ref的值。


create database  db_track;
create table track_log(
id              string,
url             string,
referer         string,     //外部链接
keyword         string,
type            string,
guid            string,
pageId          string,
moduleId        string,
linkId          string,
attachedInfo    string,
sessionId       string,
trackerU        string,
trackerType     string,
ip              string,
trackerSrc      string,
cookie          string,
orderCode       string,
trackTime       string,
endUserId       string,
firstLink       string,
sessionViewNo   string,
productId       string,
curMerchantId   string,
provinceId      string,
cityId          string,
fee             string,
edmActivity     string,
edmEmail        string,
edmJobId        string,
ieVersion       string,
platform        string,
internalKeyword string,
resultSum       string,
currentPage     string,
linkPosition    string,
buttonPosition  string
)
partitioned by (date string,hour string)
row format delimited fields terminated by '\t';        




load data local inpath '/home/user01/Downloads/2015082818' into table track_log partition (date="20150828",hour="18");


load data local inpath '/home/user01/Downloads/2015082819' into table track_log partition (date="20150828",hour="19");




create table static_PVUV(
date string,
hour string,
pv int,
uv int
)
row format delimited fields terminated by '\t'; 


insert overwrite table static_PVUV select date,hour,count(url),count(distinct guid)from track_log where date="20150828" group by date,hour;


20150828 18 64972 23938
20150828 19 61162 22330


//在mysql中创建用于接收使用sqoop导出的表格
create table static_PVUV(
date int,
hour int,
pv int,
uv int
);


网盟
http://baike.baidu.com/link?url=3lgUc2Ti-dNlHHT2WpHLBoDPJM7GWzeJW9R564CMSZKJL-F7kn98dsj55JoyQTMizJcX6GMY83KI-M-CmTJQ1aMQ4V2pwgZpPrg4ExQz2k_


深入分析
1、借助上个案例创建的表
2、设计一张会话信息表
create table db_track.session_info(
session_id string,                      //会话id
guid string, //唯一用户id
trackerU string, //网盟用户id
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" ;


3、生成中间表1


--创建中间表1


create table session_info_tmp1(
session_id string,
guid string,
endUserId string,
pv string,
stay_time string,
min_trackTime string,
ip string,
provinceId string
)
row format delimited fields terminated by "\t" ;


--导入数据


insert overwrite table session_info_tmp1
select 
sessionId,
max(guid),
max(endUserId),
count(url),
max(unix_timestamp(trackTime))-min(unix_timestamp(trackTime)),
min(trackTime),
max(ip),
max(provinceId)
from track_log where date='20150828' 
group by sessionId ;


4、生成临时表2


create table session_info_tmp2(
session_id string,
trackTime string,
trackerU string,
landing_url string,
landing_url_ref string
)
row format delimited fields terminated by "\t" ;


--导入数据
insert overwrite table session_info_tmp2
select
sessionId,
trackTime,
trackerU,
url,
referer
from track_log where date='20150828' ;


5、得出会话信息表结果（join）


insert overwrite table session_info partition (date='20150828')
select 
a.session_id ,
a.guid ,
b.trackerU ,
b.landing_url ,
b.landing_url_ref,
a.endUserId ,
a.pv ,
a.stay_time ,
a.min_trackTime ,
a.ip ,
a.provinceId 
from session_info_tmp1 a join session_info_tmp2 b 
on a.session_id=b.session_id and a.min_trackTime=b.trackTime ;


** 求出了每个session的数据


6、求出需求表结果


日期 UV PV登录人数 游客人数 平均访问时长二跳率 独立IP


=================================================================
二跳率
count(case when pv >=2 then guid else null end ) / count(guid) 
*****************************************************************
create table visit_users (
date string,
uv string,
pv string,
login_users string,
visit_users string,
stay_time string,
perofsecvis string,
ip string
)
row format delimited fields terminated by "\t" ;


insert overwrite table visit_users select
date date,
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) visit_users,
avg(stay_time) stay_time,
count(case when pv >=2 then guid else null end ) / count(guid) ,
max(ip) ip 
from session_info where date='20150828'
group by date ;


Exec Result：
visit_users.date visit_users.uvvisit_users.pvvisit_users.login_usersvisit_users.visit_usersvisit_users.stay_timevisit_users.perofsecvisvisit_users.ip
20150828 2392868036.0120811272150.106362390129830.5225385049592775 99.244.129.62








7、导入mysql表


sqoop --> mysql




select sal,
case when comm is null then 0 
else comm end 
from emp ;
总结
mysql> create table visit_users(
date varchar(20),
uv int(12),
pv double,
login_users int,
visit_users int,
stay_time double,
perofsecvis double,
ip varchar(20)
);


bin/sqoop export \
    --connect jdbc:mysql://[hostname]:3306/db_160729 \
    --username root \
    --password root123 \
    --table visit_users \
    --input-fields-terminated-by "\t" \
    --export-dir /user/hive/warehouse/db_track.db/visit_users