Share Ideas, Start Something Good.

This topic contains 0 replies, has 1 voice, and was last updated by  Avikumar 3 years, 3 months ago.

Viewing 1 post (of 1 total)
• Author
Posts
• #1271

Avikumar
Participant

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:

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 |
+—–+————–+——–+—————————+——+
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 |
+—–+—————+——-+———————————-+
+—–+———–+———–+———————————-+
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:

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:

+——–+
+——–+
| 50 |
+——–+

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

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.

Built-In Functions:
Hive supports the following built-in functions:

Example
The following queries demonstrate some built-in functions:

round() function:
hive> SELECT round(2.6) from temp;
On successful execution of query, you get to see the following response:

floor() function:
hive> SELECT floor(2.6) from temp;
On successful execution of the query, you get to see the following response:

floor() function:
hive> SELECT ceil(2.6) from temp;
On successful execution of the query, you get to see the following response:

Creating a View
You can create a view at the time of executing a SELECT statement. The syntax is as follows:

CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], …) ]
[COMMENT table_comment]
AS SELECT …

The following query retrieves the employee details using the above scenario:

hive> CREATE VIEW emp_30000 AS
SELECT * FROM employee
WHERE salary>30000;
Dropping a View
Use the following syntax to drop a view:

DROP VIEW view_name:
The following query drops a view named as emp_30000:

hive> DROP VIEW emp_30000;
Creating an Index
An Index is nothing but a pointer on a particular column of a table. Creating an index means creating a pointer on a particular column of a table. Its syntax is as follows:

CREATE INDEX index_name
ON TABLE base_table_name (col_name, …)
AS ‘index.handler.class.name’
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name=property_value, …)]
[IN TABLE index_table_name]
[PARTITIONED BY (col_name, …)]
[
[ ROW FORMAT …] STORED AS …
| STORED BY …
]
[LOCATION hdfs_path]
[TBLPROPERTIES (…)]

Dropping an Index
The following syntax is used to drop an index:

DROP INDEX <index_name> ON <table_name>
The following query drops an index named index_salary:

hive> DROP INDEX index_salary ON employee;

Viewing 1 post (of 1 total)

The forum ‘General Discussion’ is closed to new topics and replies.