Summary -
In this topic, we described about the below sections -
VIEW is nothing but the table which can be derived from one or more tables. In other words, the view can be defined as “The Table which can be derived from one or more tables based on selection criteria”. A VIEW is an alternative representation of data which is derived from one or more tables.
VIEW can combine the data from different tables. VIEW can omit the columns from base tables and can only create on with some columns of the base tables. VIEW is temporary table in storage point of view and no memory will be allocated.
CREATE VIEW -
CREATE VIEW is used to create the VIEW.
Syntax -
CREATE VIEW [IF NOT EXISTS] view_name [(column_list)]
AS select_statement
IF NOT EXISTS clause used to create the view if the specified view is not existed.
Example -
Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.
| Std No | Std Name | Std Class | Std Grade |
|---|---|---|---|
| 1 | Pawan | 10 | A |
| 2 | Srinivas | 10 | A |
| 3 | Sridhar | 10 | A |
| 4 | Kumar | 10 | B |
The below command is used to create a view Std_details_v on the table std_details in the database std_db with all rows.
HCatalog> CREATE VIEW std_details_v AS
SELECT * FROM std_db.std_details;
Once the above statement successfully executed, the view will be created with all the columns of the table std_db.std_details.
ALTER VIEW -
ALTER VIEW used to alter the view after it created. ALTER VIEW involves changes to metadata in the metastore database. ALTER VIEW won’t touch any data files in HDFS.
Syntax -
ALTER VIEW [database_name.]view_name AS select_statement
ALTER VIEW [database_name.]view_name
RENAME TO [database_name.]view_name
Example -
Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.
| Std No | Std Name | Std Class | Std Grade |
|---|---|---|---|
| 1 | Pawan | 10 | A |
| 2 | Srinivas | 10 | A |
| 3 | Sridhar | 10 | A |
| 4 | Kumar | 10 | B |
The below command will alter the view change its source of creation from std_db.std_details to std_db1.std_details1.
HCatalog> ALTER VIEW std_details_v AS
SELECT * FROM std_db1.std_details1
Once the above statement successfully executed, the view will be created with all the columns of the table std_db1.std_details1.
DROP VIEW -
Deletes/Removes the specified view. DROP VIEW involves deletes the metadata in the metastore database. DROP VIEW won’t touch any data files in HDFS as those are related to table.
Syntax -
DROP VIEW [IF EXISTS] [database_name.]view_name
Example -
Assume we have a requirement to create student information with the columns student number, name, class, grade for a school.
| Std No | Std Name | Std Class | Std Grade |
|---|---|---|---|
| 1 | Pawan | 10 | A |
| 2 | Srinivas | 10 | A |
| 3 | Sridhar | 10 | A |
| 4 | Kumar | 10 | B |
The below command used to drop the view
HCatalog> DROP VIEW std_details_v
Once the above statement successfully executed, the view will be dropped.