tag:blogger.com,1999:blog-4358489820319105877.post1215312695000969232..comments2024-03-21T04:57:05.394-07:00Comments on Dennis and Jim's Data Engineering Blog: Using the XML SerDe in Hive for Exploding Nested XML ElementsJimhttp://www.blogger.com/profile/08462849367548283573noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-4358489820319105877.post-7874889652438007902021-10-30T06:39:19.987-07:002021-10-30T06:39:19.987-07:00Thanks for sharing this Informative content. Well ...Thanks for sharing this Informative content. Well explained.<br />Visit us: <a href="https://onlineitguru.com/dot-net-online-training-placement.html" rel="nofollow">Dot Net Online Training Hyderabad</a> <br />Visit us: <a href="https://onlineitguru.com/dot-net-online-training-placement.html" rel="nofollow">.net online training india</a> Kaparthicynixithttps://www.blogger.com/profile/08721903793774330174noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-25021682181049284322019-12-26T09:48:20.333-08:002019-12-26T09:48:20.333-08:00I'll need a proper example of your xml to help...I'll need a proper example of your xml to help.Jimhttps://www.blogger.com/profile/08462849367548283573noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-23064237552779454812019-12-14T01:13:22.168-08:002019-12-14T01:13:22.168-08:00Thanks for this wonderful blog. I tried the same w...Thanks for this wonderful blog. I tried the same way to parse below xml files.<br /><br />200000510925SDP_X_UDC_ASSET_IDTPDDL10035443METER_X_UNIVERSAL_IDCkVAhRegister0KVAH3Delivered2019-12-04T00:00:00.000+05:304629.17VALfalseCkWh<br /><br /> Register<br /> 0<br /> KWH<br /> 1<br /> Delivered<br /><br /><br /> 2019-12-04T00:00:00.000+05:30<br /> 4369.52<br /> <br />VAL<br />false<br /> <br /><br /> <br /><br />Below is my syntax:-<br />CREATE EXTERNAL TABLE XML.MeterReads_tmp<br />(meterread <br />array,<br />Meter:struct,<br /> IntervalBlock: array>><br /> ><br /> >>)<br />ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'<br />WITH SERDEPROPERTIES ( "column.xpath.meterread"="/payload/MeterReading" )<br />STORED AS<br />INPUTFORMAT 'com.ibm.spss.hive.serde2.xml.XmlInputFormat'<br />OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'<br />LOCATION 'hdfs://bdacdh-ns/user/test'<br />TBLPROPERTIES ("xmlinput.start"="<payload", "xmlinput.end"="</payload>");<br /><br />IntervalBlock is populating 3 times with NULL.<br /><br />[{"meterreading":{"servicedeliverypoint":{"mrid":"200000510925","idtype":"SDP_X_UDC_ASSET_ID"},"meter":{"mrid":"10035443","idtype":"METER_X_UNIVERSAL_ID"},"intervalblock":[{"readingtypeid":"CkVAh","readingtype":null},{"readingtypeid":null,"readingtype":{"measurementtype":"Register","toubinnumber":"0"}},{"readingtypeid":null,"readingtype":null},{"readingtypeid":"CkWh","readingtype":null},{"readingtypeid":null,"readingtype":{"measurementtype":"Register","toubinnumber":"0"}},{"readingtypeid":null,"readingtype":null}]}}]<br /><br />Please correct my syntax to parse XML data. ThanksPankajhttps://www.blogger.com/profile/02769089802797680926noreply@blogger.comtag:blogger.com,1999:blog-4358489820319105877.post-30699339701457152182019-12-14T01:02:18.360-08:002019-12-14T01:02:18.360-08:00This comment has been removed by the author.Pankajhttps://www.blogger.com/profile/02769089802797680926noreply@blogger.com