Summary -
In this topic, we described about the below sections -
There are four types of built-in operators available in Hive.
- Relational
- Arithmetic
- Logical
- Complex Operators
Let us discuss about each type in detail.
Relational Operators –
Relational Operators are used to compare the operands. Relational Operators generate a TRUE or FALSE value depending on condition.
| Relational Operator | Operand types | 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 A matches the SQL simple regular expression B, otherwise FALSE. |
| A RLIKE B | strings | 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 | strings | NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B, otherwise FALSE. |
Example -
Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.
| Std No | Std Name | Std Class | Std Grade |
|---|---|---|---|
| 1 | Pawan | 10 | A |
| 2 | Srinivas | 10 | A |
| 3 | Sridhar | 10 | A |
| 4 | Kumar | 10 | B |
The below are the examples which shows usage of relational operators on table columns.
| Relational Operator | Example Requirement | Example Query |
|---|---|---|
| A = B | Get the student details studying 10th class | SELECT * FROM std_db.std_details WHERE std_class = 10 |
| A != B | Get the student details not studying 10th class | SELECT * FROM std_db.std_details WHERE std_class != 10 |
| A < B | Get the student details from 1st to 7th class | SELECT * FROM std_db.std_details WHERE std_class < 8 |
| A <= B | Get the student details from 1st to 7th class | SELECT * FROM std_db.std_details WHERE std_class <= 7 |
| A > B | Get the student details from 8th to 10th class | SELECT * FROM std_db.std_details WHERE std_class > 7 |
| A >= B | Get the student details from 8th to 10th class | SELECT * FROM std_db.std_details WHERE std_class >= 8 |
| A IS NULL | Get the student details without having any class details in database. | SELECT * FROM std_db.std_details WHERE std_class IS NULL |
| A IS NOT NULL | Get the student details having any class details in database. | SELECT * FROM std_db.std_details WHERE std_class IS NOT NULL |
| A LIKE B | Get the student details whose names starts with S letter. | SELECT * FROM std_db.std_details WHERE std_name LIKE ‘S%’ |
| A RLIKE B | Get the student details whose names starts with S letter. | SELECT * FROM std_db.std_details WHERE std_name RLIKE ‘S%’ |
| A REGEXP B | Get the student details whose names starts with S letter. | SELECT * FROM std_db.std_details WHERE std_name REGEXP ‘S%’ |
Arithmetic Operators –
Arithmetic operators are to support common arithmetic operations. The input and output by using arithmetic operators are numbers or numeric.
| Arithmetic Operators | 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 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 -
Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.
| Std No | Std Name | Std Class | Std Grade |
|---|---|---|---|
| 1 | Pawan | 10 | A |
| 2 | Srinivas | 10 | A |
| 3 | Sridhar | 10 | A |
| 4 | Kumar | 10 | B |
The below are the examples which shows usage of arithmetic operators on table columns.
| Arithmetic Operators | Example Requirement | Example Query |
|---|---|---|
| A + B | Adding 20 and 30 | SELECT 20+30 AS RESULT FROM temp |
| A - B | Subtracting 20 from 50 | SELECT 50-20AS RESULT FROM temp |
| A * B | Multiplying 3 and 4 | SELECT 3*4 AS RESULT FROM temp |
| A / B | Dividing 100 by 10 | SELECT 100/10 AS RESULT FROM temp |
| A % B | 10% | SELECT 100%10 AS RESULT FROM temp |
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 | Operands types | 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 |
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 | returns the value corresponding to the key in the map |
| S.x | S is a struct | returns the x field of S |