Hive Create View Syntax and Examples

  • Post author:
  • Post last modified:October 3, 2019
  • Post category:BigData
  • Reading time:4 mins read

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.

Hadoop Hive Create View

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:

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?

Tags: ,