hive内置函数大全

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 Hive 1.3.0, 2.0.0). 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 Hive 1.3.0, 2.0.0). 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 seed)|Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifying the seed will make sure the generated random number sequence is deterministic.每行返回一个DOUBLE型随机数seed是随机因子
DOUBLE|exp(DOUBLE a), exp(DECIMAL a)|Returns ea where e is the base of the natural logarithm. Decimal version added in Hive 0.13.0.返回e的a幂次方， a可为小数
DOUBLE|ln(DOUBLE a), ln(DECIMAL a)|Returns the natural logarithm of the argument a. Decimal version added in Hive 0.13.0.以自然数为底d的对数，a可为小数
DOUBLE|log10(DOUBLE a), log10(DECIMAL a)|Returns the base-10 logarithm of the argument a. Decimal version added in Hive 0.13.0.以10为底d的对数，a可为小数
DOUBLE|log2(DOUBLE a), log2(DECIMAL a)|Returns the base-2 logarithm of the argument a. Decimal version added in Hive 0.13.0.以2为底数d的对数，a可为小数
DOUBLE|log(DOUBLE base, DOUBLE a)log(DECIMAL base, DECIMAL a)|Returns the base-base logarithm of the argument a. Decimal versions added in Hive 0.13.0.以base为底的对数，base 与 a都是DOUBLE类型
DOUBLE|pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)|Returns ap.计算a的p次幂
DOUBLE|sqrt(DOUBLE a), sqrt(DECIMAL a)|Returns the square root of a. Decimal version added in Hive 0.13.0.计算a的平方根
STRING|bin(BIGINT a)|Returns the number in binary format (see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_bin).计算二进制a的STRING类型，a为BIGINT类型
STRINGhex(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. Otherwise if the number is a STRING, it converts each character into its hexadecimal representation and returns the resulting STRING. (Seehttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex, BINARY version as of Hive 0.12.0.)计算十六进制a的STRING类型，如果a为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. Otherwise if the number is a STRING, it converts each character into its hexadecimal representation and returns the resulting STRING. (Seehttp://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex, BINARY version as of Hive 0.12.0.)计算十六进制a的STRING类型，如果a为STRING类型就转换成字符相对应的十六进制
BINARY|unhex(STRING a)|Inverse of hex. Interprets each pair of characters as a hexadecimal number and converts to the byte representation of the number. (BINARY version as of Hive 0.12.0, used to return a string.)hex的逆方法
STRING|conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)|Converts a number from a given base to another (see http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv).将GIGINT/STRING类型的num从from_base进制转换成to_base进制
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 Hive 0.13.0.求a的正弦值
DOUBLE|asin(DOUBLE a), asin(DECIMAL a)|Returns the arc sin of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0.求d的反正弦值
DOUBLE|cos(DOUBLE a), cos(DECIMAL a)|Returns the cosine of a (a is in radians). Decimal version added in Hive 0.13.0.求余弦值
DOUBLE|acos(DOUBLE a), acos(DECIMAL a)|Returns the arccosine of a if -1<=a<=1 or NULL otherwise. Decimal version added in Hive 0.13.0.求反余弦值
DOUBLE|tan(DOUBLE a), tan(DECIMAL a)|Returns the tangent of a (a is in radians). Decimal version added in Hive 0.13.0.求正切值
DOUBLE|atan(DOUBLE a), atan(DECIMAL a)|Returns the arctangent of a. Decimal version added in Hive 0.13.0.求反正切值
DOUBLE|degrees(DOUBLE a), degrees(DECIMAL a)|Converts value of a from radians to degrees. Decimal version added in Hive 0.13.0.奖弧度值转换角度值
DOUBLE|radians(DOUBLE a), radians(DOUBLE a)|Converts value of a from degrees to radians. Decimal version added in Hive 0.13.0.将角度值转换成弧度值
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' otherwise. The decimal version returns INT instead of DOUBLE. Decimal version added in Hive 0.13.0.如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
DOUBLE|e()|Returns the value of e.数学常数e
DOUBLE|pi()|Returns the value of pi.数学常数pi
BIGINT|factorial(INT a)|Returns the factorial of a (as of Hive 1.2.0). Valid a is [0..20].求a的阶乘
DOUBLE|cbrt(DOUBLE a)|Returns the cube root of a double value (as of Hive 1.2.0).求a的立方根
 INT BIGINT|shiftleft(TINYINT|SMALLINT|INT a, INT b)shiftleft(BIGINT a, INT b)|Bitwise left shift (as of Hive 1.2.0). Shifts a b positions to the left.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.按位左移
INTBIGINT|shiftright(TINYINT|SMALLINT|INT a, INTb)shiftright(BIGINT a, INT b)|Bitwise right shift (as of Hive 1.2.0). Shifts a b positions to the right.Returns int for tinyint, smallint and int a. Returns bigint for bigint a.按拉右移
INTBIGINT|shiftrightunsigned(TINYINT|SMALLINT|INTa, INT b),shiftrightunsigned(BIGINT a, INT b)|Bitwise unsigned right shift (as of Hive 1.2.0). 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 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with '>' operator (as of Hive 2.0.0).求最大值
T|least(T v1, T v2, ...)|Returns the least value of the list of values (as of Hive 1.1.0). Fixed to return NULL when one or more arguments are NULL, and strict type restriction relaxed, consistent with '<' operator (as of Hive 2.0.0).求最小值
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。，否则返回false
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 0.9.0).按自然顺序对数组进行排序并返回
Return Type|Name(Signature)|
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 representation. 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)|
string|from_unixtime(bigint unixtime[, string format])|Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing 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 seconds.获取本地时区下的时间戳
bigint|unix_timestamp(string date)|Converts time string in format yyyy-MM-dd HH:mm:ss to Unix timestamp (in seconds), 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 (see [http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html]) to Unix time stamp (in seconds), 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 1.3.0). 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|second(string date)|Returns the second of the timestamp.返回时间字符串的秒
int|weekofyear(string date)|Returns the week numb