sqoop和hive对web日志进行处理分析

创建weblogs_parse 表：
create table weblogs_parse (
client_ip string,
full_request_date string,
day string,
http_verb string,
url string,
status_code int,
bytes_returned int,
referrer string,
user_agent string,
day_one string,
month string,
year string,
hour string,
minute string,
second string,
month_num string)
row format delimited fields terminated by '\t';

把数据导入到mysql中：

sqoop export --connect jdbc:mysql://192.168.222.1:3306/weblogs --username root --password 123456 --table weblogs_parse --export-dir /user/hive/warehouse/weblogs_parse

//设置头部
set hive.cli.print.header=true;
select * from weblogs_parse limit 10;

结果：

//创建hive表的分隔符
row format delimited fields terminated by '\t';
//sqoop分隔符
--input-fields-terminated-by '***TAB***' 
--fields-terminated-by'\t'

//统计排名前10的ip访问量
create table if not exists ip_result row format delimited fields terminated by '\t' lines terminated by '\n' as select weblogs_parse.client_ip,count(weblogs_parse.client_ip) as count from weblogs_parse group by weblogs_parse.client_ip order by count desc limit 10;

运行结果：

查看ip_result表：

//把结果存入数据库
sqoop export --connect jdbc:mysql://192.168.222.1:3306/weblogs --username root --password 123456 --table ip_result --export-dir /user/hive/warehouse/ip_result --input-fields-terminated-by '\t' 

结果：

//统计PV(PageView): 页面访问量统计，统计排名前十
create table if not exists pv_result row format delimited fields terminated by '\t' lines terminated by '\n' as select weblogs_parse.url,count(weblogs_parse.url) as count from weblogs_parse group by weblogs_parse.url order by count desc limit 10;

运行结果：

//把结果存入数据库
sqoop export --connect jdbc:mysql://192.168.222.1:3306/weblogs --username root --password 123456 --table pv_result --export-dir /user/hive/warehouse/pv_result --input-fields-terminated-by '\t' 

运行结果：

结果：

//统计Brower: 用户设备统计
create table if not exists brower_result row format delimited fields terminated by '\t' lines terminated by '\n' as select trim(weblogs_parse.user_agent),count(weblogs_parse.user_agent) as count from weblogs_parse group by weblogs_parse.user_agent order by count desc;

运行结果：

//把结果存入数据库
sqoop export --connect jdbc:mysql://192.168.222.1:3306/weblogs --username root --password 123456 --table brower_result --export-dir /user/hive/warehouse/brower_result --input-fields-terminated-by '\t'

结果：

//另外种导入方式
sqoop import --connect jdbc:mysql://192.168.222.1:3306/weblogs --username root --password 123456 --table ip_result --fields-terminated-by'\t'  --hive-import --hive-table ip_result --hive-overwrite -m 1