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__

__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__

__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__

__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__

__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__

__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 |