CODEWITHSUNDEEP
jsonhadoophive
saurabh agarwal
saurabh agarwal

Reputation: 2184

Convert Json to separate columns in HIVE

I have 4 columns in Hive database table. First two columns are of type string, 3rd and 4th are of JSON. Type. How to extract json data in different columns. SERDE available in Hive seems to be handling only json data. I have both normal (STRING) and JSON data. How can I extract data in separate colums here.

Example:

abc 2341    {max:2500e0,value:"20",Type:"1",ProviderType:"ABC"} {Name:"ABC",minA:1200e0,StartDate:1483900200000,EndDate:1483986600000,Flags:["flag4","flag3","flag2","flag1"]}

xyz 6789    {max:1300e0,value:"10",Type:"0",ProviderType:"foo"} {Name:"foo",minA:3.14159e0,StartDate:1225864800000,EndDate:1225864800000,Flags:["foo","foo"]}

Upvotes: 2

Views: 3598

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

Given a fixed JSON

create table mytable (str string,i int,jsn1 string, jsn2 string);

insert into mytable values
    ('abc',2341,'{"max":2500e0,"value":"20","Type":"1","ProviderType":"ABC"}','{"Name":"ABC","minA":1200e0,"StartDate":1483900200000,"EndDate":1483986600000,"Flags":["flag4","flag3","flag2","flag1"]}')
   ,('xyz',6789,'{"max":1300e0,"value":"10","Type":"0","ProviderType":"foo"}','{"Name":"foo","minA":3.14159e0,"StartDate":1225864800000,"EndDate":1225864800000,"Flags":["foo","foo"]}')
;

select  str,i
       ,jsn1_max,jsn1_value,jsn1_type,jsn1_ProviderType
       ,jsn2_Name,jsn2_minA,jsn2_StartDate,jsn2_EndDate
       ,jsn2_Flags

from    mytable

        lateral view json_tuple (jsn1,'max','value','Type','ProviderType') 
            j1 as jsn1_max,jsn1_value,jsn1_type,jsn1_ProviderType

        lateral view json_tuple (jsn2,'Name','minA','StartDate','EndDate','Flags') 
            j2 as jsn2_Name,jsn2_minA,jsn2_StartDate,jsn2_EndDate,jsn2_Flags
;            

+-----+------+----------+------------+-----------+-------------------+-----------+-----------+----------------+---------------+-----------------------------------+
| str |  i   | jsn1_max | jsn1_value | jsn1_type | jsn1_providertype | jsn2_name | jsn2_mina | jsn2_startdate | jsn2_enddate  |            jsn2_flags             |
+-----+------+----------+------------+-----------+-------------------+-----------+-----------+----------------+---------------+-----------------------------------+
| abc | 2341 | 2500.0   |         20 |         1 | ABC               | ABC       | 1200.0    |  1483900200000 | 1483986600000 | ["flag4","flag3","flag2","flag1"] |
| xyz | 6789 | 1300.0   |         10 |         0 | foo               | foo       | 3.14159   |  1225864800000 | 1225864800000 | ["foo","foo"]                     |
+-----+------+----------+------------+-----------+-------------------+-----------+-----------+----------------+---------------+-----------------------------------+

Upvotes: 2

Related Questions