HDInsight Hive Explode

Question

Hi I have a JSON document in my HDInsight Hadoop unix cluster like below and my requirememt is like below:

{
"timestamp":"1475746926","mac":"00-1E-C0-E3-0E-62","free":"24816","irradiation":"742.7","irradiation_kwh":"1.011999",
"meters":[{"id":"10","meter_kwh_total":"58844.2"},{"id":"11","meter_kwh_total":"58846.2"}
]}

SELECT
  GET_JSON_OBJECT(SolarStageing.json_body,'$.mac'), 
  GET_JSON_OBJECT(SolarStageing.json_body,'$.meters.id'),
  GET_JSON_OBJECT(SolarStageing.json_body,'$.meters.meter_kwh_total')
FROM  SolarStageing 

 Output:

{"00-1E-C0-E3-0E-62",["10","11"],["58844.2","58846.2"]}

But I want output like below:

{"00-1E-C0-E3-0E-62",["10"],["58844.2"]}

{"00-1E-C0-E3-0E-62",["11"],["58846.2"]}

to get that output I written below query:

SELECT
  GET_JSON_OBJECT(SolarStageing.json_body,'$.mac'), 
  GET_JSON_OBJECT(SolarStageing.json_body,'$.meters.id'),
  GET_JSON_OBJECT(SolarStageing.json_body,'$.meters.meter_kwh_total')
FROM 
(SELECT explode(json_body) as json_body FROM SolarStageing)  AS SolarStageing

When I executed that query I got the below error:

UDFArugumentException explode() takes an array or a map as a parameter.

Can you guys help me how to get that result, Thanks in Advance!!

Answer 1

Hi AnilKumar_Motif,

Thank you for contacting Microsoft forums. We are pleased to answer your query.

We are checking on the query and will get back to you soon on this.

Meanwhile you can refer the link  http://thornydev.blogspot.in/2013/07/querying-json-records-via-hive.html which might help to resolve your issue.

I apologize for the inconvenience and appreciate your time and patience in this matter.

Regards,

Bhanu Prakash

Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

------------------------------------------------------------------------------------------------------------Kindly click "Mark as Answer" on the post that helps you, this can be beneficial to other community members reading the thread and also “Vote as Helpful”.

Answer 2

Hi AnilKumar_Motif,

In addition of above post,you may also  use the third party SerDe to meet the requirement. Please find the Link below which may help to resolve your issue.

https://docs.microsoft.com/en-us/azure/hdinsight/hdinsight-using-json-in-hive

I hope that the reply will assist you in getting your query addressed.

In case you require further assistance, please do reply to the thread as we are always available to your queries.

Regards,

Bhanu Prakash

----------------------------------------------------------------------------------------------------------Kindly click "Mark as Answer" on the post that helps you, this can be beneficial to other community members reading the thread and also “Vote as Helpful”.