How to define an HDInsight hive external table based on XMLs in a container

Question

I tried creating a hive external table:

CREATE EXTERNAL TABLE TestXML (storexml string) STORED AS TEXTFILE LOCATION 'wasb:///test/';

However when i try executing query like below, its not able to extract the fields: SELECT xpath_string (storexml, '/trades/trade/USI')

 FROM TestXML;

I saw a post, that talked about specifying the input format. add JARS <> set xmlinput.element=Store; CREATE EXTERNAL TABLE EventStoreXML (storexml string) STORED AS INPUTFORMAT 'msdn.hadoop.mapreduce.input.XmlElementStreamingInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'wasb:///[email protected]/';

I could not determine, which jars to include in the add JARs statement. I am using HDInsight on Linux.

Any pointers will be appreciated. -Madhu

madhu m madhu m · Accepted Answer · 2016-10-27T03:30:04

Realised the issue was with the XML having carriage return, as a result it was not able to read the XML.

How to define an HDInsight hive external table based on XMLs in a container

1 Answers