使用hive分析nginx访问日志方法

蔚落 2022-03-30 12:48 254阅读 0赞

以下案例是使用hive分析nginx的访问日志案例，其中字段分隔通过正则表达式匹配，具体步骤如下：

日志格式：  
192.168.5.139 - - \[08/Jun/2017:17:09:12 +0800\] "GET //oportal/static/ui/layer/skin/default/icon.png HTTP/1.1" 200 9905 [http://192.168.100.126//oportal/static/ui/layer/skin/layer.css][http_192.168.100.126_oportal_static_ui_layer_skin_layer.css] "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.111 Safari/537.36" -  
192.168.5.139 - - \[08/Jun/2017:17:09:25 +0800\] "GET //oportal/page/homepage/images/icon-02.png HTTP/1.1" 200 1322 [http://192.168.100.126//dsfdsal/page/homepage/css/indet.css][http_192.168.100.126_dsfdsal_page_homepage_css_indet.css] "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.111 Safari/537.36" -  
192.168.5.139 - - \[08/Jun/2017:17:09:25 +0800\] "GET /dsfdortal/page/waittodo/waittodo.jsp?registesfsdame=%25E7%25BB%25BC%25E5%sdf2590%2588%25E9%25A2%2584%25E7%25AE%25sdf97&registerAsdfsdppid=bsdfsdas,ssdfsdfpf,bsdfsdgt,insdfsddi,hqrsdfdseport,hqosdfa,hqsfdsbi&resdfgisterId=FD748AA3sd82851A37F1693D3880C844EF&allviewsdfnum=10&appSource=undefined&tokenid=5728A0ED7998CC84B88FE8717A33FAB8aK79UkfS&waittodoNums=0&showway=0 HTTP/1.1" 200 3121 [http://192.168.100.126//fposdfsdrtal/page/homdsfdepage/homepage.jsp?tokenid=5728A0ED7998CC84B88FE8717A33FAB8aK79UkfS][http_192.168.100.126_fposdfsdrtal_page_homdsfdepage_homepage.jsp_tokenid_5728A0ED7998CC84B88FE8717A33FAB8aK79UkfS] "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.111 Safari/537.36" -  
192.168.5.139 - - \[08/Jun/2017:17:09:25 +0800\] "GET //fposdfgfrtal/page/waittodo/css/db\_index.css HTTP/1.1" 200 6310 [http://192.168.100.126/fpdsfdsfortal/page/waittodo/waittodo.jsp?registerName=%25E7%25BB%25BC%25E5%2590%2588%25E9%25A2%2584%25E7%25AE%2597&registerAppid=bas,spf,bgt,indi,hqreport,hqoa,hqbi&registerId=FD748AA382851A37F1693D3880C844EF&allviewnum=10&appSource=undefined&tokenid=5728A0ED7998CC84B88FE8717A33FAB8aK79UkfS&waittodoNums=0&showway=0][http_192.168.100.126_fpdsfdsfortal_page_waittodo_waittodo.jsp_registerName_25E7_25BB_25BC_25E5_2590_2588_25E9_25A2_2584_25E7_25AE_2597_registerAppid_bas_spf_bgt_indi_hqreport_hqoa_hqbi_registerId_FD748AA382851A37F1693D3880C844EF_allviewnum_10_appSource_undefined_tokenid_5728A0ED7998CC84B88FE8717A33FAB8aK79UkfS_waittodoNums_0_showway_0] "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.111 Safari/537.36" -

正则匹配：  
测试网站：[http://wpjam.qiniudn.com/tool/regexpal/][http_wpjam.qiniudn.com_tool_regexpal]

(\[^ |^\\n\]\*) (\[^ \]\*) (\[^ \]\*) (\\\[.\*\\\]) (\\".\*?\\") (-|\[0-9\]\*) (-|\[0-9\]\*) (\\".\*?\\") (\\".\*?\\") (-)

建立原表：

drop table if exists chavin.nginx\_access\_log;  
CREATE TABLE chavin.nginx\_access\_log(  
host STRING,  
identity STRING,  
user STRING,  
time STRING,  
request STRING,  
status STRING,  
size STRING,  
referer STRING,  
agent STRING,  
other STRING)  
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'  
WITH SERDEPROPERTIES (  
"input.regex" = "(\[^ |^\\n\]\*) (\[^ \]\*) (\[^ \]\*) (\\\\\[.\*\\\\\]) (\\".\*?\\") (-|\[0-9\]\*) (-|\[0-9\]\*) (\\".\*?\\") (\\".\*?\\") (-)",  
"output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s %9$s %10$s"  
)  
STORED AS TEXTFILE;

清除access.log日志中空白行：

sed -i '/^$/d' access.log

加载access.log日志数据到hive中：

load data local inpath '/opt/datas/access.log' overwrite into table chavin.nginx\_access\_log;

查询数据，进行验证：

select \* from chavin.nginx\_access\_log limit 5;

接下来可以建立业务子表，进行定向分析了。

[http_192.168.100.126_oportal_static_ui_layer_skin_layer.css]: http://192.168.100.126//oportal/static/ui/layer/skin/layer.css
[http_192.168.100.126_dsfdsal_page_homepage_css_indet.css]: http://192.168.100.126//dsfdsal/page/homepage/css/indet.css
[http_192.168.100.126_fposdfsdrtal_page_homdsfdepage_homepage.jsp_tokenid_5728A0ED7998CC84B88FE8717A33FAB8aK79UkfS]: http://192.168.100.126//fposdfsdrtal/page/homdsfdepage/homepage.jsp?tokenid=5728A0ED7998CC84B88FE8717A33FAB8aK79UkfS
[http_192.168.100.126_fpdsfdsfortal_page_waittodo_waittodo.jsp_registerName_25E7_25BB_25BC_25E5_2590_2588_25E9_25A2_2584_25E7_25AE_2597_registerAppid_bas_spf_bgt_indi_hqreport_hqoa_hqbi_registerId_FD748AA382851A37F1693D3880C844EF_allviewnum_10_appSource_undefined_tokenid_5728A0ED7998CC84B88FE8717A33FAB8aK79UkfS_waittodoNums_0_showway_0]: http://192.168.100.126/fpdsfdsfortal/page/waittodo/waittodo.jsp?registerName=%25E7%25BB%25BC%25E5%2590%2588%25E9%25A2%2584%25E7%25AE%2597&registerAppid=bas,spf,bgt,indi,hqreport,hqoa,hqbi&registerId=FD748AA382851A37F1693D3880C844EF&allviewnum=10&appSource=undefined&tokenid=5728A0ED7998CC84B88FE8717A33FAB8aK79UkfS&waittodoNums=0&showway=0
[http_wpjam.qiniudn.com_tool_regexpal]: http://wpjam.qiniudn.com/tool/regexpal/