概述
视图是一个纯粹的逻辑数据结构，通过隐藏复杂度（如joins, subqueries, filters）来简化查询。之所以说视图是一种逻辑结果，是因为视图只有元数据信息，不会关联HDFS中的任何存储，也就是不存储任何实际数据。视图一旦被创建，它的schema信息就会被冻结，如果视图底层关联的表的结构发生了变化（比如增加了字段），并不会反射到视图中。如果底层关联的表被删除或发生了变化，那么对视图的查询操作将会失败。另外，视图是只读的，不能使用LOAD/INSERT/ALTER语句将数据插入其中。
创建视图
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)]
AS SELECT ...;
CREATE VIEW语句可以创建指定名称的视图。创建时，如果没有提供字段名，将会从SELECT表达式自动衍生。如果SELECT中包含了未指定别名的数据表达式，如 x + y。那么视图将按照_C0, _C1这样的格式生成字段名。
示例
说明：创建视图不会触发yarn作业，因为这仅仅会修改hive的元数据信息。但是，当查询视图的时候可能会触发yarn作业。
> CREATE VIEW IF NOT EXISTS employee_view_skills
AS
SELECT
name, skills_score['DB'] as DB,
skills_score['Perl'] as Perl,
skills_score['Python'] as Python,
skills_score['Sales'] as Sales,
skills_score['HR'] as HR
FROM employee_internal;
从Hive2.2.0以后，可以使用SHOW VIEWS语句查看数据库中的视图。之前的版本可以使用 SHOW TABLES 语句查看：
> SHOW VIEWS;
+-----------------------+
| tab_name |
+-----------------------+
| employee_view_skills |
+-----------------------+
查看视图
和表一样，可以使用DESC ，SHOW CREATE TABLE语句查看视图的信息。
示例
> SHOW CREATE TABLE employee_view_skills;
+----------------------------------------------------+
| createtab_stmt |
+----------------------------------------------------+
| CREATE VIEW `employee_view_skills` AS SELECT |
| `employee_internal`.`name`, `employee_internal`.`skills_score`['DB'] as `DB`, |
| `employee_internal`.`skills_score`['Perl'] as `Perl`, |
| `employee_internal`.`skills_score`['Python'] as `Python`, |
| `employee_internal`.`skills_score`['Sales'] as `Sales`, |
| `employee_internal`.`skills_score`['HR'] as `HR` |
| FROM `test2`.`employee_internal` |
+----------------------------------------------------+
> DESC FORMATTED employee_view_skills;
+-------------------------------+----------------------------------------------------+----------------------------------------------------+
| col_name | data_type | comment |
+-------------------------------+----------------------------------------------------+----------------------------------------------------+
| # col_name | data_type | comment |
| name | string | |
| db | int | |
| perl | int | |
| python | int | |
| sales | int | |
| hr | int | |
| | NULL | NULL |
| # Detailed Table Information | NULL | NULL |
| Database: | test2 | NULL |
| OwnerType: | USER | NULL |
| Owner: | hadoop | NULL |
| CreateTime: | Fri Sep 27 18:16:27 CST 2019 | NULL |
| LastAccessTime: | UNKNOWN | NULL |
| Retention: | 0 | NULL |
| Table Type: | VIRTUAL_VIEW | NULL |
| Table Parameters: | NULL | NULL |
| | bucketing_version | 2 |
| | transient_lastDdlTime | 1569579387 |
| | NULL | NULL |
| # Storage Information | NULL | NULL |
| SerDe Library: | null | NULL |
| InputFormat: | org.apache.hadoop.mapred.TextInputFormat | NULL |
| OutputFormat: | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat | NULL |
| Compressed: | No | NULL |
| Num Buckets: | -1 | NULL |
| Bucket Columns: | [] | NULL |
| Sort Columns: | [] | NULL |
| | NULL | NULL |
| # View Information | NULL | NULL |
| Original Query: | SELECT | NULL |
| | | name, skills_score['DB'] as DB, |
| | | skills_score['Perl'] as Perl, |
| | | skills_score['Python'] as Python, |
| | | skills_score['Sales'] as Sales, |
| | | skills_score['HR'] as HR |
| | | FROM employee_internal |
| Expanded Query: | SELECT | NULL |
| | | `employee_internal`.`name`, `employee_internal`.`skills_score`['DB'] as `DB`, |
| | | `employee_internal`.`skills_score`['Perl'] as `Perl`, |
| | | `employee_internal`.`skills_score`['Python'] as `Python`, |
| | | `employee_internal`.`skills_score`['Sales'] as `Sales`, |
| | | `employee_internal`.`skills_score`['HR'] as `HR` |
| | | FROM `test2`.`employee_internal` |
+-------------------------------+----------------------------------------------------+----------------------------------------------------+
修改视图
使用ALTER VIEW语句可以修改视图。
修改视图的属性
ALTER VIEW [db_name.]view_name SET TBLPROPERTIES table_properties;
table_properties:
: (property_name = property_value, property_name = property_value, ...)
示例：修改注释
> ALTER VIEW employee_view_skills SET TBLPROPERTIES ('comment'='This is a view');
> DESC FORMATTED employee_view_skills;
+-------------------------------+----------------------------------------------------+----------------------------------------------------+
| col_name | data_type | comment |
+-------------------------------+----------------------------------------------------+----------------------------------------------------+
...
| Table Parameters: | NULL | NULL |
| | bucketing_version | 2 |
| | comment | This is a view |
...
+-------------------------------+----------------------------------------------------+----------------------------------------------------+
重定义视图
ALTER VIEW [db_name.]view_name AS select_statement;
使用Alter View As Select语句可以修改视图的定义，修改的视图必须是已经存在的。
示例：
> ALTER VIEW employee_view_skills as SELECT * from employee_internal;
> desc employee_view_skills;
+---------------+--------------------------------+----------+
| col_name | data_type | comment |
+---------------+--------------------------------+----------+
| name | string | |
| work_place | array<string> | |
| gender_age | struct<gender:string,age:int> | |
| skills_score | map<string,int> | |
| depart_title | map<string,array<string>> | |
+---------------+--------------------------------+----------+
删除视图
DROP VIEW [IF EXISTS] [db_name.]view_name;
说明：如果一个视图引用了另一个视图，那么被引用的视图被删除时不会抛出异常信息，引用视图会变得不可用。
示例
> DROP VIEW employee_view_skills;
> SHOW views;
+-----------+
| tab_name |
+-----------+
+-----------+
参考
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
书籍 Apache Hive Essentials Second Edition (by Dayong Du) Chapter 3
扫一扫
6301
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
