You can use Hive create view to create a virtual table based on the result-set of a complex SQL statement that may have multiple table joins. The CREATE VIEW statement lets you create a shorthand abbreviation for a more complex and complicated query.
Apache Hive view is purely a logical construct (an alias for a complex query) with no physical data behind it.
Note that, Hive view is different from lateral view.
Read:
- Hive CREATE INDEX to Optimize and Improve Query Performance
- Hadoop Hive Bucket Concept and Bucketing Examples
- Hive Create Table Command and Examples
- Apache Hive Create External Tables and Examples
- Different Hive Join Types and Examples
In this article, we will see how to define and manage Apache hive views.
Hive Create View Syntax
CREATE VIEW [IF NOT EXISTS] view_name [(column_list)] AS select_statement;
Hive Create View Examples
Now let us consider example of creating simple view on top of student tables. This example involves single table to show how to create view. The real-life view could have hundereds of lines of code written to get particular result.
hive> create view if not exists students_vw > as select * from students where city = 'Bangalore'; OK Time taken: 0.553 seconds
Now verify the Hive view by using SELECT statements:
hive> select * from students_vw; OK 3 CDE Bangalore 5 EFG Bangalore Time taken: 0.205 seconds, Fetched: 2 row(s)
Hive ALTER VIEW
With use of Hive ALTER VIEW statement, you can change the query in the AS clause or rename the view to other name as per your requirements.
Hive ALTER VIEW Syntax
Below is the Hive ALTER VIEW Syntax:
ALTER VIEW [database_name.]view_name AS select_statement; ALTER VIEW [database_name.]view_name RENAME TO [database_name.]view_name;
Hive ALTER VIEW Examples
Below is the examples that shows how to use the Hive ALTER VIEW statement:
hive> alter view students_vw as select * from students; OK Time taken: 0.245 seconds hive> select * from students_vw; OK 4 DEF Mumbai 1 ABC London 3 CDE Bangalore 2 BCD Mumbai 5 EFG Bangalore Time taken: 0.155 seconds, Fetched: 5 row(s) hive> alter view students_vw rename to students_view; OK Time taken: 0.206 seconds hive> select * from students_view; OK 4 DEF Mumbai 1 ABC London 3 CDE Bangalore 2 BCD Mumbai 5 EFG Bangalore Time taken: 0.158 seconds, Fetched: 5 row(s)
Hive DROP VIEW
You can use Hive DROP VIEW statement to remove view from Hive metastore. This statement removes the specified view, which was originally created by the CREATE VIEW statement.
Hive DROP VIEW Syntax
Below is the Hive DROP VIEW syntax:
DROP VIEW [IF EXISTS] [db_name.]view_name;
Hive DROP VIEW Examples
Now let us drop view which we have created as a part of this tutorial:
hive> drop view if exists students_view; OK Time taken: 0.334 seconds
Related Article
What is Hive Lateral View and How to use it?