Hcatalog 简明教程
HCatalog - View
本章介绍如何在HCatalog中创建并管理 view 。数据库视图使用 CREATE 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 创建一个具有给定名称的视图。如果已经存在具有相同名称的表或视图，则会引发错误。可以使用 IF NOT EXISTS 来跳过该错误。
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.
如果没有提供列名，则视图的列名将自动从 defining SELECT expression 派生。
If no column names are supplied, the names of the view’s columns will be derived automatically from the defining SELECT expression.
Note − 如果 SELECT 包含未别名的标量表达式，例如 x+y，则生成的视图列名将采用 _C0、_C1 等形式。
Note − If the SELECT contains un-aliased 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 supplied. Comments are not automatically inherited from the underlying columns.
如果视图的 defining SELECT expression 无效，则 CREATE VIEW 语句将失败。
A CREATE VIEW statement will fail if the view’s defining SELECT expression is invalid.
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
以下是员工表数据。现在让我们看看如何创建一个名为 Emp_Deg_View 的视图，其中包含薪水高于 35000 的员工的 ID、姓名、职位和薪水字段。
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 | Masthanvali | 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;"Drop View Statement
DROP VIEW 删除指定视图的元数据。删除其他视图引用的视图时，不会给出警告（依赖视图会一直处于无效状态，并且必须由用户删除或重新创建）。
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 dangling as invalid and must be dropped or recreated by the user).