78

HiveQL – Functions

The Hive provides various in-built functions to perform mathematical and aggregate type operations. Here, we are going to execute such type of functions on the records of the below table:

Example of Functions in Hive

Let’s create a table and load the data into it by using the following steps: –

• Select the database in which we want to create a table.

• Create a hive table using the following command: –

• Now, load the data into the table.

• Let’s fetch the loaded data by using the following command: –

Now, we discuss mathematical, aggregate and other in-built functions with the corresponding examples.

Mathematical Functions in Hive

The commonly used mathematical functions in the hive are: –

Return type	Functions	Description
BIGINT	round(num)	It returns the BIGINT for the rounded value of DOUBLE num.
BIGINT	floor(num)	It returns the largest BIGINT that is less than or equal to num.
BIGINT	ceil(num), ceiling(DOUBLE num)	It returns the smallest BIGINT that is greater than or equal to num.
DOUBLE	exp(num)	It returns exponential of num.
DOUBLE	ln(num)	It returns the natural logarithm of num.
DOUBLE	log10(num)	It returns the base-10 logarithm of num.
DOUBLE	sqrt(num)	It returns the square root of num.
DOUBLE	abs(num)	It returns the absolute value of num.
DOUBLE	sin(d)	It returns the sin of num, in radians.
DOUBLE	asin(d)	It returns the arcsin of num, in radians.
DOUBLE	cos(d)	It returns the cosine of num, in radians.
DOUBLE	acos(d)	It returns the arccosine of num, in radians.
DOUBLE	tan(d)	It returns the tangent of num, in radians.
DOUBLE	atan(d)	It returns the arctangent of num, in radians.

Example of Mathematical Functions in Hive

• Let’s see an example to fetch the square root of each employee’s salary.

Aggregate Functions in Hive

In Hive, the aggregate function returns a single value resulting from computation over many rows. Let”s see some commonly used aggregate functions: –

Return Type	Operator	Description
BIGINT	count(*)	It returns the count of the number of rows present in the file.
DOUBLE	sum(col)	It returns the sum of values.
DOUBLE	sum(DISTINCT col)	It returns the sum of distinct values.
DOUBLE	avg(col)	It returns the average of values.
DOUBLE	avg(DISTINCT col)	It returns the average of distinct values.
DOUBLE	min(col)	It compares the values and returns the minimum one form it.
DOUBLE	max(col)	It compares the values and returns the maximum one form it.

Examples of Aggregate Functions in Hive

• Let’s see an example to fetch the maximum salary of an employee.

• Let’s see an example to fetch the minimum salary of an employee.

Other built-in Functions in Hive

The following are some other commonly used in-built functions in the hive: –

Return Type	Operator	Description
INT	length(str)	It returns the length of the string.
STRING	reverse(str)	It returns the string in reverse order.
STRING	concat(str1, str2, …)	It returns the concatenation of two or more strings.
STRING	substr(str, start_index)	It returns the substring from the string based on the provided starting index.
STRING	substr(str, int start, int length)	It returns the substring from the string based on the provided starting index and length.
STRING	upper(str)	It returns the string in uppercase.
STRING	lower(str)	It returns the string in lowercase.
STRING	trim(str)	It returns the string by removing whitespaces from both the ends.
STRING	ltrim(str)	It returns the string by removing whitespaces from left-hand side.
TRING	rtrim(str)	It returns the string by removing whitespaces from right-hand side.

Examples of other in-built Functions in Hive

• Let’s see an example to fetch the name of each employee in uppercase.

• Let’s see an example to fetch the name of each employee in lowercase.