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.
![hive_practial_relational](/hive/built-in-operators/hive_practial_relational.png)
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.
![hive_practial_relational](/hive/built-in-operators/hive_practial_arthmetic.png)
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 |