hive 内置函数

参考链接： Hive常用字符串函数

                           Hive中复合数据结构简介以及一些函数的用法说明

数学函数

下面表格中的函数为Hive的内置数学函数，大部分当参数为NULL时返回NULL。

返回类型	函数名	描述
bigint	round(double a)	返回a经过四舍五入的BIGINT 值
double	round(double a, int d)	返回a经过四舍五入，保留d位小数的DOUBLE 值
bigint	floor(double a)	返回小于等于a的最大BIGINT 值
bigint	ceil(double a), ceiling(double a)	返回大于等于a的最小BIGINT 值
double	rand(), rand(int seed)	返回一个均匀分布在0到1之间的随机数，指定seed将会确保产生的随机数序列是确定的
double	exp(double a), exp(decimal a)	返回ea
double	ln(double a), ln(decimal a)	返回a的自然对数值
double	log10(double a), log10(decimal a)	返回a的以10为底的对数值
double	log2(double a), log2(decimal a)	返回a的以2为底的对数值
double	log(double base, double a)	返回a以base为底的对数值，除了DOUBLE 类型，还可以是DECIMAL 类型，并且两种类型可以混合使用
double	pow(double a, double p), power(double a, double p)	返回ap
double	sqrt(double a), sqrt(decimal a)	返回a的平方根
string	bin(bigint a)	返回a的二进制格式
string	hex(bigint a) hex(string a) hex(binary a)	如果参数为整数或者二进制，返回十六进制的字符串，如果参数为字符串，该函数将每个字符转换为十六进制表示，并返回结果字符串
binary	unhex(string a)	将每对字符解释为十六进制并转换为该十六进制的字节表示
string	conv(bigint num, int from_base, int to_base), conv(string num, int from_base, int to_base)	将数值从一种进制表示转换为另一种进制，比如从二进制转换为十进制
double	abs(double a)	返回a的绝对值
int or double	pmod(int a, int b), pmod(double a, double b)	返回a mod b的正数值
double	sin(double a), sin(decimal a)	返回a的正弦值，a为弧度值
double	asin(double a), asin(decimal a)	如果1<=a<=-1，返回a的反正弦值，否则返回NULL
double	cos(double a), cos(decimal a)	返回a的余弦值，a为弧度值
double	acos(double a), acos(decimal a)	如果-1<=a<=1，返回a的反余弦值，否则返回NULL
double	tan(double a), tan(decimal a)	返回a的正切值，a为弧度值
double	atan(double a), atan(decimal a)	返回a的反正切值
double	degrees(double a), degrees(decimal a)	将a从弧度值转换为对应的角度值
double	radians(double a), radians(double a)	将a从角度值转换为弧度值
int or double	positive(int a), positive(double a)	返回a
int or double	negative(int a), negative(double a)	返回-a
double or int	sign(double a), sign(decimal a)	如果a为正数返回1.0，若为负数返回-1.0，否则返回0.0。参数为DECIMAL 时返回值为INT类型
double	e()	返回e的值
double	pi()	返回π的值

集合函数

下表为Hive中的集合函数。

返回类型	函数名	描述
int	size(Map<K.V>)	返回map中的元素数量
int	size(Array<T>)	返回数组中的元素数量
array<K>	map_keys(Map<K.V>)	返回未经排序的包含map中keys的数组
array<V>	map_values(Map<K.V>)	返回未经排序的包含map中values的数组
boolean	array_contains(Array<T>, value)	如果数组包含value，返回true
array<t>	sort_array(Array<T>)	将数组按照升序排序并返回

类型转换函数

下表为Hive中的类型转换函数。

返回类型	函数名	描述
binary	binary(string|binary)	将输入参数转换为binary
<type>	cast(expr as <type>)	将表达式的结果expr转换为<type>，如果转换不成功则返回NULL，例如cast(‘2014-08-09’ as date)

日期函数

下表中为Hive中的内置日期函数。

返回类型	函数名	描述
string	from_unixtime(bigint unixtime[, string format])	将从unix时间（1970-01-01 00：00：00 UTC）开始的秒数转换为表示当前系统时区的时间戳，格式为“1970-01-01 00：00：00”
bigint	unix_timestamp()	返回当前Unix时间戳表示的秒数
bigint	unix_timestamp(string date)	将格式为yyyy-MM-dd HH：mm：ss的字符串转换为Unix时间戳（秒），使用默认的时区和区域，如果失败返回0
bigint	unix_timestamp(string date, string pattern)	根据指定模式将时间字符串转换为Unix时间戳（秒），失败返回0
string	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	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	datediff(string enddate, string startdate)	返回从startdate到enddate的天数，如： datediff('2009-03-01', '2009-02-27') = 2
string	date_add(string startdate, int days)	向startdate增加指定的天数，如： date_add('2008-12-31', 1) = '2009-01-01'
string	date_sub(string startdate, int days)	从startdate减去指定的天数，如： date_sub('2008-12-31', 1) = '2008-12-30'
timestamp	from_utc_timestamp(timestamp, string timezone)	假设给定时间戳为UTC时间，并将其转换为给定时区的时间戳
timestamp	to_utc_timestamp(timestamp, string timezone)	假设给定时间戳为给定时区的时间，将其转换为UTC时间戳

条件函数

下表为Hive支持的一些条件函数。

返回类型	函数名	描述
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	如果testCondition为真，返回valueTrue，否则返回valueFalseOrNull
T	COALESCE(T v1, T v2, ...)	返回第一个不是NULL的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

字符串函数

下表为Hive内置的字符串函数。

返回类型	函数名	描述
int	ascii(string str)	返回str第一个字符串的数值
string	base64(binary bin)	将二进制参数转换为base64字符串
string	concat(string|binary A, string|binary B...)	返回将A和B按顺序连接在一起的字符串，如：concat('foo', 'bar') 返回'foobar'
array<struct<string,double>>	context_ngrams(array<array<string>>, array<string>, int K, int pf)	从一组标记化的句子中返回前k个文本
string	concat_ws(string SEP, string A, string B...)	类似concat() ，但使用自定义的分隔符SEP
string	concat_ws(string SEP, array<string>)	类似concat_ws() ，但参数为字符串数组
string	decode(binary bin, string charset)	使用指定的字符集将第一个参数解码为字符串，如果任何一个参数为null，返回null。可选字符集为： 'US_ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'
binary	encode(string src, string charset)	使用指定的字符集将第一个参数编码为binary ，如果任一参数为null，返回null
int	find_in_set(string str, string strList)	返回str在strList中第一次出现的位置，strList为用逗号分隔的字符串，如果str包含逗号则返回0，若任何参数为null，返回null。如： find_in_set('ab', 'abc,b,ab,c,def') 返回3
string	format_number(number x, int d)	将数字x格式化为'#,###,###.##'，四舍五入为d位小数位，将结果做为字符串返回。如果d=0，结果不包含小数点或小数部分
string	get_json_object(string json_string, string path)	从基于json path的json字符串中提取json对象，返回json对象的json字符串，如果输入的json字符串无效返回null。Json 路径只能有数字、字母和下划线，不允许大写和其它特殊字符
boolean	in_file(string str, string filename)	如果str在filename中以正行的方式出现，返回true
int	instr(string str, string substr)	返回substr在str中第一次出现的位置。若任何参数为null返回null，若substr不在str中返回0。Str中第一个字符的位置为1
int	length(string A)	返回A的长度
int	locate(string substr, string str[, int pos])	返回substr在str的位置pos后第一次出现的位置
string	lower(string A) lcase(string A)	返回字符串的小写形式
string	lpad(string str, int len, string pad)	将str左侧用字符串pad填充，长度为len
string	ltrim(string A)	去掉字符串A左侧的空格，如：ltrim(' foobar ')的结果为'foobar '
array<struct<string,double>>	ngrams(array<array<string>>, int N, int K, int pf)	从一组标记化的Returns the top-k 句子中返回前K个N-grams
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'
string	printf(String format, Obj... args)	将输入参数进行格式化输出
string	regexp_extract(string subject, string pattern, int index)	使用pattern从给定字符串中提取字符串。如： regexp_extract('foothebar', 'foo(.*?)(bar)', 2) 返回'bar' 有时需要使用预定义的字符类：使用'\s' 做为第二个参数将匹配s，'s'匹配空格等。参数index是Java正则匹配器方法group()方法中的索引
string	regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	使用REPLACEMENT替换字符串INITIAL_STRING中匹配PATTERN的子串，例如： regexp_replace("foobar", "oo|ar", "") 返回'fb'
string	repeat(string str, int n)	将str重复n次
string	reverse(string A)	将字符串A翻转
string	rpad(string str, int len, string pad)	在str的右侧使用pad填充至长度len
string	rtrim(string A)	去掉字符串A右侧的空格，如： rtrim(' foobar ') 返回 ' foobar'
array<array<string>>	sentences(string str, string lang, string locale)	将自然语言文本处理为单词和句子，每个句子在适当的边界分割，返回单词的数组。参数lang和local为可选参数，例如： sentences('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])	使用两个分隔符将文本分割为键值对。第一个分隔符将文本分割为K-V 对，第二个分隔符分隔每个K-V 对。默认第一个分隔符为“，“，第二个分隔符为=
string	substr(string|binary A, int start) substring(string|binary A, int start)	返回A从位置start直到结尾的子串
string	substr(string|binary A, int start, int len) substring(string|binary A, int start, int len)	返回A中从位置start开始，长度为len的子串，如： substr('foobar', 4, 1) 返回 'b'
string	translate(string input, string from, string to)	将input中出现在from中的字符替换为to中的字符串，如果任何参数为null，结果为null
string	trim(string A)	去掉字符串A两端的空格
binary	unbase64(string str)	将base64字符串转换为二进制
string	upper(string A) ucase(string A)	返回字符串A的大写形式

聚合函数

下表为Hive内置的聚合函数。

返回类型	函数名	描述
BIGINT	count(*) count(expr) count(DISTINCT expr[, expr_.])	count(*) – 返回检索到的行的总数，包括含有NULL值的行。count(expr) – 返回expr表达式不是NULL的行的数量count(DISTINCT expr[, expr]) – 返回expr是唯一的且非NULL的行的数量
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)	返回组内两个数字列的皮尔逊相关系数
DOUBLE	percentile(BIGINT col, p)	返回组内某个列精确的第p位百分数，p必须在0和1之间
array<double>	percentile(BIGINT col, array(p1 [, p2]...))	返回组内某个列精确的第p1，p2，……位百分数，p必须在0和1之间
DOUBLE	percentile_approx(DOUBLE col, p [, B])	返回组内数字列近似的第p位百分数（包括浮点数），参数B控制近似的精确度，B值越大，近似度越高，默认值为10000。当列中非重复值的数量小于B时，返回精确的百分数
array<double>	percentile_approx(DOUBLE col, array(p1 [, p2]...) [, B])	同上，但接受并返回百分数数组
array<struct {'x','y'}>	histogram_numeric(col, b)	使用b个非均匀间隔的箱子计算组内数字列的柱状图（直方图），输出的数组大小为b，double类型的(x,y)表示直方图的中心和高度
array	collect_set(col)	返回消除了重复元素的数组
array	collect_list(col)	返回允许重复元素的数组
INTEGER	ntile(INTEGER x)	该函数将已经排序的分区分到x个桶中，并为每行分配一个桶号。这可以容易的计算三分位，四分位，十分位，百分位和其它通用的概要统计

内置 Table-Generating函数（UDTF）

正常的用户定义函数，如concat，输入一个单行然后输出一个单行，但table-generating函数将一个单输入行转换为多个输出行。下表为Hive内置的table-generating函数。

返回类型	函数名	描述
N rows	explode(ARRAY)	参数列为数组类型，将数组数据中的每个元素做为一行返回
N rows	explode(MAP)	将输入map中的每个键值对转换为两列，一列为key，另一列为value，然后返回新行
	inline(ARRAY<STRUCT[,STRUCT]>)	分解struct数组到表中
Array Type	explode(array<TYPE> a)	对于数组a中的每个元素，该函数产生包含该元素的行For
元组	json_tuple(jsonStr, k1, k2, ...)	参数为一组键k1，k2……和JSON字符串，返回值的元组。该方法比 get_json_object 高效，因为可以在一次调用中输入多个键
元组	parse_url_tuple(url, p1, p2, ...)	该方法同parse_url() 相似，但可以一次性提取URL的多个部分，有效的参数名称为： HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY>
N rows	posexplode(ARRAY)	行为与参数为数组的explode方法相似，但包含项在原始数组中的位置，返回(pos,value)的二元组
	stack(INT n, v_1, v_2, ..., v_k)	将v_1, ..., v_k 分为n行，每行包含n/k列，n必须为常数

使用语法”SELECT udtf(col) AS colAlias...”有以下几点限制：

•  在SELECT中不允许再有其他表达式：不支持SELECT pageid, explode(adid_list) AS myCol...
•  UDTF不能够嵌套使用：不支持SELECT explode(explode(adid_list)) AS myCol...
• 不支持GROUP BY /CLUSTER BY / DISTRIBUTE BY / SORT BY：不支持SELECT explode(adid_list) AS myCol ... GROUP BY myCol