本文主要是介绍【Hive SQL】Hive Sql 列转行（lateral view 与 explode 、posexplode）详解，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Hive Sql
Hive Sql 列转行（lateral view 与 explode 、posexplode）详解
explode
描述
将hive某列一行中复杂的 array 或 map 结构拆分成多行（只能输入array或map）。
通常，explode函数会与lateral view一起结合使用;
语法
explode(col)
select explode(arraycol) as newcol from tablename;// explode()：函数中的参数传入的是arrary数据类型的列名;
// newcol：是给转换成的列命名一个新的名字，用于代表转换之后的列名;
// ablename：原表名;select explode(mapcol) as (keyname,valuename) from tablename;// map是kay-value结构的，所以它在转换的时候会转换成两列，一列是kay转换而成的，一列是value转换而成的。
// keyname：表示key转换成的列名称，用于代表key转换之后的列名。
// valuename：表示value转换成的列名称，用于代表value转换之后的列名称。
// 注意：这两个值需要在as之后用括号括起来然后以逗号分隔。
示例
// explode(array) 使得结果中将array列表里的每个元素生成一行;
select explode(array(1,2,3,4)) as col;
"""+------+| col |+------+| 1 || 2 || 3 || 4 |+------+
"""
// explode(map)使得结果中将map里的每一对元素作为一行，key为一列，value为一列;
select explode(map('a',1,'b',2)) as (k,v);
"""+-----+-----+| k | v |+-----+-----+| a | 1 || b | 2 |+-----+-----+
"""
posexplode
描述
对一列进行炸裂可以使用 explode()函数，但是如果想实现对两列都进行多行转换，那么用explode()函数就不能实现了，可以用posexplode()函数，因为该函数可以将index和数据都取出来，使用两次posexplode并令两次取到的index相等就行了。
语法
posexplode(col)
select posexplode(arraycol) as (pos,val) from tablename;// posexplode()：函数中的参数传入的是arrary数据类型的列名;
// pos：是给转换成的数组列中的数组下标 index
// val: 是给转换成的列命名一个新的名字，用于代表转换之后的列名;
// ablename：原表名;
示例
select posexplode(array('a','b','c','d'));
"""+------+------+| pos | val |+------+------+| 0 | a || 1 | b || 2 | c || 3 | d |+------+------+
"""Lateral view
描述
lateral view与用户定义的表生成函数（如explode（））一起使用。如内置表生成函数中所述，UDTF为每个输入行生成零个或多个输出行。Lateral view首先将UDTF应用于基表的每一行，然后将结果输出行与输入行连接，以形成具有所提供的表别名的虚拟表。
lateral view 主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，
不能包含其它字段以及多个UDTF的情况（不能添加额外的select列的问题）;
一个 from 子句可以有多个 lateral view 子句。后续的 lateral views 可以引用出现在 lateral view 左侧的任何表格中的列。
语法
// 方式一
lateral view udtf(expression) tableAlias as columnAlias (,columnAlias)*// lateral view在UDTF前使用，表示连接UDTF所分裂的字段;
// UDTF(expression)：使用的UDTF函数，例如explode();
// tableAlias：表示UDTF函数转换的虚拟表的名称;
// columnAlias：
// 表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可;
// 如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开;
// 从 Hive 0.12.0 开始，可以省略列别名;// 方式2
lateral view outer udtf(expression) tableAlias as columnAlias (,columnAlias)*// outer : udtf 函数中指定的列值为 null 时,添加 outer 结果会显示包含 null 的这一行数据,否则会过滤掉此行数据;
// 为了避免 当udtf 没有得到任何结果时最终虚拟结果表里丢失原数据行的问题。
// 由于later view 的工作原理是将原表与 udtf 产生的虚拟表做 inner join 操作，所以如果 udtf 不产生任何结果时，那么对应原表的那一行也会在 inner join 操作后消失。
// outer关键字就是来解决这个问题的，加上这个关键字之后执行的就是 outer join 操作了，因此原表数据会被完全保留下来。// 注：
// 1）lateral view的位置是from后where条件前
// 2）生成的虚拟表的表名不可省略
// 3）from后可带多个lateral view,之间用空格分隔;
// 3）如果要拆分的字段有null值，需要使用lateral view outer 替代，避免数据缺失;
示例
// lateral view
select *
from (select "Jane" as name,array(88,99,110) as score union all select "Sherry" as name,array(88,99,null) as score union all select "Abel" as name,null as score) A
lateral view explode(score) tmp as ss
"""+-----------+-------------------+-------+| name | score | ss |+-----------+-------------------+-------+| Jane | [88, 99, 110] | 88 || Jane | [88, 99, 110] | 99 || Jane | [88, 99, 110] | 110 || Sherry | [88, 99, NULL] | 88 || Sherry | [88, 99, NULL] | 99 || Sherry | [88, 99, NULL] | \N |+-----------+-------------------+-------+// lateral view outer
select *
from (select "Jane" as name,array(88,99,110) as score union all select "Sherry" as name,array(88,99,null) as score union all select "Abel" as name,null as score) A
lateral view outer explode(score) tmp as ss+-----------+-------------------+-------+| name | score | ss |+-----------+-------------------+-------+| Jane | [88, 99, 110] | 88 || Jane | [88, 99, 110] | 99 || Jane | [88, 99, 110] | 110 || Sherry | [88, 99, NULL] | 88 || Sherry | [88, 99, NULL] | 99 || Sherry | [88, 99, NULL] | \N || Abel | \N | \N |+-----------+-------------------+-------+
"""// from 后跟多个 lateral view子句select myCol1,myCol2
from (select array(1, 2) as col1,array("a", "b", "c") as col2 union all select array(3, 4) as col1,array("d", "e", "f") as col2) A
lateral view explode(col1) myTable1 as myCol1
lateral view explode(col2) myTable2 as myCol2;+---------+---------+| myCol1 | myCol2 |+---------+---------+| 1 | a || 1 | b || 1 | c || 2 | a || 2 | b || 2 | c || 3 | d || 3 | e || 3 | f || 4 | d || 4 | e || 4 | f |+---------+---------+
官方文档 :
1、Lateral View
2、Hive UDFs
这篇关于【Hive SQL】Hive Sql 列转行（lateral view 与 explode 、posexplode）详解的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
