HCatalog - View

This chapter describes how to create and manage a view in HCatalog. Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables, or another view.

To create a view, a user must have appropriate system privileges according to the specific implementation.

Create View Statement

CREATE VIEW creates a view with the given name. An error is thrown if a table or view with the same name already exists. You can use IF NOT EXISTS to skip the error.

If no column names are suppped, the names of the view s columns will be derived automatically from the defining SELECT expression.

Note − If the SELECT contains un-apased scalar expressions such as x+y, the resulting view column names will be generated in the form _C0, _C1, etc.

When renaming columns, column comments can also be suppped. Comments are not automatically inherited from the underlying columns.

A CREATE VIEW statement will fail if the view s defining SELECT expression is invapd.

Syntax

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

Example

The following is the employee table data. Now let us see how to create a view named Emp_Deg_View containing the fields id, name, Designation, and salary of an employee having a salary greater than 35,000.

+------+-------------+--------+-------------------+-------+
|  ID  |    Name     | Salary |    Designation    | Dept  |
+------+-------------+--------+-------------------+-------+
| 1201 |    Gopal    | 45000  | Technical manager |  TP   |
| 1202 |   Manisha   | 45000  | Proofreader       |  PR   |
| 1203 | Masthanvap | 30000  | Technical writer  |  TP   |
| 1204 |    Kiran    | 40000  | Hr Admin          |  HR   |
| 1205 |   Kranthi   | 30000  | Op Admin          | Admin |
+------+-------------+--------+-------------------+-------+

The following is the command to create a view based on the above given data.

./hcat –e "CREATE VIEW Emp_Deg_View (salary COMMENT   salary more than 35,000 )
   AS SELECT id, name, salary, designation FROM employee WHERE salary ≥ 35000;"

Output

OK
Time taken: 5.3 seconds

Drop View Statement

DROP VIEW removes metadata for the specified view. When dropping a view referenced by other views, no warning is given (the dependent views are left dangpng as invapd and must be dropped or recreated by the user).

Syntax

DROP VIEW [IF EXISTS] view_name;

Example

The following command is used to drop a view named Emp_Deg_View.

DROP VIEW Emp_Deg_View;