HiveQL  Functions
The Hive provides various inbuilt 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 inbuilt 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 base10 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 builtin Functions in Hive
The following are some other commonly used inbuilt 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 lefthand side. 
TRING 
rtrim(str) 
It returns the string by removing whitespaces from righthand side. 
Examples of other inbuilt 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.
