question Re: Help with Hive Regex extract. in Archives of Support Questions (Read Only)

Help with Hive Regex extract.

saikrishna_tara — Thu, 06 Apr 2017 23:30:18 GMT

Hi,

i have a firewall log with entries like this..

Mar 12 04:03:01 172.16.3.1 %ASA-6-106100 access-list FW-DATA permitted tcp FW-DATA 172.16.1.4 59289 OUTSIDE 52.87.195.145 22 hit-cnt 1 first hit

i created an external table in hive for this log file and i am trying to use HIVE SQL and regexp_extract to extract column out of this lines by using this regular expression.

([A-Z][a-z]+ \d+\s\d+:\d+:\d+)\s(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\s+(\%ASA-6-106100):\s+(\w+-\w+\s+\w+-\w+)\s+(\w+)\s+(\w+)\s+(\w+-\w+)\/?(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})\((\d+)\)\s+->\s+(\w+-?\w+?)\/?(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})\((\d+)\)\s+(\w+-\w+\s+\d+\s+\w+\s+\w+)

when i tested this at different sites (like https://regex101.com/) ..it is working fine..breaking this line into many different groups shown below. but when i try to use that in HIVE its not working..any help is appriciated.

Mar 12 04:03:01

172.16.3.1

%ASA-6-106100

access-list FW-DATA

permitted

tcp

FW-DATA

172.16.1.4

59289

OUTSIDE

52.87.195.145

hit-cnt 1 first hit

Re: Help with Hive Regex extract.

balavignesh_nag — Fri, 07 Apr 2017 02:18:17 GMT

@Saikrishna Tarapareddy

Could you help me with your query? Is it throwing an error when you are using it in hive or the data which you were expecting is not replicated by the code?

Re: Help with Hive Regex extract.

saikrishna_tara — Fri, 07 Apr 2017 03:53:20 GMT

Hi @Bala Vignesh N V

when I use the following SQL its not returning me anything..i am not getting any error.

select regexp_extract('Mar 12 04:03:01 172.16.3.1 %ASA-6-106100: access-list FW-DATA permitted tcp FW-DATA/172.16.1.4(59289) -> OUTSIDE/52.87.195.145(22) hit-cnt 1 first hit', '([A-Z][a-z]+ \d+\s\d+:\d+:\d+)\s(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\s+(\%ASA-6-106100):\s+(\w+-\w+\s+\w+-\w+)\s+(\w+)\s+(\w+)\s+(\w+-\w+)\/?(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})\((\d+)\)\s+->\s+(\w+-?\w+?)\/?(\d{1,3}.\d{1,3}.\d{1,3}.\d{1,3})\((\d+)\)\s+(\w+-\w+\s+\d+\s+\w+\s+\w+)',0)

How do I get my input string broken in to multiple columns based on the regex.?

Re: Help with Hive Regex extract.

pminovic — Fri, 07 Apr 2017 04:49:30 GMT

You can consider to use Hive RegexSerDe, see here for details.

Re: Help with Hive Regex extract.

saikrishna_tara — Fri, 07 Apr 2017 04:54:14 GMT

I think I found the answer..looks like we need to use double slashes in hive..

this is working when I replaced log_entry with the text from above..

select regexp_extract(log_entry, '[A-Z][a-z]+\\s\\d+\\s\\d+:\\d+:\\d+', 0) , regexp_extract(log_entry, '\\d+\\.\\d+\\.\\d\\.\\d', 0) , regexp_extract(log_entry, '%ASA-6-106100', 0) , regexp_extract(log_entry, '\\w+-\\w+\\s+\\w+-\\w+', 0)