EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials Hive Tutorial Views in Hive

Views in Hive

Priya Pedamkar
Article byPriya Pedamkar

Updated March 24, 2023

views in hive

Introduction to Views in Apache Hive

Apache Hive supports the features of views, which are logical constructs and treated the same as tables. It makes it possible to save the query, on which all DML (Data Manipulation Language) commands can be performed. It is similarly used as views in SQL; however, views are created based on user requirement. In Hive, the query referencing the view is executed first, and then the result obtained is used in the rest of the query. Also, the Hive’s query planner helps in the execution plan.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

As Apache Hive supports array types and other primitive data types, LATERAL VIEW can also be created. This type of view’s major use is to convert or expand the arrays into rows using EXPLODE or INLINE function. Let us take an example for creating a LATERAL VIEW.

Code:

SELECT ph.* FROM (SELECT 0) b
LATERAL VIEW explode ( array(98992233442, 98993344556)) ph as ph_number

The output of this query as executed on HUE editor is:

Views in Hive-1.1

Why Do We Need Hive Views?

Let us know the reason why we need hive views:

  1. One of the major reasons for using it is to reduce the complexity of a query. There are many times when long or complicated queries become difficult to manage or develop because of too many nested queries. By turning the nested queries into views, end users can encapsulate a query’s complexity and create re-usable parts. This concept is similar to functions in any programming language or a layered design concept in software.
  2. Views are also widely used to filter or restrict data from a table based on the value of one or more columns. The use case of restricted data is when we don’t want the end-user to see all the base table information. So, we restrict the data by using the “where” clause on a table and store it as a view. The underlying table’s changes would not be reflected in the view; however, the underlying table must be present; otherwise, the view will fail.
  3. Along with the primitive data types, the Hive also supports data types like maps, arrays, and struct. This gives Hive an ability to consider a field as a map, rather than fixed columns. So, by using it, a base table can be divided into multiple logical constructs or tables.
  4. Conceptually, it is evident that the Hive first executes the views and then uses its results to evaluate or execute the query. This can be better explained with an example: if the view and the query both have a LIMIT clause, where view says LIMIT 200 and query says LIMIT 400, then we will get at most 200 results.
  5. It also facilitates creating partitioned views even if the underlying table is not partitioned.ALTER VIEW students ADD/ DROP PARTITION [ if not exists] partition_spec is one way. Whereas, for creating a partitioned view, the command used is CREATE VIEW…PARTITIONED ON, while for creating a partitioned table, the command is CREATE TABLE…PARTITION BY. Metastore does not store the partition location or partition column storage descriptors as no data is stored for a hive view partition.

Examples for Creating Views in Hive

Let us take an example of creating a view that brings in the college students’ details attending the “English” class. So, first, we will create a students table as below:

Views in Hive-1.2

1. Creating Table Students

Code:

Select * from students

Output:

Views in Hive-1.3

Code:

Select * from mark_sheet

Output:

Output-1.4

2. Creating view English_class

Code:

CREATE VIEW IF NOT EXISTS English_class AS
SELECT * FROM students AS a
INNER JOIN mark_sheet AS b
ON a.roll_id = b.roll_id
WHERE upper(b.subject) = “ENGLISH”;

Output:

Output:-1.5

Code:

Select * from English_class;

Output:

Output-1.6

This view can then be used to solve the more complex problem of returning the university rank of students who have attended the “English” class.

Code:

SELECT * FROM university_rnklist AS rank
JOIN English_class AS b
ON rank.roll_id = b.roll_id
WHERE rank <=100;

The clauses which can be used on VIEWS in the same way as they are used on TABLES are

  1. IF NOT EXISTS and COMMENT clause are used in the same way as in tables. The name of a view must be unique, and it cannot be the same as any table or database or view’s name.
  2. CREATE TABLE…LIKE clause can be used to copy a view into another.
    CREATE TABLE English_class2 LIKE English_class
  3. SHOW TABLES is used to show both tables and views. There is nothing like SHOW VIEWS in Hive.
  4. DESCRIBE and DESCRIBE EXTENDED statements can be used for views like for tables, however, for DESCRIBE EXTENDED, the detailed table information has a variable named typeable which has value = ‘virtual view’ for views.
  5. EXTERNAL and LOCATION clause also works for views.
  6. WHERE, ORDER BY, SORT BY and LIMIT clause can be used on views.
  7. ALTER VIEW works on views to alter the metadata of a view. Being read-only, INSERT INTO or LOAD INTO will not work on views.

3. Deleting a View

To delete a view created by you, use the below command.

Code:

DROP VIEW IF EXISTS English_class;

DROP TABLE command cannot be used to drop a view if the EXISTS clause works similarly for tables.

Advantages of Views

Given below are the advantages expressed.

  • It helps in reducing the complexity of a query. For example, if we need only 5 columns from a table of 50 columns, we can create a view.
  • It also helps in creating staging or intermediate tables which can be used to create queries further.
  • Views are read-only and thus commands like INSERT or LOAD INTO cannot be used for a view; thus, it helps maintain the integrity of base tables.

Conclusion

The view is a standard Relational database management concept, which is similar to that in SQL. It does not support materialized views, but this view has an advantage like other features of Apache Hive. The major difference between a view and a table is that view does not store data; it is just a logical construct. With the advantage of reducing the complexity of nested queries in Hive, views are widely used.

Recommended Articles

This is a guide to Views in Hive. Here we discuss the basic concept and why we need it and advantages and examples for creating a view in the hive. You may also have a look at the following articles to learn more –

  1. How to Create Views in MySQL?
  2. Creating Data into Hive Tables
  3. Features of Bucketing in Hive
  4. Features and Limitations of HiveQL
  5. Hive Inner Join | Versions and Examples
SPSS Course Bundle - 14 Courses in 1 | 5 Mock Tests
34+ Hours of HD Videos
14 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
HADOOP Course Bundle - 32 Courses in 1 | 4 Mock Tests
125+ Hour of HD Videos
32 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
INFORMATICA Course Bundle - 7 Courses in 1
47+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
HIVE Course Bundle - 7 Courses in 1
 23+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more