面试官问：UDF是在Map端执行还是Reduce端执行?

2024-03-24 16:49:22

感谢兄弟们的关注与支持，如果觉得有帮助的话，还请来个点赞、收藏、转发三操作

该文章已更新到语雀中，后台回复“语雀”可获取进击吧大数据整个职业生涯持续更新的所有资料

感谢

首先感谢linxiang同学提供的文章素材，linxiang在一次课上抛出了这样一个问题"在面试过程中，面试官问到我UDF是在Map端执行的，还是在Reduce端执行的"。我刚听到这个问题的时候，有点没反应过来，因为这个问题确实平时没有去思考过，后来仔细想了下，才有了现在的这篇文章。通过本文，你将可以了解到:
1、UDF和UDAF和UDTF之间的区别
2、UDF和UDAF和UDTF在运行过程中分别是在哪端执行的?
3、UDF和UDAF和UDTF在各个Hive版本中的内置函数(共175项函数)
4、UDF和UDAF和UDTF在日常开发中的实操

UDF、UDAF、UDTF的区别

UDF

UDF全称为User Defined Function（即用户自定义函数），UDF开发在日常工作当中是非常普遍的。我们写一段SQL，调用UDF，得到结果就算是结束了，但大家有没有想过UDF底层是怎么执行的呢？那么我们拿MR引擎为例，那UDF是在Map端执行还是在Reduce端执行的呢？说实话，我之前没想过。既然没想过，那今天就来想一想。首先抛开在哪端执行不说，那我们知道UDF的模式是我们给一个值，然后再返回一个值。如上图所示，传一个A，返回给一个A_1；传一个B，返回给一个B_1;传一个C返回给一个C_1；这种模式就相当于在传入的一个值上进行了一些修饰后再返回给我们，相当于是一对一的模式。梳理到这里，答案也比较清晰了，这不就是map功能吗。有些同学可能会质疑，没关系，我们explain一下就知道了。

如上图所示，只有一个fetch Operator，当然这个demo比较简单，不会走MR的。从这里也可以看出来这就是一个转换功能，但有些同学仍有疑惑，没关系，让我们来一个走MR的例子。

explain select substr(id,2),count(1) from test group by substr(id,2);

到这里总能证明UDF函数是在Map阶段执行的吧！

UDAF

UDAF全称为User-defined Aggregation Function，从命名来看，这是一种聚合函数，比如像我们常用的sum、max。如下图所示，可以抽象的理解成传入多个值，最后返回给我们一个值。那么对于该类型的函数是不是一定在reduce端执行了，为什么这么说呢？你看sum函数是不是会发生shuffle，是不是在reduce端做全局聚合呢（如果你这样想也没问题，但也有问题）我们通过explain命令来验证一下想法。我们执行如下命令：

explain select sum(id) from test

如上图所示，对于SUM类型的UDAF是在map端和reduce端都执行了，哎呦，这是怎么回事呢？我们回想一下MapReduce机制，如果我们要做全局聚合，难道要把所有的数据都拉取到reduce端吗？那reduce端压力是不是就会很大。所以有了局部聚合的这么一种优化方式。

那我们把局部聚合优化阶段给关闭后，再来看一下UDAF会在那一端执行

--关闭map端聚合
set hive.map.aggr=false;

如上图所示，当我们把局部聚合优化功能给关闭后，UDAF只会在reduce执行。

UDTF

UDTF全称为User-defined Table Generating Function，该模式的功能是通过输入一行，返回多行。在实际场景中用的不多，该类型的执行阶段通常是在本地，大家也可以理解成是做map转换和UDF是一样的阶段。我们仍然用示例sql通过explain命令来验证一下

explain select explode(array(1,2,3,45));

区别总结

ok，到了这里不知道大家对三种F的端执行类型是否有一些清晰的认识，这里再次做一些总结：
1、对于UDF来说，也就是大家通常认知的map转换功能，一般是在task本地执行，不会发生shuffle，一句话概括就是map端执行。
2、对于UDAF来说，通常是聚合类函数，那么就会发生shuffle网络传输，如果不做任何优化的话，只在reduce端执行，如果开启了局部优化，那么map端和reduce端都会执行。
3、对于UDTF来说，属于数据炸裂，可以和UDF同样认为都是在map端执行的。

各版本内置函数列表

注意：Hive中所有的关键词都是忽略大小写的。无论是通过Beeline还是CLI模式，都可以通过以下命令来查看Hive 函数

-- 查看所有的函数
show functions;

---查看具体某个函数的帮助手册
desc function function_name;

--查看函数示例功能
desc function extended function_name;

注意点：当hive.cache.expr.evaluation参数设置为true的时候(当然默认值就是true)，在0.12.0、0.13.0和0.13.1版本中，使用UDF嵌套到其他UDF函数中可能会有问题。具体可以见HIVE-7314

一、内置UDF函数

1.1、数学函数

注意：当传入值为NULL的时候，返回值大多数也是NULL;

支持版本+	返回值类型	函数名称	功能描述

DOUBLE	abs(DOUBLE a)	返回绝对值
Hive0.13.0	DOUBLE	acos(DOUBLE a), acos(DECIMAL a)	如果 -1<=a<=1 或 NULL，则返回 a 的反余弦值。
Hive0.13.0	DOUBLE	asin(DOUBLE a), asin(DECIMAL a)	如果 -1<=a<=1 或 NULL，则返回 a 的反正弦。
Hive0.13.0	DOUBLE	atan(DOUBLE a), atan(DECIMAL a)	返回a的正切值。

STRING	bin(BIGINT a)	返回二进制格式的数字
Hive1.3.0	DOUBLE	bround(DOUBLE a)	使用HALF_EVEN四舍五入模式返回a的BIGINT值。例如：bround(2.5) = 2, bround(3.5) = 4.
Hive1.3.0	DOUBLE	bround(DOUBLE a, INT d)	使用HALF_EVEN四舍五入模式返回一个四舍五入到小数点后d位的数值。例如：bround(8.25, 1) = 8.2，bround(8.35, 1) = 8.4
Hive1.2.0	DOUBLE	cbrt(DOUBLE a)	返回双精度值的立方根

BIGINT	ceil(DOUBLE a), ceiling(DOUBLE a)	返回等于或大于 a 的最小 BIGINT 值

STRING	conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)	将一个数字从一个给定的基数转换为另一个基数
Hive0.13.0	DOUBLE	cos(DOUBLE a), cos(DECIMAL a)	返回 a 的余弦（a 以弧度为单位）
Hive0.13.0	DOUBLE	degrees(DOUBLE a), degrees(DECIMAL a)	将 a 的值从弧度转换为度数

DOUBLE	e()	返回 e 的值。
Hive0.13.0	DOUBLE	exp(DOUBLE a), exp(DECIMAL a)	返回 ea，其中 e 是自然对数的底数
Hive1.2.0	BIGINT	factorial(INT a)	返回a的阶乘

BIGINT	floor(DOUBLE a)	返回等于或小于 a 的最大 BIGINT 值。
Hive1.1.0	T	greatest(T v1, T v2, …)	返回值列表的最大值

STRING	hex(BIGINT a) hex(STRING a) hex(BINARY a)	如果参数是 INT 或二进制，则十六进制将数字作为十六进制格式的 STRING 返回。否则，如果数字是 STRING，它会将每个字符转换为其十六进制表示并返回结果 STRING。

T	least(T v1, T v2, …)	返回值列表的最小值
Hive0.13.0	DOUBLE	ln(DOUBLE a), ln(DECIMAL a)	返回参数 a 的自然对数
Hive0.13.0	DOUBLE	log2(DOUBLE a), log2(DECIMAL a)	返回参数 a 的以 2 为底的对数
Hive0.13.0	DOUBLE	log10(DOUBLE a), log10(DECIMAL a)	返回参数 a 的以 10 为底的对数

DOUBLE	log(DOUBLE base, DOUBLE a)
log(DECIMAL base, DECIMAL a)	返回参数 a 的底对数

INT or DOUBLE	negative(INT a), negative(DOUBLE a)	返回-a。

DOUBLE	pi()	返回π的值

INT or DOUBLE	pmod(INT a, INT b), pmod(DOUBLE a, DOUBLE b)	返回 a mod b 的正值

INT or DOUBLE	positive(INT a), positive(DOUBLE a)	返回a

DOUBLE	pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)	返回 ap
它返回 a值的 p 次幂
Hive0.13.0	DOUBLE	radians(DOUBLE a), radians(DOUBLE a)	将 a 的值从度数转换为弧度

DOUBLE	rand(), rand(INT seed)	返回从 0 到 1 均匀分布的随机数（从行到行变化）。指定种子将确保生成的随机数序列是确定性的

DOUBLE	round(DOUBLE a)	返回a的四舍五入的BIGINT值

DOUBLE	round(DOUBLE a, INT d)	返回四舍五入到 d 位小数
Hive1.2.0	INT
BIGINT	shiftleft(TINYINT	SMALLINT
Hive1.2.0	INT
BIGINT	shiftright(TINYINT	SMALLINT
Hive1.2.0	INT
BIGINT	shiftrightunsigned(TINYINT	SMALLINT
Hive0.13.0	DOUBLE or INT	sign(DOUBLE a), sign(DECIMAL a)	将 a 的符号返回为“1.0”（如果 a 为正）或“-1.0”（如果 a 为负），否则返回“0.0”。十进制版本返回 INT 而不是 DOUBLE
Hive0.13.0	DOUBLE	sin(DOUBLE a), sin(DECIMAL a)	返回 a 的正弦值（a 以弧度为单位
Hive0.13.0	DOUBLE	sqrt(DOUBLE a), sqrt(DECIMAL a)	返回 a 的平方根
Hive0.13.0	DOUBLE	tan(DOUBLE a), tan(DECIMAL a)	返回a的正切值（a的单位是弧度）
Hive0.12.0	BINARY	unhex(STRING a)	十六进制的倒数。将每对字符解释为一个十六进制数并转换为该数的字节表示
Hive3.0.0	INT	width_bucket(NUMERIC expr, NUMERIC min_value, NUMERIC max_value, INT num_buckets)	通过将 expr 映射到第 i 个相同大小的存储桶，返回 0 到 num_buckets+1 之间的整数。通过将 [min_value, max_value] 分成大小相等的区域来制作桶。如果expr < min_value，返回1，如果expr > max_value 返回num_buckets+1

1.1.1、abs

作用：返回绝对值

 select abs(-1),abs(1);

1.1.2、acos–>从Hive0.13.0

作用：如果 -1<=a<=1 或 NULL，则返回 a 的反余弦值。

select acos(0.1),acos(-1),acos(1),acos(-0.6)

1.1.3、asin–>从Hive0.13.0

作用：如果 -1<=a<=1 或 NULL，则返回 a 的反正弦。

select asin(0.1),asin(-1),asin(1),asin(-0.6),asin(null);

1.1.4、atan–>从Hive0.13.0

select atan(1.1),atan(0.543535)

1.1.5、bin

select bin(234234324234);

1.1.6、bround–>Hive1.3.0

select bround(2.4),bround(2.5),bround(3.5),bround(3.6554),bround(8.324,2),bround(3.234,1);

1.1.7、cbrt–>Hive1.2.0

select cbrt(3.34543);

1.1.8、ceil

select ceil(2.234),ceil(2.65),ceil(3.343);

1.1.9、conv

select conv(234234,10,2),conv(234234,10,16);

1.1.10、cos–>Hive0.13.0

 select cos(23.23),cos(60),cos(90),cos(180);

1.1.11、degress–>Hive0.13.0

select degrees(23.23432),degrees(23.66545);

1.1.12、e

 select e();

1.1.13、exp–>Hive0.13.0

 select exp(23.234),exp(23.354),exp(23.65),exp(2);

1.1.14、factorial–>Hive1.2.0

1.1.15、floor

select floor(2.34),floor(2.756),floor(3.54);

1.1.16、greatest–>Hive1.1.0

 select greatest(1,2,34,45,234,342,42324);

1.1.17、hex

select hex(23),hex(2);

1.1.18、least

select least(1,2,34,45,234,342,42324);

1.1.19、ln–>Hive0.13.0

 select ln(10),ln(20),ln(0),ln(1);

1.1.20、log2–>Hive0.13.0

select log2(4),log2(6),log2(8);

1.1.21、log10–>Hive0.13.0

select log10(40),log10(60),log10(80),log10(100);

1.1.22、negative

select negative(1),negative(20.234),negative(-20);

1.1.23、pi

 select pi();

1.1.24、pmod

select pmod(10,2),pmod(10,3),pmod(10,4);

1.1.25、positive

select positive(20),positive(10.234353);

1.1.26、pow

select pow(5,2),pow(4,2);

1.1.27、radians–>Hive0.13.0

select radians(4),radians(180),radians(360);

1.1.28、rand

-- 可以指定种子序列，这样可以保障每次的结果都一致
select rand(),rand(5),rand(10);

1.1.29、round

select round(3),round(3.2),round(3.43);

1.1.30、shiftleft–>Hive1.2.0

1.1.31、shiftright–>Hive1.2.0

1.1.32、shiftrightunsigned–>Hive1.2.0

1.1.33、sign–>Hive0.13.0

select sign(23.23),sign(23),sign(-10);

1.1.34、sin–>Hive0.13.0

select sin(60),sin(90),sin(180),sin(270),sin(360)

1.1.35、sqrt–>Hive0.13.0

select sqrt(3),sqrt(4),sqrt(10);

1.1.36、tan–>Hive0.13.0

select tan(30),tan(90),tan(180);

1.1.37、unhex–>Hive0.12.0

select hex(57),unhex(57)

1.1.38、width_bucket–>Hive3.0.0

1.2、集合函数

支持版本+	返回值类型	函数名称	功能描述

int	size(Map<K.V>)	返回map中的元素个数

int	size(Array)	返回array数组中的元素个数

array	map_keys(Map<K.V>)	返回map中的所有key

array	map_values(Map<K.V>)	返回map中的所有value

boolean	array_contains(Array, value)	如果数组包含值，则返回 TRUE。
Hive0.9.0	array	sort_array(Array)	根据数组元素的自然顺序对输入数组进行升序排序并返回

1.2.1、size

select map('a',1,'b',2),size(map('a',1,'b',2)),array(1,2,3,4),size(array(1,2,3,4));

1.2.2、map_keys

select map('a',1,'b',2), map_keys(map('a',1,'b',2));

1.2.3、map_values

select map('a',1,'b',2), map_values(map('a',1,'b',2));

1.2.4、array_contains

select array(1,2,3,4),array_contains(array(1,2,3,4),1),array_contains(array(1,2,3,4),5);

注意：该函数中判断参数的类型要和数组元素类型保持一致，否则会查询失败

select array(1,2,3,4),array_contains(array(1,2,3,4),1),array_contains(array(1,2,3,4),'a');

1.2.5、sort_array–>0.9.0

select array(4,2,3,234,234,23454,23),sort_array(array(4,2,3,234,234,23454,23));

1.3、类型转化函数

返回值类型	函数名称	功能描述
binary	binary(string	binary)
Expected “=” to follow "type"	cast(expr as)	将表达式 expr 的结果转换为。例如， cast(‘1’ as BIGINT) 会将字符串 ‘1’ 转换为其整数表示。如果转换不成功，则返回 null。如果 cast(expr as boolean) Hive 为非空字符串返回 true

1.3.1、binary

select binary('a'),binary('2');

1.3.2、cast

select cast('123' as int);

1.4、日期函数

支持版本+	返回值类型	函数名称	功能描述

string	from_unixtime(bigint unixtime[, string format])	将 unix epoch (1970-01-01 00:00:00 UTC) 的秒数转换为表示当前系统时区中该时刻时间戳的字符串，格式为“1970-01-01 00:00: 00”。

bigint	unix_timestamp()	以秒为单位获取当前的 Unix 时间戳。这个函数不是确定性的，它的值在查询执行的范围内不是固定的，因此阻止了查询的正确优化 - 自 2.0 以来，它已被弃用，以支持 CURRENT_TIMESTAMP 常量

bigint	unix_timestamp(string date)	将格式为 yyyy-MM-dd HH:mm:ss 的时间字符串转换为 Unix 时间戳（以秒为单位），使用默认时区和默认语言环境，如果失败则返回 0：unix_timestamp('2009-03-20 11:30:01 ') = 1237573801

bigint	unix_timestamp(string date, string pattern)	将具有给定模式的时间字符串转换为 Unix 时间戳（以秒为单位），如果失败则返回 0：unix_timestamp(‘2009-03-20’, ‘yyyy-MM-dd’) = 1237532400

pre 2.1.0: string
2.1.0 on: date	to_date(string timestamp)	返回时间戳字符串的日期部分（Hive 2.1.0 之前）：to_date(“1970-01-01 00:00:00”) = “1970-01-01”。从 Hive 2.1.0 开始，返回一个日期对象。
在 Hive 2.1.0 (HIVE-13248) 之前，返回类型是 String，因为在创建方法时不存在 Date 类型。

int	year(string date)	返回日期或时间戳字符串的年份部分：year(“1970-01-01 00:00:00”) = 1970, year(“1970-01-01”) = 1970。
Hive1.3.0	int	quarter(date/timestamp/string)	返回 1 到 4 范围内的日期、时间戳或字符串的一年中的季度（从 Hive 1.3.0 开始））。示例：季度（‘2015-04-08’）= 2

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
Hive2.2.0	int	extract(field FROM source)	从源（从 Hive 2.2.0 开始）检索字段，例如天数或小时数。来源必须是日期、时间戳、间隔或可以转换为日期或时间戳的字符串。支持的字段包括：天、星期几、小时、分钟、月、季度、秒、周和年

示例:
1. select extract(month from “2016-10-20”) results in 10.
1. select extract(hour from “2016-10-20 05:06:07”) results in 5.
1. select extract(dayofweek from “2016-10-20 05:06:07”) results in 5.
1. select extract(month from interval ‘1-3’ year to month) results in 3.
1. select extract(minute from interval ‘3 12:20:30’ day to second) results in 20.
|
|
| int | datediff(string enddate, string startdate) | 返回从 startdate 到 enddate 的天数：datediff(‘2009-03-01’, ‘2009-02-27’) = 2。 |
|
| pre 2.1.0: string
2.1.0 on: date | date_add(date/timestamp/string startdate, tinyint/smallint/int days) | 将天数添加到开始日期：date_add(‘2008-12-31’, 1) = ‘2009-01-01’。在 Hive 2.1.0 (HIVE-13248) 之前，返回类型是 String，因为在创建方法时不存在 Date 类型 |
|
| pre 2.1.0: string
2.1.0 on: date | date_sub(date/timestamp/string startdate, tinyint/smallint/int days) | 减去开始日期的天数：date_sub(‘2008-12-31’, 1) = ‘2008-12-30’。在 Hive 2.1.0 (HIVE-13248) 之前，返回类型是 String，因为在创建方法时不存在 Date 类型 |
| Hive0.8.0 | timestamp | from_utc_timestamp({any primitive type} ts, string timezone) | 将 UTC 中的时间戳转换为给定的时区（从 Hive 0.8.0 开始）。timestamp 是原始类型，包括timestamp/date、tinyint/smallint/int/bigint、float/double 和decimal。小数值被视为秒。整数值被视为毫秒。例如 from_utc_timestamp(2592000.0,‘PST’), from_utc_timestamp(2592000000,‘PST’) 和 from_utc_timestamp(timestamp ‘1970-01-30 16:00:00’,‘PST’)1930-1930-1930-时间戳08:00:00 |
| Hive0.8.0 | timestamp | to_utc_timestamp({any primitive type} ts, string timezone) | 将给定时区中的时间戳转换为 UTC（从 Hive 0.8.0 开始）。timestamp 是原始类型，包括timestamp/date、tinyint/smallint/int/bigint、float/double 和decimal。小数值被视为秒。整数值被视为毫秒。例如 to_utc_timestamp(2592000.0,‘PST’), to_utc_timestamp(2592000000,‘PST’) 和 to_utc_timestamp(timestamp ‘1970-01-30 16:00:00’,‘PST’)1930-1930-1930-timestamp都返回时间戳00:00:00 |
| Hive1.2.0 | date | current_date | 返回查询开始时的当前日期（从Hive 1.2.0开始）。在同一个查询中对current_date的所有调用都会返回相同的值 |
| Hive1.2.0 | timestamp | current_timestamp | 返回查询评估开始时的当前时间戳（从Hive 1.2.0开始）。在同一个查询中调用current_timestamp，都会返回相同的值 |
| Hive1.1.0 | string | add_months(string start_date, int num_months, output_date_format) | 返回 start_date 之后 num_months 的日期（从 Hive 1.1.0 开始）。start_date 是一个字符串、日期或时间戳。num_months 是一个整数。如果 start_date 是该月的最后一天，或者如果结果月份的天数少于 start_date 的日期部分，则结果是结果月份的最后一天。否则，结果与 start_date 具有相同的日期部分。默认输出格式为“yyyy-MM-dd”。在 Hive 4.0.0 之前，忽略日期的时间部分。
从 Hive 4.0.0 开始，add_months 支持可选参数 output_date_format，它接受一个表示输出的有效日期格式的 String。这允许在输出中保留时间格式
For example :
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’. |
| Hive1.1.0 | string | last_day(string date) | 返回日期所属月份的最后一天（从 Hive 1.1.0 开始）。日期是格式为“yyyy-MM-dd HH:mm:ss”或“yyyy-MM-dd”的字符串。日期的时间部分被忽略。 |
| Hive1.2.0 | string | next_day(string start_date, string day_of_week) | 返回晚于 start_date 并命名为 day_of_week 的第一个日期（从 Hive 1.2.0）。start_date 是一个字符串/日期/时间戳。day_of_week 是星期几的 2 个字母、3 个字母或全名（例如 Mo、tue、FRIDAY）。start_date 的时间部分被忽略。示例：next_day(‘2015-01-14’, ‘TU’) = 2015-01-20 |
| Hive1.2.0 | string | trunc(string date, string format) | 返回截断为格式指定单位的日期（从 Hive 1.2.0 开始）。支持的格式：MONTH/MON/MM、YEAR/YYYY/YY。示例：trunc(‘2015-03-17’, ‘MM’) = 2015-03-01 |
| Hive1.2.0 | double | months_between(date1, date2) | 返回日期 date1 和 date2 之间的月数（从 Hive 1.2.0）。如果 date1 晚于 date2，则结果为正。如果 date1 早于 date2，则结果为负数。如果 date1 和 date2 是该月的同一天或都是该月的最后几天，则结果始终为整数。否则，UDF 会根据有 31 天的月份计算结果的小数部分，并考虑时间分量 date1 和 date2 的差异。date1 和 date2 类型可以是日期、时间戳或字符串，格式为“yyyy-MM-dd”或“yyyy-MM-dd HH:mm:ss”。结果四舍五入到小数点后 8 位。示例：months_between(‘1997-02-28 10:30:00’, ‘1996-10-30’) = 3.94959677 |
| Hive1.2.0 | string | date_format(date/timestamp/string ts, string fmt) | 将日期/时间戳/字符串转换为日期格式 fmt 指定格式的字符串值（从 Hive 1.2.0 开始）。第二个参数 fmt 应该是常量. 示例: date_format(‘2015-04-08’, ‘y’) = ‘2015’.
date_format can be used to implement other UDFs, e.g.:
- dayname(date) is date_format(date, ‘EEEE’)
- dayofyear(date) is date_format(date, ‘D’)
|

1.4.1、from_unixtime

 select from_unixtime(1638602968),from_unixtime(1638602968,'yyyy-MM-dd HH:mm:SS'),from_unixtime(1638602968,'yyyy-MM-dd');

1.4.2、unix_timestamp

select unix_timestamp();

1.4.3、to_date

select to_date('2021-12-04 2021-12-04 15:29:28'),to_date('2021-12-04 15:29:28');

1.4.4、year

select year('2021-12-04 15:29:28');

1.4.5、quarter–>Hive1.3.0

1.4.6、month

select month('2021-12-04 15:29:28');

1.4.7、day

select day('2021-12-04 15:29:28');

1.4.8、hour

select hour('2021-12-04 15:29:28');

1.4.9、minute

 select minute('2021-12-04 15:29:28');

1.4.10、second

select second('2021-12-04 15:29:28');

1.4.11、weekofyear

 select weekofyear('2021-12-04 15:29:28');

1.4.12、extract–>Hive2.2.0

1.4.13、datediff

select datediff('2009-03-01', '2009-02-27') ;

1.4.14、date_add

select date_add('2021-12-04 15:29:28',1);

1.4.15、date_sub

 select date_add('2021-12-04 15:29:28',-1),date_sub('2021-12-04 15:29:28',1),date_sub('2021-12-04 15:29:28',-1);

1.4.16、from_utc_timestamp–>Hive0.8.0

select  from_utc_timestamp(timestamp '1970-01-30 16:00:00','PST') ,from_utc_timestamp(2592000.0,'PST');

1.4.17、to_utc_timestamp–>Hive0.8.0

select to_utc_timestamp(2592000.0,'PST'), to_utc_timestamp(2592000000,'PST'),to_utc_timestamp(timestamp '1970-01-30 16:00:00','PST') ;

1.4.18、current_date -->Hive1.2.0

select current_date();

1.4.19、current_timestamp -->Hive1.2.0

select current_timestamp();

1.4.20、add_months–>Hive1.1.0

 select add_months('2009-08-31', 1),add_months('2017-12-31 14:15:16',3) ;

1.4.21、last_day --> Hive1.1.0

select last_day('2021-12-04');

1.4.22、next_day --> Hive1.2.0

 --获取下一个周二
 select next_day('2021-12-04', 'TU');

1.4.23、trunc --> Hive1.2.0

--返回截断为格式指定单位的日期（从 Hive 1.2.0 开始）。支持的格式：MONTH/MON/MM、YEAR/YYYY/YY。
select trunc('2021-12-04', 'MM');

1.4.24、months_between–>Hive1.2.0

1.4.25、date_format --> Hive1.2.0

1.5、判断函数

支持版本	返回值类型	函数名称	功能描述

T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	当 testCondition 为真时返回 valueTrue，否则返回 valueFalseOrNull

boolean	isnull( a )	如果 a 为 NULL，则返回 true，否则返回 false。

boolean	isnotnull ( a )	如果 a 不是 NULL，则返回 true，否则返回 false。

T	nvl(T value, T default_value)	如果值为 null 则返回默认值，否则返回值（从 Hive 0.11 开始）

T	COALESCE(T v1, T v2, …)	返回第一个不是 NULL 的 v，如果所有 v 都是 NULL，则返回 NULL。

T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	当 a = b 时，返回 c；当 a = d 时，返回 e；否则返回 f

T	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END	当 a = true 时，返回 b；当 c = true 时，返回 d；否则返回 e。
Hive2.3.0	T	nullif( a, b )	如果 a=b，则返回 NULL；否则返回一个（从 Hive 2.3.0 开始）。简写：CASE WHEN a = b then NULL else a
Hive0.8.0	void	assert_true(boolean condition)	如果“条件”不为真，则抛出异常，否则返回 null（从 Hive 0.8.0 开始）。例如，选择 assert_true (2<1)

1.5.1、if

select if(1=1,'a','b'),if(1=2,'a','b') ;

1.5.2、isnull

select isnull(1),isnull(null);

1.5.3、isnotnull

select isnotnull(1),isnotnull(null);

1.5.4、nvl

select nvl(1,1),nvl(null,1);

1.5.5、coalesce

select coalesce(1,null,2,3,null,4,null),coalesce(null,null,null,2,3,4),coalesce(null,null,null,null);

1.5.6、case when

select case when 1=1 then '1' else 'b' end ,case when 1=1 then '1' end;

1.5.7、nullif -->Hive2.3.0

1.5.8、assert_true --> Hive0.8.0

 select assert_true(1=1),assert_true(1=2);

1.6、字符串函数

支持版本	返回值类型	函数名称	功能描述

int	ascii(string str)	返回 str 的第一个字符的数值。
Hive0.12.0	string	base64(binary bin)	将参数从二进制转换为 base 64 字符串（从 Hive 0.12.0 开始）
Hive2.2.0	int	character_length(string str)	返回 str 中包含的 UTF-8 字符数（从 Hive 2.2.0 开始）。函数 char_length 是该函数的简写。
Hive1.3.0	string	chr(bigint	double A)

string	concat(string	binary A, string

array<struct<string,double>>	context_ngrams(array<array>, array, int K, int pf)	给定一串“上下文”，从一组标记化的句子中返回前 k 个上下文 N-gram。

string	concat_ws(string SEP, string A, string B…)	与上面的 concat() 类似，但使用自定义分隔符 SEP

string	concat_ws(string SEP, array)	与上面的 concat_ws() 类似，但采用字符串数组。（从 Hive 0.9.0 开始）
Hive0.12.0	string	decode(binary bin, string charset)	使用提供的字符集（‘US-ASCII’、‘ISO-8859-1’、‘UTF-8’、‘UTF-16BE’、‘UTF-16LE’、‘UTF- 16’)。如果任一参数为空，则结果也将为空。（从 Hive 0.12.0 开始。）

string	elt(N int,str1 string,str2 string,str3 string,…)	返回索引号处的字符串。例如 elt(2,‘hello’,‘world’) 返回 ‘world’。如果 N 小于 1 或大于参数数量，则返回 NULL。
Hive0.12.0	binary	encode(string src, string charset)	使用提供的字符集（‘US-ASCII’、‘ISO-8859-1’、‘UTF-8’、‘UTF-16BE’、‘UTF-16LE’、‘UTF- 16’)。如果任一参数为空，则结果也将为空。（从 Hive 0.12.0 开始。）

int	field(val T,val1 T,val2 T,val3 T,…)	返回 val1,val2,val3,… 列表中 val 的索引，如果未找到则返回 0。例如 field(‘world’,‘say’,‘hello’,‘world’) 返回 3。支持所有原始类型，使用 str.equals(x) 比较参数。如果 val 为 NULL，则返回值为 0。

int	find_in_set(string str, string strList)	返回 strList 中第一次出现的 str ，其中 strList 是逗号分隔的字符串。如果任一参数为 null，则返回 null。如果第一个参数包含任何逗号，则返回 0。例如， find_in_set(‘ab’, ‘abc,b,ab,c,def’) 返回 3
Hive0.10.0	string	format_number(number x, int d)	将数字 X 格式化为类似 ‘#,###,###.##’ 的格式，四舍五入到 D 位小数，并将结果作为字符串返回。如果 D 为 0，则结果没有小数点或小数部分。（从 Hive 0.10.0 开始；在 Hive 0.14.0 中修复了浮点类型的错误，在 Hive 0.14.0 中添加了十进制类型支持）

string	get_json_object(string json_string, string path)	根据指定的json路径从json字符串中提取json对象，并返回提取的json对象的json字符串。如果输入的 json 字符串无效，它将返回 null。注意：json 路径只能包含字符 [0-9a-z_]，即不能包含大写或特殊字符。此外，键不能以数字开头。这是由于对 Hive 列名称的限制

boolean	in_file(string str, string filename)	如果字符串 str 在文件名中显示为整行，则返回 true。

int	instr(string str, string substr)	返回 substr 在 str 中第一次出现的位置。如果任一参数为 null，则返回 null，如果在 str 中找不到 substr，则返回 0。请注意，这不是基于零的。str 中的第一个字符的索引为 1

int	length(string A)	返回字符串的长度

int	locate(string substr, string str[, int pos])	返回位置 pos 之后 str 中第一次出现 substr 的位置。

string	lower(string A) lcase(string A)	返回将 B 的所有字符转换为小写的字符串。例如，lower(‘fOoBaR’) 结果是 ‘foobar’

string	lpad(string str, int len, string pad)	返回 str，用 pad 左填充，长度为 len。如果 str 比 len 长，则返回值将缩短为 len 个字符。如果填充字符串为空，则返回值为空

string	ltrim(string A)	返回从 A 的开头（左侧）修剪空格产生的字符串。例如， ltrim(’ foobar ') 结果为 'foobar ’

array<struct<string,double>>	ngrams(array<array>, int N, int K, int pf)	返回一组标记化的句子中的top-k N-grams，例如由sentences()UDAF返回的那些
Hive2.2.0	int	octet_length(string str)	返回以 UTF-8 编码保存字符串 str 所需的八位字节数（自 Hive 2.2.0）。请注意，octet_length(str) 可以大于 character_length(str)。

string	parse_url(string urlString, string partToExtract [, string keyToExtract])	从 URL 返回指定的部分。partToExtract 的有效值包括 HOST、PATH、QUERY、REF、PROTOCOL、AUTHORITY、FILE 和 USERINFO。例如， parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘HOST’) 返回 ‘facebook.com’。还可以通过提供键作为第三个参数来提取 QUERY 中特定键的值，例如， parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1’, ‘QUERY’, ‘k1’) 返回 ‘v1’。
Hive0.9.0	string	printf(String format, Obj… args)	返回按照printf风格的格式字符串格式化的输入（从Hive 0.9.0开始）

string	quote(String text)	返回带引号的字符串（包括任何单引号 HIVE-4.0.0 的转义字符)


string	regexp_extract(string subject, string pattern, int index)	返回使用模式提取的字符串。例如，regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) 返回 ‘bar.‘请注意，在使用预定义字符类时需要小心：使用 ‘\s’ 作为第二个参数将匹配字母 s；’\s’ 是匹配空格等所必需的。‘index’ 参数是 Java 正则表达式 Matcher group() 方法索引

string	regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	返回将 INITIAL_STRING 中与 PATTERN 中定义的 java 正则表达式语法匹配的所有子字符串替换为 REPLACEMENT 实例所产生的字符串。例如，regexp_replace(“foobar”, "oo

string	repeat(string str, int n)	重复 str n 次。
Hive1.3.0	string	replace(string A, string OLD, string NEW)	返回所有非重叠出现的OLD被替换为NEW的字符串A（从Hive 1.3.0和2.1.0起). 例如：选择 replace(“ababab”, “abab”, “Z”); 返回 “Zab”

string	reverse(string A)	返回反转的字符串

string	rpad(string str, int len, string pad)	返回 str，用 pad 右填充，长度为 len。如果 str 比 len 长，则返回值将缩短为 len 个字符。如果填充字符串为空，则返回值为空。

string	rtrim(string A)	返回从 A 的末尾（右侧）修剪空格产生的字符串。例如， rtrim(’ foobar ‘) 结果为 ’ foobar’

array<array>	sentences(string str, string lang, string locale)	将一串自然语言文本标记为单词和句子，其中每个句子在适当的句子边界处断开并作为单词数组返回。‘lang’ 和 ‘locale’ 是可选参数。例如，句子(‘Hello there! How are you?’) 返回 ( (“Hello”, “there”), (“How”, “are”, “you”) )

string	space(int n)	返回一个包含 n 个空格的字符串

array	split(string str, string pat)	在 pat 周围拆分 str （pat 是一个正则表达式）

map<string,string>	str_to_map(text[, delimiter1, delimiter2])	使用两个分隔符将文本拆分为键值对。Delimiter1 将文本分成 K-V 对，Delimiter2 拆分每个 K-V 对。默认分隔符是“,”用于 delimiter1，“:”用于 delimiter2。

string	substr(string	binary A, int start) substring(string

string	substr(string	binary A, int start, int len) substring(string
Hive1.3.0	string	substring_index(string A, string delim, int count)	在分隔符 delim 出现计数之前返回字符串 A 中的子字符串（从 Hive 1.3.0 开始））。如果 count 为正数，则返回最终分隔符左侧的所有内容（从左侧开始计数）。如果计数为负，则返回最终分隔符右侧的所有内容（从右侧开始计数）。Substring_index 在搜索 delim 时执行区分大小写的匹配。示例：substring_index(‘www.apache.org’, ‘.’, 2) = ‘www.apache’
Hive0.14.0	string	translate(string	char

string	trim(string A)	返回从 A 的两端修剪空格产生的字符串。例如，trim(’ foobar ') 结果为 ‘foobar’
Hive0.12.0	binary	unbase64(string str)	将参数从 base 64 字符串转换为 BINARY。（从 Hive 0.12.0 开始。）

string	upper(string A) ucase(string A)	返回将 A 的所有字符转换为大写的字符串。例如， upper(‘fOoBaR’) 结果为 ‘FOOBAR’
Hive1.1.0	string	initcap(string A)	返回字符串，每个单词的第一个字母大写，所有其他字母小写。单词由空格分隔。（截至 Hive 1.1.0.)
Hive1.2.0	int	levenshtein(string A, string B)	返回两个字符串之间的 Levenshtein 距离（从 Hive 1.2.0）。例如， levenshtein(‘kitten’, ‘sitting’) 结果为 3
Hive1.2.0	string	soundex(string A)	返回字符串的 soundex 代码（从 Hive 1.2.0 开始）。例如， soundex(‘Miller’) 结果为 M460

1.6.1、ascii

select ascii('a')

1.6.2、base64–>Hive0.12.0

select base64(cast('abcd' as binary));

1.6.3、character_length–>Hive2.2.0

1.6.4、chr–>Hive1.3.0

1.6.5、concat

select concat(1,'1','2','a'),concat(1,'a',null);

1.6.6、context_ngrams

1.6.7、concat_ws

--该拼接函数仅支持字符串类型或者字符串数组类型
select concat_ws('-','1','2','3'),concat_ws('-',array('1','2','3'));

1.6.8、decode -->Hive0.12.0

select decode(cast('abc' as binary),'ISO-8859-1');

1.6.9、elt

--返回指定索引处的字符串
SELECT elt(2,'hello','world'),elt(1,'jello','world');

1.6.10、encode -->Hive0.12.0

select encode('abc1233424asfsd','UTF-16LE');

1.6.11、field

--返回指定字符串所在的索引处，如world字符串在第3个位置上
select field('world','say','hello','world')

1.6.12、find_in_set

--返回 'abc,b,ab,c,def' 中第一次出现的 ab ，其中 'abc,b,ab,c,def' 是逗号分隔的字符串。如果任一参数为 null，则返回 null。如果第一个参数包含任何逗号，则返回 0
select find_in_set('ab', 'abc,b,ab,c,def') ;

1.6.13、format_number–>Hive0.10.0

 select format_number(2,1),format_number(2,2),format_number(2,3);

1.6.14、get_json_object

select get_json_object('{"a":2,"b":1}','$.a');

1.6.15、in_file

1.6.16、instr

select instr('asfdsarsrsf234','6'),instr('asfdsarsrsf234','a');

1.6.17、length

select length('asfdsarsrsf234');

1.6.18、locate

select locate('a','asfdsarsrsf234'),locate('6','asfdsarsrsf234');

1.6.19、lower

select lower('AWFDSFDS');

1.6.20、lpad

--返回10位长度的字符串，如果不原始字符串不足10位，则用*在左位填充
select lpad('sdfsd',10,'*');

1.6.21、ltrim

select ltrim('safdsf  '),length(ltrim('safdsf  ')),length('safdsf   ');

1.6.22、ngrams

1.6.23、octet_length -->Hive2.2.0

1.6.24、parse_url

-- 可以指定HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, 和 USERINFO
select parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');

1.6.25、printf -->0.9.0

SELECT printf("His Name is%s, Age Is %d","Jetty",100);

1.6.26、regexp_extract

SELECT regexp_extract('foothebar', 'foo(.*?)(bar)', 2) ;

1.6.27、regexp_replace

select regexp_replace("foobar", "oo|ar", "");

1.6.28、repeat

SELECT repeat('a',3);

1.6.29、replace–>HIve1.3.0

1.6.30、reverse

select reverse("abcde");

1.6.31、rpad

select rpad('ancd',10,'*');

1.6.32、rtrim

 select rtrim('    safdsf  '),length(rtrim('    safdsf  ')),length('    safdsf   ');

1.6.33、sentences

select sentences('Hello there! How are you?');

1.6.34、space

select space(3),length(space(3));

1.6.35、split

 select split('ansdasdfc','a');

1.6.36、str_to_map

select str_to_map('a-1,b-2,c-3',',','-');

1.6.37、substr

select  substr('foobar', 4), substr('foobar', 4, 1);

1.6.38、substring_index -->Hive1.3.0

1.6.39、translate–>Hive0.14.0

 select translate('asefsda12313asdf','123','*');

1.6.40、trim

 select trim('    safdsf  '),length(trim('    safdsf  ')),length('    safdsf   ');

1.6.41、unbase64 -->Hive0.12.0

select unbase64('wwewe.csdf');

1.6.42、upper

select upper('fOoBaR') ;

1.6.43、initcap–>Hive1.1.0

select initcap('asdfcasd');

1.6.44、levenshtein -->Hive1.2.0

levenshtein('kitten', 'sitting');

1.6.45、soundex -->Hive1.2.0

select soundex('Miller');

1.7、掩码函数

支持版本	返回值类型	函数名称	功能描述
Hive2.1.0	string	mask(string str[, string upper[, string lower[, string number]]])	返回 str 的掩码版本（从 Hive 2.1.0 开始）。默认情况下，大写字母转换为“X”，小写字母转换为“x”，数字转换为“n”。例如 mask(“abcd-EFGH-8765-4321”) 结果为 xxxx-XXXX-nnnn-nnnn。您可以通过提供附加参数来覆盖掩码中使用的字符：第二个参数控制大写字母的掩码字符，第三个参数控制小写字母，第四个参数控制数字。例如， mask(“abcd-EFGH-8765-4321”, “U”, “l”, “#”) 结果为 llll-UUUU-####-####
Hive2.1.0	string	mask_first_n(string str[, int n])	返回前 n 个值被屏蔽的 str 的屏蔽版本（从 Hive 2.1.0 开始）。大写字母转换为“X”，小写字母转换为“x”，数字转换为“n”。例如， mask_first_n(“1234-5678-8765-4321”, 4) 结果为 nnnn-5678-8765-4321
Hive2.1.0	string	mask_last_n(string str[, int n])	返回一个掩码版本的 str 掩码了最后 n 个值（从 Hive 2.1.0 开始）。大写字母转换为“X”，小写字母转换为“x”，数字转换为“n”。例如: mask_last_n(“1234-5678-8765-4321”, 4) 结果为 1234-5678-8765-nnnn
Hive2.1.0	string	mask_show_first_n(string str[, int n])	返回 str 的掩码版本，显示未掩码的前 n 个字符（从 Hive 2.1.0 开始）。大写字母转换为“X”，小写字母转换为“x”，数字转换为“n”。例如：mask_show_first_n(“1234-5678-8765-4321”, 4) 结果为 1234-nnnn-nnnn-nnnn
Hive2.1.0	string	mask_show_last_n(string str[, int n])	返回 str 的掩码版本，显示未掩码的最后 n 个字符（从 Hive 2.1.0 开始）。大写字母转换为“X”，小写字母转换为“x”，数字转换为“n”。例如， mask_show_last_n(“1234-5678-8765-4321”, 4) 结果为 nnnn-nnnn-nnnn-4321
Hive2.1.0	string	mask_hash(string	char

1.7.1、mask–>Hive2.1.0

1.7.2、mask_first_n->Hive2.1.0

1.7.3、mask_last_n -->Hive2.1.0

1.7.4、mask_show_first_n–>Hive2.1.0

1.7.5、mask_show__last_n -->Hive2.1.0_

1.7.6、mask_hash -->Hive2.1.0

1.8、杂项函数

支持版本	返回值类型	函数名称	功能描述
Hive0.9.0	varies	java_method(class, method[, arg1[, arg2…]])	反射
Hive0.7.0	varies	reflect(class, method[, arg1[, arg2…]])	使用反射通过匹配参数签名来调用 Java 方法。
Hive0.4	int	hash(a1[, a2…])	返回参数的哈希值。（从 Hive 0.4 开始）
Hive1.2.0	string	current_user()	从配置的身份验证器管理器返回当前用户名（从 Hive 1.2.0）。可能与连接时提供的用户相同，但对于某些身份验证管理器（例如 HadoopDefaultAuthenticator），它可能不同。
Hive2.2.0	string	logged_in_user()	从会话状态返回当前用户名（从 Hive 2.2.0）。这是连接到 Hive 时提供的用户名。
Hive0.13.0	string	current_database()	返回当前数据库名称（从 Hive 0.13.0 开始）
Hive1.3.0	string	md5(string/binary)	计算字符串或二进制的 MD5 128 位校验和（从 Hive 1.3.0 开始）。该值作为 32 个十六进制数字的字符串返回，如果参数为 NULL，则返回 NULL。示例：md5(‘ABC’) = ‘902fbdd2b1df0c4f70b4a5d23525e932’
Hive1.3.0	string	sha1(string/binary)
sha(string/binary)	计算字符串或二进制的 SHA-1 摘要并将值作为十六进制字符串返回（从 Hive 1.3.0）。示例：sha1(‘ABC’) = ‘3c01bdbb26f358bab27f267924aa2c9a03fcfdb8’
Hive1.3.0	bigint	crc32(string/binary)	计算字符串或二进制参数的循环冗余校验值并返回 bigint 值（从 Hive 1.3.0）。示例：crc32(‘ABC’) = 2743272264。
Hive1.3.0	string	sha2(string/binary, int)	计算 SHA-2 系列哈希函数（SHA-224、SHA-256、SHA-384 和 SHA-512）（从 Hive 1.3.0 开始））。第一个参数是要散列的字符串或二进制文件。第二个参数表示结果的所需位长，其值必须为 224、256、384、512 或 0（相当于 256）。从 Java 8 开始支持 SHA-224。如果任一参数为 NULL 或哈希长度不是允许的值之一，则返回值为 NULL。示例：sha2(‘ABC’, 256) = ‘b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78’
Hive1.3.0	binary	aes_encrypt(input string/binary, key string/binary)	使用 AES 加密输入（从 Hive 1.3.0 开始）。可以使用 128、192 或 256 位的密钥长度。如果安装了 Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files，则可以使用 192 和 256 位密钥。如果任一参数为 NULL 或密钥长度不是允许的值之一，则返回值为 NULL。示例：base64(aes_encrypt(‘ABC’, ‘1234567890123456’)) = ‘y6Ss+zCYObpCbgfWfyNWTw==’。
Hive1.3.0	binary	aes_decrypt(input binary, key string/binary)	使用 AES 解密输入（从 Hive 1.3.0 开始）。可以使用 128、192 或 256 位的密钥长度。如果安装了 Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files，则可以使用 192 和 256 位密钥。如果任一参数为 NULL 或密钥长度不是允许的值之一，则返回值为 NULL。示例：aes_decrypt(unbase64(‘y6Ss+zCYObpCbgfWfyNWTw==’), ‘1234567890123456’) = ‘ABC’。
Hive2.1.0	string	version()	返回 Hive 版本（从 Hive 2.1.0 开始）。该字符串包含 2 个字段，第一个是内部版本号，第二个是内部版本哈希。示例：select version()可能会返回“2.1.0.2.5.0.0-1245 r027527b9c5ce1a3d7d0b6d2e6de2378fb0c39232”。实际结果将取决于您的构建

bigint	surrogate_key([write_id_bits, task_id_bits])	当您将数据输入表格时，自动为行生成数字 ID。只能用作酸表或仅插入表的默认值。

1.8.1、java_method–>Hive0.9.0

 SELECT java_method("java.lang.String", "valueOf", 1),
       java_method("java.lang.String", "isEmpty"),
       java_method("java.lang.Math", "max", 2, 3),
       java_method("java.lang.Math", "min", 2, 3),
       java_method("java.lang.Math", "round", 2.5),
       java_method("java.lang.Math", "exp", 1.0),
       java_method("java.lang.Math", "floor", 1.9)

1.8.2、reflect --> Hive0.7.0

SELECT reflect("java.lang.String", "valueOf", 1),
       reflect("java.lang.String", "isEmpty"),
       reflect("java.lang.Math", "max", 2, 3),
       reflect("java.lang.Math", "min", 2, 3),
       reflect("java.lang.Math", "round", 2.5),
       reflect("java.lang.Math", "exp", 1.0),
       reflect("java.lang.Math", "floor", 1.9)

1.8.3、hash --> Hive0.4

 select hash('asfsd');

1.8.4、current_user --> Hive1.2.0

select current_user();

1.8.5、logged_in_user --> Hive2.2.0

1.8.6、current_database -->Hive0.13.0

 select current_database();

1.8.7、md5 -->Hive1.3.0

 select md5('234');

1.8.8、sha1 --> Hive1.3.0

1.8.9、crc32 --> Hive1.3.0

1.8.10、sha2–>Hive1.3.0

1.8.11、aes_encrypt --> Hive1.3.0

1.8.12、version --> Hive2.1.0

1.8.13、surrogate_key

二、内置UDAF函数

支持版本	返回值类型	函数名称	功能描述

BIGINT	count(*), count(expr), count(DISTINCT expr[, expr…])	count(*) - 返回检索到的总行数，包括包含 NULL 值的行

count(expr) - 返回提供的表达式为非 NULL 的行数.
count(DISTINCT expr[, expr]) -返回提供的表达式唯一且非 NULL 的行数。可以使用 hive.optimize.distinct.rewrite 优化此操作的执行. |
|
| DOUBLE | sum(col), sum(DISTINCT col) | 返回组中元素的总和或组中列的不同值的总和。 |
|
| DOUBLE | avg(col), avg(DISTINCT col) | 返回组中元素的平均值或组中列的不同值的平均值 |
|
| DOUBLE | min(col) | 返回组中列的最小值 |
|
| DOUBLE | max(col) | 返回组中列的最大值。 |
|
| DOUBLE | variance(col), var_pop(col) | 返回组中数字列的方差 |
|
| DOUBLE | var_samp(col) | 返回组中数字列的无偏样本方差 |
|
| DOUBLE | stddev_pop(col) | 返回组中数字列的标准差 |
|
| DOUBLE | stddev_samp(col) | 返回组中数字列的无偏样本标准差 |
|
| DOUBLE | covar_pop(col1, col2) | 返回组中一对数值列的总体协方差 |
|
| DOUBLE | covar_samp(col1, col2) | 返回组中一对数字列的样本协方差 |
|
| DOUBLE | corr(col1, col2) | 返回组中一对数字列的 Pearson 相关系数 |
|
| DOUBLE | percentile(BIGINT col, p) | 返回组中列的确切第 p 个百分位数（不适用于浮点类型）。p 必须介于 0 和 1 之间。注意：只能为整数值计算真正的百分位数。如果您的输入是非整数，请使用 PERCENTILE_APPROX。 |
|
| array | percentile(BIGINT col, array(p1 [, p2]…)) | 返回组中列的确切百分位数 p1、p2、…（不适用于浮点类型）。pi 必须介于 0 和 1 之间。注意：只能为整数值计算真正的百分位数。如果您的输入是非整数，请使用 PERCENTILE_APPROX |
|
| DOUBLE | percentile_approx(DOUBLE col, p [, B]) | 返回组中数字列（包括浮点类型）的近似第 p 个百分位。B 参数以内存为代价控制近似精度。较高的值会产生更好的近似值，默认值为 10,000。当 col 中不同值的数量小于 B 时，这给出了精确的百分位值 |
|
| array | percentile_approx(DOUBLE col, array(p1 [, p2]…) [, B]) | 同上，但接受并返回百分位值数组而不是单个值 |
| Hive2.2.0 | double | regr_avgx(independent, dependent) | 相当于 avg（依赖）。从 Hive 2.2.0 开始。 |
| Hive2.2.0 | double | regr_avgy(independent, dependent) | 相当于 avg（独立）。从 Hive 2.2.0 开始 |
| Hive2.2.0 | double | regr_count(independent, dependent) | 返回用于拟合线性回归线的非空对的数量。从 Hive 2.2.0 开始 |
| Hive2.2.0 | double | regr_intercept(independent, dependent) | 返回线性回归线的 y 截距，即等式中 b 的值依赖 = a * independent + b。从 Hive 2.2.0 开始 |
| Hive2.2.0 | double | regr_r2(independent, dependent) | 返回回归的决定系数。从 Hive 2.2.0 开始。 |
| Hive2.2.0 | double | regr_slope(independent, dependent) | 返回线性回归线的斜率，即等式中 a 的值依赖 = a * independent + b。从 Hive 2.2.0 开始。 |
| Hive2.2.0 | double | regr_sxx(independent, dependent) | 相当于 regr_count(independent,dependent) * var_pop(dependent)。从 Hive 2.2.0 开始。 |
| Hive2.2.0 | double | regr_sxy(independent, dependent) | 相当于 regr_count(independent,dependent) * covar_pop(independent,dependent)。从 Hive 2.2.0 开始 |
| Hive2.2.0 | double | regr_syy(independent, dependent) | 相当于 regr_count(independent,dependent) * var_pop(independent)。从 Hive 2.2.0 开始 |
|
| array<struct {‘x’,‘y’}> | histogram_numeric(col, b) | 使用 b 个非均匀间隔的 bin 计算组中数字列的直方图。输出是表示 bin 中心和高度的双值 (x,y) 坐标的大小为 b 的数组 |
|
| array | collect_set(col) | 返回一组消除了重复元素的对象，可实现去重作用 |
| Hive0.13.0 | array | collect_list(col) | 返回具有重复项的对象列表，（从 Hive 0.13.0 开始。） |
| Hive0.11.0 | INTEGER | ntile(INTEGER x) | 将一个有序的分区分成 x 个称为桶的组，并为分区中的每一行分配一个桶号。这允许轻松计算三分位数、四分位数、十分位数、百分位数和其他常见的汇总统计数据。（从 Hive 0.11.0 开始。） |

2.1、count

--可以发现count(id)会把id=null的值剔除掉
select count(1),count(*),count(distinct id),count(id) from test

2.2、sum

select sum(1) from test;

2.3、avg

该函数太简单了，就不给大家演示了

2.4、min

该函数太简单了，就不给大家演示了

2.5、max

该函数太简单了，就不给大家演示了

2.6、variance

2.7、var_sample

2.8、stddev_pop

2.9、covar_pop

2.10、covar_samp

2.11、corr

2.12、percentile

2.13、percentile_approx

2.14、regr_avgx -->Hive2.2.0

2.15、regr_avgy -->Hive2.2.0

2.16、regr_count -->Hive2.2.0

2.17、regr_intercept -->Hive2.2.0

2.18、regr_r2 -->Hive2.2.0

2.19、regr_slope -->Hive2.2.0

2.20、regr_sxx -->Hive2.2.0

2.21、regr_sxy -->Hive2.2.0

2.22、regr_syy -->Hive2.2.0

2.23、histogram_numeric

2.24、collect_set

select collect_set(id),count(1) from test;

2.25、collect_list --> Hive0.13.0

 select collect_list(id),count(1) from test;

2.26、ntile --> Hive0.11.0

select 
 id,
 ntile(1) over(partition by id),--分组内的数据切分为1份
 ntile(2) over(partition by id),--分组内的数据切分为2份
 ntile(3) over(partition by id )--分组内的数据切分为2份 
from test

三、内置UDTF函数

返回值类型	函数名称	功能描述
T	explode(ARRAYa)	将数组分解为多行。返回具有单列 (col) 的行集，数组中的每个元素对应一行。
Tkey,Tvalue	explode(MAP<Tkey,Tvalue> m)	将map分解为多行。返回一个包含两列 (key,value) 的行集，输入映射中的每个键值对对应一行。（从 Hive 0.8.0 开始。）,炸裂
int,T	posexplode(ARRAYa)	使用 int 类型的附加位置列（原始数组中项目的位置，从 0 开始）将数组分解为多行。返回一个包含两列 (pos,val) 的行集，数组中的每个元素占一行，其实就是带有序号的explode功能
T1,…,Tn	inline(ARRAYSTRUCTf1:T1,...,fn:Tn a)	将一个结构数组分解为多行。返回一个有N列的行集（N=结构中*元素的数量），每个结构在数组中都有一行。(从Hive 0.10开始）
T1,…,Tn/r	stack(int r,T1 V1,…,Tn/r Vn)	将 n 个值 V1,…,Vn 分解为 r 行。每行将有 n/r 列。r 必须是常数
string1,…,stringn	json_tuple(string jsonStr,string k1,…,string kn)	接受 JSON 字符串和一组 n 个键，并返回 n 个值的元组。这是 get_json_object UDF 的更高效版本，因为它可以通过一次调用获取多个键。
string 1,…,stringn	parse_url_tuple(string urlStr,string p1,…,string pn)	获取 URL 字符串和一组 n 个 URL 部分，并返回一个包含 n 个值的元组。这类似于 parse_url() UDF，但可以一次从 URL 中提取多个部分。有效的部分名称为：HOST、PATH、QUERY、REF、PROTOCOL、AUTHORITY、FILE、USERINFO、QUERY:。

3.1、explode

select explode(array(100,200,300));

ArraymyCol
[100,200,300]
[400,500,600]

得到的结果如下：

(int) myNewCol
100
200
300
400
500
600

3.2、posexplode

select posexplode(array('A','B','C'));

3.3、parse_url_tuple

select parse_url_tuple('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1','HOST', 'PATH', 'QUERY', 'QUERY:id');

3.5、inline

 select inline(array(struct('A',10,date '2015-01-01'),struct('B',20,date '2016-02-02')));

3.6、stack

select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01');

3.7、json_tuple

 select json_tuple('{"a":1,"b":2}','a','b','c');

UDF和UDAF和UDTF开发

UDF开发

public class UDFYear extends UDF {
  private final SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
  private final Calendar calendar = Calendar.getInstance();

  private final IntWritable result = new IntWritable();

  public UDFYear() {
  }

  public IntWritable evaluate(Text dateString) {

    if (dateString == null) {
      return null;
    }

    try {
      Date date = formatter.parse(dateString.toString());
      calendar.setTime(date);
      result.set(calendar.get(Calendar.YEAR));
      return result;
    } catch (ParseException e) {
      return null;
    }
  }

  public IntWritable evaluate(DateWritable d) {
    if (d == null) {
      return null;
    }

    calendar.setTime(d.get());
    result.set(calendar.get(Calendar.YEAR));
    return result;
  }

  public IntWritable evaluate(TimestampWritable t) {
    if (t == null) {
      return null;
    }

    calendar.setTime(t.getTimestamp());
    result.set(calendar.get(Calendar.YEAR));
    return result;
  }

}

UDAF开发

public class UDAFExampleMax extends UDAF {

  static public class MaxShortEvaluator implements UDAFEvaluator {
    private short mMax;
    private boolean mEmpty;

    public MaxShortEvaluator() {
      super();
      init();
    }

    public void init() {
      mMax = 0;
      mEmpty = true;
    }

    public boolean iterate(ShortWritable o) {
      if (o != null) {
        if (mEmpty) {
          mMax = o.get();
          mEmpty = false;
        } else {
          mMax = (short) Math.max(mMax, o.get());
        }
      }
      return true;
    }

    public ShortWritable terminatePartial() {
      return mEmpty ? null : new ShortWritable(mMax);
    }

    public boolean merge(ShortWritable o) {
      return iterate(o);
    }

    public ShortWritable terminate() {
      return mEmpty ? null : new ShortWritable(mMax);
    }
  }

  static public class MaxIntEvaluator implements UDAFEvaluator {
    private int mMax;
    private boolean mEmpty;

    public MaxIntEvaluator() {
      super();
      init();
    }

    public void init() {
      mMax = 0;
      mEmpty = true;
    }

    public boolean iterate(IntWritable o) {
      if (o != null) {
        if (mEmpty) {
          mMax = o.get();
          mEmpty = false;
        } else {
          mMax = Math.max(mMax, o.get());
        }
      }
      return true;
    }

    public IntWritable terminatePartial() {
      return mEmpty ? null : new IntWritable(mMax);
    }

    public boolean merge(IntWritable o) {
      return iterate(o);
    }

    public IntWritable terminate() {
      return mEmpty ? null : new IntWritable(mMax);
    }
  }

  static public class MaxLongEvaluator implements UDAFEvaluator {
    private long mMax;
    private boolean mEmpty;

    public MaxLongEvaluator() {
      super();
      init();
    }

    public void init() {
      mMax = 0;
      mEmpty = true;
    }

    public boolean iterate(LongWritable o) {
      if (o != null) {
        if (mEmpty) {
          mMax = o.get();
          mEmpty = false;
        } else {
          mMax = Math.max(mMax, o.get());
        }
      }
      return true;
    }

    public LongWritable terminatePartial() {
      return mEmpty ? null : new LongWritable(mMax);
    }

    public boolean merge(LongWritable o) {
      return iterate(o);
    }

    public LongWritable terminate() {
      return mEmpty ? null : new LongWritable(mMax);
    }
  }

  static public class MaxFloatEvaluator implements UDAFEvaluator {
    private float mMax;
    private boolean mEmpty;

    public MaxFloatEvaluator() {
      super();
      init();
    }

    public void init() {
      mMax = 0;
      mEmpty = true;
    }

    public boolean iterate(FloatWritable o) {
      if (o != null) {
        if (mEmpty) {
          mMax = o.get();
          mEmpty = false;
        } else {
          mMax = Math.max(mMax, o.get());
        }
      }
      return true;
    }

    public FloatWritable terminatePartial() {
      return mEmpty ? null : new FloatWritable(mMax);
    }

    public boolean merge(FloatWritable o) {
      return iterate(o);
    }

    public FloatWritable terminate() {
      return mEmpty ? null : new FloatWritable(mMax);
    }
  }

  static public class MaxDoubleEvaluator implements UDAFEvaluator {
    private double mMax;
    private boolean mEmpty;

    public MaxDoubleEvaluator() {
      super();
      init();
    }

    public void init() {
      mMax = 0;
      mEmpty = true;
    }

    public boolean iterate(DoubleWritable o) {
      if (o != null) {
        if (mEmpty) {
          mMax = o.get();
          mEmpty = false;
        } else {
          mMax = Math.max(mMax, o.get());
        }
      }
      return true;
    }

    public DoubleWritable terminatePartial() {
      return mEmpty ? null : new DoubleWritable(mMax);
    }

    public boolean merge(DoubleWritable o) {
      return iterate(o);
    }

    public DoubleWritable terminate() {
      return mEmpty ? null : new DoubleWritable(mMax);
    }
  }

  static public class MaxStringEvaluator implements UDAFEvaluator {
    private Text mMax;
    private boolean mEmpty;

    public MaxStringEvaluator() {
      super();
      init();
    }

    public void init() {
      mMax = null;
      mEmpty = true;
    }

    public boolean iterate(Text o) {
      if (o != null) {
        if (mEmpty) {
          mMax = new Text(o);
          mEmpty = false;
        } else if (mMax.compareTo(o) < 0) {
          mMax.set(o);
        }
      }
      return true;
    }

    public Text terminatePartial() {
      return mEmpty ? null : mMax;
    }

    public boolean merge(Text o) {
      return iterate(o);
    }

    public Text terminate() {
      return mEmpty ? null : mMax;
    }
  }

}

UDTF开发

public class GenericUDTFExplode extends GenericUDTF {

  private transient ObjectInspector inputOI = null;
  @Override
  public void close() throws HiveException {
  }

  @Override
  public StructObjectInspector initialize(ObjectInspector[] args) throws UDFArgumentException {
    if (args.length != 1) {
      throw new UDFArgumentException("explode() takes only one argument");
    }

    ArrayList<String> fieldNames = new ArrayList<String>();
    ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();

    switch (args[0].getCategory()) {
    case LIST:
      inputOI = args[0];
      fieldNames.add("col");
      fieldOIs.add(((ListObjectInspector)inputOI).getListElementObjectInspector());
      break;
    case MAP:
      inputOI = args[0];
      fieldNames.add("key");
      fieldNames.add("value");
      fieldOIs.add(((MapObjectInspector)inputOI).getMapKeyObjectInspector());
      fieldOIs.add(((MapObjectInspector)inputOI).getMapValueObjectInspector());
      break;
    default:
      throw new UDFArgumentException("explode() takes an array or a map as a parameter");
    }

    return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,
        fieldOIs);
  }

  private transient final Object[] forwardListObj = new Object[1];
  private transient final Object[] forwardMapObj = new Object[2];

  @Override
  public void process(Object[] o) throws HiveException {
    switch (inputOI.getCategory()) {
    case LIST:
      ListObjectInspector listOI = (ListObjectInspector)inputOI;
      List<?> list = listOI.getList(o[0]);
      if (list == null) {
        return;
      }
      for (Object r : list) {
        forwardListObj[0] = r;
        forward(forwardListObj);
      }
      break;
    case MAP:
      MapObjectInspector mapOI = (MapObjectInspector)inputOI;
      Map<?,?> map = mapOI.getMap(o[0]);
      if (map == null) {
        return;
      }
      for (Entry<?,?> r : map.entrySet()) {
        forwardMapObj[0] = r.getKey();
        forwardMapObj[1] = r.getValue();
        forward(forwardMapObj);
      }
      break;
    default:
      throw new TaskExecutionException("explode() can only operate on an array or a map");
    }
  }

  @Override
  public String toString() {
    return "explode";
  }
}

进击吧大数据

从事大数据行业多年，涉及范围包括不局限于基础支撑、计算引擎、数据整合、数据应用等多方向，参与过大型企业数仓体系建设、对数据建模、数据治理、业务增长有一定的理解;曾收获过多家一线大厂offer，目前带领团队建设企业实时数仓；欢迎大佬们一起加入交流成长

码农公寓

感谢

UDF、UDAF、UDTF的区别

UDF

UDAF

UDTF

区别总结

各版本内置函数列表

一、内置UDF函数

1.1、数学函数

1.1.1、abs

1.1.2、acos–>从Hive0.13.0

1.1.3、asin–>从Hive0.13.0

1.1.4、atan–>从Hive0.13.0

1.1.5、bin

1.1.6、bround–>Hive1.3.0

1.1.7、cbrt–>Hive1.2.0

1.1.8、ceil

1.1.9、conv

1.1.10、cos–>Hive0.13.0

1.1.11、degress–>Hive0.13.0

1.1.12、e

1.1.13、exp–>Hive0.13.0

1.1.14、factorial–>Hive1.2.0

1.1.15、floor

1.1.16、greatest–>Hive1.1.0

1.1.17、hex

1.1.18、least

1.1.19、ln–>Hive0.13.0

1.1.20、log2–>Hive0.13.0

1.1.21、log10–>Hive0.13.0

1.1.22、negative

1.1.23、pi

1.1.24、pmod

1.1.25、positive

1.1.26、pow

1.1.27、radians–>Hive0.13.0

1.1.28、rand

1.1.29、round

1.1.30、shiftleft–>Hive1.2.0

1.1.31、shiftright–>Hive1.2.0

1.1.32、shiftrightunsigned–>Hive1.2.0

1.1.33、sign–>Hive0.13.0

1.1.34、sin–>Hive0.13.0

1.1.35、sqrt–>Hive0.13.0

1.1.36、tan–>Hive0.13.0

1.1.37、unhex–>Hive0.12.0

1.1.38、width_bucket–>Hive3.0.0

1.2、集合函数

1.2.1、size

1.2.2、map_keys

1.2.3、map_values

1.2.4、array_contains

1.2.5、sort_array–>0.9.0

1.3、类型转化函数

1.3.1、binary

1.3.2、cast

1.4、日期函数

1.4.1、from_unixtime

1.4.2、unix_timestamp

1.4.3、to_date

1.4.4、year

1.4.5、quarter–>Hive1.3.0

1.4.6、month

1.4.7、day

1.4.8、hour

1.4.9、minute

1.4.10、second

1.4.11、weekofyear

1.4.12、extract–>Hive2.2.0

1.4.13、datediff

1.4.14、date_add

1.4.15、date_sub

1.4.16、from_utc_timestamp–>Hive0.8.0

1.4.17、to_utc_timestamp–>Hive0.8.0

1.4.18、current_date -->Hive1.2.0

1.4.19、current_timestamp -->Hive1.2.0

1.4.20、add_months–>Hive1.1.0

1.4.21、last_day --> Hive1.1.0

1.4.22、next_day --> Hive1.2.0