Hadoop-Hive |在Hive中将单行列转换为多行(Hadoop-Hive | Convert single row columns into multiple rows in Hive)

295 IT屋

I have a Hive table like this

 Created_date   ID1 Name1 Age1 Gender1 Name2 ID2 Age2 Gender2 ID3 Name3  Age3 Gender3....
  2014-02-01    1   ABC   21    M      MNP    2  22   F       3   XYZ    25   M
  2015-06-06    11  LMP   31    F      PLL   12  42   M       13  UIP    37   F

This table may have any no. of repeated set of the 4 columns pair. The sequence of these 4 columns is also not fix and there may be 1 or 2 more columns which are not repeated like created_date

I need to convert the above table into a new Hive table having only 4 columns ID, Name, Age and Gender like below. I dont need created_date column in my result table.

ID  Name  Age Gender
1   ABC   21  M
2   MNP   22  F
3   XYZ   25  M
11  LMP   31  F
12  PLL   42  M
13  UIP   37  F

Please suggest how can I achieve this in Hive.

解决方案
select  inline
        (
            array
            (
                struct(ID1,Name1,Age1,Gender1)
               ,struct(ID2,Name2,Age2,Gender2)
               ,struct(ID3,Name3,Age3,Gender3)
             )
        ) as (ID,Name,Age,Gender)

 from   mytable

+----+------+-----+--------+
| id | name | age | gender |
+----+------+-----+--------+
|  1 | ABC  |  21 | M      |
|  2 | MNP  |  22 | F      |
|  3 | XYZ  |  25 | M      |
| 11 | LMP  |  31 | F      |
| 12 | PLL  |  42 | M      |
| 13 | UIP  |  37 | F      |
+----+------+-----+--------+



 创建日期ID1姓名1年龄1性别1姓名2 ID2年龄2性别2 ID3姓名3年龄3性别3 ... 
2014-02-01 1 ABC 21 M MNP 2 22 F 3 XYZ 25 M
2015-06-06 11 LMP 31 F PLL 12 42 M 13 UIP 37 F


此表可能有任何编号。重复设置4列对。这4列的顺序也不是固定的,可能有1或2个列不重复,像created_date



我需要将上表转换为新Hive表只有4列ID,名称,年龄和性别如下。

  ID姓名年龄性别
1 ABC 21 M
我不需要结果表中的created_date列。 2 MNP 22 F
3 XYZ 25 M
11 LMP 31 F
12 PLL 42 M
13 UIP 37 F


请建议我如何在Hive中实现此功能。

解决方案
<$ p


struct(ID1,Name1,Age1,Gender1)
,struct(ID2,Name2)

$ ,年龄2,性别2)
,结构(ID3,姓名3,年龄3,性别3)

)作为(ID,姓名,年龄,性别)

from mytable





  + ---- + ------ + ----- + -------- + 
| id |名称|年龄|性别|
+ ---- + ------ + ----- + -------- +
| 1 | ABC | 21 | M |
| 2 | MNP | 22 | F |
| 3 | XYZ | 25 | M |
| 11 | LMP | 31 | F |
| 12 | PLL | 42 | M |
| 13 | UIP | 37 | F |
+ ---- + ------ + ----- + -------- +

本文地址:IT屋 » Hadoop-Hive |在Hive中将单行列转换为多行