Hadoop-Hive |在Hive中将单行列转换为多行 [英] Hadoop-Hive | Convert single row columns into multiple rows in Hive
问题描述
创建日期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 |
+ ---- + ------ + ----- + -------- +
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 |
+----+------+-----+--------+
这篇关于Hadoop-Hive |在Hive中将单行列转换为多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!