HiveQL - Operators

The HiveQL operators facilitate to perform various arithmetic and relational operations. Here, we are going to execute such type of operations on the records of the below table:

Example of Operators 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.

hive> use hql;  

• Create a hive table using the following command: -

hive> create table employee (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_data' into table employee;

• Let's fetch the loaded data by using the following command: -

hive> select * from employee;

Now, we discuss arithmetic and relational operators with the corresponding examples.

Arithmetic Operators in Hive

In Hive, the arithmetic operator accepts any numeric type. The commonly used arithmetic operators are: -

Examples of Arithmetic Operator in Hive

• Let's see an example to increase the salary of each employee by 50.

hive> select id, name, salary + 50 from employee;

• Let's see an example to decrease the salary of each employee by 50.

hive> select id, name, salary - 50 from employee;  

• Let's see an example to find out the 10% salary of each employee.

hive> select id, name, (salary * 10) /100 from employee;  

Relational Operators in Hive

In Hive, the relational operators are generally used with clauses like Join and Having to compare the existing records. The commonly used relational operators are: -

Examples of Relational Operator in Hive

• Let's see an example to fetch the details of the employee having salary>=25000.

hive> select * from employee where salary >= 25000;

• Let's see an example to fetch the details of the employee having salary<25000.

hive> select * from employee where salary < 25000;