Hive function reference manual

See: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

1. built-in Operators
1.1 Relational operators

Operator	Type	Description
A = B	All original types	True is returned if A is equal to B. Otherwise, false is returned.
A = B	None	Failed because of invalid syntax. Use "=" for SQL, not "=" for SQL ".
A <> B	All original types	If a is not equal to B, true is returned; otherwise, false is returned. If A or B is set to "null", return "null ".
A <B	All original types	If a is less than B, true is returned; otherwise, false is returned. If A or B is set to "null", return "null ".
A <= B	All original types	If a is less than or equal to B, true is returned; otherwise, false is returned. If A or B is set to "null", return "null ".
A> B	All original types	If a is greater than B, true is returned; otherwise, false is returned. If A or B is set to "null", return "null ".
A> = B	All original types	If a is greater than or equal to B, true is returned. Otherwise, false is returned. If A or B is set to "null", return "null ".
A is null	All Types	True is returned if A is null. Otherwise, false is returned.
A is not null	All Types	If the value of A is not "null", true is returned; otherwise, false is returned.
A like B	String	If A or B is set to "null", return "null ". String A and string B are matched through SQL. True is returned if they match, and false is returned if they do not match. "_" In string B represents any character, and "%" represents Multiple Arbitrary characters. For example, ('foobar' like 'foo') returns false, ('foobar' like 'foo _ 'or 'foobar' like 'foo %') returns true.
A rlike B	String	If A or B is set to "null", return "null ". String A and string B are matched through java. If they match, true is returned, false is returned. For example, ('foobar' rlike 'foo') returns false, ('foobar' rlike '^ F. * r$') returns true.
A Regexp B	String	Same as rlike.

1.2 Arithmetic Operators

Operator	Type	Description
A + B	All numeric types	A and B are added together. The result is of the same type as the operation value. For example, each integer is a floating point, and the floating point contains an integer. Therefore, the result of adding a floating point number and an integer is also a floating point number.
A-B	All numeric types	A and B subtract. The result is of the same type as the operation value.
A * B	All numeric types	A and B are multiplied. The result is of the same type as the operation value. It should be noted that if multiplication causes overflow, a higher type will be selected.
A/B	All numeric types	The result of division A and B is a double (double) type.
A % B	All numeric types	The remainder of a divided by B is of the same type as the operation value.
A & B	All numeric types	Operator to view the values of the two parameters in binary notation, and perform the bitwise AND operation. When one of the two expressions is 1, the result is 1. Otherwise, the result is 0.
A | B	All numeric types	Operator to view the values of the two parameters in binary notation and perform the bitwise OR operation. If one of the expressions is 1, the result is 1. Otherwise, the result is 0.
A ^ B	All numeric types	Operator to view the values of the two parameters in binary notation, and perform the bitwise "XOR" operation. If only one expression is 1, the result is 1. Otherwise, the result is 0.
~ A	All numeric types	Execute bitwise "Non" (inverse) on an expression ).

1.3 logical operators

Operator	Type	Description
A and B	Boolean Value	If both A and B are correct, true is returned; otherwise, false is returned. If A or B is null, null is returned.
A & B	Boolean Value	Same as "A and B"
A or B	Boolean Value	If A or B is correct, or both are correct, true is returned; otherwise, false is returned. If both A and B are null, null is returned.
A | B	Boolean Value	Same as "A or B"
Not	Boolean Value	If expression A is null or incorrect, true is returned. Otherwise, false is returned.
! A	Boolean Value	Same as "not"

1.4 Complex Type Functions

Function	Type	Description
Map	(Key1, value1, key2, value2 ,...)	Creates a map using the specified key/value pair.
Struct	(Val1, val2, val3 ,...)	Creates a structure using the specified field value. The structure field name will be col1, col2 ,...
Array	(Val1, val2 ,...)	Creates an array using the specified element.

1.5 complex type function operations

Function	Type	Description
A [n]	A is an array, and N is int type.	Returns the nth element of array A. The index of the first element is 0. If array a is ['foo', 'bar'], a [0] returns 'foo' and a [1], and returns "bar ".
M [Key]	M is the key K type of Map <K, V>.	Return the value corresponding to the key value. For example, mapm is \ {'F'-> 'foo', 'B'-> 'bar ', 'all'-> 'foobar' \}, M ['all'] returns 'foobar '.
S. x	S is struct	Returns the storage location of the X string in structure S. For example, the integer stored in the field foobar \ {int Foo, int bar \} foobar. Foo.

2. built-in functions
2.1 mathematical functions

Return type	Function	Description
Bigint	Round (double)	Rounding
Double	Round (double A, int D)	After the decimal part is d, the number is rounded in, for example, round (21.263, 2), 21.26 is returned.
Bigint	Floor (double)	Round down the given data to the nearest integer. For example floor (21.2), return 21.
Bigint	Ceil (double A), ceiling (double)	Rounds up the parameter to the nearest integer. For example, Ceil (21.2) returns 23.
Double	Rand (), Rand (INT seed)	Returns an average random number equal to or greater than 0 and less than 1 (changed based on recalculation)
Double	Exp (double)	Returns the N power of E.
Double	Ln (double)	Returns the natural logarithm of a given number.
Double	Log10 (double)	Returns the base-10 natural logarithm of a given number.
Double	Log2 (double)	Returns the base-2 natural logarithm of a given number.
Double	Log (double base, double)	Returns the given base number and exponential returns the natural logarithm.
Double	Pow (double A, Double P) power (double A, Double P)	Returns the power of a number.
Double	SQRT (double)	Returns the square root of a number.
String	Bin (bigint)	Returns the binary format, see: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex
String	Hex (bigint A) hex (string)	Converts an integer or character to a hexadecimal format. Reference: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex
String	Unhex (string)	Hexadecimal character conversion: a number.
String	Conv (bigint num, int from_base, int to_base)	Converts a specified value from the original measurement system to the specified test system. For example, Conv ('A', 16,2), return. Reference: '200' http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv
Double	ABS (double)	Take absolute value
Int double	Pmod (int A, int B) pmod (double A, double B)	Returns the absolute value of the remainder of A except B.
Double	Sin (double)	Returns the sine of a given angle.
Double	Asin (double)	Returns the arc sine of X, that is, X. If X is a sine from-1 to 1, null is returned.
Double	Cos (double)	Return cosine
Double	ACOs (double)	Returns the arc cosine of X, that is, the cosine of X. If-1 <= A <= 1, otherwise null is returned.
Int double	Positive (int A) positive (double)	Returns the value of A, for example, positive (2). Returns 2.
Int double	Negative (int A) negative (double)	Returns the opposite number of A, for example, negative (2), and-2.

2.2 collection functions

Return type	Function	Description
Int	Size (Map <K. V>)	Number of elements of the returned map type
Int	Size (array <t>)	Returns the number of elements of the array type.

2.3 type conversion functions

Return type	Function	Description
Specify "type"	Cast (expr as <type>)	Type conversion. For example, convert the character "1" to an integer: Cast ('1' as bigint). If the conversion fails, null is returned.

2.4 date functions

Return type	Function	Description
String	From_unixtime (bigint unixtime [, string format])	The unix_timestamp parameter indicates that a value 'yyyy-mm-dd hh: mm: ss' or yyyymmddhhmmss. uuuuuu format is returned, depending on whether the function is used in a string or digit context. This value indicates the current time zone.
Bigint	Unix_timestamp ()	If a call without parameters is performed, a Unix timestamp (from '2017-01-0100:00:00' to the current UTC seconds) is returned as an unsigned integer.
Bigint	Unix_timestamp (string date)	The specified date parameter calls unix_timestamp (). It returns the number of seconds from the parameter value '2017-01-0100:00:00 'to the specified date.
Bigint	Unix_timestamp (string date, string pattern)	Specify the time input format, return to 1970 seconds: unix_timestamp ('2017-03-20 ', 'yyyy-mm-dd') = 2009 reference: http://java.sun.com/j2se/1.4.2/docs/api/java/text/SimpleDateFormat.html
String	To_date (string timestamp)	Year, month, and day in the returned Time: to_date ("00:00:00") = ″
String	To_dates (string date)	Returns the number of days (the number of days since 0) for a given date)
Int	Year (string date)	Returns the year of the specified time, ranging from 1000 to 9999, or 0 of the "zero" date.
Int	Month (string date)	Returns the month of the specified time, ranging from 1 to 0000, or a portion of 0 months, such as the date of '2017-00-00 'or '2017-00-00.
Int	Day (string date) dayofmonth (date)	Returns the date of the specified time.
Int	Hour (string date)	Returns the hour of the specified time, ranging from 0 to 23.
Int	Minute (string date)	Returns the minute of the specified time, ranging from 0 to 59.
Int	Second (string date)	Returns the second of the specified time, ranging from 0 to 59.
Int	Weekofyear (string date)	Returns the day of the week in the year of the specified date, ranging from 0 to 53.
Int	Datediff (string enddate, string startdate)	The difference between the two time parameters.
Int	Date_add (string startdate, int days)	The specified time period is added based on the given time period.
Int	Date_sub (string startdate, int days)	For a given time, subtract the specified time period.

2.5 conditional Functions

Return type	Function	Description
T	If (Boolean testcondition, t valuetrue, t valuefalseornull)	Determines whether the condition is met. If the condition is met, a value is returned. If the condition is not met, another value is returned.
T	Coalesce (T V1, t V2 ,...)	Returns the first non-null value in a group of data. If both are null, null is returned.
T	Case A when B then C [when d then E] * [else f] End	If a = B, c is returned. If a = d, e is returned. Otherwise, F is returned.
T	Case when a then B [when C then D] * [else E] End	If the value is a, return B. If the value is C, return D. Otherwise, e is returned.

2.6-character Functions

Return type	Function	Description
Int	Length (string)	Returns the length of a string.
String	Reverse (string)	Returns an inverted string.
String	Concat (string a, string B ...)	Concatenates multiple strings and combines them into one string, which can accept any number of input strings.
String	Concat_ws (string SEP, string a, string B ...)	Links multiple strings separated by the specified separator.
String	Substr (string a, int start) substring (string a, int start)	The character after the specified start position in the text string.
String	Substr (string a, int start, int Len) substring (string a, int start, int Len)	Specifies the length of characters from the specified position in the text string.
String	Upper (string a) ucase (string)	Convert text strings into uppercase letters
String	Lower (string a) lcase (string)	Converts a text string to lowercase letters.
String	Trim (string)	Delete the spaces at both ends of the string. the spaces between characters are retained.
String	Ltrim (string)	Removes spaces on the left of the string. Other spaces are retained.
String	Rtrim (string)	Removes spaces on the right of a string. Other spaces are retained.
String	Regexp_replace (string a, string B, string C)	The B character in string a is replaced by the C character
String	Regexp_extract (string subject, string pattern, int index)	Returns the part specified by the regular expression by subscript. Regexp_extract ('foothebar', 'foo (.*?) (Bar) ', 2) returns 'bar .'
String	Parse_url (string urlstring, string parttoextract [, string keytoextract])	Returns the part specified by the URL. Parse_url ('HTTP: // facebook.com/path1/p.php? K1 = V1 & k2 = v2 # ref1 ', 'host') Return: 'Facebook. com'
String	Get_json_object (string json_string, string path)	Select a. timestamp, get_json_object (A. appevents, '$. eventid'), get_json_object (A. appenvets,' $. eventname') from log;
String	Space (int n)	Returns a specified number of spaces.
String	Repeat (string STR, int N)	Repeat strings for n times
Int	ASCII (string Str)	Returns the number of the first character in the string.
String	Lpad (string STR, int Len, string pad)	Returns a string of the specified length. When the length of a given string is smaller than the specified length, the specified character is filled from the left.
String	Rpad (string STR, int Len, string pad)	Returns a string of the specified length. When the length of a given string is smaller than the specified length, the specified character is filled from the right side.
Array	Split (string STR, string Pat)	Converts a string to an array.
Int	Find_in_set (string STR, string strlist)	Returns the position where STR appears in strlist for the first time. If any parameter is null, null is returned. If the first parameter contains a comma, 0 is returned.
Array <array <string>	Sentences (string STR, string Lang, string locale)	Groups the content of a string by statement. Each word is separated by commas and an array is returned. For example, sentences ('Hello there! How are you? ') Return :( ("hello", "there"), ("how", "are", "you "))
Array <struct <string, double>	Ngrams (array <string>, int N, int K, int PF)	Select ngrams (sentences (lower (Tweet), 2,100 [, 1000]) from Twitter;
Array <struct <string, double>	Context_ngrams (array <string>, array <string>, int K, int PF)	Select context_ngrams (sentences (lower (Tweet), array (null, null), 100, [, 1000]) from Twitter;

3. built-in Aggregate functions (UDAF)

Return type	Function	Description
Bigint	Count (*), count (expr), count (distinct expr [, expr _., expr _.])	Number of returned records.
Double	Sum (COL), sum (distinct col)	Sum
Double	AVG (COL), AVG (distinct col)	Average Value
Double	Min (COL)	Returns the minimum value in a specified column.
Double	Max (COL)	Returns the maximum value in a specified column.
Double	Var_pop (COL)	Returns the variance of a specified column.
Double	Var_samp (COL)	Returns the sample variance of a specified column.
Double	Stddev_pop (COL)	Returns the deviation of a specified column.
Double	Stddev_samp (COL)	Returns the sample deviation of a specified column.
Double	Covar_pop (col1, col2)	Two-column numerical Covariance
Double	Covar_samp (col1, col2)	Two-column numerical sample covariance
Double	Corr (col1, col2)	Returns the correlation coefficient of two values.
Double	Percentile (COL, P)	Returns the percentage value of the value range. 0 <= P <= 1; otherwise, null is returned. floating point values are not supported.
Array <double>	Percentile (COL, array (p ~ 1, \ [, P, 2,]…)	Returns the value points corresponding to a group of percentage values in the value area. 0 <= P <= 1; otherwise, null is returned. floating point values are not supported.
Double	Percentile_approx (COL, P [, B])	Returns an approximate P ^ th ^ percentile of a numeric column (including floating point types) in the group. the B parameter controls approximation accuracy at the cost of memory. higher values yield better approximations, and the default is 10,000. when the number of distinct values in col is smaller than B, this gives an exact percentile value.
Array <double>	Percentile_approx (COL, array (p ~ 1, [, P, 2 _]…) [, B])	Same as abve, but accepts and returns an array of percentile values instead of a single one.
Array <struct \ {'x', 'y' \}>	Histogram_numeric (COL, B)	Computes a histogram of a numeric column in the group using B non-uniformly spaced bins. the output is an array of size B of double-valued (x, y) coordinates that represent the bin centers and heights
Array	Collect_set (COL)	No repeated records are returned.

4. built-in table generation function (udtf)

Return type	Function	Description
Array	Explode (array <type>)	An array contains multiple parameters in a record. Each parameter is split to generate a column.
	Json_tuple	Get_json_object statement: select. timestamp, get_json_object (. appevents, '$. eventid'), get_json_object (. appenvets, '$. eventname') from log a; json_tuple statement: select. timestamp, B. * From log a lateral view json_tuple (. appevent, 'eventid', 'eventname') B as F1, F2

Explode example:

Array	SQL	Return
Mycol [1, 2] [3, 4]	Select explode (mycol) as mynewcol from mytable	Mynewcol 1 2 3 4