Hive Tutorial 21 : Hive Views

RDBMS systems have the concept of "views" that are entities similar to a table. 
Hive also has the concept of a View as a "copy" of the table. 
The main reason for having a view of a table is to simplify some of the complexities of a larger table into a more Flat structure. For instance, if you have a table that has 100 columns, but you are only interested in 5, you could create a View with those 5 columns. 

Unlike some RDBMS, once the Hive view is created, its schema is frozen immediately.
Subsequent changes in the underlying table will not be reflected in the View; and if the table is dropped, the view will fail. 


Hive supports only logical view but not physical view

Creation of View:

Syntax:

Create VIEW < VIEWNAME> AS SELECT

Example:

Hive>Create VIEW Sample_ViewAS SELECT * FROM employees WHERE salary>25000

In this example, we are creating view Sample_View where it will display all the row values with salary field greater than 25000.

Benefits:

• To hide the underlying table and column names, to minimise maintenance problems if those names change. In that case, you re-create the view using the new names, and all queries that use the view rather than the underlying tables keep running with no changes.
• To experiment with optimization techniques and make the optimized queries available to all applications. For example, if you find a combination of WHERE conditions, join order, join hints, and so on that works the best for a class of queries, you can establish a view that incorporates the best-performing techniques. Applications can then make relatively simple queries against the view, without repeating the complicated and optimized logic over and over. If you later find a better way to optimize the original query, when you re-create the view, all the applications immediately take advantage of the optimized base query.
• To simplify a whole class of related queries, especially complicated queries involving joins between multiple tables, complicated expressions in the column list, and other SQL syntax that makes the query difficult to understand and debug. For example, you might create a view that joins several tables, filters using several WHERE conditions, and selects several columns from the result set. Applications might issue queries against this view that only vary in their LIMIT, ORDER BY, and similar simple clauses.
• For queries that require repeating complicated clauses over and over again, for example in the select list, ORDER BY, and GROUP BY clauses, you can use the WITH clause as an alternative to creating a view.