# Hive Operators – A Complete Tutorial for Hive Built-in Operators

Boost your career with Free Big Data Courses!!

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:

OperatorOperand Types

Description

A=B

All primitive types

TRUE if expression A is equal to expression B. Otherwise FALSE.

A!=B

All primitive typesTRUE if expression A is not equal to expression B. Otherwise FALSE.
A<BAll primitive types

TRUE if expression A is less than expression B. Otherwise FALSE.

A <= B

All primitive typesTRUE 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 >= BAll primitive types

TRUE if expression A is greater than or equal to expression B. Otherwise FALSE.

A IS NULL

All typesTRUE if expression A evaluates to NULL. Otherwise FALSE.
A IS NOT NULLAll types

FALSE if expression A evaluates to NULL. Otherwise FALSE.

A LIKE B

StringTRUE if string pattern A matches to B. Otherwise FALSE.
 A REGEXP B
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 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.

We work very hard to provide you quality material
Could you take 15 seconds and share your happy experience on Google

### 3 Responses

1. PRIYANKA AGARWAL says:

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.

2. rachana says:

hai
am not able to do logical operations in hive
even simple command like add 20+30 also am getting error.