Hive常用函数——UDF、UDAF、UDTF、Lateral view

【Hive】Hive常用函数

Hive的函数分为三类，分别是UDF、UDAF、UDTF，简单区分这三类函数就是

UDF是单行输入，单行输出；UDAF是多行输入，单行输出；UDTF是单行输入，多行或多列输出。

另：所有的hive函数，都可以使用desc function 函数名 查看使用方法，若想查看更详细的使用方法，则可以使用desc function extended 函数名

UDF

字符串类

返回值类型	用法	描述
string	concat(string|binary A, string|binary B…)	拼接多个字符串
string	concat_ws(string SEP, string A, string B…)	拼接多个字符串并按指定分隔符拼接
string	concat_ws(string SEP, array)	另一种用法，可以直接拼接数组的元素 (as of Hive 0.9.0)
string	decode(binary bin, string charset)	从 (one of ‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’). 中指定解码格式，解码二进制 (As of Hive 0.12.0.)
string	elt(N int,str1 string,str2 string,str3 string,…)	示例：elt(2,‘hello’,‘world’) 返回 ‘world’.
binary	encode(string src, string charset)	从 (one of ‘US-ASCII’, ‘ISO-8859-1’, ‘UTF-8’, ‘UTF-16BE’, ‘UTF-16LE’, ‘UTF-16’). 指定编码格式，编码字符串. (As of Hive 0.12.0.)
int	field(val T,val1 T,val2 T,val3 T,…)	示例： field(‘world’,‘say’,‘hello’,‘world’) 返回 3. 否则返回 0
int	find_in_set(string str, string strList)	示例：find_in_set(‘ab’, ‘abc,b,ab,c,def’) 返回 3，参数是null返回null，没找到返回 0
string	get_json_object(string json_string, string path)	示例：get_json_object(‘{“name”:“zs”,“id”:“2”}’,‘$.i{d}^{\prime })返回2<br/>ge{t}_{j}so{n}_{o}bject{(}^{\prime }{"name":"zs","id":"2"}^{\prime }{,}^{\prime }$.name’) 返回 zs
int	instr(string str, string substr)	示例：instr(‘substr’, ‘str’) 返回 4， instr(‘substr’, ‘astr’) 返回 0 参数带null返回null
int	length(string A)	返回字符串长度
string	lower(string A) lcase(string A)	字符串转小写，示例：lower(‘fOoBaR’) 返回 ‘foobar’.
string	lpad(string str, int len, string pad)	字符串左补齐，示例：lpad(‘str’, 5, ‘0’) 返回 00str lpad(‘str’, 2, ‘0’) 返回 st 参数带null返回null
string	ltrim(string A)	字符串去空格 ，示例：ltrim(’ foobar ') 返回 'foobar '.
string	regexp_extract(string subject, string pattern, int index)	字符串则模板提取，示例：regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) 返回 bar
string	regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	字符串正则替换，示例：regexp_replace(“foobar”, “oo|ar”, “”) 返回 ‘fb.’
string	repeat(string str, int n)	重复字符串，示例：repeat(‘str’, 2) 返回 strstr
string	replace(string A, string OLD, string NEW)	字符串替换，示例：replace(“ababab”, “abab”, “Z”)返回 “Zab”.
string	reverse(string A)	字符串翻转
string	rpad(string str, int len, string pad)	字符串右补齐
string	rtrim(string A)	字符串去空格 示例：rtrim(’ foobar ‘) results in ’ foobar’.
string	space(int n)	返回一个 n 个空格的字符串
array	split(string str, string pat)	指定分隔符分割字符串，返回数组
map<string,string>	str_to_map(text[, delimiter1, delimiter2])	指定两个分隔符分割字符串，返回map，默认元素分隔符 ‘,’ 默认键值分隔符 ‘:’
string	substr(string|binary A, int start) substring(string|binary A, int start)	字符串截取 示例：substr(‘foobar’, 4) 返回 ‘bar’
string	substr(string|binary A, int start, int len) substring(string|binary A, int start, int len)	字符串截取，另一种用法 示例：substr(‘foobar’, 4, 1) 返回 ‘b’
string	substring_index(string A, string delim, int count)	字符串截取，示例： substring_index(‘www.apache.org’, ‘.’, 2) 返回’www.apache’.
string	translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)	字符串替换，可做到字符级别，示例：translate(‘abcdeab’, ‘abc’, ‘olcc’) 返回 olcdeol
string	trim(string A)	字符串去空格 示例：trim(’ foobar ') 返回 ‘foobar’
string	upper(string A) ucase(string A)	字符串转大写 示例 upper(‘fOoBaR’) 返回 ‘FOOBAR’.

日期类

返回值类型	用法	描述
string	from_unixtime(bigint unixtime[, string format])	Unix时间戳（秒）转格式化日期
bigint	unix_timestamp(string date)	格式为uuu MM-dd HH:MM:ss的时间字符串转换为Unix时间戳（秒）
bigint	unix_timestamp(string date, string pattern)	指定时间格式转换为Unix时间戳（秒）
pre 2.1.0: string2.1.0 on: date	to_date(string timestamp)	返回字符串的日期 示例： to_date(“1970-01-01 00:00:00”) = “1970-01-01”.
int	year(string date)	返回年份，示例：year(“1970-01-01 00:00:00”) = 1970, year(“1970-01-01”) = 1970.
int	quarter(date/timestamp/string)	返回季度，示例：quarter(‘2015-04-08’) = 2. (as of Hive 1.3.0).
int	month(string date)	返回月份，示例：month(“1970-11-01 00:00:00”) = 11, month(“1970-11-01”) = 11.
int	day(string date) dayofmonth(date)	返回日期，示例： day(“1970-11-01 00:00:00”) = 1, day(“1970-11-01”) = 1.
int	hour(string date)	返回小时，示例：hour(‘2009-07-30 12:58:59’) = 12, hour(‘12:58:59’) = 12.
int	minute(string date)	返回分钟
int	second(string date)	返回秒数
int	weekofyear(string date)	返回日期在对应年份的周数，示例： weekofyear(“1970-11-01 00:00:00”) = 44, weekofyear(“1970-11-01”) = 44.
int	extract(field FROM source)	支持的第三个参数 date格式, timestamp格式, interval 或 一个date或timestamp的格式字符串. 支持的第一个参数: day, dayofweek, hour, minute, month, quarter, second, week and year. 示例：select extract(month from “2016-10-20”) 返回 10. select extract(hour from “2016-10-20 05:06:07”)返回 5. select extract(dayofweek from “2016-10-20 05:06:07”) 返回 5. select extract(month from interval ‘1-3’ year to month) 返回 3. select extract(minute from interval ‘3 12:20:30’ day to second) 返回 20.
int	datediff(string enddate, string startdate)	计算日期差值，示例： datediff(‘2009-03-01’, ‘2009-02-27’) = 2.
pre 2.1.0: string2.1.0 on: date	date_add(date/timestamp/string startdate, tinyint/smallint/int days)	日期增加天数，示例： date_add(‘2008-12-31’, 1) = ‘2009-01-01’.Prior to Hive 2.1.0 (HIVE-13248)
pre 2.1.0: string2.1.0 on: date	date_sub(date/timestamp/string startdate, tinyint/smallint/int days)	日期减少天数，示例： date_sub(‘2008-12-31’, 1) = ‘2008-12-30’.Prior to Hive 2.1.0 (HIVE-13248)
timestamp	from_utc_timestamp({any primitive type} ts, string timezone)	将UTC时间戳转换为指定时区的timestamp格式 (as of Hive 0.8.0).示例： from_utc_timestamp(2592000.0,‘PST’), from_utc_timestamp(2592000000,‘PST’) and from_utc_timestamp(timestamp ‘1970-01-30 16:00:00’,‘PST’) 返回 timestamp 1970-01-30 08:00:00.
date	current_date	返回当前的日期 (as of Hive 1.2.0)
timestamp	current_timestamp	返回当前的时间 (as of Hive 1.2.0)
string	add_months(string start_date, int num_months, output_date_format)	日期增加月数，示例：add_months(‘2009-08-31’, 1) 返回 ‘2009-09-30’. add_months(‘2017-12-31 14:15:16’, 2, ‘YYYY-MM-dd HH:mm:ss’) 返回 ‘2018-02-28 14:15:16’.
string	last_day(string date)	返回日期所在月的最后一天 (as of Hive 1.1.0). 示例：last_day(‘2022-10-01’) 返回 2022-10-31
string	next_day(string start_date, string day_of_week)	返回日期下一周的星期几日期，示例： next_day(‘2015-01-14’, ‘TU’) = 2015-01-20.
string	trunc(string date, string format)	返回指定格式的日期值(as of Hive 1.2.0). 支持的格式: MONTH/MON/MM, YEAR/YYYY/YY. 示例： trunc(‘2015-03-17’, ‘MM’) = 2015-03-01.
double	months_between(date1, date2)	返回两个日期之间的月数，示例：months_between(‘1997-03-30’, ‘1997-10-30’) = 7
string	date_format(date/timestamp/string ts, string fmt)	格式化日期，支持的格式 https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html. 示例： date_format(‘2015-04-08’, ‘y’) = ‘2015’

集合类

返回值类型	用法	描述
int	size(Map<K.V>)	返回Map的元素个数
int	size(Array)	返回数组的的元素个数
array	map_keys(Map<K.V>)	以数组的形式返回，Map的所有key
array	map_values(Map<K.V>)	以数组的形式返回，Map的所有value
boolean	array_contains(Array, value)	如果数组包含对应值的元素返回true，否则返回false
array	sort_array(Array)	将数组元素按升序排序后返回

条件类

返回值类型	用法	描述
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	对一个返回值是boolean类型的表达式进行判断，如果返回值是true就返回 第二个值，其它情况返回第三个值
boolean	isnull( a )	判空，null值返回true，否则返回false
boolean	isnotnull ( a )	判非空，非null值返回true，否则返回false
T	nvl(T value, T default_value)	两个值，如果第一个值是null则返回第二个值，否则返回第一个值 (as of HIve 0.11).
T	COALESCE(T v1, T v2, …)	上面函数的多值情况，如果前面值为null则取后面的值，可以一直判断下去直到取到非null值
T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	case when的一种写法，建议必加else
T	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END	case when的一种写法，建议必加else，推荐
T	nullif( a, b )	相当于CASE WHEN a=b THEN NULL else a (as of Hive 2.3.0)
void	assert_true(boolean condition)	Throw an exception if ‘condition’ is not true, otherwise return null (as of Hive 0.8.0). For example, select assert_true (2<1).

UDAF

函数返回值	用法	描述
BIGINT	count(*), count(expr), count(DISTINCT expr[, expr…])	计数
DOUBLE	sum(col), sum(DISTINCT col)	求和
DOUBLE	avg(col), avg(DISTINCT col)	求平均值
DOUBLE	min(col)	求最小值
DOUBLE	max(col)	求最大值
array	collect_set(col)	返回去重集合
array	collect_list(col)	返回不去重集合

上面的聚合函数我就不详细讲了，主要是配合 group by 使用；下面的UDTF才是重点噢

UDTF

返回值类型	用法	描述
T	explode(ARRAY a)	注意参数类型是数组，详情看示例
Tkey,Tvalue	explode(MAP<Tkey,Tvalue> m)	注意参数类型是Map，详情看示例
int,T	posexplode(ARRAY a)	注意参数类型是数组，详情看示例
T1,…,Tn	inline(ARRAY<STRUCTf1:T1,...,fn:Tn> a)	不常用，详情看示例
T1,…,Tn/r	stack(int r,T1 V1,…,Tn/r Vn)	不常用，详情看示例
string1,…,stringn	json_tuple(string jsonStr,string k1,…,string kn)	用于json解析，详情看示例
string 1,…,stringn	parse_url_tuple(string urlStr,string p1,…,string pn)	用户url解析，详情看示例

explode：常和split连用

-- 基本效果
select explode(array('A','B','C')) as col;
-- 一般用法
select t.id,tf.col from (select 0 as id) t lateral view explode(array('A','B','C')) tf as col;
-- 最佳用法
select t.id,tf.col from (select 1 as id,'A,B,C' as a) t lateral view explode(split(a,',')) tf as col;

参数是Map的就不演示了，只看最基础的效果

-- 基本效果
select explode(map('A',10,'B',20,'C',30)) as (key,value);

posexplode：和explode类似，区别是会带一个单独的列，看最基础的效果

-- 基本效果
select posexplode(array('A','B','C')) as (pos,val);

inline：基础效果演示

-- 基本效果
select inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02'))) as (col1,col2,col3);

stack：基础效果演示

-- 基本效果
select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') as (col0,col1,col2);

json_tuple：效果和 get_json_object函数一样，不同的是可以同时返回多个值，然后用法略有不同

-- 基本效果
select json_tuple('{"id":"2","name":"zs"}','id','name') as (id,name);
-- 最佳用法
select t.age,tf.id,tf.name from(select 18 as age,'{"id":"2","name":"zs"}' as json_str) t lateral view json_tuple(json_str,'id','name') tf as id,name;
-- 对比错误用法
select json_tuple(json_str,'id','name') as (id,name) from(select 18 as age,'{"id":"2","name":"zs"}' as json_str) t;
-- 注意错误用法
select age,json_tuple(json_str,'id','name') as (id,name) from(select 18 as age,'{"id":"2","name":"zs"}' as json_str) t;

parse_url_tuple：和json_tuple类似，不过是解析url，只展示最基本效果

-- 基本效果
select parse_url_tuple('https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF', 'HOST', 'PATH', 'QUERY', 'QUERY:id') as (host, path, query, query_id);

Lateral view

官方解释

Lateral view is used in conjunction with user-defined table generating functions such as explode(). As mentioned in Built-in Table-Generating Functions, a UDTF generates zero or more output rows for each input row. A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias.

百度翻译了一下

Lateral view 与用户定义的表生成函数（如explode（））结合使用。如内置表生成函数中所述，UDTF为每个输入行生成零个或多个输出行。Lateral view 首先将UDTF应用于基表的每一行，然后将结果输出行与输入行连接，以形成具有所提供的表别名的虚拟表。

UDTF也可以是生成多个列的噢

另：可以多次使用 lateral view

-- 示例
select col1, col2 from (select '1,2' as id,'a,b,c' as name) as t0
lateral view explode(split(id,',')) t1 as col1
lateral view explode(split(name,',')) t2 as col2;