Hive匯入Apache Nginx等日誌與分析

將nginx日誌匯入到hive中的兩種方法 

1 在hive中建表

1. CREATE TABLE apachelog (ipaddress STRING, identd STRING, user STRING,finishtime STRING,requestline string, returncode INT, size INT,referer string,agent string) ROW FORMAT SERDE 'org.apache.Hadoop.hive.serde2.dynamic_type.DynamicSerDe'WITH SERDEPROPERTIES ('serialization.format'='org.apache.hadoop.hive.serde2.thrift.TCTLSeparatedProtocol','quote.delim'='("|\\[|\\])','field.delim'=' ','serialization.null.format'='-')STORED AS TEXTFILE;   

匯入後日誌格式為 

203.208.60.91 -  -  05/May/2011:01:18:47 +0800      GET /robots.txt HTTP/1.1        404     1238 Mozilla/5.0 

 此方法支援hive中函數parse_url(referer,"HOST")

第二種方法匯入

 注意：這個方法在建表後，使用查詢語句等前要先執行

hive> add jar /home/hjl/hive/lib/hive_contrib.jar;

或者設定hive/conf/hive-default.conf  添加

<property>
<name>hive.aux.jars.path</name>
<value>file:///usr/local/hadoop/hive/lib/hive-contrib-0.7.0-cdh3u0.jar</value>
</property>

儲存配置

1. CREATE TABLE apilog20110505 (ipaddress STRING,identity STRING,user STRING,time STRING,request STRING,protocol STRING,status STRING,size STRING,referer STRING,agent STRING) ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' WITH SERDEPROPERTIES ("input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) (-|\\[[^\\]]*\\]) ([^ \"]*|\"[^\"]*) ([^ ]*\") (-|[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;  

203.208.60.91   -       -       [05/May/2011:01:18:47 +0800]    "GET /robots.txt HTTP/1.1"      404     1238 "-"      "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)" 

此方法中的欄位類型string from deserializer   經測試不支援parse_url(referer,"HOST")擷取網域名稱

可以用select split(referer,"/")[2] from apilog 擷取網域名稱

如果檔案資料是純文字，可以使用 STORED AS TEXTFILE。如果資料需要壓縮，使用 STORED AS SEQUENCE  。

匯入日誌命令

hive>load data local inpath '/home/log/map.gz' overwrite into table log;  

匯入日誌支援.gz等格式

 

匯入日誌後進行分析 例句

統計行數
select count(*) from nginxlog;

統計IP數
select count(DISTINCT ip) from nginxlog;

排行
select t2.ip,t2.xx from (SELECT ip, COUNT(*) AS xx FROM nginxlog GROUP by ip) t2 sort by t2.xx desc

hive>SELECT * from apachelog  WHERE ipaddress = '216.211.123.184';

 

hive> SELECT ipaddress, COUNT(1) AS numrequest FROM apachelog GROUP BY ipaddress SORT BY numrequest DESC LIMIT 1;

hive> set mapred.reduce.tasks=2;
hive> SELECT ipaddress, COUNT(1) AS numrequest FROM apachelog GROUP BY ipaddress SORT BY numrequest DESC LIMIT 1; hive>CREATE TABLE ipsummary (ipaddress STRING, numrequest INT);
hive>INSERT OVERWRITE TABLE ipsummary SELECT ipaddress, COUNT(1) FROM apachelog GROUP BY ipaddress; hive>SELECT ipsummary.ipaddress, ipsummary.numrequest FROM (SELECT MAX(numrequest) AS themax FROM ipsummary) ipsummarymax JOIN ipsummary ON ipsummarymax.themax = ipsummary.numrequest; hive查詢結果匯出為csv的方法（未測試） hive> set hive.io.output.fileformat=CSVTextFile;
hive> insert overwrite local directory '/tmp/CSVrepos/' select * from S where ... ;