Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hive : How to explode a JSON column with an array, and embedded in a CSV file?

From a CSV file (with a header and a pipe delimiter) I've got the following content which contains a JSON column (with a collection inside), like this:

ProductId|IngestTime|ProductOrders
9180|20171025145034|[{"OrderId":"299","Location":"NY"},{"OrderId":"499","Location":"LA"}]
8251|20171026114034|[{"OrderId":"1799","Location":"London"}]

What I need is to create a SELECT Hive query which returns:

ProductId  IngestTime      OrderId        OrderLocation
9180       20171025145034  299            NY
9180       20171025145034  499            LA
8251       20171026114034  1799           London

So far, I tried many combinations by using 'explode', 'get_json_object' and so on, but I still haven't found the right SQL query.

Have you got a solution ?

Thanks a lot for your help :-)

like image 500
prossblad Avatar asked Oct 25 '17 21:10

prossblad


People also ask

What does explode () do on a JSON field?

The explode function explodes the dataframe into multiple rows.

Does Hive support JSON data type?

Apache Hive There is another interesting rather unconventional method to handle JSON data in HIVE. json_tuple and LATERAL VIEW. Table here only has one column that loads JSON data as a single string. json_tuple() is a User defined Table Function ( UDTF ) introduced in Hive 0.7.


Video Answer


1 Answers

I was having similar kind of requirement. The solution from this link helped me solve it. BTW, below is the query for your requirement assuming all the columns in your DB_TABLE are of type 'String'.

    SELECT ProductId,
       IngestTime,
       split(split(results,",")[0],':')[1] AS OrderId,
       regexp_replace(split(split(results,",")[1],':')[1], "[\\]|}]", "") AS OrderLocation
    FROM
       (SELECT ProductId,
             IngestTime,
             split(translate(ProductOrders, '"\\[|]|\""',''), "},") AS r
       FROM DB_TABLE) t1 LATERAL VIEW explode(r) rr AS results
like image 136
pushpavanthar Avatar answered Nov 15 '22 01:11

pushpavanthar