Hive中explode和lateral view函数使用_laterval

Hive中的三种自定义函数类型

• UDF：用户自定义标量函数(User Defined Function)，使用场景是一进一出（一个数据项一个输出项）。
• UDAF：用户自定义聚合函数(User Defined Aggregation Function)，使用场景是多进一出（多个数据项返回一个结果），常见的是聚合函数
• UDTF：用户自定义表格函数(User Defined Table Function)，使用场景是一进多出（一个输入项转换为多个输出项），lateral和view explode可以实现类似效果

常见内置函数分类

Hive内置了不少函数，使用show functions命令可以查看当下版本支持的函数，使用describe function extended funcname可以查看函数的使用方法。

• UDF函数

   upper    字符串小写转大写
   lower    字符串大写转小写
   length    字符串长度函数
   concat,concat_ws    字符串连接
   substr    字符串截取
   regexp_extract    正则表达式解析
   day    日期转天
   weekofyear    日期转周
   cast    数据类型转换
   mask_hash    数据脱敏
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

• UDAF函数

   avg    计算平均数
   sum    求和
   count    计数
   max    求最大值
   min    求最小值
  1
  2
  3
  4
  5

• UDTF函数

   explode    将一个数组或Map类型的列拆分成多行（一行产生多行）
   lateral view     与explode函数一起使用，一行产生多行进行统计分析
   transpose    多列转换为行
   json_tuple    解析json格式数据
  1
  2
  3
  4

UDAF函数使用

explode函数使用

explode函数支持输入数组或字典类型的参数，将一行复杂数据拆分为多行，如果需要分割string，可以和split函数嵌套使用。

• 数组类型参数
  select explode(array(1,2,3,4))
  

• 与split函数结合使用
  select explode(split('A,B,C,D',',')) as letter
  

• Map类型参数
  select explode(map('A','1','B','2','C','3','D','4')) as (key_name,value_name)
  

lateral view函数使用

lateral view一般与explode等UDTF函数使用，首先将UDTF函数应用到每一行上，每一行得到多行输出，这些输出组建成一张虚拟表，然后这张虚拟表会跟当前表进行join操作，join完成之后会得出一张结果虚拟表。
语法格式：
LATERAL VIEW udtf (expression) tableAlias[虚拟表别名] AS coluumAlias[产生列别名] ( ',' , columnAlias)

• 产生多行

  select letter,tmp_id
  from(
  select 'A' letter,array(1,2,3) numbers
  union all
  select 'B' letter,array(1,2,3) numbers
  )t
  lateral view explode(numbers) tmp_table as tmp_id
  1
  2
  3
  4
  5
  6
  7

  结果如下：
  

• 分组统计

  select tmp_id,count(1)
  from(
  select 'A' letter,array(1,2,3) numbers
  union all
  select 'B' letter,array(1,2,3) numbers
  )t
  lateral view explode(numbers) tmp_table as tmp_id
  group by tmp_id
  1
  2
  3
  4
  5
  6
  7
  8

  结果如下：
  

• 一行多个字段转换为多行一个字段

-- 直接展开多行
select 
	flow_no,
	tmp_id
from(
select 
	flow_no,
	array(<字段1>,<字段2>,<字段3>) as tmp
from
	tableName
)t
lateral view explode(tmp) tmp_table as tmp_id

-- 展开多行并添加字段标记每行记录的关系或类型
select
	flow_no,
	tmp_id,
	tmp_value
from(
select
	flow_no,
	map('字段1名称',<字段1>,'字段2名称',<字段2>,'字段3名称',<字段3>) as tmp
from
	tableName
)t 
lateral view explode(tmp) tmp_table as tmp_id,tmp_value
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