Apache Hive provides multiple types of built-in operators that are used to perform various operations such as comparison, arithmetic operations, logical operations, and so on.
The following are the lists of built-in operators provided by Apache Hive.
Let us see each built-in operators in detail.
1. Relational Operators
The Relational operators are used to compare two expressions and provide output. Apache Hive provides the below list of relational operators.
| Operator | Operand | Description |
|---|---|---|
| A = B | all primitive types | It will return TRUE if expression A is equivalent to expression B otherwise FALSE. |
| A != B | all primitive types | It will return TRUE if expression A is not equivalent to expression B otherwise FALSE. |
| A < B | all primitive types | It will return TRUE if expression A is less than expression B otherwise FALSE. |
| A <= B | all primitive types | It will return TRUE if expression A is less than or equal to expression B otherwise FALSE. |
| A > B | all primitive types | It will return TRUE if expression A is greater than expression B otherwise FALSE. |
| A >= B | all primitive types | It will return TRUE if expression A is greater than or equal to expression B otherwise FALSE. |
| A IS NULL | all types | It will return TRUE if expression A evaluates to NULL otherwise FALSE. |
| A IS NOT NULL | all types | It will return FALSE if expression A evaluates to NULL otherwise TRUE. |
| A LIKE B | Strings | It will return TRUE if string pattern A matches to B otherwise FALSE. |
| A RLIKE B | Strings | It will return NULL if A or B is NULL, TRUE if any substring of A matches the Java regular expression B, otherwise FALSE. |
| A REGEXP B | Strings | This function is the same as RLIKE. |
Example of Relational operators.
2. Arithmetic Operators
The Arithmetic Operators are used to perform an arithmetic operation. Apache Hive provides the below list of arithmetic operators.
| Operator | Operand | Description |
|---|---|---|
| A + B | all number types | It will give the result of adding A and B. |
| A - B | all number types | It will give the result of subtracting B from A. |
| A * B | all number types | It will give the result of multiplying A and B. |
| A / B | all number types | It will give the result of dividing B from A. |
| A % B | all number types | It will give the remainder resulting from dividing A by B. |
| A & B | all number types | It will give the result of bitwise AND of A and B. |
| A | B | all number types | It will give the result of bitwise OR of A and B. |
| A ^ B | all number types | It will give the result of bitwise XOR of A and B. |
| ~A | all number types | It will give the result of bitwise NOT of A. |
Example of Arithmetic operators.
3. Logical Operators
Apache Hive provides the following list of Logical operators.
| Operator | Operand | Description |
|---|---|---|
| A AND B | boolean | It will return TRUE if both A and B are TRUE, otherwise FALSE. |
| A && B | boolean | It is the same as A AND B. |
| A OR B | boolean | It will return TRUE if either A or B or both are TRUE, otherwise FALSE. |
| A || B | boolean | It is the same as A OR B. |
| NOT A | boolean | It will return TRUE if A is FALSE, otherwise FALSE. |
| !A | boolean | It is same as NOT A. |
4. Complex Operators
The Complex operator works with an expression to access the elements of Complex Types.
| Operator | Operand | Description |
|---|---|---|
| aa[n] | aa is an Array and n is an int | This function returns the nth element of the array aa. |
| mm[key] | M is a Map <K, V> and the key has type K | This function returns the corresponding value to the key. |
| S.x | S is a struct | This function returns the x value of S. |
Operators Precedences
Through the operator's Precedence, it is decided which operation should perform first. Apache Hive provides the following list of Operators Precedence.
| Operator | Operand | Description |
|---|---|---|
| A[B] , A.identifier | bracket_op([]), dot(.) | element selector, dot |
| -A | unary(+), unary(-), unary(~) | unary prefix operators |
| A IS [NOT] (NULL|TRUE|FALSE) | IS NULL,IS NOT NULL, ... | unary suffix |
| A ^ B | bitwise xor(^) | bitwise xor |
| A * B | star(*), divide(/), mod(%), div(DIV) | multiplicative operators |
| A + B | plus(+), minus(-) | additive operators |
| A || B | string concatenate(||) | string concatenate |
| A & B | bitwise and(&) | bitwise and |
| A | B | bitwise or(|) | bitwise or |