[Hive]表生成函数(UDTF)使用指南 |
您所在的位置:网站首页 › 动态数据表格生成函数 › [Hive]表生成函数(UDTF)使用指南 |
UDTF是User-Defined Table-Generating Functions 的缩写,即用户定义的表生成函数。UDTF 用于从原始表中的一行生成多行数据。典型的 UDTF有EXPLODE、posexplode等函数,它能将array或者map展开。 表生成函数和聚合函数是相反的,表生成函数可以把单列扩展到多列。表生成函数:可以理解为一个函数可以生成一个表。 1、explode函数-行转列explode函数以array类型数据输入,然后对数组中的数据进行迭代,返回多行结果,一行一个数组元素值。 ARRAY函数是将一列输入转换成一个数组输出。 1.1 explode函数语法 返回类型函数名描述Texplode(ARRAY a)可以返回0到多行的结果,每行对应的是array数组中的一个元素。现在使用explode函数: hive (default)> SELECT explode(array(1,2,3)) AS num; OK num 1 2 3 Time taken: 0.327 seconds, Fetched: 3 row(s)SQL说明: array 函数是将一列输入转换成一个数组输出;num 是给转换成的列命名一个新的名字,用于代表转换之后的列名。 1.2 explode(用于map类型数据)由于map是key-value结构的,所以它在转换的时候会转换成两列,一列是key转换而成的,一列是value转换而成的。 select explode(mapcol) as (key,value) from tablename;SQL说明: explode():函数中的参数传入的是map数据类型的列名。由于map是kay-value结构的,所以它在转换的时候会转换成两列,一列是key转换而成的,一列是value转换而成的。key:表示key转换成的列名称,用于代表key转换之后的列名。value:表示value转换成的列名称,用于代表value转换之后的列名称。注意:这两个值需要在as之后用括号括起来然后以逗号分隔。 2、posexplode函数posexplode与explode类似,不同的是还返回各元素在数组中的位置。 返回类型函数名描述Tposexplode (array a)可以返回0到多行的结果,每行对应的是array数组中的一个元素。具体的posexplode函数使用方法: select posexplode(array('A','B','C')) as (pos,val);SQL说明: posexplode():函数中的参数传入的是array数据类型的列名。pos:表示数组中的位置转换成的列名。val:表示元素转换后对应的列名。 执行结果如下所示: posval0A1B2C 3、表生成函数的局限性UDTF 有一个限制,它出现在 SELECT 子句中的时候,不能与其它列共同出现。例如,下面这段SQL是会报错的。 SELECT pageid, EXPLODE(adid_list) AS adid FROM pageAds;此外,使用表生成函数的局限性还表现如下方面: 1)不能关联原有的表中的其他字段。 2)不能与group by、cluster by、distribute by、sort by联用。 3)不能进行UDTF嵌套。 4)不允许选择其他表达式。 为了解决这个UDTF问题,我们就需要引入 LATERAL VIEW。 4、Lateral ViewLateral View一般与用户自定义表生成函数(如explode())结合使用。 如表生成函数中所述,UDTF为每个输入行生成零个或多个输出行。 Lateral View 首先将UDTF应用于基表的每一行,然后将结果输出行连接到输入行,以形成具有提供的表别名的虚拟表。 lateral view是Hive中提供给UDTF的结合,它可以解决UDTF不能添加额外的select列的问题。LATERAL VIEW将explode生成的结果当做一个视图来处理。 Lateral View其实就是用来和像类似explode这种UDTF函数联用的,Lateral View会将UDTF生成的结果放到一个虚拟表中,然后这个虚拟表会和输入行进行inner join来达到连接UDTF外的select字段的目的。而如果要保留输出为零的行,则需使用 LATERAL VIEW OUTER 执行 OUTER JOIN。 4.1 Lateral View语法格式 select col1 [,col2,col3……] from 表名 lateral view udtf(expression) 虚拟表别名 as col1 [,col2,col3……]SQL语句说明: 在from子句中使用,一般和格式一搭配使用,这个格式只是说明了lateral view的使用位置。from子句后面也可以跟多个lateral view语句,使用空格间隔就可以了。lateral view跟在from后面,其后面是要使用的UDTF,为生成的虚拟表起一个表别名,不写会报错。然后跟as 列别名,有些UDTF会产生多个列,所以有时要跟多个列别名,不过列的别名可以省略,在实际使用中最好指定别名。 4.2 outer关键字的作用用户可以指定outer关键字来生成行,即使LATERAL VIEW通常不会生成行。当所使用的UDTF不产生任何行时(使用explode()函数时,explode的列为空时,很容易发生上述这种情况)。 在这种情况下,源数据行不会出现在结果中。 select col1 [,col2,col3……] from ( select explode(array('A', 'B')) as col ) lateral view explode(array()) tf as explode_colSQL执行的结果是空,如果想让源数据行继续出现在结果中,可以使用 OUTER可以用来阻止关键字,并且来自UDTF的列使用NULL值代替。 select col1 [,col2,col3……] from ( select explode(array('A', 'B')) as col ) lateral view outer explode(array()) tf as explode_col添加outer关键字之后,SQL的执行结果如下所示: colexplode_colAnullBnull这个outer的作用是在UDTF转换列的时候将其中的空也给展示出来,UDTF默认是忽略输出空的,加上outer之后,会将空也输出,显示为NULL。这个功能是在Hive0.12是开始支持的。 4.3 多个Lateral View outer 语句联合使用FROM子句可以有多个LATERAL VIEW子句。 后面的LATERAL VIEWS子句可以引用出现在LATERAL VIEWS左侧表的任何列。 Array col1Array col2[1, 2][a", “b”, “c”][3, 4][d", “e”, “f”]例如,如下查询: SELECT * FROM exampleTable LATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(col2) myTable2 AS myCol2;LATERAL VIEW子句按照它们出现的顺序应用。 下面我们对上述SQL语句进行拆解: (1)应用第一个LATERAL VIEW子句的结果 SELECT * FROM exampleTable LATERAL VIEW explode(col1) myTable1 AS myCol1 mycol1col21[a", “b”, “c”]1[a", “b”, “c”]3[d", “e”, “f”]4[d", “e”, “f”](2)应用第二个LATERAL VIEW子句的结果 SELECT * FROM exampleTable LATERAL VIEW explode(col1) myTable1 AS myCol1 LATERAL VIEW explode(col2) myTable2 AS myCol2;SQL的执行结果: 使用lateral view explode的结合使用,关键点在于构造一个array数组。传入explode函数的可以是自定义的数组,也可以是from表中的数组字段。 LATERAL VIEW可以让我们像使用普通的表一样使用表生成函数,也就是说我们可以基于表生成函数的结果使用select、joins以及更多的操作。 例如, select tf1.*, tf2.* from (select 0) t lateral view explode(map('A',10,'B',20,'C',30)) tf1 lateral view explode(map('A',10,'B',20,'C',30)) tf2 where tf1.key = tf2.key;SQL的执行结果如下所示: 以及其他的一些表操作,在Lateral View中都可以使用。 参考链接: Built-in Table-Generating Functions (UDTF) LanguageManual LateralView Hive Explode / Lateral View clarification |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |