Import logs and analysis from Hive to Apache Nginx

Two methods to import nginx logs to hive

1. Create a table in hive

1. Create table apachelog (ipaddress STRING, identd STRING, user STRING, finishtime STRING, requestlineString, Returncode INT, size INT, refererString, AgentString) ROW FORMAT SERDE'Org. apache. Hadoop. hive. serde2.dynamic _ type. dynamicserde'With serdeproperties ('Serialization. format'='Org. apache. hadoop. hive. serde2.thrift. tctlseparatedprotocol','Quote. delimiter'='("| \ [| \])','Field. delimiter'='','Serialization. null. format'='-') Stored as textfile;

The log format after import is

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

This method supports the parse_url (referer, "HOST") function in hive ")

Method 2 Import

Note: This method must be executed before using the query statement after creating a table.

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

Or set hive/conf/hive-default.conf to add

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

Save Configuration

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 )"

The field type in this method is string From deserializer is tested and does not support parse_url (referer, "HOST") to retrieve domain names

You can use select split (referer, "/") [2] from apilog to get the Domain Name

If the file data is plain text, you can use stored as textfile. If data needs to be compressed, use stored as sequence.

Import log command

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

Imported logs support formats such as .gz

 

Example

Number of Statistics rows
Select count (*) from nginxlog;

IP count statistics
Select count (DISTINCT ip) from nginxlog;

Ranking
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; Export hive query results to csv (not tested) hive> set hive.io.output.fileformat=CSVTextFile;
hive> insert overwrite local directory '/tmp/CSVrepos/' select * from S where ... ;