HCatalog Views

A VIEW is essentially a table that is derived from one or more base tables. A VIEW provides an alternative representation of data, allowing you to combine information from different tables.

a VIEW can exclude certain columns from the base tables, creating a customized version that includes only selected columns. From a storage perspective, a VIEW is considered a temporary table, meaning that no memory is allocated for it.

CREATE VIEW -

CREATE VIEW statement is used to create the view.

Syntax -

CREATE VIEW [IF NOT EXISTS] view_name [(column_list)]
 [COMMENT view_comment]
 [TBLPROPERTIES (property_name = property_value, ...)]
 AS SELECT ...;

IF NOT EXISTS clause is used to create the view if the specified view is not existed.

Example -

The following is the student table details. Now let us see how to create a view named std_details_v containing all the columns from std_details.

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.

./hcat e "CREATE VIEW std_details_v AS 
SELECT * FROM std_db.std_details;"

Once the above statement successfully executed, the view gets created with all the columns of the table std_db.std_details.

ALTER VIEW -

ALTER VIEW is used to modify the view after its creation and changes to the metadata in the metastore database. It does not affect any data files stored in HDFS.

Syntax -

ALTER VIEW [database_name.]view_name AS select_statement

	ALTER VIEW [database_name.]view_name

Example -

The below command will alter the view change its source of creation from std_db.std_details to std_db1.std_details1.

./hcat e "ALTER VIEW std_details_v AS 
SELECT * FROM std_db1.std_details1"

Once the above statement successfully executed, the view gets created with all the columns of the table std_db1.std_details1.

DROP VIEW -

Deletes or removes the specified view. The DROP VIEW command deletes the metadata in the metastore database. However, DROP VIEW will not affect any data files in HDFS, as those are associated with tables.

Syntax -

DROP VIEW [IF EXISTS] [database_name.]view_name

Example -

The below command used to drop the view std_details_v -

./hcat e "DROP VIEW std_details_v;"

Once the above statement successfully executed, the view gets dropped.

---