Reading XML with PIG
I am trying to read data from xml file using PIG, but I am getting incomplete output.
Input file
<document>
<url>htp://www.abc.com/</url>
<category>Sports</category>
<usercount>120</usercount>
<reviews>
<review>good site</review>
<review>This is Avg site</review>
<review>Bad site</review>
</reviews>
</document>
and the code i am using:
register 'Desktop/piggybank-0.11.0.jar';
A = load 'input3' using org.apache.pig.piggybank.storage.XMLLoader('document') as (data:chararray);
B = foreach A GENERATE FLATTEN(REGEX_EXTRACT_ALL(data,'(?s)<document>.*?<url>([^>]*?)</url>.*?<category>([^>]*?)</category>.*?<usercount>([^>]*?)</usercount>.*?<reviews>.*?<review>\\s*([^>]*?)\\s*</review>.*?</reviews>.*?</document>')) as (url:chararray,catergory:chararray,usercount:int,review:chararray);
And I get the output:
(htp://www.abc.com/,Sports,120,good site)
which is an incomplete output. Someone please help me on what I am missing?
+3
source to share
1 answer
Yes!! Finally, it worked with help cross
. I am using XPath
, you can use regex if you like. I find the XPath way to be simpler and cleaner than regex. I think you can see it too. Don't forget to replace testXML.xml
with your XML.
XPath path:
DEFINE XPath org.apache.pig.piggybank.evaluation.xml.XPath();
A = LOAD 'testXML.xml' using org.apache.pig.piggybank.storage.XMLLoader('document') as (x:chararray);
B = FOREACH A GENERATE XPath(x, 'document/url'), XPath(x, 'document/category'), XPath(x, 'document/usercount');
C = LOAD 'testXML.xml' using org.apache.pig.piggybank.storage.XMLLoader('review') as (review:chararray);
D = FOREACH C GENERATE XPath(review,'review');
E = cross B,D;
dump E;
Regular expression path:
A = LOAD 'testXML.xml' using org.apache.pig.piggybank.storage.XMLLoader('document') as (x:chararray);
B = FOREACH A GENERATE FLATTEN(REGEX_EXTRACT_ALL(x,'(?s)<document>.*?<url>([^>]*?)</url>.*?<category>([^>]*?)</category>.*?<usercount>([^>]*?)</usercount>.*?</document>')) as (url:chararray,catergory:chararray,usercount:int);
C = LOAD 'testXML.xml' using org.apache.pig.piggybank.storage.XMLLoader('review') as (review:chararray);
D = FOREACH C GENERATE FLATTEN(REGEX_EXTRACT_ALL(review,'<review>([^>]*?)</review>'));
E = cross B,D;
dump E;
Output:
(htp://www.abc.com/,Sports,120,Bad site)
(htp://www.abc.com/,Sports,120,This is Avg site)
(htp://www.abc.com/,Sports,120,good site)
Didn't you expect this?;)
+2
source to share