hadoop - 如何在配置单元中取消透视表？

Question

我有如下数据集:

item|location|week1|week2|week3|week4
_____________________________________
1000|10000000|1.2  |2.2  |3.2  |4.5  
1001|10000001|1.8  |2.5  |3.5  |4.1  
1002|10000002|9.3  |2.9  |3.7  |4.8  

我希望数据像下面这样逆轴旋转:

item|location|week_name|week_value
__________________________________
1000|10000000|week1    |1.2       
1000|10000000|week2    |2.2       
1000|10000000|week3    |3.2
1000|10000000|week4    |4.5
1001|10000001|week1    |1.8
1001|10000001|week2    |2.5
1001|10000001|week3    |3.5
1001|10000001|week4    |4.1
1002|10000002|week1    |9.3
1002|10000002|week2    |2.9
1002|10000002|week3    |3.7
1002|10000002|week4    |4.8

告诉我任何有效的方法/查询来做到这一点？

Answer 1

*根据我评论的 OP 回复更新(使用 week_number 而不是 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         |
+-------+-----------+--------------+-------------+