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: -
hive> create table employee_data (Id int, Name string , Salary float)
row format delimited
fields terminated by ',' ;
- Now, load the data into the table.
hive> load data local inpath '/home/codegyani/hive/emp_details' into table employee_data;
- Let's fetch the loaded data by using the following command: -
hive> select * from employee_data;
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.
hive> select Id, Name, sqrt(Salary) from employee_data ;
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.
hive> select max(Salary) from employee_data;
- Let's see an example to fetch the minimum salary of an employee.
hive> select min(Salary) from employee_data;
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.
select Id, upper(Name) from employee_data;
- Let's see an example to fetch the name of each employee in lowercase.
select Id, lower(Name) from employee_data;
|