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.     