regex for access log in hive serde

Question

I want to extract out (ip, requestUrl, timeStamp) from the access logs to load to hive database. One line from 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 tried with following and several variations of regex without any success. (The loaded table is with all NULL values indicating the regex doesn't 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 anybody help me with this?

score 13 · Answer 1 · answered Nov 11 '12 at 22:00

13

Use double '\' and '.*' in the end (it's 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/';

P.S. Hive 0.7.1

answered Nov 11 '12 at 22:00

agorokhov

196
1
4

Can you explain the reason for 'Use double '\' and '.*' in the end ' – saurzcode Jan 02 '15 at 14:23
1

This is WAY more important than it got credit for. The \\ is necessary because the regex is inside of a string that's part of a SQL query... so in order to get a \S, you need to type \\S so that you're getting an actual (i.e. escaped) backslash as part of the command. \\S+ will match one or more non-whitespace chars. \S* is just trying to match 0 or more uppercase S characters. – TheProletariat Apr 14 '17 at 22:07
The .* at the end is just for cleanup, in case you missed anything with your matching groups. – TheProletariat Apr 14 '17 at 22:08
The double \ saved my day. Thanks a lot. – Ali Hashemi Apr 16 '17 at 11:00
I wish the Hive, AWS Athena and other docs would include this example. – Michael Helmke Jun 21 '17 at 06:33

score 7 · Accepted Answer · answered Aug 15 '12 at 20:49

I use rubular to test my regex. 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

score 1 · Answer 3 · edited Feb 02 '12 at 14:30

Not fool-proof, but given that it is a log file in a known format then the following should work (untested in Hive, but works with grep -E and with http://www.regexplanet.com/simple/index.html if you replace [^[] with [^\[] and [^]] with [^\]]). Assumes you only want the three values you specifically mentioned.

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

regex for access log in hive serde

3 Answers3

Linked