Views In Apache Hive

The VIEW database object is one of the most often used database objects in SQL, and every SQL developer is familiar with its creation, use, and benefits. This article is designed for bigdata learners who have had little or no prior experience with SQL.

A VIEW is a virtual table with rows and columns defined by a SQL query that is stored in the database. We can build a VIEW by selecting all or selected columns from one or more database tables. A VIEW can include all of the data in a table or only some rows based on a set of criteria.

Views are excellent in hiding the data complexity; Views can also useful to join and simplify numerous tables into a single virtual table. Views can function as aggregated tables, in which the database engine aggregates data (sum, average, etc.). Views can represent a subset of the data in a table. When data is accessed and inserted via a view, the DBMS can automatically examine the data to ensure that it satisfies the given integrity requirements. The view name should be unique in a database.

The VIEW can be created, treated and dropped in the same manner as a table can.

Please keep in mind that the syntax below applies not just to SQL but also to Apache Hive and Cloudera’s Impala.

Example:
CREATE VIEW Emp_View
AS
SELECT * FROM Emp WHERE DeptNo IN(10,20);

CREATE VIEW EmpDeptView
AS
SELECT Ename, DName FROM Emp
INNER JOIN Dept ON Emp.DeptNo = Dept.DeptNo;

CREATE VIEW EmpDeptView
AS
SELECT Ename, DName FROM Emp
INNER JOIN Dept ON Emp.DeptNo = Dept.DeptNo
WHERE DName = ‘Sales’;

SELECT * FROM Emp_View;
SELECT Ename, DeptNo FROM Emp_View;
SELECT * FROM EmpDeptView;