【hive】行转列—explode()/posexplode()/lateral view 函数使用场景_hive怎么把一条数据按列显示

作者：我家小花儿 | 2024-06-01 03:29:15

踩

hive怎么把一条数据按列显示

文章目录

一、lateral view函数
二、explode()函数
三、posexplode()函数
四、行转列使用
- 单列转多行
- 多列转多行

一、lateral view函数

功能:
- 用于和UDTF函数（explode,split）结合使用，把某一行数据拆分成多行数据，再将多行结果组合成一个支持别名的虚拟表。
- 主要解决select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。
语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (‘,’ columnAlias)
- columnAlias是给udtf(expression)列起的别名。
- tableAlias 虚拟表的别名。
lateral view函数会将lUDTF生成的结果放到一个虚拟表中，然后这个虚拟表和输入行进行join来达到连接UDTF外的select字段的目的。

使用方式：

举例1：与explode() 函数连用

--测试表的数据结构，可以看到第一列是id(string类型),第二列是rule_array(array<string>类型)
> desc test_2;

--结果：
+-------------+----------------+----------+
|  col_name   |   data_type    | comment  |
+-------------+----------------+----------+
| id          | string         |          |
| rule_array  | array<string>  |          |
+-------------+----------------+----------+

--查看测试表的内容
> select * from test_2;

--结果：
+------------+----------------------------------------+
| test_2.id  |           test_2.rule_array            |
+------------+----------------------------------------+
| a          | ["501","502","503","501","512"]        |
| b          | ["511","512","513","511","512","511"]  |
| c          | ["512","513","511","512"]              |
+------------+----------------------------------------+

--拆分rule_array
> select id, rule_explode 
> from test_2
> lateral view explode(rule_array) adTable AS rule_explode;

--结果：
+-----+---------------+
| id  | rule_explode  |
+-----+---------------+
| a   | 501           |
| a   | 502           |
| a   | 503           |
| a   | 501           |
| a   | 512           |
| b   | 511           |
| b   | 512           |
| b   | 513           |
| b   | 511           |
| b   | 512           |
| b   | 511           |
| c   | 512           |
| c   | 513           |
| c   | 511           |
| c   | 512           |
+-----+---------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

举例2：与parse_url_tuple()函数连用

parse_url_tuple()函数
- 语法：parse_url(string urlString, string partToExtract [, string keyToExtract])
- 功能：UDTF函数，解析URL字符串，它通过一次指定多个参数，从URL中解析出多个参数值返回多列。
- 参数：partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO。
- 注意：在使用 select 时不能同时返回其他字段，不能嵌套调用，不能与group by放在一起调用等。为解决此问题，可以通过测试图(Lateral View)搭配调用。

--测试表的数据结构，可以看到第一列是id(int类型),第二列是url(string类型)
> desc tb_url;

--结果：
+-----------+------------+----------+
| col_name  | data_type  | comment  |
+-----------+------------+----------+
| id        | int        |          |
| url       | string     |          |
+-----------+------------+----------+

--查看测试表的内容
> select * from tb_url;

--结果：
+------------+-------------------------------------------------+
| tb_url.id  |                   tb_url.url                    |
+------------+-------------------------------------------------+
| 1          | http://facebook.com/path/p1.php?query=1         |
| 2          | http://www.baidu.com/news/index.jsp?uuid=frank  |
| 3          | http://www.jd.com/index?source=baidu            |
+------------+-------------------------------------------------+

--提取tb_url的query部分
> select
> a.id as id,
> c.query as query
> from tb_url a
> lateral view parse_url_tuple(url,"QUERY") c as query
> ;

--结果：
+-----+---------------+
| id  |     query     |
+-----+---------------+
| 1   | query=1       |
| 2   | uuid=frank    |
| 3   | source=baidu  |
+-----+---------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

举例3：多个lateral view连用

--提取tb_url的HOST、PATH和QUERY部分
> select
> a.id as id,
> b.host as host,
> b.path as path,
> c.query as query
> from tb_url a
> lateral view parse_url_tuple(url,"HOST","PATH") b as host,path
> lateral view parse_url_tuple(url,"QUERY") c as query
> ;

--结果：
+-----+----------------+------------------+---------------+
| id  |      host      |       path       |     query     |
+-----+----------------+------------------+---------------+
| 1   | facebook.com   | /path/p1.php     | query=1       |
| 2   | www.baidu.com  | /news/index.jsp  | uuid=frank    |
| 3   | www.jd.com     | /index           | source=baidu  |
+-----+----------------+------------------+---------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

注意：

lateral view函数的位置在from后where条件前。
生成虚拟表的别名不可省略。
from后可带多个 lateral view函数。
如果要拆分的字段有null值，需要使用 lateral view outer替代，避免数据缺失。

二、explode()函数

功能：UDTF函数，可以将一个array或者map展开
语法：explode(map<string> ,array <string>)
- explode(array)：将array列表里的每个元素生成一行
- explode(map)：每一对元素作为一行，key为一列，value为一列

使用方式:

举例1：直接使用

--explode(array)
> select explode(array(11,22,33)) as item;

--结果：
+-------+
| item  |
+-------+
| 11    |
| 22    |
| 33    |
+-------+

--explode(map)
> select explode(map("id",11,"name","lily","age",18)) as (k,v);

--结果：
+-------+-------+
|   k   |   v   |
+-------+-------+
| id    | 11    |
| name  | lily  |
| age   | 18    |
+-------+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

举例2：与lateral view一起使用

语法：lateral view explode(字段) 表别名 as 列别名
如果字段类型是map：lateral view explode(字段) 表别名 as (key别名,value别名)

> select * from test_2;

--结果：
+------------+----------------------------------------+
| test_2.id  |           test_2.rule_array            |
+------------+----------------------------------------+
| a          | ["501","502","503","501","512"]        |
| b          | ["511","512","513","511","512","511"]  |
| c          | ["512","513","511","512"]              |
+------------+----------------------------------------+

> select id,rule
> from test_2 
> lateral view explode(rule_array) tbl as rule
> ;

--结果：
+-----+-------+
| id  | rule  |
+-----+-------+
| a   | 501   |
| a   | 502   |
| a   | 503   |
| a   | 501   |
| a   | 512   |
| b   | 511   |
| b   | 512   |
| b   | 513   |
| b   | 511   |
| b   | 512   |
| b   | 511   |
| c   | 512   |
| c   | 513   |
| c   | 511   |
| c   | 512   |
+-----+-------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

举例3：使用局限性

不能关联原有的表中其它字段。例如：

> select id, explode(rule_array) as rule from test_2;

--报错：
Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)
1
2
3
4

不能与group by、cluster by 、distribute by、sort by联用。例如：

> select explode(rule_array) as rule from test_2 group by explode(rule_array);

--报错：
Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)
1
2
3
4

不能进行UDTF嵌套。例如：

> select explode(explode(rule_array)) from test_2 ;

--报错：
Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)
1
2
3
4

不允许选择其他表达式。例如：

> select explode("1,2,3,4,5") from test_2 ; 

--报错：
Error: Error while compiling statement: FAILED: UDFArgumentException explode() takes an array or a map as a parameter (state=42000,code=40000)
1
2
3
4

三、posexplode()函数

功能：UDTF函数，将一个array或者map展开，可以将index（索引）和数据都取出来，使用两次posexplode()并用where语句使两次取到的index相等，从而实现对多列进行多行转换。explode()函数只能将对一列进行行转换。
语法：posexplode(map<string> ,array <string>)

使用方式:

举例1：

> desc test_2;

--结果：
+-------------+----------------+----------+
|  col_name   |   data_type    | comment  |
+-------------+----------------+----------+
| id          | string         |          |
| dt_array    | array<string>  |          |
| rule_array  | array<string>  |          |
+-------------+----------------+----------+

> select * from test_2;

--结果：
+------------+----------------------------------------------------+----------------------------------------+
| test_2.id  |                  test_2.dt_array                   |           test_2.rule_array            |
+------------+----------------------------------------------------+----------------------------------------+
| a          | ["20230809","20230811","20230812","20230812","20230813"] | ["501","502","503","501","512"]        |
| b          | ["20230809","20230811","20230812","20230812","20230813","20230809"] | ["511","512","513","511","512","511"]  |
| c          | ["20230811","20230812","20230812","20230813"]      | ["512","513","511","512"]              |
+------------+----------------------------------------------------+----------------------------------------+

--注：语句中b.idxb和c.idxc分别是子视图的两个索引，我们where子句中使用b.idxb=c.idxc保证顺序的一致性。
> select a.id,b.cur_day,c.rule 
> from test_2 a
> lateral view posexplode(dt_array) b as idxb,cur_day,
> lateral view posexplode(rule_array) c as idxc,rule where b.idxb=c.idxc
> ;

--结果：
+-------+------------+---------+
| a.id  | b.cur_day  | c.rule  |
+-------+------------+---------+
| a     | 20230809   | 501     |
| a     | 20230811   | 502     |
| a     | 20230812   | 503     |
| a     | 20230812   | 501     |
| a     | 20230813   | 512     |
| b     | 20230809   | 511     |
| b     | 20230811   | 512     |
| b     | 20230812   | 513     |
| b     | 20230812   | 511     |
| b     | 20230813   | 512     |
| b     | 20230809   | 511     |
| c     | 20230811   | 512     |
| c     | 20230812   | 513     |
| c     | 20230812   | 511     |
| c     | 20230813   | 512     |
+-------+------------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

四、行转列使用

单列转多行

举例1：使用explode()+lateral view

> select t.id,a.cur_day
> from test_2 t
> lateral view explode(t.dt_array) a as cur_day
> ;

--结果：
+-------+------------+
| t.id  | a.cur_day  |
+-------+------------+
| b     | 20230809   |
| b     | 20230811   |
| b     | 20230812   |
| b     | 20230812   |
| b     | 20230813   |
| b     | 20230809   |
| c     | 20230811   |
| c     | 20230812   |
| c     | 20230812   |
| c     | 20230813   |
| a     | 20230809   |
| a     | 20230811   |
| a     | 20230812   |
| a     | 20230812   |
| a     | 20230813   |
+-------+------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

举例2：explode字段是string格式，先split()指定分隔符，如果省略split()则使用默认分隔符是英文逗号。

> desc test_3;

--结果：
+-----------+------------+----------+
| col_name  | data_type  | comment  |
+-----------+------------+----------+
| id        | string     |          |
| dt        | string     |          |
| rule1     | string     |          |
+-----------+------------+----------+

> select * from test_3;

--结果：
+------------+----------------------------------------------------+--------------------------+
| test_3.id  |                     test_3.dt                      |       test_3.rule1       |
+------------+----------------------------------------------------+--------------------------+
| a          | 20230809,20230811,20230812,20230812,20230813       | 501,502,503,501,512      |
| b          | 20230809,20230811,20230812,20230812,20230813,20230809 | 511,512,513,511,512,511  |
| c          | 20230811,20230812,20230812,20230813                | 512,513,511,512          |
+------------+----------------------------------------------------+--------------------------+

> select t.id,a.cur_day
> from test_3 t
> lateral view explode(split(t.dt,',')) a as cur_day
> ;

--结果：
+-------+------------+
| t.id  | a.cur_day  |
+-------+------------+
| a     | 20230809   |
| a     | 20230811   |
| a     | 20230812   |
| a     | 20230812   |
| a     | 20230813   |
| b     | 20230809   |
| b     | 20230811   |
| b     | 20230812   |
| b     | 20230812   |
| b     | 20230813   |
| b     | 20230809   |
| c     | 20230811   |
| c     | 20230812   |
| c     | 20230812   |
| c     | 20230813   |
+-------+------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

多列转多行

举例1：使用posexplode()+lateral view

先测试使用explode(), 看看效果:

> select  a.id,b.cur_day,c.rule 
> from test_2 a
> lateral view explode(dt_array) b as cur_day,
> lateral view explode(rule_array) c as rule
> ;

--结果：
+-------+------------+---------+
| a.id  | b.cur_day  | c.rule  |
+-------+------------+---------+
| b     | 20230809   | 511     |
| b     | 20230809   | 512     |
| b     | 20230809   | 513     |
| b     | 20230809   | 511     |
| b     | 20230809   | 512     |
| b     | 20230809   | 511     |
| b     | 20230811   | 511     |
| b     | 20230811   | 512     |
| b     | 20230811   | 513     |
| b     | 20230811   | 511     |
| b     | 20230811   | 512     |
| b     | 20230811   | 511     |
| b     | 20230812   | 511     |
| b     | 20230812   | 512     |
| b     | 20230812   | 513     |
| b     | 20230812   | 511     |
| b     | 20230812   | 512     |
| b     | 20230812   | 511     |
| b     | 20230812   | 511     |
| b     | 20230812   | 512     |
| b     | 20230812   | 513     |
| b     | 20230812   | 511     |
| b     | 20230812   | 512     |
| b     | 20230812   | 511     |
| b     | 20230813   | 511     |
| b     | 20230813   | 512     |
| b     | 20230813   | 513     |
| b     | 20230813   | 511     |
| b     | 20230813   | 512     |
| b     | 20230813   | 511     |
| b     | 20230809   | 511     |
| b     | 20230809   | 512     |
| b     | 20230809   | 513     |
| b     | 20230809   | 511     |
| b     | 20230809   | 512     |
| b     | 20230809   | 511     |
| c     | 20230811   | 512     |
| c     | 20230811   | 513     |
| c     | 20230811   | 511     |
| c     | 20230811   | 512     |
| c     | 20230812   | 512     |
| c     | 20230812   | 513     |
| c     | 20230812   | 511     |
| c     | 20230812   | 512     |
| c     | 20230812   | 512     |
| c     | 20230812   | 513     |
| c     | 20230812   | 511     |
| c     | 20230812   | 512     |
| c     | 20230813   | 512     |
| c     | 20230813   | 513     |
| c     | 20230813   | 511     |
| c     | 20230813   | 512     |
| a     | 20230809   | 501     |
| a     | 20230809   | 502     |
| a     | 20230809   | 503     |
| a     | 20230809   | 501     |
| a     | 20230809   | 512     |
| a     | 20230811   | 501     |
| a     | 20230811   | 502     |
| a     | 20230811   | 503     |
| a     | 20230811   | 501     |
| a     | 20230811   | 512     |
| a     | 20230812   | 501     |
| a     | 20230812   | 502     |
| a     | 20230812   | 503     |
| a     | 20230812   | 501     |
| a     | 20230812   | 512     |
| a     | 20230812   | 501     |
| a     | 20230812   | 502     |
| a     | 20230812   | 503     |
| a     | 20230812   | 501     |
| a     | 20230812   | 512     |
| a     | 20230813   | 501     |
| a     | 20230813   | 502     |
| a     | 20230813   | 503     |
| a     | 20230813   | 501     |
| a     | 20230813   | 512     |
+-------+------------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

出现这种情况，是因为两个并列的explode()的hql没办法识别cur_day对应的rule是什么，对于多个数组的行转列可以使用posexplode()函数。
例如使用如下查询语句：

> select a.id,b.cur_day,c.rule 
> from test_2 a
> lateral view posexplode(dt_array) b as idxb,cur_day,
> lateral view posexplode(rule_array) c as idxc,rule where b.idxb=c.idxc
> ;

--结果：
+-------+------------+---------+
| a.id  | b.cur_day  | c.rule  |
+-------+------------+---------+
| b     | 20230809   | 511     |
| b     | 20230811   | 512     |
| b     | 20230812   | 513     |
| b     | 20230812   | 511     |
| b     | 20230813   | 512     |
| b     | 20230809   | 511     |
| c     | 20230811   | 512     |
| c     | 20230812   | 513     |
| c     | 20230812   | 511     |
| c     | 20230813   | 512     |
| a     | 20230809   | 501     |
| a     | 20230811   | 502     |
| a     | 20230812   | 503     |
| a     | 20230812   | 501     |
| a     | 20230813   | 512     |
+-------+------------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/655720