CODEWITHSUNDEEP
sqlhive
alexxx
alexxx

Reputation: 135

Hive sql expand multiple columns to rows

I have a hive table which has the following format：

user | item | like | comment
 Joe     5      1       0
 Lan     3      0       1
Mack     5      1       1

and I want use HIVE SQL to convert like and comment to the user behavior column, then keep rows which user and item and times of behaviors：

user | item | behavior | times
 Joe     5      like       1
 Joe     5      comment    0
 Lan     3      like       0
 Lan     3      comment    1
 Mack    5      like       1
 Mack    5      comment    1

could you please give any advice?

Upvotes: 0

Views: 971

Answers (3)

damientseng
damientseng

Reputation: 553

Great answer by Prabhala and Linoff. Here I'm offering yet another way, the builtin UDTF stack, which is both intuitive and native.

select
    stack(2, user, item, 'like',    like,
             user, item, 'comment', comment)
         as (user, item, behavior, times)
from tbl
);

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Using map and explode.

select user,item,behavior,times
from tbl 
lateral view explode(map('like',like,'comment',comment)) t as behavior,times

As as side note, you should avoid using reserved keywords like user, like, comment as column names.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

One method uses union all:

select user, item, 'like' as behavior, like as times
from t
union all
select user, item, 'comment' as behavior, comment as times
from t;

Upvotes: 0

Related Questions