Hive - Hive中的“Unpivot”功能

Question

I have two table as follows:

Table A

userid | code | code_name | property_id
0001   | 1    | apple_id  | Y1234
0031   | 4    | mango_id  | G4567
0008   | 3    | grape_id  | H1209
00013  | 2    | peach_id  | Z5643

表格 2

apple_id | mango_id | grape_id | peach_id | new_id
Y1234    |  R1890   |          |          | N456098
         | G4567    |          |  B3490   | N002345
T3336    |          | H1209    |  F3467   | N129087
         |  D7865   | J6543    |  Z5643   | N109876

期望的结果表

userid | new_id
0001   | N456098
0031   | N002345
0008   | N129087
00013  | N109876

使用Table A中的code_name，我想在Table B中找到相应的property_id。基本上，在Table B中按列名进行匹配。目标是获得相应的new_id。

苹果、芒果、葡萄和桃子的id可以相同。然而，new_id值将是唯一的。

这在Hive中是否可行？Hive似乎没有任何未旋转/旋转功能。

任何帮助都将非常棒。谢谢！

- activelearner

---

您最终解决了这个问题吗？ - o-90

你能否重新考虑一下你选择的正确答案。GoBrewers14提到的map-explode方法是一个更好的方法。 - NG Algo

Answer 1

每当我想在Hive中透视表时，我会将key:value对收集到一个映射中，然后在下一级引用每个键，创建新的列。这与此相反。

查询：

select a.userid, y.new_id
from (
  select new_id, fruit_name, fruit_code
  from (
    select new_id, map("apple_id", apple_id
                     , "mango_id", mango_id
                     , "grape_id", grape_id
                     , "peach_id", peach_id) as fruit_map
    from table_2 ) x
  lateral view explode(fruit_map) exptbl1 as fruit_name, fruit_code ) y
join table_A a
on (y.fruit_code=a.property_id)

输出:

0001    N456098
0031    N002345
0008    N129087
00013   N109876

- o-90

---

这应该被标记为正确答案。这是对于此类问题使用 map 和 explode 的出色演示。与使用 UNION 相比，这是一种更好的方法，特别是在处理来自 SQL 的大型 MapReduce 作业时，在 Hive 中使用。 - NG Algo

如何扩展此功能，以便您无需手动编写所有不同级别的代码名称（例如苹果，芒果，葡萄，桃子）。如果像“select distinct code_name”这样的输入可以用来创建新列，那就太理想了。 - Anonymous

Answer 2

实际上，您不需要进行反转操作即可获得结果。

SELECT * FROM 
(
    SELECT userid,
        CASE WHEN property_id = apple_id THEN new_id  
             WHEN property_id = mango_id THEN new_id  
             WHEN property_id = grape_id THEN new_id  
             WHEN property_id = peach_id THEN new_id
        END AS newid
    FROM
    (SELECT * FROM a FULL JOIN b) X
) y
WHERE newid IS NOT NULL;

结果：

y.userid  y.newid
1         N456098
31        N002345
8         N129087
13        N109876

- Gautham Kondapaneni

---

这个答案被踩是因为它的计算成本比 explod 函数更高吗？ - Zafar