hive近百个函数

背景

Apache Hive是一个建立在Apache Hadoop之上的数据仓库软件项目，用于提供数据查询和分析，现支持引擎有MapReduce、Tez、Spark等等。

Hive像传统的关系型数据库一样含有大量内置函数，但也可支持UDF、UDAF等用户自定义函数编写。Hive自身支持函数的隐式转换，方便用户使用。但是这些隐式转换出现问题可能不会报错，但是也会给用户带来非期望的结果。建议大家函数按照规范来使用。

内置函数

现在分门别类地整理了日期、数值、集合、条件、字符串、聚合等内置函数的详解与举例说明等

日期函数：

• date_add 指定日期n天之后
  用法：date_add(date('2018-09-09'),10) 参数类型分别为date和int，返回date类型
  例如：

select date_add(date('2018-09-09'),10)
-- 返回：2018-09-19

• add_months 指定日期n月之后
  用法：add_months(date('2018-09-09'),2) 参数类型分别为date和int类型，返回date类型
  例如：

select add_months(date('2018-09-09'),2)   
-- 返回：2018-11-09

• datediff 计算两个日期相差天数
  用法：datediff(date('2018-09-19'),date('2018-09-09'))参数类型都为date类型，返回值int，
  例如：

select datediff(date('2018-09-19'),date('2018-09-09'))
-- 返回：10

• dayofmonth 返回日期中的日
  用法：dayofmonth(date('2018-09-09'))参数类型为date类型，返回值int
  例如：

select dayofmonth(date('2018-09-09'))  
-- 返回：9

• weekofyear 返回日期在一年中的周数
  用法：weekofyear(date('2018-09-09'))参数类型为date类型，返回值int
  例如：

select weekofyear(date('2018-09-09'))
-- 返回：36

• to_date 返回时间戳中的日期部分
  用法：to_date(cast('2018-09-09 12:00:00' as timestamp))或to_date(date('2018-09-09'))参数类型为timestamp时间戳或date类型，返回值为string
  例如：

select to_date(cast('2018-09-09 12:00:00' as timestamp))
select to_date(date('2018-09-09'))
-- 返回 ：2018-09-09

• unix_timestamp 将日期转为unix时间戳
  用法：unix_timestamp(datedate('2018-09-19'))参数类型为date类型，返回值bigint
  例如：

select unix_timestamp(date('2018-09-19'))
-- 返回：1537286400

• year 返回日期或时间戳的年份
  用法：year(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型
  例如：

select year(date('2018-09-19'))
--返回：2018

• quarter 返回日期或时间戳的季度
  用法：quarter(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型
  例如：

select quarter(date('2018-09-19'))
--返回：3

• month 返回日期或时间戳的月份
  用法：month(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型
  例如：

select quarter(date('2018-09-19'))
--返回：3

• day 返回日期或时间戳的天
  用法：day(date('2018-09-19'))参数类型为date类型或timestamp类型，返回值int类型
  例如：

select day(date('2018-09-19'))
--返回：19

• hour 返回时间戳中的小时
  用法：hour(timestamp('2009-07-30 12:58:59'))参数类型为时间戳类型，返回值int类型
  例如：

select hour(timestamp('2009-07-30 12:58:59'))
--返回：12

• minute 返回时间戳中的分钟
  用法：minute(timestamp('2009-07-30 12:58:59'))参数类型为时间戳类型，返回值int类型
  例如：

select minute(timestamp('2009-07-30 12:58:59'))
--返回：58

• second 返回时间戳中的秒数
  用法：second(timestamp('2009-07-30 12:58:59'))参数类型为时间戳类型，返回值int类型
  例如：

select second(timestamp('2009-07-30 12:58:59'))
--返回：59

• date_sub 返回指定日期n天之前日期
  用法：date_sub(date('2018-09-09'),5) 参数类型分别为date和int，返回date类型
  例如：

select date_sub(date('2018-09-09'),5)
-- 返回：2018-09-04

• current_date、current_timestamp 返回当前日期、当前时间戳
  用法：select current_date,current_timestamp 返回当前日期、当前时间戳作为字段

select current_date,current_timestamp
--返回：2018-10-092018-10-09 17:14:25.868

集合函数：

• size 返回map或array中元素的大小
  用法：size(map('A',10,'B',20,'C',30))或size(array('A',10,'B',20,'C',30))参数类型为map类型或array类型，返回值int类型
  例如：

select size(map('A',10,'B',20,'C',30)),size(array('A',10,'B',20,'C',30))
--返回：36

• map_keys 返回map类型中的keys
  用法：map_keys(map('A',10,'B',20,'C',30))参数类型为map类型，返回值array类型
  例如：

select map_keys(map('A',10,'B',20,'C',30))
--返回：["A","B","C"]

• map_values 返回map类型中的values
  用法：map_values(map('A',10,'B',20,'C',30))参数类型为map类型，返回值array类型
  例如：

select map_values(map('A',10,'B',20,'C',30))
--返回：[10,20,30]

• array_contains数组中是否包含某值，如果存在，返回true；反之，返回false
  用法：array_contains(array(10,20,30),10)参数类型分别为数组类型及数组中的元素一致的数据类型
  例如：

select array_contains(array(10,20,30),10)
--返回：true

• sort_array数组排序
  用法：sort_array(30,20,10)参数类型为数组类型，返回值为数组类型
  例如：

select sort_array(array(30,20,10))
--返回：[10,20,30]

条件函数：

• if判断函数，判断条件为true返回为true的条件值，否则返回为false的条件值
  用法：if(boolean testCondition, T valueTrue, T valueFalseOrNull)有三个参数，第一个为布尔类型表达式，第二、三参数为泛型T，返回值类型T
  例如：

select if(2>8,'A','C')
--返回：C
select if(10>8,'A','C')
--返回：A

• isnull判断输入参数是否为空，为空返回ture；反之，返回false
  用法：isnull(T a)参数类型为泛型T，返回值类型为boolean类型
  例如：

select isnull(null)
--返回：true

• isnotnull判断输入参数是否为空，为空返回ture；反之，返回false
  用法：isnull(T a)参数类型为泛型T，返回值类型为boolean类型
  例如：

select isnotnull('hello')
--返回：false

• nvl返回如果当前值为空，返回默认值
  用法：nvl(T value, T default_value)参数类型为泛型T，返回值类型为T
  例如：

select nvl(null,'a')
--返回：a
select nvl('b','c')
--返回：b

• coalesce返回第一个不为空的值
  用法：coalesce(T v1, T v2, ...),参数类型为泛型T，返回值类型为T
  例如：

select coalesce('a','b','c')
--返回：a
select coalesce(null,'b','c')
--返回：b
select coalesce( null,null,'c')
--返回：c

字符串函数：

• substr或substring从字符串中截取子字符串
  用法：substr(str1,start_index,end_index)或substring(str1,start_index,end_index)参数类型依次为string，int和int，返回值为string
  例如：

select substring('helloworld',1,5)
-- 返回 ：hello
select substring('helloworld',-3)
-- 返回 ：rld

禁止使用date和数值类型使用此函数进行字符串截取

• instr返回子字符串在字符串中位置
  用法：instr(str, substr)参数类型都为string，返回值int
  例如：

select instr('helloworld','wo')
-- 返回 ：6

• ascii返回字符串第一字母的ascii码
  用法：ascii('abcde')参数类型为string，返回值类型int
  例如：

select ascii('abcde')
--返回：97

• concat字符串连接
  用法：concat('a','b','c',....)参数类型为string，返回值类型string
  例如：

select concat('a','b','c','d','e')
--返回：abcde

• length返回字符串长度
  用法：length('abcde')参数类型string，返回类型int
  例如：

select length('abcde')
--返回：5

• lower把字符串转换为小写字符串
  用法：lower('fOoBaR')参数类型为string，返回值string
  例如：

select lower('fOoBaR')
--返回：foobar

• upper把字符串转换为小写字符串
  用法：upper('fOoBaR')参数类型为string，返回值string
  例如：

select upper('fOoBaR')
--返回：FOOBAR

• ltrim去除字符串左侧空格
  用法：ltrim(' foobar ')参数类型string，返回值string
  例如：

select ltrim(' foobar ')
--返回：'foobar '

• rtrim去除字符串右侧空格
  用法：rtrim(' foobar ')参数类型string，返回值string
  例如：

select ltrim(' foobar ')
--返回：' foobar'

• trim去除字符串左右两侧空格
  用法：rtrim(' foobar ')参数类型string，返回值string
  例如：

select ltrim(' foobar ')
--返回：'foobar'

• initcap返回字符串首字母大写
  用法：initcap('foobar')参数类型string，返回值string
  例如：

select initcap('foobar')
--返回：Foobar

• reverse返回字符串反序
  用法：reverse('foobar')参数类型string，返回值string
  例如：

select reverse('foobar')
--返回：raboof

• space返回指定n个空格字符串
  用法：space(10)参数类型int，返回值string
  例如：

select space(10)
--返回：'         '

• repeat返回字符串重复n次后的字符串
  用法：repeat('a',10)
  例如：

select repeat('a',10)
--返回：'aaaaaaaaaa'

• split将字符串按指定分隔符，拆分为数组
  用法：

select split('a,b,c,d',',')
--返回：["a","b","c","d"]

• lpad返回指定长度字符串，不足指定的长度的字符串，则用指定字符从左边补全
  用法：lpad('AAAAA',10,'d')
  例如：

select lpad('AAAAA',10,'d')
--返回：dddddAAAAA

• rpad返回指定长度字符串，不足指定的长度的字符串，则用指定字符从右边补全
  用法：rpad('AAAAA',10,'d')
  例如：

select rpad('AAAAA',10,'d')
--返回：AAAAAddddd

• regexp_extract返回被解析字符串，匹配到正则表达式中index指定组的结果
  用法：regexp_extract('foothebar', 'foo(.*?)(bar)', 2)第一个参数为被解析字符串，第二参数为正则表达式，第三参数为指定组索引index。注意：第三参数index，0代表返回全部，默认为1，索引数不能大于正则表达中括号，大于会报错。
  例如：

select regexp_extract('foothebar', 'foo(.*?)(bar)', 0)
--返回：foothebar
select regexp_extract('foothebar', 'foo(.*?)(bar)', 1)
--返回：the
select regexp_extract('foothebar', 'foo(.*?)(bar)', 0)
--返回：bar

• regexp_replace匹配正则表达式的字符串替换
  用法：regexp_replace('foobar', 'oo|ar', 'A')第一参数为目标字符串，第二参数为正则表达式，第三参数为替换字符串。
  例如：

select regexp_replace('foobar', 'oo|ar', 'A')
--返回：fAbA

• get_json_object依据json路径从json字符串提取json对象
  用法：select get_json_object(json,'$.id')
  例如：

select get_json_object(json,'$.id')
--返回：0001

• str_to_map用两个分隔符将文本拆分为key-value键值对
  用法：str_to_map(text, delimiter1, delimiter2)第一参数为拆分文本，第二参数为拆分key-values，第三参数把key-values拆分为key和value，返回值类型map
  例如：

select str_to_map('a:1,b:2,c:3,d:4',',',':')
--返回：{"b":"2","a":"1","d":"4","c":"3"}

数值函数：

• negative返回数值相反数
  用法：negative(value)返回值-value
  例如：

select negative(3)
-- 返回 ：-3
select negative(-3)
-- 返回 ：3

• round(double a)返回数值类型四舍五入的结果
  用法：round(4.5)输入参数为double类型，返回值double类型
  例如：

select round(4.5)
--返回：5.0

• round(double a, INT d)返回数值类型四舍五入的结果，并指定保留小数点位数
  用法：round(4.5555,3)第一参数double类型，第二参数int类型
  例如：

select round(4.5555,3)
--返回：4.556

• floor返回小于或等于该数值参数的最大整数
  用法：floor(9.6)参数为double类型，返回值类型bigint
  例如：

select floor(9.6)
--返回：9

• ceil或ceiling返回大于或等于该数值参数的最小整数
  用法：ceil(9.6)参数为double类型，返回值类型bigint
  例如：

select ceil(9.6)
--返回：10

• rand(), rand(INT seed)返回一个0到1范围内的随机数。如果指定种子seed，则得到一个稳定的随机数序列。
  用法：rand()或rand(100)返回一个0到1范围内Doubel类型的随机数
  例如：

select rand()
--返回：0.311864052663654
select rand()
--返回：0.48138097605120345
select rand(100)
--返回：0.7220096548596434
select rand(100)
--返回：0.7220096548596434
--rand(100) 两次执行返回结果相同

• exp自然指数函数返回e的n次方
  用法：exp(double n)或exp(decimal n)参数类型double或decimal类型，返回值double类型
  例如：

select exp(1)
--返回：2.718281828459045
select exp(10)
--返回：22026.465794806718

• ln返回以e为底log对数
  用法：ln(double n)或ln(Decimal n)参数类型double或decimal类型，返回值double类型
  例如：

select ln(2.718281828459045)
--返回：1.0
select ln(22026.465794806718)
--返回：10.0

• log10返回以10为底log对数
  用法：log10(double n)或log10(Decimal n)参数类型double或decimal类型，返回值double类型
  例如：

select log10(10)
--返回：1.0
select log10(1000)
--返回：3.0

• log2返回以2为底log对数
  用法：log2(double n)或log2(Decimal n)参数类型double或decimal类型，返回值double类型
  例如：

select log2(2)
--返回：1.0
select log2(8)
--返回：3.0

• pow或power返回a的n次方
  用法：pow(double a,double n)参数类型都为double类型，返回值double类型
  例如：

select pow(2,3)
--返回：8.0
select pow(10,3)
--返回：1000.0

• sqrt返回平方根
  用法：sqrt(double a)参数类型double类型，返回值double类型
  例如：

select sqrt(4)
--返回：2.0
select sqrt(100)
--返回：10.0

• abs求绝对值
  用法：abs(double a)参数类型double类型，返回值double类型
  例如：

select abs(-100)
--返回：100
select abs(10)
--返回：10

• sin正弦函数
  用法：sin(double a)或sin(Decimal a)，返回值double类型
  例如：

select sin(0.5235987755982989)
--返回：0.5

• asin反正弦函数
  用法：asin(double a)或asin(Decimal a)，返回值double类型
  例如：

select asin(0.5)
--返回：0.5235987755982989

• cos余弦函数
  用法：cos(double a)或cos(Decimal a)，返回值double类型
  例如：

select cos(1.0471975511965979)
--返回：0.4999999999999999

• acos反余弦函数
  用法：acos(double a)或acos(Decimal a)，返回值double类型
  例如：

select acos(0.4999999999999999)
--返回：1.0471975511965979

• tan正切函数
  用法：tan(double a)或tan(Decimal a)，返回值double类型
  例如：

select tan(0.5235987755982989)
--返回：0.5773502691896258

• atan正切函数
  用法：atan(double a)或atan(Decimal a)，返回值double类型
  例如：

select atan(0.5773502691896258)
--返回：0.5235987755982989

• degrees弧度值转换为度数值
  用法：degrees(double a)或degrees(Decimal a)，返回值double类型
  例如：

select degrees(0.5235987755982989)
--返回：30.000000000000004

• radians度数值转换为弧度值
  用法：radians(double a)或radians(Decimal a)，返回值double类型
  例如：

select radians(30)
--返回：0.5235987755982989

• pi返回派常量
  用法：pi() 返回值double类型
  例如：

select pi()
--返回：3.141592653589793

• e返回Euler的e常量
  用法：e()返回值double类型
  例如：

select e()
--返回：2.718281828459045

• greatest返回指定参数列表中最大值
  用法：greatest(T v1, T v2, …)返回值类型T
  例如：

select greatest(1,10,4,5,9)
--返回：10

• least返回指定参数列表中最小值
  用法：least(T v1, T v2, ...)返回值类型T
  例如：

select least(1,10,4,5,9)
--返回：1

• sign返回数值符号类型，如果参数大于0，返回1.0；小于0，返回-1.0；等于0，返回0.0
  用法：sign(double a)或sign(Decimal a)，返回值double类型
  例如：

select sign(100.0)
--返回：1.0
select sign(-100.0)
--返回：-1.0
select sign(0.0)
--返回：0.0

• positive返回返回参数自身的函数
  用法：positive(double a)或positive(int a)，返回值double类型或int类型
  例如：

select sign(100)
--返回：100.0
select sign(-100)
--返回：-100

聚合函数：

假设有t表，数据如下： 

id	col1	col2
A	1	4
A	2	5
B	3	6

• count返回总记录行数
  用法：count(*) 或 count(distinct col1)返回值为bigint类型
  例如：

select count(*) from t
--返回：3

• sum返回分组组内元素汇总值
  用法：sum(col1)或sum(distinct col1) 返回类型为double类型
  例如：

select id,sum(col1) from t group by id
--返回:
A 3
B 3

• avg返回分组组内元素平均值
  用法：avg(col1)或avg(distinct col1) 返回类型为double类型
  例如：

select id,avg(col1) from t group by id
--返回:
A 1.5
B 3

• min返回分组组内元素最小值
  用法：min(col1)返回类型为double类型
  例如：

select id,min(col1) from t group by id
--返回:
A 1
B 3

• max返回分组组内元素最小值
  用法：max(col1)返回类型为double类型
  例如：

select id,max(col1) from t group by id
--返回:
A 2
B 3

• var_pop(col1)返回分组组内元素的方差
  用法：var_pop(col1)返回类型为double类型
  例如：

select id,var_pop(col1) from t group by id
--返回：
A0.25
B0

• var_samp(col1)返回分组组内元素的无偏样本方差
  用法：var_samp(col1)返回类型为double类型
  例如：

select id,var_samp(col1) from t group by id
--返回：
A0.5
B0

• stddev_pop(col1)返回分组组内元素的标准差
  用法：stddev_pop(col1)返回类型为double类型
  例如：

select id,stddev_pop(col1) from t group by id
--返回：
A0.5
B0

• stddev_samp(col1)返回分组组内元素的无偏样本标准差
  用法：stddev_samp(col1)返回类型为double类型
  例如：

select id,stddev_samp(col1) from t group by id
--返回：
A0.7071067811865476
B0

• covar_pop(col1, col2)返回分组组内一对元素的总体协方差
  用法：covar_pop(col1, col2)返回类型为double类型
  例如：

select id,covar_pop(col1, col2) from t group by id
--返回：
A0.25
B0

• covar_samp(col1, col2)返回分组组内一对元素的样本协方差
  用法：covar_samp(col1, col2)返回类型为double类型
  例如：

select id,covar_samp(col1, col2) from t group by id
--返回：
A0.5
B0

• corr返回分组组内一对元素的皮尔逊相关系数
  用法：corr(col1, col2)返回类型为double类型
  例如：

select id,corr(col1, col2) from t group by id
--返回：
A0.9999999999999999
BNULL

总结

这里Hive整理的是日常常用的函数，同时这也是Presto有的函数，只是函数名称、字段类型、参数的顺序不同而已。但是Presto这些函数都要求规范的数据类型，不然会报错的。