Constructing complex data type in Hive

2
votes

I have Hive table with primitive data types like this:

CustomerName|City|Product1|RatingByMe|RatingByOthers|Product2|RatingByMe|RatingByOthers

I wish to convert it to complex data types with 2 separate lines for 2 products and having this schema:

CustomerName|City|Product1|struct[RatingByMe,RatingByOthers]
CustomerName|City|Product2|struct[RatingByMe,RatingByOthers]

How can achieve this in Hive? Any leads would be appreciated.

1
hivehiveql

1 Answers

2
votes

You need just use an union and a named_struct

select
    CustomerName,
    City,
    Product1,
    named_struct("RatingByMe", RatingByMe, "RatingByOthers", RatingByOthers) as rating
from your_table
union all
select
    CustomerName,
    City,
    Product2,
    named_struct("RatingByMe", RatingByMe, "RatingByOthers", RatingByOthers) as rating
from your_table