Hive - 配置单元中的Unpivot功能 [英] Hive - Unpivot functionality in hive

问题描述

我有两张表如下：

表A

  userid |代码| 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 
  

使用表A中的code_name，我想查找Table A中的各自的property_id B.基本上匹配表B中的列名称。目的是获得相应的new_id。苹果，芒果，葡萄和桃子ID可以是相同的。但是，new_id值将是唯一的。

这可能在Hive中吗？ Hive中似乎没有任何unpivot / pivot功能。

任何帮助都会非常棒。感谢！

解决方案

无论何时我想在Hive中旋转表，我都会收集 key：value 与地图配对，然后引用下一级中的每个键，创建新列。这与此相反。

查询：

 <从$ b $选择a.userid，y.new_id 
从（
）中选择new_id，fruit_name，fruit_code $ b $ select new_id，map（apple_id，apple_id 
，mango_id，mango_id 
，grape_id，grape_id 
，peach_id，peach_id）作为fruit_map 
 from table_2）x 
横向视图爆炸（fruit_map）作为fruit_name，fruit_code的exptbl1）y 
连接table_A a 
 on（y.fruit_code = a.property_id）
  

输出：

  0001 N456098 
 0031 N002345 
 0008 N129087 
 00013 N109876 
  

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

Table 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

Desired Resultant table

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

Using the code_name in Table A, I would like to find the respective property_id from Table A in Table B. Basically, match on the column name in Table B. The aim is to get the corresponding new_id.

Apple, mango, grape and peach ids can be the same. However, new_id values will be unique.

Is this possible in Hive? There does not seem to be any unpivot/pivot functionality in Hive.

Any help would be really great. Thanks!

解决方案

Whenever I want to pivot a table in Hive, I collect key:value pairs to a map and then reference each key in the next level, creating new columns. This is the opposite of that.

Query:

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)

Output:

0001    N456098
0031    N002345
0008    N129087
00013   N109876

这篇关于Hive - 配置单元中的Unpivot功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！