作业十八【Hive日志分析案例】

北风网大数据培训

一) 建立Hive表

#建立数据库
create database web;
#建立表
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' ;

二) 导入数据到Hive

load data local inpath '/home/sjf/2015082818' into table track_log partition(date='20150828',hour='18') ;
load data local inpath '/home/sjf/2015082819' into table track_log partition(date='20150828',hour='19') ;

三)统计结果

#建立hive临时表
create table track_log_tmp(
date string,
hour string,
pv string,
uv string
)
row format delimited fields terminated by '\t';
#查询统计数据
insert into table track_log_tmp select date,hour,count(url),count(distinct guid) from track_log group by date,hour ;

四)数据导入Mysql

#建立Mysql表
create table track_log(
date varchar(20),
hour varchar(10),
pv varchar(50),
uv varchar(50),
primary key (date,hour)
) ;

#导入数据到Mysql
bin/sqoop export \
--connect jdbc:mysql://njt.song.s0:3306/web \
--username root \
--password 123456 \
--table track_log \
--export-dir /user/hive/warehouse/track_log_tmp \
--input-fields-terminated-by '\t'

执行结果: