Hive Operators – A Complete Tutorial for Hive Builtin Operators
In our previous blog, we have discussed the introduction to Apache Hive and Hive Data types in detail. In this Hive tutorial, we are going to cover the Hive Operators – Relational Operators, Arithmetic Operators, Logical Operators, String Operators, Operators on Complex Types etc in detail.
What is Hive Operators?
Apache Hive provides various Builtin operators for data operations to be implemented on the tables present inside Apache Hive warehouse.
Hive operators are used for mathematical operations on operands. It returns specific value as per the logic applied.
Types of Hive Builtin Operators
 Relational Operators
 Arithmetic Operators
 Logical Operators
 String Operators
 Operators on Complex Types
3.1. Hive Relational Operators
These operators compare two operands and generate a TRUE or FALSE value. The below table describes the relational operators available in Hive:
Operator  Operand Types 
Description 

A=B 
All primitive types 
TRUE if expression A is equal to expression B. Otherwise FALSE. 

A!=B 
All primitive types  TRUE if expression A is not equal to expression B. Otherwise FALSE.  
A<B  All primitive types 
TRUE if expression A is less than expression B. Otherwise FALSE. 

A <= B 
All primitive types  TRUE if expression A is less than or equal to expression B. Otherwise FALSE.  
A > B 
All primitive types 
TRUE if expression A is greater than expression B. Otherwise FALSE. 

A >= B  All primitive types 
TRUE if expression A is greater than or equal to expression B. Otherwise FALSE. 

A IS NULL 
All types  TRUE if expression A evaluates to NULL. Otherwise FALSE.  
A IS NOT NULL  All types 
FALSE if expression A evaluates to NULL. Otherwise FALSE. 

A LIKE B 
String  TRUE if string pattern A matches to B. Otherwise FALSE.  

String 
Same as RLIKE. 
3.2. Hive Arithmetic Operators
Arithmetic Operators in Hive supports various arithmetic operations on the operands. All return number types. If any of the operands are NULL, then the result is also NULL.
Operator 
Operand types 
Description 
A+B 
All number types  Gives the result of adding A and B 
AB 
All number types  Gives the result of subtracting B from A 
A*B 
All number types 
Gives the result of multiplying A and 
A/B  All number types 
Gives the result of dividing A by B 
A % B 
All number types  Gives the remainder resulting from dividing A by B 
A & B  All number types 
Gives the result of bitwise AND of A and B 
A  B 
All number types 
Gives the result of bitwise OR of A and B 
A ^ B 
All number types 
Gives the result of bitwise XOR of A and B 
~A  All number types 
Gives the result of bitwise NOT of A. 
3.3. Hive Logical Operators
Logical operators in Hive provide support for creating logical expressions. All return boolean TRUE, FALSE, or NULL depending upon the boolean values of the operands. IN this NULL behaves as an “unknown” flag, so if the result depends on the state of an unknown, the result itself is unknown.
Operator 
Operand types 
Description 
A AND B 
Boolean 
TRUE if both A and B are TRUE. Otherwise FALSE. NULL if A or B is NULL. 
A OR B 
Boolean  RUE if either A or B or both are TRUE, FALSE OR NULL is NULL. Otherwise FALSE. 
NOT A 
Boolean 
TRUE if A is FALSE or NULL if A is NULL. Otherwise FALSE. 
! A  Boolean 
Same as NOT A. 
3.4. Hive String Operators
Operator 
Operand types 
Description 
A  B  strings 
Concatenates the operands – shorthand for concat(A,B) 
3.5. Operators on Complex Types
These operators provide the mechanism to access elements in Complex Types.
Operator 
Operand types  Description 
A[n] 
A is an Array and n is an int  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 K 
Returns the value corresponding to the key in the map 
S.x  S is a struct 
Returns the x field of S. 
So, this was all in Hive Operators. Hope you like our explanation.
Conclusion
In conclusion, Hive provides different types of Buildin operators which are used to perform mathematical operations on operands. Hope this blog will help you to understand the Hive essentials.
In the next section, we will discuss the Hive functions in detail, where we will learn to use these Hive Operators.
A IS NOT NULL
FALSE if expression A evaluates to NULL. Otherwise FALSE.
It should be the following instead of above statement (may be typing mistake I think),
A IS NOT NULL
FALSE if expression A evaluates to NULL. Otherwise TRUE.
hai
am not able to do logical operations in hive
even simple command like add 20+30 also am getting error.
>select 20+30 add from temp;
FAILED: SemanticException [Error 10001]: Line 1:22 Table not found ‘temp’
sry above example for arithmetic operators example.
am getinng eroor for both arithmetic n logical too