[Go] Hive built-in functions

See the original: Https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

1. Built-in operators
1.1 Relational operators

Operator	Type	Description
A = B	All primitive types	Returns True if A and B are equal, otherwise false
A = = B	No	failed because of invalid syntax. SQL uses "=" and does not use "= =".
A <> B	All primitive types	Returns False if a is not equal to B returns true. If the A or B value is "null", the result returns "null".
A < B	All primitive types	Returns True if A is less than B, otherwise false. If the A or B value is "null", the result returns "null".
A <= B	All primitive types	Returns True if a is less than or equal to B, otherwise false. If the A or B value is "null", the result returns "null".
A > B	All primitive types	Returns True if A is greater than B, otherwise false. If the A or B value is "null", the result returns "null".
A >= B	All primitive types	Returns False if a is greater than or equal to B returns true. If the A or B value is "null", the result returns "null".
A is NULL	All types	Returns True if the A value is "NULL", otherwise false
A is not NULL	All types	Returns True if the A value is not "NULL", otherwise false
A like B	String	If the A or B value is "null", the result returns "null". Strings A and B are matched by SQL, and if true, the match returns false. The "_" in the B string represents any character, and "%" represents more than one character. For example: (' foobar ' like ' foo ') return False, (' Foobar ' like ' foo_ _ _ ' or ' foobar ' as ' foo% ') return to Ture
A Rlike B	String	If the A or B value is "null", the result returns "null". Strings A and B are matched by Java, and if true, the match returns false. 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 number Types	A and b add up. The result has a common type with the operand value. For example, each integer is a floating-point number, and the floating-point number contains integers. Therefore, a floating-point number and an integer addition result in a floating-point number.
A–b	All number Types	Subtract A and B. The result has a common type with the operand value.
A * B	All number Types	A and B are multiplied, and the result has a common type with the operating value. It should be explained that if multiplication causes overflow, a higher type is selected.
A/b	All number Types	A and B divide, and the result is a double (double) type result.
A% B	All number Types	A divided by the B remainder has a common type with the operating value.
A & B	All number Types	The operator looks at the value of the binary notation of the two parameters and performs a bitwise AND operation. When one of the two expressions is 1 o'clock, the result is 1. Otherwise, the result of this bit is 0.
a| B	All number Types	The operator looks at the value of the binary notation of the two parameters and performs a bitwise OR operation. As long as one of the expressions is 1, the result of this bit is 1. Otherwise, the result of this bit is 0.
A ^ B	All number Types	The operator looks at the value of the binary notation of two parameters and performs a bitwise XOR operation. This bit of the result is 1 if and only if there is a bit on one expression that is 1 o'clock. Otherwise the result of this bit is 0.
~a	All number Types	Performs a bitwise "Non" (inverse) on an expression.

1.3 Logical operators

Operator	Type	Description
A and B	Boolean value	A and B are correct at the same time, return true, otherwise false. Returns null if the A or B value is null.
A && B	Boolean value	Same as "A and B"
A OR B	Boolean value	A or B is correct, or both correctly returns True, otherwise false. Returns null if both A and B values are null at the same time.
A | B	Boolean value	Same as "A OR B"
Not A	Boolean value	Returns False if A is null or incorrect when ture is returned.
! A	Boolean value	Same as "not A"

1.4 Complex Type functions

Function	Type	Description
Map	(Key1, value1, Key2, value2, ...)	Creates a map with the specified key/value pair.
struct	(Val1, Val2, Val3, ...)	Creates a structure by specifying the field value. The structure field name will be Col1,col2, ...
Array	(Val1, Val2, ...)	Creates an array from the specified element.

1.5 operation on complex type functions

Function	Type	Description
A[n]	A is an array, n is an int type	Returns the nth element of array A, with the index of the first element 0. If the a array is [' foo ', ' Bar '], then a[0] returns ' Foo ' and a[1] back to "bar".
M[key]	M is map<k, V>, key type K	Returns the value corresponding to the key value, for example, MAPM is \{' f ', ' foo ', ' B ', ' Bar ', ' All ', ' Foobar ' \}, then m[' all ' returns ' Foobar '.
S.x	S is a struct	Returns the location where the structure x string is stored in structure s. An integer stored in the realm such as Foobar \{int foo, int bar\} foobar.foo.

2. Built-in functions
2.1 Mathematical functions

return type	Function	Description
BIGINT	Round (Double A)	Rounded
DOUBLE	Round (double A, int d)	Number of decimal parts after D digit rounding, for example round (21.263,2), return 21.26
BIGINT	Floor (Double A)	Rounds down the nearest integer to the given data. For example floor (21.2), return 21.
BIGINT	Ceil (double A), ceiling (double A)	Rounds the parameter up to the nearest integer. For example Ceil (21.2), returns 23.
Double	RAND (), rand (int seed)	Returns the average distributed random number, which is greater than or equal to 0 and less than 1 (varies by recalculation)
Double	EXP (Double A)	Returns the n-th square of E
Double	ln (double A)	Returns the natural logarithm of a given number
Double	LOG10 (Double A)	Returns the base 10 natural logarithm of a given number
Double	LOG2 (Double A)	Returns the base 2 natural logarithm of a given number
Double	Log (double base, double a)	Returns the natural logarithm of a given base and exponent
Double	Pow (double A, double p) power (double A, double p)	Returns the number of a
Double	sqrt (Double A)	Returns the square root of a value
String	Bin (BIGINT a)	Return binary format, reference: Http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_hex
String	Hex (BIGINT a) hex (string a)	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 a)	The hexadecimal character converts the character represented by a number.
String	Conv (BIGINT num, int from_base, int to_base)	Converts the specified value from the original measurement system to the specified question system. For example Conv (' A ', 16,2), returned. Reference: ' 1010′http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html#function_conv
Double	ABS (Double A)	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 A)	Returns the sine value of the given angle
Double	ASIN (Double A)	Returns the sine of x, which is x. If x is a sine value of 1 to 1, NULL is returned.
Double	COS (double A)	Return cosine
Double	ACOs (Double A)	Returns the inverse cosine of x, that is, the cosine is x, if -1<= A <= 1, otherwise null is returned.
int double	positive (int a) positive (double A)	Returns the value of a, such as positive (2), which returns 2.
int double	Negative (int a) negative (double A)	Returns the opposite number of a, such as negative (2), which returns-2.

2.2 Collection functions

return type	Function	Description
Int	Size (map<k.v>)	Returns the number of elements of the map type
Int	Size (array<t>)	Returns the number of elements of an array type

2.3 Type conversion function

return type	Function	Description
Specify "type"	Cast (expr as <type>)	Type conversions. For example, convert the character "1″" to an integer: Cast (' 1′as bigint) if the conversion fails to return NULL.

2.4 Date function

return type	Function	Description
String	From_unixtime (bigint unixtime[, string format])	The Unix_timestamp parameter indicates that a value of ' yyyy-mm–dd HH:MM:SS ' or yyyymmddhhmmss.uuuuuu format is returned, depending on whether the function is used in a string or a numeric context. The value is expressed in the current time zone.
bigint	Unix_timestamp ()	If called without parameters, returns a Unix timestamp (from ' 1970-01–0100:00:00′ to now UTC seconds) as an unsigned integer.
bigint	Unix_timestamp (string date)	The specified date parameter calls Unix_timestamp (), which returns the number of seconds that the parameter value ' 1970-01–0100:00:00′ to the specified date.
bigint	Unix_timestamp (string date, string pattern)	Specify time input format, return to 1970 seconds: Unix_timestamp (' 2009-03-20′, ' yyyy-mm-dd ') = 1237532400 reference: Http://java.sun.com/j2se/1.4.2/docs /api/java/text/simpledateformat.html
String	To_date (string timestamp)	Month Day of Return time: To_date ("1970-01-01 00:00:00″) =" 1970-01-01″
String	To_dates (string date)	Returns a number of days (0 years since) given a date.
Int	Year (string date)	Returns the year of the specified time, in the range 1000 to 9999, or 0 for the "0" date.
Int	Month (string date)	Returns the month of the specified time, in the range of January-December, or a portion of 01 months, such as the date of ' 0000-00-00′ or ' 2008-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 a specified time, ranging from 0 to 59.
Int	WeekOfYear (string date)	Returns the number of days of the year in which the specified date is, ranging from 0 to 53.
Int	DateDiff (String enddate, String startdate)	The difference between the dates of the two time parameters.
Int	Date_add (string startdate, int days)	Given time, this is based on the specified time period.
Int	Date_sub (string startdate, int days)	For a given time, subtract the specified time period on this basis.

2.5 Bar functions

return type	Function	Description
T	If (Boolean testcondition, T valuetrue, T Valuefalseornull)	Determines whether the condition is satisfied, returns a value if satisfied, and returns another value if it is not satisfied.
T	COALESCE (t v1, T v2, ...)	Returns the first non-null value in a set of data, or null if both are null.
T	Case a while B then C [when D then e]* [ELSE F] END	When a=b, returns C; When A=d, returns E, otherwise returns F.
T	Case when A and B [When C then d]* [ELSE E] END	Returns B when the value is a, and d when the value is C. Otherwise, E is returned.

2.6 Character functions

return type	Function	Description
Int	Length (String A)	Returns the length of a string
String	Reverse (String A)	Returns the reverse string
String	Concat (String A, String B ...)	Concatenate multiple strings, merge into a single string, and accept any number of input strings
String	Concat_ws (String SEP, String A, String B ...)	Links multiple strings, separating the strings with the specified delimiter.
String	SUBSTR (string A, int start) substring (string a, int start)	The character from the starting position specified in the text string.
String	SUBSTR (string A, int start, int len) substring (string A, int start, int len)	Specifies the length of the character from the position specified in the text string.
String	Upper (String a) UCase (string a)	Converts a text string to all uppercase letters
String	Lower (String a) LCase (string a)	Converts a text string into all lowercase letters
String	Trim (String A)	Remove spaces at both ends of a string, preserving spaces between characters
String	LTrim (String A)	Remove the space to the left of the string, leaving the other spaces
String	RTrim (String A)	Remove the space to the right of the string, leaving the other spaces
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 portion of the regular expression specified by the subscript. Regexp_extract (' Foothebar ', ' foo (. *?) (bar) ', 2) returns ' Bar. '
String	Parse_url (String urlstring, String parttoextract [, String keytoextract])	Returns the specified portion of 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 A;
String	Space (int n)	Returns the specified number of spaces
String	Repeat (string str, int n)	Repeats n-Times string
Int	ASCII (String str)	Returns the numeric value of the first character in a string
String	Lpad (string str, int len, string pad)	Returns a string of the specified length, filled by the specified character from the left side when the given string length is less than the specified length.
String	Rpad (string str, int len, string pad)	Returns a string of the specified length, filled by the specified character from the right side when the given string length is less than the specified length.
Array	Split (String str, string pat)	Converts a string to an array.
Int	Find_in_set (String str, string strlist)	Returns the position of the first occurrence of the string str in strlist. Returns null if either parameter is NULL, or 0 if the first argument contains a comma.
Array<array<string>>	Sentences (string str, string lang, string locale)	Groups the contents of a string by a statement, separating each word with a comma, and finally returning an array. For example sentences (' Hello there! How is it? ') Return: (("Hello", "There"), ("How", "is", "you")
Array<struct<string,double>>	Ngrams (array<array<string>>, int N, int K, int pf)	SELECT ngrams (Sentences (lower (tweet)), 2, [+]) from Twitter;
Array<struct<string,double>>	Context_ngrams (ARRAY<ARRAY<STRING>>, array<string>, int K, int pf)	SELECT context_ngrams (Sentences (lower), Array (null,null), + [, +]) from Twitter;

3. Built-in aggregation function (UDAF)

return type	Function	Description
bigint	Count (*), count (expr), count (DISTINCT expr[, Expr_, Expr_.])	Returns the number of record bars.
Double	Sum (col), sum (DISTINCT col)	Sum
Double	Avg (COL), avg (DISTINCT col)	Averaging
Double	Min (col)	Returns the minimum value in the specified column
Double	Max (COL)	Returns the maximum value in the specified column
Double	Var_pop (COL)	Returns the variance of the specified column
Double	Var_samp (COL)	Returns the sample variance for the specified column
Double	Stddev_pop (COL)	Returns the deviation of the specified column
Double	Stddev_samp (COL)	Returns the sample deviation for the specified column
Double	Covar_pop (col1, col2)	Two-column numeric covariance
Double	Covar_samp (col1, col2)	Two-column numeric sample covariance
Double	Corr (col1, col2)	Returns the correlation coefficient of two column values
Double	Percentile (col, p)	Returns the percentage value point of a range of values. 0<=p<=1, otherwise returns NULL, and does not support floating-point numeric values.
Array<double>	Percentile (col, array (p~1,,\ [, p,,2,,] ...))	Returns a numeric point that corresponds to a set of percentage values for a range of values. 0<=p<=1, otherwise returns NULL, and does not support floating-point numeric values.
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 are smaller than B, this gives an exact percentile value.
Array<double>	Percentile_approx (col, Array (p~1,, [, p,,2_] ...) [, B])	Same as above, 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)	Returns no duplicate records

4. Built-in table generation function (UDTF)

return type	Function	Description
Array	Explode (array<type> a)	An array has multiple parameters in a record, splits the parameters, and generates a column for each parameter.
	Json_tuple	Get_json_object statements: Select A.timestamp, Get_json_object (a.appevents, ' $.eventid '), Get_json_object (A.appenvets, ' $. EventName ') from log A; Json_tuple statement: Select A.timestamp, b.* from log a lateral view json_tuple (a.appevent, ' EventID ', ' eventname ') b as F1, F2

Explode example:

Array	Sql	Return
MyCol [Up] [3,4]	SELECT explode (MyCol) as Mynewcol from myTable	Mynewcol 1 2 3 4

[Go] Hive built-in functions