用Hive分析Nginx日志

Nginx日志文件格式

111.37.1.15|100.109.0.149|[28/Apr/2017:14:36:08 +0800]|GET /service/getCity.do?region=河北 HTTP/1.0|200|1292|0.002|-|Dalvik/1.6.0 (Linux; U; Android 4.4.2;  Build/KOT49H)|MISS|127.0.0.1:8080|api.maidou.com

在Hive中创建nginx_log表，列分隔符为【|】

CREATE TABLE nginx_log (ip STRING,proxy_ip STRING,time STRING,request_url STRING,status STRING,size STRING,w1 STRING,w2 STRING,agent STRING,w3 STRING,w4 STRING,w5 STRING) row format delimited fields terminated by '\|' stored as textfile;

从本地文件系统中把Nginx日志文件导入Hive

在Hive命令提示符下输入：load data local inpath '/home/lizh/devtool/hadoop-2.7.3/access.log_2017-04-27' into table nginx_log;

筛选数据

筛选出一天访问量超过500的IP

select ip,count(*) as count from nginx_log group by ip having count>500 sort by count;

筛选每小时的访问量超过500的IP

select substring(time,2,14) date,ip,count(*) as count from nginx_log group by substring(time,2,14),ip having count>500 sort by date,count;