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

1. Objective

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.

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

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

Learn Apache Hive installation step by step to practically use this Operators.

2. 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.

Hadoop Quiz

Get the most demanding skills of IT Industry - Learn Hadoop

3. 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/BAll number types

Gives the result of dividing A by B

A % B

All number typesGives the remainder resulting from dividing A by B
A & BAll 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

~AAll 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

BooleanRUE 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.

! ABoolean

Same as NOT A.

3.4. Hive String Operators

Operator

Operand types

Description

A || Bstrings

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 typesDescription

A[n]

A is an Array and n is an intReturns the nth element in the array A. The first element has index 0
M[key]M is a Map<K, V> and key has type K

Returns the value corresponding to the key in the map

S.xS is a struct

Returns the x field of S.

So, this was all in Hive Operators. Hope you like our explanation.

4. 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.
See Also-

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.
    >select 20+30 add from temp;
    FAILED: SemanticException [Error 10001]: Line 1:22 Table not found ‘temp’

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.