在 hiveql 中将列转换为行(UNPIVOT) [英] Converting columns to rows (UNPIVOT) in hiveql

问题描述

我有一个结构如下的表:

I have a table with a structure like this:

column1, column2, column3, X1, X2, X3, X4
A1,      A2,      A3,      5,  6,  1,  4

我想把它转换成

column1, column2, column3, Key, Value 
 A1,      A2,      A3,      X1,  5
 A1,      A2,      A3,      X2,  6 
 A1,      A2,      A3,      X3,  1
 A1,      A2,      A3,      X4   4

我已经能够使用与UNION ALL"缝合在一起的 4 个查询来做到这一点，但是由于表很大并且每个选择都转换为冗长的 map-reduce，使用 UNION 使查询花费的时间是它应该的 N 倍理想地采取.其中 N 是要旋转的列数.

I was able to do this already using 4 queries stitched together with "UNION ALL", but since the table is huge and each select translates into a lengthy map-reduce, using UNION makes the query takes N times the time it should ideally take. Where N is number of columns to pivot.

我尝试探索explode() 预定义的UDTF，但我无法在本示例中使用它.我尝试了类似以下的内容，但无法使语法正常工作.

I tried exploring the explode() pre-defined UDTF, but I am not able to work it in this example. I tried something like the following, but am not able to make the syntax work.

select column1, column2, column3, explode(Map('X1':X1, 'X2':X2, ..))

有人可以指出具体如何进行这项工作吗??我猜我可以推出自己的 UDTF，但希望这是非常标准的东西?

Can someone please point out exactly how to make this work?? I am guessing I could roll my own UDTF, but am hoping this is something pretty standard?

在 stackoverflow 上有另一个问题，其中提出了类似的问题，但公式很复杂，在我看来，错误的答案目前被标记为正确答案.我认为这个问题更简洁明了.

There is another question on stackoverflow where something similar was asked, but the formulation is convoluted and in my opinion the wrong answer is currently marked as the correct answer. I think this question is more succint and to the point.

推荐答案

糟糕，这个帖子好像很匆忙.我有答案.将其张贴在此处供其他人可能会发现它有用.这是处理映射和爆炸以实现此目的的正确语法.

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    

这篇关于在 hiveql 中将列转换为行(UNPIVOT)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！