Hive - Unpivot Functionality in Hive

Converting columns to rows (UNPIVOT) in hiveql

Whoops, posted this in a hurry it seems. I have the answer. Posting it here for others who might find this useful.
Here is the correct syntax to deal with map and explode to achieve this.

select column1, column2, column3, m_key, m_val from
    (select column1, column2, column3, map("X1", X1, "X2", X2, "X3", X3, "X4", X4) as map1
    from table1) as t1
lateral view explode(map1) xyz as m_key, m_val    

How to unpivot table in hive?

*Updated according to the OP reply for my comment (using week_number instead of week_name)

select  item
       ,location
       ,pe.pos+1  as week_number
       ,pe.val    as week_value

from    mytable t
        lateral view    posexplode(array(week1,week2,week3,week4)) pe
;

---

+-------+-----------+--------------+-------------+
| item  | location  | week_number  | week_value  |
+-------+-----------+--------------+-------------+
| 1000  | 10000000  | 1            | 1.2         |
| 1000  | 10000000  | 2            | 2.2         |
| 1000  | 10000000  | 3            | 3.2         |
| 1000  | 10000000  | 4            | 4.5         |
| 1001  | 10000001  | 1            | 1.8         |
| 1001  | 10000001  | 2            | 2.5         |
| 1001  | 10000001  | 3            | 3.5         |
| 1001  | 10000001  | 4            | 4.1         |
| 1002  | 10000002  | 1            | 9.3         |
| 1002  | 10000002  | 2            | 2.9         |
| 1002  | 10000002  | 3            | 3.7         |
| 1002  | 10000002  | 4            | 4.8         |
+-------+-----------+--------------+-------------+

How to transpose/pivot data in hive?

Here is the solution I ended up using:

add jar brickhouse-0.7.0-SNAPSHOT.jar;
CREATE TEMPORARY FUNCTION collect AS 'brickhouse.udf.collect.CollectUDAF';

select 
    id, 
    code,
    group_map['p'] as p,
    group_map['q'] as q,
    group_map['r'] as r,
    group_map['t'] as t
    from ( select
        id, code,
        collect(proc1,proc2) as group_map 
        from test_sample 
        group by id, code
    ) gm;

The to_map UDF was used from the brickhouse repo: https://github.com/klout/brickhouse

---

Related Topics