Unstructured data into structured data using Pig

Question

I'm trying to structure the un-structured data using PIG for doing some processing.

Here's the sample of the data:

Nov 1   18:23:34    dev_id=03   user_id=000 int_ip=198.0.13.24  ext_ip=68.67.0.14   src_port=99 dest_port=213   response_code=5

Expected output:

Nov 1 18:23:34, 03 , 000, 198.0.13.24, 68.67.0.14, 99, 213, 5

As we can see data is not properly separated (like tab or comma), so i tried to load the data using '\t' and dumped on the terminal.

A = LOAD '----' using PigStorage('\t') as (mnth: chararray, day: int, --------);

dump A;

Store A into '\root\output';

Output:

Dump output:

(Nov,1,18:23:34,dev_id=03,user_id=000,int_ip=198.0.13.24,ext_ip=68.67.0.14,src_port=99,dest_port=213,response_code=5)

Store oputut: Results are stored as the same as the input, not as dump(comma separated).

Nov 1   18:23:34    dev_id=03   user_id=000 int_ip=198.0.13.24  ext_ip=68.67.0.14   src_port=99 dest_port=213   response_code=5

Alternative: I also tried to load the data using DataStorage() as (value: varchar) and performed TOKENIZE also, but not able to achieve the objective.

Few more suggestion i need:

1. As i stored 3 fields as Month:"Nov", Day:"1", and Time:"18:23:34". Is it possible to join all three fields as time: "Nov 1 18:23:34".

2. All data stored with information like dev_id=03, user_id=000 but i need to remove the information and stored the information like 03,000,198.0.13.24 etc.

Is it possible to do all the processing using PIG or we need to write the MapReduce program.

EDIT:1

After getting the comment, I tried REGEX_EXTRACT for single column which works fine. For multiple column, i tried REGEX_EXTRACT_ALL as follows:

A = LOAD '----' using PigStorage('\t') as (mnth: chararray, day: int, dev: chararray, user: chararray --------);

B = foreach A generate REGEX_EXTRACT_All(devid, userid, '(^.*=(.*)$) (^.*=(.*)$)');

Dump B;

I got error:

Error: ERROR org.apache.pig.tools.grunt.Grunt - ERROR 1070: Could not resolve REGEX_EXTRACT_All using imports.

Can we extract multiple fields using REGEX_EXTRACT_All.

Answer 1

Just write a custom loader for your data and all your issues will be solved easily with java. Example of doing just that step by step can be found here

Answer 2

As i stored 3 fields as Month:"Nov", Day:"1", and Time:"18:23:34". Is it possible to join all three fields as time: "Nov 1 18:23:34".

You can use CONCAT to concatenate two chararrays in a FOREACH. In this case (this is kind of awkward, perhaps someone can suggest an alternative that doesn't require a UDF):

CONCAT(CONCAT(CONCAT(CONCAT(mnth, ' '), day), ' '), 'time')

---

All data stored with information like dev_id=03, user_id=000 but i need to remove the information and stored the information like 03,000,198.0.13.24 etc.

You should use REGEX_EXTRACT for this one, which extracts a piece of text given a regular expression. Use it in a FOREACH. Build a regular expression that grabs everything after the =. In this case:

REGEX_EXTRACT(field2, '^.*=(.*)$', 1);

---

Some other options:

• Writing your own custom Java storage function to do the parsing in java
• Write UDFs (python, java, whatever) to perform the above operations, instead of REGEX_EXTRACT and nested CONCAT.
• Load the line in its entirety as one chararray, then pass that into a UDF that does all the parsing, returning the result. This UDF gets put into a FOREACH. I like this better than writing a custom storage function, since I think it is a bit easier.
*