Integrated operators in Hive (Built-in Operators)

Tram Ho

This chapter explains the integrated operators of Hive. There are four types of operators in Hive:

Relational operator

Arithmetic operators

Logical operator

Complex operators

1. Relational Operators – Relational Operators

These operators are used to compare two operands. The following table describes the relational operators available in Hive:

OperatorOperandDescribe
A = BAll primitive formsTRUE if expression A is equivalent to expression B otherwise FALSE.
A! = BAll primitive formsTRUE if expression A is not equivalent to expression B otherwise FALSE.
A <BAll primitive formsTRUE if expression A is less than expression B otherwise FALSE.
A <= BAll primitive formsTRUE if expression A is less than or equal to expression B otherwise FALSE.
A> BAll primitive formsTRUE if expression A is greater than expression B otherwise FALSE.
A> = BAll primitive formsTRUE if expression A is greater than or equal to expression B otherwise FALSE.
A IS NULLAll formsTRUE if expression A evaluates to NULL otherwise FALSE.
A IS NOT NULLAll formsFALSE if expression A evaluates to NULL otherwise TRUE.
A LIKE BStringsTRUE if the string pattern A matches B if not then FALSE.
A RLIKE BStringsNULL if A or B is NULL, TRUE if any of A’s substrings match the Java B regular expression, otherwise FALSE.
A REGEXP BStringsLike RLIKE

For example: We assume the employee table includes many fields: Id, Name, Salary, Designation, Dept as follows. Create a query to retrieve employee information with Id = 1205.

The following query is executed to retrieve employee information using the table above:

Successful execution of the query results in the following response:

The following query executes to retrieve employee information with salary greater than or equal to Rs 4000.

And response:

2. Arithmetic Operators – Arithmetic operators

These operators support various common arithmetic operations on operands. All return numeric types. The following table describes the arithmetic operators available in Hive:

OperatorOperandDescribe
A + BAll numbersReturns the sum of A and B
A – BAll numbersGive the result A minus B
A * BAll numbersThe result is the multiplication of 2 numbers A and B
A / BAll numbersResult A divided by B
A% BAll numbersResult A minus B to take the remainder
A & BAll numbersReturns the AND bits of A and B
ABAll numbers
A ^ BAll numbersGiven the XOR bit results of A and B
~ AAll numbersReturns the NOT bit of A

Example: The following query sums 2 numbers: 20 and 30:

Response upon successful execution:

3. Logical Operators – Logical operators

Operators are logical expressions. All return TRUE or FALSE.

OperatorOperandDescribe
A AND BbooleanTRUE if both A and B are TRUE, if different then FALSE
A&& BbooleanLike A AND B
A OR BbooleanTRUE if either 1 or both A and B are TRUE, the other returns FALSE
AB
NOT AbooleanReturns TRUE if A is FALSE, and otherwise returns FALSE
AbooleanLike NOT A

Example:

The following query retrieves employee information whose TP and Salary are greater than Rs 40000.

Successful execution will result:

4. Complex Operators – Complex operators

These operators provide an expression for accessing Complex Types elements.

OperatorOperandDescribe
A [n]A is an Array and n is an intReturns the nth element of array A. The first element has index = 0
M [key]M is a Map <K, V> and the key has the form KReturns the value corresponding to the key in the map
ManufacturingS is structReturns the x field of S
Share the news now

Source : Viblo