Hive Built-In Operators

There are four types of built-in operators available in Hive -

1. Relational
2. Arithmetic
3. Logical
4. Complex Operators

Relational Operators –

Relational Operators are used to compare the operands. Relational Operators generate a TRUE or FALSE value depending on condition.

Relational Operator	Description
A = B	TRUE if expression A is equivalent to expression B. Otherwise FALSE
A != B	TRUE if expression A is not equivalent to expression B. Otherwise FALSE
A < B	TRUE if expression A is less than expression B. Otherwise FALSE
A <= B	TRUE if expression A is less than or equal to expression B. Otherwise FALSE
A > B	TRUE if expression A is greater than expression B. Otherwise FALSE
A >= B	TRUE if expression A is greater than or equal to expression B. Otherwise FALSE
A IS NULL	TRUE if expression A evaluates to NULL. Otherwise FALSE
A IS NOT NULL	FALSE if expression A evaluates to NULL. Otherwise TRUE
A LIKE B	TRUE if string A matches the SQL simple regular expression B. Otherwise FALSE.
A RLIKE B	NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B. Otherwise FALSE.
A REGEXP B	NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B. Otherwise FALSE.

Example -

SELECT * FROM orders WHERE order_amount >= 1000;

This query will give you all the orders where the amount is greater than or equal to 1000.

Arithmetic Operators –

Arithmetic operators help you perform calculations directly inside your Hive queries. We can use them to add, subtract, multiply, or divide values in your tables.

Arithmetic Operators	Description
A + B	Gives the result of adding A and B.
A - B	Gives the result of subtracting B from A.
A * B	Gives the result of multiplying A and B.
A / B	Gives the result of dividing B from A.
A % B	Gives the reminder resulting from dividing A by B.
A & B	Gives the result of bitwise AND of A and B.
A | B	Gives the result of bitwise OR of A and B.
A ^ B	Gives the result of bitwise XOR of A and B.
~A	Gives the result of bitwise NOT of A.

Example -

SELECT item_price * quantity AS total_price FROM sales_data;

This calculates the total price by multiplying the item price with the quantity for each sale.

Logical Operators –

Logical operators are used to create logical expressions. All of them return boolean TRUE or FALSE based on the values in operands.

Logical Operators	Description
A AND B	TRUE if both A and B are TRUE, otherwise FALSE
A && B	Same as A AND B
A OR B	TRUE if either A or B or both are TRUE, otherwise FALSE
A || B	Same as A OR B
NOT A	TRUE if A is FALSE, otherwise FALSE
!A	Same as NOT A

Example -

SELECT * FROM employee WHERE department = 'Sales' AND salary > 5000;

This query fetches employees who are in the Sales department and have a salary above 5000.

SELECT * FROM customer WHERE city = 'Delhi' OR city = 'Mumbai';

This selects customers who live in either Delhi or Mumbai.

Complex Operators –

Complex operators provide tools 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 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

---