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 | +--------+---------+-------------+---------+--+