Cloudera Community
rylala72
user rank
New Contributor

Re: how to export xml data saved in a hive table's...

by New Contributor in Support Questions
‎10-23-2016 01:37 AM
‎10-23-2016 01:37 AM
Thank you very much Hari. Option#1 is perfect in my case. ... View more

Re: how to export xml data saved in a hive table's...

by New Contributor in Support Questions
‎10-22-2016 08:54 PM
‎10-22-2016 08:54 PM
Thanks Hari, yes I have seen that post which is bit different in scenario. Post explains loading xml file "sample.xml" into hive serde table. In my case I have xml stored in a hive table field 'xmldata' and i want to use some sort of 'insert into table_serde select xmlfield from XmlTable'. I mean xml data moving from hive table to hive table. Other way for me is that I can add location parameter in serde table pointing to location where single field hive table has stored its data but this has draw back if I add other fields in single field hive table it will invalidate serde table. Thanks, Riaz Lala ... View more

how to export xml data saved in a hive table's str...

by New Contributor in Support Questions
‎10-22-2016 06:32 PM
‎10-22-2016 06:32 PM
1) below hive table "books_xml" contains only one field named "xmldata" in which I have saved xml data as a single string. hive> select xmldata from books_xml; OK <?xml version="1.0" encoding="UTF-8"?><catalog><book> <id>11</id> <genre>Computer</genre> <price>44</price></book><book> <id>44</id> <genre>Fantasy</genre> <price>5</price></book></catalog> Time taken: 0.175 seconds, Fetched: 1 row(s) 2) I want to export above xml data (which is in field) into below hive table created with SERDE properties? CREATE TABLE books_serde ( id STRING, genre STRING, price DOUBLE ) ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe' WITH SERDEPROPERTIES ( "column.xpath.id"="/book/id/text()", "column.xpath.genre"="/book/genre/text()", "column.xpath.price"="/book/price/text()" ) STORED AS INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat' TBLPROPERTIES ( "xmlinput.start"="<book>", "xmlinput.end"="</book>" ); , 1) below hive table "books_xml" contains only one field named "xmldata String" in which I have saved xml data as a single string. hive> select xmldata from books_xml; OK <?xml version="1.0" encoding="UTF-8"?><catalog><book> <id>11</id> <genre>Computer</genre> <price>44</price></book><book> <id>44</id> <genre>Fantasy</genre> <price>5</price></book></catalog> Time taken: 0.175 seconds, Fetched: 1 row(s) 2) I want to export above xml data (which is in field) into below hive table created with SERDE properties? CREATE TABLE books_serde ( id STRING, genre STRING, price DOUBLE ) ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe' WITH SERDEPROPERTIES ( "column.xpath.id"="/book/id/text()", "column.xpath.genre"="/book/genre/text()", "column.xpath.price"="/book/price/text()" ) STORED AS INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat' TBLPROPERTIES ( "xmlinput.start"="<book>", "xmlinput.end"="</book>" ); ... View more
Labels:
Contact Me
Online
Offline
Last Visited
‎11-09-2016 03:47 PM
Community Statistics
Member Since ‎10-22-2016 06:24 PM
Last Visited ‎11-09-2016 03:47 PM
Posts 3