**1. 视图的特点** * 通过隐藏子查询、连接查询、函数来简化查询的逻辑结构； * 只保存定义，不存储数据，相当于是建立了索引； * 如果删除或者更改基础表，因为视图是基于表建立的，则查询视图将失败； * <mark>视图是只读，不能插入或装载数据；</mark> * 视图在查询和修改操作基本和表一样，只是不能插入数据。 **2. 应用场景** * 将特定的列提供给用户，保护数据隐私； * 用于查询语句复杂的场景； **3. 示例：创建关于people表的视图** （1）数据`people.txt` ```xml 001,tom,23,2019-03-16 002,jack,12,2019-03-13 003,robin,14,2018-08-13 004,justin,34,2018-10-12 005,jarry,24,2017-11-11 006,jasper,24,2017-12-12 ``` （2）创建people表并导入数据 ```sql create table if not exists people( id int, name string, age int, peo_date date ) row format delimited fields terminated by ','; load data local inpath "/hdatas/people.txt" into table people; ``` （3）为people创建多个视图 ```sql #### 创建整张表的视图 #### create view if not exists view_001 as select * from people; #### 视图可以像表那样进行查询 #### 0: jdbc:hive2://hadoop101:10000> select * from view_001; +--------------+----------------+---------------+--------------------+--+ | view_001.id | view_001.name | view_001.age | view_001.peo_date | +--------------+----------------+---------------+--------------------+--+ | 1 | tom | 23 | 2019-03-16 | | 2 | jack | 12 | 2019-03-13 | | 3 | robin | 14 | 2018-08-13 | | 4 | justin | 34 | 2018-10-12 | | 5 | jarry | 24 | 2017-11-11 | | 6 | jasper | 24 | 2017-12-12 | +--------------+----------------+---------------+--------------------+--+ #### 只为name一列创建视图 #### create view if not exists view_002 as select name from people; -- 只查询到name一列的数据，做到保护其它列的数据隐私 0: jdbc:hive2://hadoop101:10000> select * from view_002; +----------------+--+ | view_002.name | +----------------+--+ | tom | | jack | | robin | | justin | | jarry | | jasper | +----------------+--+ #### 查询当前数据库有多少个视图 #### -- 低于hive2.2.0版本 0: jdbc:hive2://hadoop101:10000> show tables; -- hive2.2.0+版本 0: jdbc:hive2://hadoop101:10000> show views; #### 查看视图定义 #### 0: jdbc:hive2://hadoop101:10000> show create table view_002; +----------------------------------------------------+--+ | createtab_stmt | +----------------------------------------------------+--+ | CREATE VIEW `view_002` AS select `people`.`name` from `hivedb2`.`people` | +----------------------------------------------------+--+ #### 删除视图 #### 0: jdbc:hive2://hadoop101:10000> drop view if exists view_001; #### 修改视图的属性，比如comment #### 0: jdbc:hive2://hadoop101:10000> alter view view_002 set tblproperties('comment'='This is a view'); #### 更改视图定义 #### alter view view_002 as select age from people; 0: jdbc:hive2://hadoop101:10000> select * from view_002; +---------------+--+ | view_002.age | +---------------+--+ | 23 | | 12 | | 14 | | 34 | | 24 | | 24 | +---------------+--+ ```