Apache Hive Views

What is Apache Hive View?

A view is a logical unit of the table which allows a query to be saved and treated as a table. The view does not store data like a table. When a query locates a view, the information in its definition is combined with the rest of the query by Hive’s query planner. If changes are applied to the data in a relevant underlying table then it will be reflected in the data shown in subsequent invocations of the view.

Views provide the following advantages over tables.

  • A view is a subpart of a table.
  • A view can be created by joining multiple tables.
  • Views can hide the complexity of data.
  • The view is just a definition so it doesn't take space in the database.

Create View

In Apache Hive, a view can be created using the Create view command as mentioned below.

For this example we have created a table “cloudduggudb.employee_detail” which will have the detail of employees like empid, name, salary, and designation, we want to fetch results of employees who earn more than 40000 salaries.

Eid Name Salary Designation 1201 Ankit 45000 Technical manager 1202 Avinash 45000 Proof reader 1203 Sarvesh 40000 Technical writer 1204 Bhavani 40000 Hr Admin 1205 Kanheya 30000 Op Admin 1206 deepak 60000 lead

Create View Syntax:
CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]
[COMMENT table_comment]
AS SELECT ...

Create View Statement:
CREATE VIEW cloudduggudb.empsalary AS
SELECT * FROM cloudduggudb.employee_detail
WHERE salary>40000;

Command Output:

create view


Drop View

We can drop a view using the drop view command.

Drop View Syntax:
DROP VIEW view_name;

Drop View Statement:
DROP VIEW cloudduggudb.empsalary;

Command Output:

drop view