Hive2.0函数⼤全（中⽂版）
摘要
Hive内部提供了很多函数给开发者使⽤，包括数学函数，类型转换函数，条件函数，字符函数，聚合函数，表⽣成函数等等，这些函数都统称为内置函数。
⽬录
数学函数
Return
Type
Name (Signature)Description
DOUBLE round(DOUBLE a)
Returns the rounded BIGINT value of a.
返回对a四舍五⼊的BIGINT值
DOUBLE round(DOUBLE a, INT d)
Returns a rounded to d decimal places.
返回DOUBLE型d的保留n位⼩数的DOUBLW型的近似值
DOUBLE bround(DOUBLE a)Returns the rounded BIGINT value of a using HALF_EVEN rounding mode (as of ). Also known as Gaussian rounding or bankers' rounding. Example: bround(2.5) = 2, bround(3.5) = 4.
银⾏家舍⼊法（1~4：舍，6~9：进，5->前位数是偶：舍，5->前位数是奇：进）
DOUBLE bround(DOUBLE a, INT d)Returns a rounded to d decimal places using HALF_EVEN rounding mode (as of ). Example: bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4.
银⾏家舍⼊法，保留d位⼩数
BIGINT floor(DOUBLE a)
Returns the maximum BIGINT value that is equal to or less than a
向下取整，最数轴上最接近要求的值的左边的值如：6.10->6 -3.4->-4 BIGINT ceil(DOUBLE a), ceiling(DOUBLE a)
Returns the minimum BIGINT value that is equal to or greater than a.
求其不⼩于⼩给定实数的最⼩整数如：ceil(6) = ceil(6.1)= ceil(6.9) = 6
DOUBLE rand(), rand(INT ed)Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the ed will make sure the generated random number quence is deterministic.
每⾏返回⼀个DOUBLE型随机数ed是随机因⼦猫的图片大全
DOUBLE exp(DOUBLE a), exp(DECIMAL a)
Returns e a where e is the ba of the natural logarithm. Decimal version added in .
返回e的a幂次⽅， a可为⼩数
DOUBLE ln(DOUBLE a), ln(DECIMAL a)
Returns the natural logarithm of the argument a. Decimal version added in .
以⾃然数为底d的对数，a可为⼩数
DOUBLE log10(DOUBLE a), log10(DECIMAL a)
Returns the ba-10 logarithm of the argument a. Decimal version added in .
以10为底d的对数，a可为⼩数
DOUBLE log2(DOUBLE a), log2(DECIMAL a)Returns the ba-2 logarithm of the argument a. Decimal version added in .以2为底数d的对数，a可为⼩数
礼记大学DOUBLE log(DOUBLE ba, DOUBLE a)
log(DECIMAL ba, DECIMAL a)
Returns the ba-ba logarithm of the argument a. Decimal versions added in .
以ba为底的对数，ba 与 a都是DOUBLE类型
DOUBLE pow(DOUBLE a, DOUBLE p),
power(DOUBLE a, DOUBLE p)
Returns a p.
计算a的p次幂
power(DOUBLE a, DOUBLE p)计算a的p次幂
DOUBLE sqrt(DOUBLE a), sqrt(DECIMAL a)
Returns the square root of a. Decimal version added in .
计算a的平⽅根
STRING bin(BIGINT a)
Returns the number in binary format (e ).
计算⼆进制a的STRING类型，a为BIGINT类型
STRING hex(BIGINT a) hex(STRING a) hex(BINARY
a)
If the argument is an INT or binary, hex returns the number as a STRING in
hexadecimal format. Otherwi if the number is a STRING, it converts each
character into its hexadecimal reprentation and returns the resulting STRING.
(See, BINARY version as of Hive .)
计算⼗六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的⼗六
进制
BINARY unhex(STRING a)Inver of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte reprentation of the number. (BINARY version as of Hive , ud to return a string.)
hex的逆⽅法
STRING conv(BIGINT num, INT from_ba, INT
to_ba), conv(STRING num, INT
from_ba, INT to_ba)
Converts a number from a given ba to another (e ).
将GIGINT/STRING类型的num从from_ba进制转换成to_ba进制
DOUBLE abs(DOUBLE a)
Returns the absolute value.
计算a的绝对值
INT or DOUBLE pmod(INT a, INT b), pmod(DOUBLE a,
DOUBLE b)
无氧运动有哪些项目Returns the positive value of a mod b.
a对b取模
DOUBLE sin(DOUBLE a), sin(DECIMAL a)
Returns the sine of a (a is in radians). Decimal version added in .
求a的正弦值
DOUBLE asin(DOUBLE a), asin(DECIMAL a)
Returns the arc sin of a if -1<=a<=1 or NULL otherwi. Decimal version added in .
求d的反正弦值
DOUBLE cos(DOUBLE a), cos(DECIMAL a)
Returns the cosine of a (a is in radians). Decimal version added in .
求余弦值
DOUBLE acos(DOUBLE a), acos(DECIMAL a)Returns the arccosine of a if -1<=a<=1 or NULL otherwi. Decimal version added in .
求反余弦值
DOUBLE tan(DOUBLE a), tan(DECIMAL a)
Returns the tangent of a (a is in radians). Decimal version added in .
求正切值
DOUBLE atan(DOUBLE a), atan(DECIMAL a)
Returns the arctangent of a. Decimal version added in .
求反正切值
DOUBLE degrees(DOUBLE a), degrees(DECIMAL a)
Converts value of a from radians to degrees. Decimal version added in .
奖弧度值转换⾓度值
DOUBLE radians(DOUBLE a), radians(DOUBLE a)
Converts value of a from degrees to radians. Decimal version added in .
将⾓度值转换成弧度值
INT or DOUBLE positive(INT a), positive(DOUBLE a)
Returns a.
返回a
INT or DOUBLE negative(INT a), negative(DOUBLE a)Returns -a.
返回a的相反数
DOUBLE or INT sign(DOUBLE a), sign(DECIMAL a)
Returns the sign of a as '1.0' (if a is positive) or '-1.0' (if a is negative), '0.0'
otherwi. The decimal version returns INT instead of DOUBLE. Decimal version
added in .
如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
DOUBLE e()
Returns the value of e.
数学常数e DOUBLE pi()
Returns the value of pi.
数学常数pi
数学常数pi
BIGINT factorial(INT a)Returns the factorial of a (as of Hive ). Valid a is [0..20].求a的阶乘
DOUBLE cbrt(DOUBLE a)Returns the cube root of a double value (as of Hive ).求a的⽴⽅根
INT BIGINT shiftleft(TINYINT|SMALLINT|INT a, INT b)
shiftleft(BIGINT a, INT b)
Bitwi left shift (as of Hive ). Shifts a b positions to the left.
小孩咬指甲
Returns int for tinyint, smallint and int a. Returns bigint for bigint a.
按位左移
INT BIGINT shiftright(TINYINT|SMALLINT|INT a, INTb)
shiftright(BIGINT a, INT b)
Bitwi right shift (as of Hive ). Shifts a b positions to the right.
Returns int for tinyint, smallint and int a. Returns bigint for bigint a.
按拉右移
INT BIGINT shiftrightunsigned(TINYINT|SMALLINT|INTa,
INT b),
shiftrightunsigned(BIGINT a, INT b)
Bitwi unsigned right shift (as of Hive ). Shifts a b positions to the right.
Returns int for tinyint, smallint and int a. Returns bigint for bigint a.
⽆符号按位右移（<<<）
T greatest(T v1, T v2, ...)Returns the greatest value of the list of values (as of Hive ). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with ">" operator (as of Hive ).
求最⼤值
T least(T v1, T v2, ...)Returns the least value of the list of values (as of Hive ). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with "<" operator (as of Hive ).
求最⼩值
葱爆鸡丁集合函数
Return
Type
Name(Signature)Description
int size(Map<K.V>)
Returns the number of elements in the map type.
求map的长度
int size(Array<T>)
Returns the number of elements in the array type.
求数组的长度
array<K>map_keys(Map<K.V>)
Returns an unordered array containing the keys of the input map.
返回map中的所有key
array<V>map_values(Map<K.V>)
Returns an unordered array containing the values of the input map.
离婚手续如何办理返回map中的所有value
boolean array_contains(Array<T>,
value)
Returns TRUE if the array contains value.
夸女人漂亮的词语如该数组Array<T>包含value返回true。，否则返回fal
array sort_array(Array<T>)Sorts the input array in ascending order according to the natural ordering of the array elements and returns it (as of version ).
按⾃然顺序对数组进⾏排序并返回
类型转换函数
Return Type Name(Signature)Description binary binary(string|binary)
Casts the parameter into a binary.
将输⼊的值转换成⼆进制
Expected "=" to follow "type"cast(expr as
<type>)
Converts the results of the expression expr to <type>. For example, cast('1' as BIGINT) will convert the
string '1' to its integral reprentation. A null is returned if the conversion does not succeed. If cast(expr
as boolean) Hive returns true for a non-empty string.
将expr转换成type类型如：cast("1" as BIGINT) 将字符串1转换成了BIGINT类型，如果转换失败将返回
NULL
⽇期函数
Return
Type
Name(Signature)Description
string from_unixtime(bigint unixtime[,
string format])
Converts the number of conds from unix epoch (1970-01-01 00:00:00 UTC) to a string
reprenting the timestamp of that moment in the current system time zone in the format of
"1970-01-01 00:00:00".
将时间的秒值转换成format格式（format可为“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-
MM-dd hh:mm”等等）如from_unixtime(1250111000,"yyyy-MM-dd") 得到2009-03-12
bigint unix_timestamp()
Gets current Unix timestamp in conds.
获取本地时区下的时间戳
bigint unix_timestamp(string date)Converts time string in format yyyy-MM-dd HH:mm:ss to Unix timestamp (in conds), using the default timezone and the default locale, return 0 if fail: unix_timestamp('2009-03-20
11:30:01') = 1237573801
将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳如unix_timestamp('2009-03-20 11:30:01') = 1237573801
bigint unix_timestamp(string date, string
pattern)
Convert time string with given pattern (e []) to Unix time stamp (in conds), return 0 if fail:
unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400.
将指定时间字符串格式字符串转换成Unix时间戳，如果格式不对返回0 如：
unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400
string to_date(string timestamp)
Returns the date part of a timestamp string: to_date("1970-01-01 00:00:00") = "1970-01-01".
返回时间字符串的⽇期部分
int year(string date)Returns the year part of a date or a timestamp string: year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970.
返回时间字符串的年份部分
int quarter(date/timestamp/string)Returns the quarter of the year for a date, timestamp, or string in the range 1 to 4 (as of Hive ). Example: quarter('2015-04-08') = 2.
返回当前时间属性哪个季度如quarter('2015-04-08') = 2
int month(string date)Returns the month part of a date or a timestamp string: month("1970-11-01 00:
00:00") = 11, month("1970-11-01") = 11.
返回时间字符串的⽉份部分
int day(string date) dayofmonth(date)Returns the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1.
返回时间字符串的天
int hour(string date)
Returns the hour of the timestamp: hour('2009-07-30 12:58:59') = 12, hour('12:58:59') = 12.
返回时间字符串的⼩时
int minute(string date)
Returns the minute of the timestamp.
返回时间字符串的分钟
int cond(string date)
Returns the cond of the timestamp.
返回时间字符串的秒
int weekofyear(string date)Returns the week number of a timestamp string: weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44.
返回时间字符串位于⼀年中的第⼏个周内如weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44
int datediff(string enddate, string
startdate)
Returns the number of days from startdate to enddate: datediff('2009-03-01', '2009-02-27') =
2.
计算开始时间startdate到结束时间enddate相差的天数
string date_add(string startdate, int
days)
Adds a number of days to startdate: date_add('2008-12-31', 1) = '2009-01-01'.
从开始时间startdate加上days
string date_sub(string startdate, int
days)
Subtracts a number of days to startdate: date_sub('2008-12-31', 1) = '2008-12-30'.
从开始时间startdate减去days
timestamp from_utc_timestamp(timestamp,
string timezone)
Assumes given timestamp is UTC and converts to given timezone (as of Hive ). For
example, from_utc_timestamp('1970-01-01 08:00:00','PST') returns 1970-01-01 00:00:00.
如果给定的时间戳并⾮UTC，则将其转化成指定的时区下时间戳
timestamp to_utc_timestamp(timestamp,借代手法
string timezone)
Assumes given timestamp is in given timezone and converts to UTC (as of Hive ). For
example, to_utc_timestamp('1970-01-01 00:00:00','PST') returns 1970-01-01 08:00:00.
如果给定的时间戳指定的时区下时间戳，则将其转化成UTC下的时间戳
date current_date Returns the current date at the start of query evaluation (as of Hive ). All calls of current_date within the same query return the same value.
返回当前时间⽇期
timestamp current_timestamp Returns the current timestamp at the start of query evaluation (as of Hive ). All calls of current_timestamp within the same query return the same value.
返回当前时间戳
string add_months(string start_date, int
num_months)
Returns the date that is num_months after start_date (as of Hive ). start_date is a string, date
or timestamp. num_months is an integer. The time part of start_date is ignored. If start_date
is the last day of the month or if the resulting month has fewer days than the day component
of start_date, then the result is the last day of the resulting month. Otherwi, the result has
the same day component as start_date.
返回当前时间下再增加num_months个⽉的⽇期
string last_day(string date)Returns the last day of the month which the date belongs to (as of Hive ). date is a string in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'. The time part of date is ignored.
返回这个⽉的最后⼀天的⽇期，忽略时分秒部分（HH:mm:ss）
string next_day(string start_date, string
day_of_week)
Returns the first date which is later than start_date and named as day_of_week (as of
Hive). start_date is a string/date/timestamp. day_of_week is 2 letters, 3 letters or full name of
the day of the week (e.g. Mo, tue, FRIDAY). The time part of start_date is ignored. Example:
next_day('2015-01-14', 'TU') = 2015-01-20.
返回当前时间的下⼀个星期X所对应的⽇期如：next_day('2015-01-14', 'TU') = 2015-01-20
以2015-01-14为开始时间，其下⼀个星期⼆所对应的⽇期为2015-01-20
string trunc(string date, string format)Returns date truncated to the unit specified by the format (as of Hive ). Supported formats: MONTH/MON/MM, YEAR/YYYY/YY. Example: trunc('2015-03-17', 'MM') = 2015-03-01.返回时间的最开始年份或⽉份如trunc("2016-06-26",“MM”)=2016-06-01 trunc("2016-06-26",“YY”)=2016-01-01 注意所⽀持的格式为MONTH/MON/MM, YEAR/YYYY/YY
double months_between(date1, date2)Returns number of months between dates date1 and date2 (as of Hive ). If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwi the UDF calculates the fractional portion of the result bad on a 31-day month and considers the difference in time components date1 and date2. date1 and date2 type can be date, timestamp or string in the format 'yyyy-MM-dd' or 'yyyy-MM-dd HH:mm:ss'. The result is rounded to 8 decimal places. Example:
months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677
返回date1与date2之间相差的⽉份，如date1>date2，则返回正，如果date1<date2，则返回负，否则返回0.0 如：months_between('1997-02-28 10:30:00', '1996-10-30') = 3.94959677 1997-02-28 10:30:00与1996-10-30相差3.94959677个⽉
string date_format(date/timestamp/string
ts, string fmt)
Converts a date/timestamp/string to a value of string in the format specified by the date
format fmt (as of Hive ). Supported formats are Java SimpleDateFormat formats –. The
cond argument fmt should be constant. Example: date_format('2015-04-08', 'y') = '2015'.
date_format can be ud to implement other UDFs, e.g.:
dayname(date) is date_format(date, 'EEEE')
dayofyear(date) is date_format(date, 'D')
按指定格式返回时间date 如：date_format("2016-06-22","MM-dd")=06-22
Return
Type
Name(Signature)Description
条件函数
Return
Type
Name(Signature)Description
T if(boolean testCondition, T
valueTrue, T
valueFalOrNull)
Returns valueTrue when testCondition is true, returns valueFalOrNull otherwi.
如果testCondition 为true就返回valueTrue，否则返回valueFalOrNull ，
（valueTrue，valueFalOrNull为泛型）
Returns default value if value is null el returns value (as of HIve ).
