78

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.

• 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 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: –

Operators	Description
A + B	This is used to add A and B.
A – B	This is used to subtract B from A.
A * B	This is used to multiply A and B.
A / B	This is used to divide A and B and returns the quotient of the operands.
A % B	This returns the remainder of A / B.
A | B	This is used to determine the bitwise OR of A and B.
A & B	This is used to determine the bitwise AND of A and B.
A ^ B	This is used to determine the bitwise XOR of A and B.
~A	This is used to determine the bitwise NOT of A.

Examples of Arithmetic Operator in Hive

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

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

• Let’s see an example to find out the 10% salary of each 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: –

Operator	Description
A=B	It returns true if A equals B, otherwise false.
A <> B, A !=B	It returns null if A or B is null; true if A is not equal to B, otherwise false.
A<B	It returns null if A or B is null; true if A is less than B, otherwise false.
A>B	It returns null if A or B is null; true if A is greater than B, otherwise false.
A<=B	It returns null if A or B is null; true if A is less than or equal to B, otherwise false.
A>=B	It returns null if A or B is null; true if A is greater than or equal to B, otherwise false.
A IS NULL	It returns true if A evaluates to null, otherwise false.
A IS NOT NULL	It returns false if A evaluates to null, otherwise true.

Examples of Relational Operator in Hive

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

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