Support Questions
Find answers, ask questions, and share your expertise

Transpose dataset in Hive

Highlighted

Transpose dataset in Hive

New Contributor
 

I'm trying to transpose a variable in Hive such as:

Id1  Id2 Event
 1    1   7
 2    2   3
 2    2   7

to

 Id1  Id2 Event_7 Event_3
  1    1   1
  2    2   1        1

Following is what I have so far:

 create temporary table event_trans as 
           select Id1, Id2,Event
           kv['3'] as Event_3,           kv['7'] as Event_7
           from(
             select Id1, Id2, collect(Event, '1') as kv
             from event1
             group by Id1, Id2

             )t

Error: Error while compiling statement: FAILED: ParseException line 1:84 missing EOF at '[' near 'kv'

I'm also interested to know how to transpose a dataset in Hive with duplicates such as to the same output:

Id1  Id2 Event
 1    1   7
 2    2   3
 2    2   7
 2    2   7

to

 Id1  Id2 Event_7 Event_3
  1    1   1
  2    2   1        1

Appreciate for any help!