apache hive - Hive Built in Operators - hive tutorial - hadoop hive - hadoop hive - hiveql




What is an Operators in Hive?

  • An operator is an object that is capable of manipulating a value or operator.
  • For example, in "1 + 2", the "1" and "2" are the operands and the plus symbol is the operator.
  • Booleans are also considered operators where AND, OR, and NOT can also be used in most programming languages.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

There are four types of operators in Hive:

  • Arithmetic Operators
  • Relational Operators
  • Logical Operators
  • Complex Operators
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

Arithmetic Operators in Hive

  • These operators support various common arithmetic operations on the operands.
  • All of them return number types.
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

  • The following query adds two numbers, 25 and 35.
hive> SELECT 25+35 ADD FROM temp;
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

Result:


+--------+
|   ADD  |
+--------+
|  60    |
+--------+
 learn hive tutorial - arithmetic operator - hive example

apache hive - learn hive - hive tutorial - arithmetic operator - hive example

Relational Operators in Hive:

  • These operators are used to compare two operands.

To give below table describes the Relational 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.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

Example

  • Assume the wikitechy_employee table is composed of fields named Id, Name, Salary, Designation, and Dept.
  • Generate a query to retrieve the wikitechy_employee details whose Id is 2205.

+-----+--------------+--------+---------------------------+------+
| Id     | Name      | Salary | Designation         | Dept |
+-----+--------------+------------------------------------+------+
|2201 | Anu          | 45000  | Technical manager   | TP   |
|2202 | Bastin       | 45000  | Proofreader         | PR   |
|2203 | Celin        | 40000  | Technical writer    | TP   |
|2204 | Kavin        | 40000  | Hr Admin            | HR   |
|2205 | Karthi       | 30000  | Op Admin            | Admin|
+-----+--------------+--------+---------------------------+------+

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

Code

hive> SELECT * FROM wikitechy_employee WHERE Id=2205;
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

Output


+-----+-----------+-----------+----------------------------------+
| ID  | Name      | Salary    | Designation              | Dept  |
+-----+---------------+-------+----------------------------------+
|2205 | Karthi    | 30000     | Op Admin                 | Admin |
+-----+-----------+-----------+----------------------------------+

Below query is executed to retrieve the wikitechy_employee details whose salary is more than or equal to Rs 40000.

Code

hive> SELECT * FROM wikitechy_employee WHERE Salary>=40000;
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

Output


+-----+------------+--------+----------------------------+------+
| ID  | Name       | Salary | Designation                | Dept |
+-----+------------+--------+----------------------------+------+
|2201 | Anu        | 45000  | Technical manager          | TP   |
|2202 | Bastin     | 45000  | Proofreader                | PR   |
|2203 | Celin      | 40000  | Technical writer           | TP   |
|2204 | Kavin      | 40000  | Hr Admin                   | HR   |
+-----+------------+--------+----------------------------+------+

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.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

Example

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

Code

hive> SELECT * FROM wikitechy_employee WHERE Salary>40000 && Dept=TP;
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

Output


+------+--------------+-------------+-------------------+--------+
| ID   | Name         | Salary      | Designation       | Dept   |
+------+--------------+-------------+-------------------+--------+
|2201  | Anu          | 45000       | Technical manager | TP     |
+------+--------------+-------------+-------------------+--------+
 learn hive tutorial - logical operator - hive example

apache hive - learn hive - hive tutorial - logical operator - hive example

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.

Wikitechy Apache Hive tutorials provides you the base of all the following topics . Enjoy learning on big data , hadoop , data analytics , big data analytics , mapreduce , hadoop tutorial , what is hadoop , big data hadoop , apache hadoop , apache hive , hadoop wiki , hadoop jobs , hadoop training , hive tutorial , hadoop big data , hadoop architecture , hadoop certification , hadoop ecosystem , hadoop fs , apache pig , hadoop cluster , cloudera hadoop , hadoop download , hadoop mapreduce , hadoop workflow , hive data types , hadoop hive , pig hadoop , hadoop administration , hadoop installation , hive hadoop , learn hadoop , hadoop for dummies , hadoop commands , hive definition , hiveql , learnhive , hive sql , hive database , hive date functions , hive query , apache hive tutorial , hive apache , hive wiki , what is a hive , hive big data , programming hive , what is hive in hadoop , hive documentation , how does hive work

Related Searches to Hive Built in Operators

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×