Hive Operators – A Complete Tutorial for Hive Built-in 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 Built-in 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 Built-in 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 |
A-B | 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 Build-in 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.
You give me 15 seconds I promise you best tutorials
Please share your happy experience on Google
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