【hive】行转列—explode()/posexplode()/lateral view 函数使用场景

最新推荐文章于 2024-06-21 16:47:31 发布
最新推荐文章于 2024-06-21 16:47:31 发布
阅读量2.8k 收藏 14
点赞数 2
分类专栏： hive 文章标签： hive hadoop 数据仓库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sodaloveer/article/details/132837340
版权

文章目录

一、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           |
+-----+---------------+

举例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  |
+-----+---------------+

举例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  |
+-----+----------------+------------------+---------------+

注意：

  • 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    |
+-------+-------+

举例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   |
+-----+-------+

举例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)
  • 不能与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)
  • 不能进行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)
  • 不允许选择其他表达式。例如：
> 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)

三、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：使用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   |
+-------+------------+

举例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：使用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     |
+-------+------------+---------+

出现这种情况，是因为两个并列的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     |
+-------+------------+---------+
sodaloveer
关注
  • 2
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
Hive SQL中的列转行（lateral view与explode）、行转列
weixin_49709859的博客
01-30 5750
列转行、行转列的灵活运用
Hive中的explode函数、posexplode函数与later view函数
weixin_44852067的博客
03-15 3209
在离线数仓处理通过HQL业务数据时，经常会遇到行转列或者列转行之类的操作，就像concat_ws之类的函数被广泛使用，今天这个也是经常要使用的拓展方法。
【Hive SQL】Hive Sql 列转行（lateral view 与 explode 、posexplode）详解
最新发布
qq_34446614的博客
06-21 531
将hive某列一行中复杂的 array 或 map 结构拆分成多行（只能输入array或map）。通常，explode函数会与lateral view一起结合使用;
Hive SQL中的lateral view explode
热门推荐
数据森麟
03-31 3万+
公众号后台回复“图书“，了解更多号主新书内容 作者：胖里 来源：胖里的日常 标题中直接写lateral view explode是我鲁莽了，毕竟这俩不是法定cp，也并不是不能分开各自生活。...
Hive SQL 中ARRAY或MAP类型数据处理：lateral view explode()/posexplode()——行转列函数
Zheng_Tito的博客
01-19 6044
在对表数据进行批量处理过程中，常常碰上某个字段是一个array或者map形式的字段，一列数据的该字段信息同时存在多个值，当我们需要取出该数组中的每一个值实现一一对应关系的时候，可以考虑使用lateral view explode()/posexplode() 进行处理。
hive 炸裂函数的使用
weixin_45621200的博客
01-12 5489
hive中，炸裂函数有两个explode 和posexplode 都是可以把数组类型或map类型的字段拆分成多行，属于udtf函数。 也可以配合split函数使用。 lateral view侧写函数 只要是想查询除exlpode之外的字段就要使用侧写函数lateral view； posexplode 是在explode 的基础上多生成一列索引列，索引从0开始， 用法如下： select dept_id,name,idx,col from sys_dept lateral view posexplode(s
LATERAL VIEW EXPLODE函数详解及应用
Nice的博客
10-31 8080
在进行统计分析的时候有时候会有类似这样的需求 比如求某个平台某一天所有的订单总和，或者淘宝所有pc 端的交易总和，这个时候我们可以基于原本基础的数据进行炸裂处理之后得出结结果值，方便后续进行查询，这种提前进行预聚合的思想长用于即席查询分析的场景中，比如为了适用于某张报表的多条件查询可以采用此种方式结合预聚合的方式进行操作。
HiveSQL题——炸裂函数(explode/posexplode)
爱吃辣条的博客
01-31 5119
HiveSQL题——炸裂函数(explode/posexplode)
python 实现 hive中类似 lateral view explode的功能示例
12-17
在处理分隔符分隔的数组数据时，Hive 提供了一种称为 `lateral view` 的机制，配合 `explode` 函数可以将单一记录中的数组元素拆分成多条记录。本文主要探讨如何在 Python 中模拟实现 Hive 的 `lateral view explode...
09.hive内置函数--表生成函数--行转列explode--lateral-view.mp4
04-29
09.hive内置函数--表生成函数--行转列explode--lateral-view.mp4
hive列转行案例
01-07
解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此 基础上可以对拆分后的数据进行聚合。 2．数据准备 move_name category 《疑犯追踪》 悬疑,动作,科幻,剧情 《Lie to me...
源码复盘 你真的了解Lateral View explode吗.docx
11-12
在 Hive 中，有八种 UDTF 函数，包括 explode、posexplode、inline、json_tuple、lateral_view_explode、collect_list、collect_set 和 aggregate_udtf。其中，explode 函数是最常用的 UDTF 函数之一。 Lateral ...
大数据学习：Hive函数.pdf
12-07
Hive函数学习指南 Hive是一个基于Hadoop的数据仓库工具，用于存储、...Hive也支持用户自定义函数，可以根据实际使用场景编写函数，如UDF、UDTF和UDAF。用户可以使用Java语言实现自定义函数，然后在Hive中注册和使用。
hive explode 使用
weixin_40809627的博客
02-23 899
hive explode 使用和行转列的两种写法
Hivesql中的explode()与posexplode()函数，夹带lateral view()
啊帅和和的博客
01-10 2249
目录later viewexplode()与posexplode() later view lateral view的语法： lateralView: LATERAL VIEW udtf(expression) tableAlias AS columnAlias (’,’ columnAlias) fromClause: FROM baseTable (lateralView) 描述： lateral view用于和split、explode等UDTF一起使用的，能将一行数据拆分成多行数据，在此基础上可以对
hive explode
遥望......
09-05 9599
1、hive explode函数可以将一个array或者map展开，其中explode(array)使得结果中将array列表里的每个元素生成一行；explode(map)使得结果中将map里的每一对元素作为一行，key为一列，value为一列，一般情况下，直接使用即可，但是遇到以下情况时需要结合lateral view 使用。 1、No other expressions are allowe...
Hive sql 行列转换(行转列，列转行)
m0_67402026的博客
07-31 368
是concat的特殊形式，第一个参数是分隔符，分隔符会放到要连接的字符串之间,分隔符可以是字符串，也可以是其他参数。lateralview首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateralview再把结果组合，产生一个支持别名表的虚拟表。select分类字段,concat_ws(’,’,collect_set(合并字段))as别名fromtable_namegroup分类字段;例concat_ws(‘,’,[‘a‘,‘b’])–>‘a,b’...
Hive炸裂函数explode的使用
FlatTiger的博客
02-20 8573
explode的作用 将一行集合类型的数据炸裂为多行数据。 格式 lateral view explode(字段) 表别名 as 列别名 如果字段类型是map：lateral view explode(字段) 表别名 as key别名,value别名 示例 有如下表数据 +-------------------+----------------------+ | movie_info.movie | movie_info.category | +-------------------+---------
lateral view explode函数是如何使用的
05-31
lateral view explode函数是Hive中用于将一个数组列拆分成多行的函数。在使用时，需要使用lateral view将数组列展开，再使用explode函数将数组列拆分成多行。 举个例子，假设有一张表mytable，其中包含一个名为col1的数组列，其值为[1,2,3]。如果想将该数组列展开成3行，可以使用以下语句： ``` SELECT col1_exploded FROM mytable LATERAL VIEW explode(col1) exploded_table AS col1_exploded; ``` 在执行上述语句后，查询结果将展示3行数据，分别为1、2和3。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值