Select Query with Where clause in Hive

We will see how to write simple ‘Select’ queries with Where clause in Hive.

Hive Table

We have a table ‘Employee’ in Hive with the following schema.

0: jdbc:hive2://localhost:10000> desc Employee;
+-------------+------------+----------+--+
|  col_name   | data_type  | comment  |
+-------------+------------+----------+--+
| id          | bigint     |          |
| name        | string     |          |
| age         | int        |          |
| salary      | bigint     |          |
| department  | string     |          |
+-------------+------------+----------+--+

Simple Select Query

Select all the columns from the table in the select query:-

0: jdbc:hive2://localhost:10000> select * from Employee;
+------------------+--------------------+-------------------+----------------------+--------------------------+--+
| employee.id      | employee.name      | employee.age      | employee.salary      | employee.department      |
+------------------+--------------------+-------------------+----------------------+--------------------------+--+
| 60001            | Sudip              | 34                | 62000                | HR                       |
| 70001            | Suresh             | 45                | 76000                | FINANCE                  |
| 80001            | Aarti              | 25                | 37000                | BIGDATA                  |
| 80002            | Neha               | 27                | 39000                | FINANCE                  |
| 80003            | Rajesh             | 29                | 59000                | BIGDATA                  |
+------------------+--------------------+-------------------+----------------------+--------------------------+--+

We can select only specific columns from the table in the Select Query as shown below :-

0: jdbc:hive2://localhost:10000> select id, name, department from Employee;
+--------+---------+-------------+--+
|   id   |  name   | department  |
+--------+---------+-------------+--+
| 60001  | Sudip   | HR          |
| 70001  | Suresh  | FINANCE     |
| 80001  | Aarti   | BIGDATA     |
| 80002  | Neha    | FINANCE     |
| 80003  | Rajesh  | BIGDATA     |
+--------+---------+-------------+--+

Select Query With a Where Clause

We can filter out the data by using where clause in the select query. If we want to see employees having salary greater than 50000 OR employees from department ‘BIGDATA’, then we can add a where clause in the select query and the result will get modified accordingly.

select id, name, department, salary from Employee where salary > 50000;
+--------+---------+-------------+---------+--+
|   id   |  name   | department  | salary  |
+--------+---------+-------------+---------+--+
| 60001  | Sudip   | HR          | 62000   |
| 70001  | Suresh  | FINANCE     | 76000   |
| 80003  | Rajesh  | BIGDATA     | 59000   |
+--------+---------+-------------+---------+--+

Where clause with AND and OR Operators

We can also have multiple conditions in the where clause by using AND and OR operators. If AND operator is used then the rows will be included in the result only if both the conditions surrounding the AND operator are true. If OR operator is used then the rows will be included in the result if any of the conditions surrounding the OR operator is true.

#Select all the employees having salary >50000 from BIGDATA department
select id, name, department, salary from Employee where salary > 50000 and department='BIGDATA';
+--------+---------+-------------+---------+--+
|   id   |  name   | department  | salary  |
+--------+---------+-------------+---------+--+
| 80003  | Rajesh  | BIGDATA     | 59000   |
+--------+---------+-------------+---------+--+

#Select all the employees in their twentys
select id, name, department, age from Employee where age >= 20 and age <30;
+--------+---------+-------------+------+--+
|   id   |  name   | department  | age  |
+--------+---------+-------------+------+--+
| 80001  | Aarti   | BIGDATA     | 25   |
| 80002  | Neha    | FINANCE     | 27   |
| 80003  | Rajesh  | BIGDATA     | 29   |
+--------+---------+-------------+------+--+

#Select all the employees from FINANCE department as well as employees having salary > 50000
select id, name, department, salary from Employee where salary > 50000 or department='FINANCE';
+--------+---------+-------------+---------+--+
|   id   |  name   | department  | salary  |
+--------+---------+-------------+---------+--+
| 80003  | Rajesh  | BIGDATA     | 59000   |
| 70001  | Suresh  | FINANCE     | 76000   |
| 80002  | Neha    | FINANCE     | 39000   |
| 60001  | Sudip   | HR          | 62000   |
+--------+---------+-------------+---------+--+

Where clause with Like Operator

We use like operator in the where clause to select rows based on some patterns in column values.

#Select all the employees whose names start with 'S'
select id, name, department, age from Employee where name like "S%";
+--------+---------+-------------+------+--+
|   id   |  name   | department  | age  |
+--------+---------+-------------+------+--+
| 60001  | Sudip   | HR          | 34   |
| 70001  | Suresh  | FINANCE     | 45   |
+--------+---------+-------------+------+--+

#Select all the employees whose names contains 'es'
select id, name, department, age from Employee where name like "%es%";
+--------+---------+-------------+------+--+
|   id   |  name   | department  | age  |
+--------+---------+-------------+------+--+
| 70001  | Suresh  | FINANCE     | 45   |
| 80003  | Rajesh  | BIGDATA     | 29   |
+--------+---------+-------------+------+--+

#Select all the employees whose names ends with 'p'
select id, name, department, age from Employee where name like "%p";
+--------+--------+-------------+------+--+
|   id   |  name  | department  | age  |
+--------+--------+-------------+------+--+
| 60001  | Sudip  | HR          | 34   |
+--------+--------+-------------+------+--+

Where clause with IN Operator

We use IN operator in the where clause to select the rows which matches any of the values specified in the IN operator’s list.

#Select the employee from HR and BIGDATA department
select id, name, department, salary from Employee where department in ('HR', 'BIGDATA');
+--------+---------+-------------+---------+--+
|   id   |  name   | department  | salary  |
+--------+---------+-------------+---------+--+
| 80001  | Aarti   | BIGDATA     | 37000   |
| 80003  | Rajesh  | BIGDATA     | 59000   |
| 60001  | Sudip   | HR          | 62000   |
+--------+---------+-------------+---------+--+

Where clause with NOT IN Operator

We use NOT IN operator in the where clause to select the rows which do not match any of the values specified in the NOT IN operator’s list.

#Select all the employees not in the HR department
select id, name, department, salary from Employee where department not in ('HR');
+--------+---------+-------------+---------+--+
|   id   |  name   | department  | salary  |
+--------+---------+-------------+---------+--+
| 80001  | Aarti   | BIGDATA     | 37000   |
| 80003  | Rajesh  | BIGDATA     | 59000   |
| 70001  | Suresh  | FINANCE     | 76000   |
| 80002  | Neha    | FINANCE     | 39000   |
+--------+---------+-------------+---------+--+

 

Leave a Reply

Your email address will not be published. Required fields are marked *