Hive Built-in functions

This article describes the built-in functions that are available in hive. These functions look very similar to SQL functions, except that their use is a bit different.

Built-in functions

Hive supports the following built-in functions:

return type	Signature	Description
BIGINT	Round (Double A)	Returns the most recent double value of bigint.
BIGINT	Floor (Double A)	Returns the maximum bigint value equal to or less than double.
BIGINT	Ceil (Double A)	It returns the minimum bigint value equal to or greater than double.
Double	RAND (), rand (int seed)	It returns a random number, changed from row to row.
String	Concat (String A, String B,...)	It returns the string generated from Series B after a
String	substr (string A, int start)	It returns a starting substring from the starting position until the end of a.
String	substr (string A, int start, int length)	Returns a string starting at the starting start position for a given length.
String	Upper (String A)	It returns the string from which all characters of the conversion are capitalized.
String	UCase (String A)	Same as above.
String	Lower (string A)	It returns the string that is generated for all characters of transformation B as lowercase.
String	LCase (String A)	Same as above.
String	Trim (String A)	It returns the result of a string trimming a space from both ends of a.
String	LTrim (String A)	It returns a string (left-hand side) resulting from trimming a space from the beginning
String	RTrim (String A)	RTrim (String a), which returns a string from the end of a trimmed space (right)
String	Regexp_replace (String A, String B, String C)	It returns a string from replacing all the children in B result mates with C. In Java Regular expression syntax
Int	Size (map<k.v>)	It returns the number of elements in the mapped type.
Int	Size (array<t>)	It returns the number of elements in the array type.
Value of <type>	Cast (<expr> as <type>)	It converts the result of the expression to the expr< type > such as cast (' 1 ' as bigint) representing the whole to the string ' 1 '. If the conversion is unsuccessful, NULL is returned.
String	From_unixtime (int unixtime)	The number of seconds converted from the Unix era (1970-01-0100:00:00 UTC) represents that moment, the string format of the timestamp character in the current system time zone: "1970-01-01 00:00:00"
String	To_date (string timestamp)	Returns the date part of a string timestamp: To_date ("1970-01-01 00:00:00") = "1970-01-01"
Int	Year (string date)	Returns the date or timestamp string for the year part: Years ("1970-01-01 00:00:00") = 1970 ("1970-01-01") = 1970
Int	Month (string date)	Returns the date or time stamp string month part: Month ("1970-11-01 00:00:00") = one, month ("1970-11-01") = 11
Int	Day (string date)	Returns the date or time stamp string for the day part: Days ("1970-11-01 00:00:00") = 1, daily ("1970-11-01") = 1
String	Get_json_object (string json_string, String path)	Extracts a JSON object from a JSON string based on the specified JSON path, and returns a JSON object that extracts the JSON string. Returns NULL if the input JSON string is not valid.

Example

The following query demonstrates some of the built-in functions:

Round () function

Hive> SELECT Round (2.6) from temp;

Successful execution of the query, you can see the following response:

2.0

Floor () function

Hive> SELECT Floor (2.6) from temp;

Successful execution of the query, you can see the following response:

2.0

Floor () function

Hive> SELECT ceil (2.6) from temp;

Successful execution of the query, you can see the following response:

3.0

Aggregation functions

Hive supports the following built-in aggregation functions. The use of these functions is similar to SQL aggregation functions.

return type	Signature	Description
BIGINT	Count (*), count (expr),	COUNT (*)-Returns the total number of rows retrieved.
DOUBLE	Sum (col), sum (DISTINCT col)	Returns the grouping of different values for the group or the columns in the group and the sum of all the elements.
DOUBLE	Avg (COL), avg (DISTINCT col)	Returns the average of the elements of the group or the different values of the columns in the group.
DOUBLE	Min (col)	Returns the minimum value of a column in this group.
DOUBLE	Max (COL)	Returns the maximum value of a column in this group.

Hive Tutorial: http://www.yiibai.com/hive/

Hive Built-in functions