- Hive Tutorial
- Hive - Home
- Hive - Introduction
- Hive - Installation
- Hive - Data Types
- Hive - Create Database
- Hive - Drop Database
- Hive - Create Table
- Hive - Alter Table
- Hive - Drop Table
- Hive - Partitioning
- Hive - Built-In Operators
- Hive - Built-In Functions
- Hive - Views And Indexes

- HiveQL
- HiveQL - Select Where
- HiveQL - Select Order By
- HiveQL - Select Group By
- HiveQL - Select Joins

- Hive Useful Resources
- Hive - Questions and Answers
- Hive - Quick Guide
- Hive - Useful Resources

- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who

# Hive - Built-in Operators

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

- Relational Operators
- Arithmetic Operators
- Logical Operators
- Complex Operators

## Relational Operators

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

Operator | Operand | Description |
---|---|---|

A = B | all primitive types | TRUE if expression A is equivalent to expression B otherwise FALSE. |

A != B | all primitive types | TRUE if expression A is not equivalent to expression B otherwise FALSE. |

A < B | all primitive types | TRUE if expression A is less than expression B otherwise FALSE. |

A <= B | all primitive types | TRUE 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 >= B | all primitive types | TRUE if expression A is greater than or equal to expression B otherwise FALSE. |

A IS NULL | all types | TRUE if expression A evaluates to NULL otherwise FALSE. |

A IS NOT NULL | all types | FALSE if expression A evaluates to NULL otherwise TRUE. |

A LIKE B | Strings | TRUE if string pattern A matches to B otherwise FALSE. |

A RLIKE B | Strings | 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 | Same as RLIKE. |

### Example

Let us assume the **employee** table is composed of fields named Id, Name, Salary, Designation, and Dept as shown below. Generate a query to retrieve the employee details whose Id is 1205.

+-----+--------------+--------+---------------------------+------+ | Id | Name | Salary | Designation | Dept | +-----+--------------+------------------------------------+------+ |1201 | Gopal | 45000 | Technical manager | TP | |1202 | Manisha | 45000 | Proofreader | PR | |1203 | Masthanvali | 40000 | Technical writer | TP | |1204 | Krian | 40000 | Hr Admin | HR | |1205 | Kranthi | 30000 | Op Admin | Admin| +-----+--------------+--------+---------------------------+------+

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

hive> SELECT * FROM employee WHERE Id=1205;

On successful execution of query, you get to see the following response:

+-----+-----------+-----------+----------------------------------+ | ID | Name | Salary | Designation | Dept | +-----+---------------+-------+----------------------------------+ |1205 | Kranthi | 30000 | Op Admin | Admin | +-----+-----------+-----------+----------------------------------+

The following query is executed to retrieve the employee details whose salary is more than or equal to Rs 40000.

hive> SELECT * FROM employee WHERE Salary>=40000;

On successful execution of query, you get to see the following response:

+-----+------------+--------+----------------------------+------+ | ID | Name | Salary | Designation | Dept | +-----+------------+--------+----------------------------+------+ |1201 | Gopal | 45000 | Technical manager | TP | |1202 | Manisha | 45000 | Proofreader | PR | |1203 | Masthanvali| 40000 | Technical writer | TP | |1204 | Krian | 40000 | Hr Admin | HR | +-----+------------+--------+----------------------------+------+

## Arithmetic Operators

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

Operators | Operand | 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 B. |

A / B | all number types | Gives the result of dividing B from A. |

A % B | all number types | Gives the reminder 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. |

### Example

The following query adds two numbers, 20 and 30.

hive> SELECT 20+30 ADD FROM temp;

On successful execution of the query, you get to see the following response:

+--------+ | ADD | +--------+ | 50 | +--------+

## Logical Operators

The operators are logical expressions. All of them return either TRUE or FALSE.

Operators | Operands | Description |
---|---|---|

A AND B | boolean | TRUE if both A and B are TRUE, otherwise FALSE. |

A && B | boolean | Same as A AND B. |

A OR B | boolean | TRUE if either A or B or both are TRUE, otherwise FALSE. |

A || B | boolean | Same as A OR B. |

NOT A | boolean | TRUE if A is FALSE, otherwise FALSE. |

!A | boolean | Same as NOT A. |

### Example

The following query is used to retrieve employee details whose Department is TP and Salary is more than Rs 40000.

hive> SELECT * FROM employee WHERE Salary>40000 && Dept=TP;

On successful execution of the query, you get to see the following response:

+------+--------------+-------------+-------------------+--------+ | ID | Name | Salary | Designation | Dept | +------+--------------+-------------+-------------------+--------+ |1201 | Gopal | 45000 | Technical manager | TP | +------+--------------+-------------+-------------------+--------+

## Complex Operators

These operators provide an expression to access the elements of Complex Types.

Operator | Operand | Description |
---|---|---|

A[n] | A is an Array and n is an int | It returns 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 | It returns the value corresponding to the key in the map. |

S.x | S is a struct | It returns the x field of S. |