Need to frame a JSON format using Hive data

user_15218404 0
Viewed 83

Could you please help me with the below requirement. I have a hive table data and using that need to frame the below JSON.

------------------------
Table : global_product
-------------------------
------------------------------------------------------------------------
Columns             Datatypes
------------------------------------------------------------------------
AAA                 varchar(10)
BBB                 varchar(10)
GEOGRAPHIC          array<struct<CCC:double,DDD:double>>    

select 
AAA, 
BBB, 
GEOGRAPHIC 
from global_product;

o/p:

 ind, 
 hyd, 
 [{"CCC":4.7,"DDD":7.6},{"CCC":5.1,"DDD":7.5}, {"CCC":5.7,"DDD":9.5}]

Need to frame the JSON format using the above column data

"GLOBAL":
{
"PRODUCTS":
        "{
        "ITEMS":{"AAA":"ind","BBB":"hyd"},
        "GEOGRAPHIC":[
                        {"CCC":45.78,"DDD":87.56},
                        {"CCC":45.73,"DDD":97.59},
                        {"CCC":45.73,"DDD":97.59}
                    ]
                }"
        }
apache-spark hive struct arrays json
1 Answers

If you are okay with complex type

struct<global: struct<products: struct<items: struct<AAA:double, BBB:double>,
                                      geographic: array< struct<ccc: double, ddd: double> >
                                      >
                   > 
      >

Then you can use named_struct() construct. In this case all attribute names will be lowercased.

Demo

with your_data as (--This is initial data example
select 'ind' as AAA, 'hyd' as BBB, 
array(named_struct("CCC",4.7,"DDD",7.6),named_struct("CCC",5.1,"DDD",7.5), named_struct("CCC",5.7,"DDD",9.5)) as GEOGRAPHIC
)

select named_struct('GLOBAL', named_struct('PRODUCTS',named_struct('ITEMS', named_struct('AAA', AAA, 'BBB', BBB), 
                                            'GEOGRAPHIC', GEOGRAPHIC) 
                                          )  
                   )
     from your_data;

Result (not pretty-printed):

{"global":{"products":{"items":{"aaa":"ind","bbb":"hyd"},"geographic":[{"ccc":4.7,"ddd":7.6},{"ccc":5.1,"ddd":7.5},{"ccc":5.7,"ddd":9.5}]}}}

And if you are not happy with such complex type and want to have STRING like yours with attributes NOT lowercased, then explode array and concatenate everything

set hive.execution.engine=mr;
with your_data as (--This is initial data example, use your table instead of this example
select 'ind' as AAA, 'hyd' as BBB, 
array(named_struct("CCC",4.7,"DDD",7.6),named_struct("CCC",5.1,"DDD",7.5), named_struct("CCC",5.7,"DDD",9.5)) as GEOGRAPHIC
)

select    concat('"GLOBAL":\n{\n"PRODUCTS":\n          "{\n    "ITEMS":{"AAA":"',AAA,'","BBB":"',BBB,'"},', '\n',
          concat('     "GEOGRAPHIC": [\n         ',concat_ws(',',collect_list(concat('{"CCC": "',e.CCC, '", "DDD": "',DDD,'"}'))),
                 '\n          ]\n     }"\n }') 
          )
     from your_data --replace with your table name
          lateral view outer inline(GEOGRAPHIC) e as CCC, DDD
          group by AAA, BBB;

Result:

"GLOBAL":
{
"PRODUCTS":
          "{
    "ITEMS":{"AAA":"ind","BBB":"hyd"},
     "GEOGRAPHIC": [
         {"CCC": "4.7", "DDD": "7.6"},{"CCC": "5.1", "DDD": "7.5"},{"CCC": "5.7", "DDD": "9.5"}
          ]
     }"
 }

Just use your table instead of CTE in FROM and it will work with your data. I added newlines \n and spaces to produce similar formatting.

user_27003440
Related