Hive Built-In Operators

Let us discuss Hive Built-In Operators,

Hive contains four types of Built-In Operators, is shown in the below diagram.

1. Relational Operators

These operators are used to compare two operands. The following table describes the relational operators available in Hive.

OperatorOperandDescription
A = BIt takes all primitive typesTRUE if expression A is equivalent to expression B otherwise FALSE.
A != BIt takes all primitive typesTRUE if expression A is not equivalent to expression B otherwise FALSE.
A < BIt takes all primitive typesTRUE if expression A is less than expression B otherwise FALSE.
A<=BIt takes all primitive typesTRUE if expression A is less than or equal to expression B otherwise FALSE.
A > BIt takes all primitive typesIt returns TRUE if expression A is greater than expression B otherwise FALSE.
A >= BIt takes all primitive typesTRUE if expression A is greater than or equal to expression B otherwise FALSE.
A IS NULLall typesTRUE if expression A evaluates to NULL otherwise FALSE.
A IS NOT NULLall typesFALSE if expression A evaluates to NULL otherwise TRUE.
A LIKE BStringsTRUE if string pattern A matches to B otherwise FALSE.
A REGEXP BStringsSame as RLIKE.

Example

Let us consider the previous employee table with the columns Id, Name, Salary, and Dept.

hive> select * from employee where Id=105;

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

2. Arithmetic Operators

         These operators perform arithmetic operations on operands. The below table describes the Arithmetic operators available in Hive.

OperatorsOperandDescription
A + BIt takes all number types It will return the output of adding A and B.
A – Ball number typesGives the result of subtracting B from A.
A * Ball number typesGives the result of multiplying A and B.
A / Ball number typesGives the result of dividing B from A.
A % Ball number typesGives the reminder resulting from dividing A by B.
A & Ball number typesGives the result of bitwise AND of A and B.
A | Ball number typesGives the result of bitwise OR of A and B.
A ^ Ball number typesGives the result of bitwise XOR of A and B.
~Aall number typesGives the result of bitwise NOT A

Example             

The following query adds two numbers, 22 and 12.

hive> select 22+12 add from temp;

3. Logical Operators

These operators perform Logical operations on operands, return either TRUE or FALSE. The following table describes the Logical operators available in Hive.

OperatorsOperandDescription
A AND BbooleanTRUE if both A and B are TRUE, otherwise FALSE.
A && BbooleanSame as A AND B.
A OR BbooleanTRUE if either A or B or both are TRUE, otherwise FALSE.
A || BbooleanSame as A OR B.
NOT AbooleanTRUE if A is FALSE, otherwise FALSE.
!AbooleanSame as NOT A.

Example

Let us consider the previous employee table with the columns Id, Name, Salary, and Dept.

hive> select * from employee where Salary>15000 && Dept=Tax;

 4. Complex Operators

    These operators provide an expression to access the elements of Complex Types. The following table describes the Complex operators available in Hive.

OperatorsOperandDescription
A[n]A is an Array and n is an intIt returns the nth element in the array A. The first element has index 0.
M[key]M is a Map<K, V> and key has type KIt returns the value corresponding to the key in the map.
S.xS is a structIt returns the x field of S.

Example

hive>create table emply(name String,sal int,age array<smallint>,feel map<string,boolean>,address struct<city:String,state:String>)
>ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,
>map KEYS terminated BY ‘:’
>LINES TERMINATED BY ‘\n’ stored AS textfile;

Conclusion

From the topic Hive Built-In Operators we can conclude that hive provides different types of Build-in operators which are used to perform mathematical operations on operands. It is similar to SQL language, which supports all type of data operations and querying on tables and databases.