Parse JSON Array and load into hive table

Question

I have a Json Array like as below

[{"Name":"xxxx","Machine":"Machine1"},{"Name":"yyyy","Machine":"Machine2"},{"Name":"zzzz","Machine":"Machine3"}]

I need to parse that data and load into a hive table like below

Name    Machine

xxxx    Machine1
yyyy    Machine2
zzzz    Machine3

could someone please help?

I have done using regex_replace, split and explode. select explode(b.arr) as output from (select split(a.new,';') as arr from (select regexp_replace(regexp_replace(jsonarray.json,"\\}\\,\\{","\\}\\;\\{"),"\[|\]","") as new FROM jsonarray)as a)as b is there any better solution ? — Shalaj
– Shalaj, Commented Jun 9, 2017 at 6:25

David דודו Markovitz · Accepted Answer · 2017-06-09 07:21:46Z

9

select  j.Name,j.Machine

from    jsonarray t
        lateral view explode(split(substr(t.json,2),'(?<=\\}),(?=\\{)')) e
        lateral view json_tuple(e.col,'Name','Machine') j as Name,Machine
;

+------+----------+
| name | machine  |
+------+----------+
| xxxx | Machine1 |
| yyyy | Machine2 |
| zzzz | Machine3 |
+------+----------+

edited Jun 9, 2017 at 7:21

answered Jun 9, 2017 at 6:55

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse JSON Array and load into hive table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related