Regex to access the magazine in hive serde

Question

I want to extract (ip, requestUrl, timeStamp) from access logs to load into the hive database. One line from the access log is as follows.

66.249.68.6 - - [14/Jan/2012:06:25:03 -0800] "GET /example.com HTTP/1.1" 200 708 "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)" 

I have tried the following and several regex options without any success. (A loaded table with all NULL values ​​indicating that the regular expression does not match the input).

 CREATE TABLE access_log ( remote_ip STRING, request_date STRING, method STRING, request STRING, protocol STRING ) ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' WITH SERDEPROPERTIES ( "input.regex" = "([^ ]) . . [([^]]+)] \"([^ ]) ([^ ]) ([^ \"])\" *", "output.format.string" = "%1$s %2$s %3$s %4$s %5$s" ) STORED AS TEXTFILE; 

I am not very experienced with regex. Can someone help me with this?

Answer 1

I use rubular to test my regular expression. You can also use this expression

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

You get the following output

 1. 66.249.68.6 2. - 3. - 4. 14/Jan/2012:06:25:03 -0800 5. "GET /example.com HTTP/1.1" 6. 200 

Answer 2

At the end, use double '\' and '. *' (it is important!):

 CREATE EXTERNAL TABLE access_log ( `ip` STRING, `time_local` STRING, `method` STRING, `uri` STRING, `protocol` STRING, `status` STRING, `bytes_sent` STRING, `referer` STRING, `useragent` STRING ) ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' WITH SERDEPROPERTIES ( 'input.regex'='^(\\S+) \\S+ \\S+ \\[([^\\[]+)\\] "(\\w+) (\\S+) (\\S+)" (\\d+) (\\d+) "([^"]+)" "([^"]+)".*' ) STORED AS TEXTFILE LOCATION '/tmp/access_logs/'; 

PS Hive 0.7.1

Answer 3

Invalid, but considering that it is a log file in a known format, then it should work (untested in Hive, but works with grep -E and http://www.regexplanet.com/simple/index.html if you replace [^[] on [^\[] and [^]] on [^\]] ). It is assumed that you only need the three values ​​that you specifically mentioned.

 "input.regex" = "([0-9]+\.[0-9]+\.[0-9]+\.[0-9]+)[^[]+\[([^]]+)\][^/]+([^ ]+).+" "output.format.string" = "%1$s %2$s %3$s"