1.Hive 视图概念
- 通过隐藏子查询、连接查询等操作的简化逻辑结构
- 它由真实表中选取出来的数据组成
- 它是一个虚拟表,只保存定义,不存储数据
- 它类似于一张表,对表的查询操作都可以在视图中使用
- 在Hive中,视图是只读的,不能向视图中插入或是装载数据
2.CREATE命令,创建视图
#CREATE VIEW是创建视图命令,其中COMMENT和TBLPROPERTIES与创建表含义相同,AS SELECT指定查询语句
CREATE VIEW [IF EXISTS][db_name.]view_name[(column_name[COMMENT column_comment],…)]
[COMMENT view_comment]
[TBLPROPERTIES(property_name=property_value)]
AS SELECT …;
3.SHOW命令,查看视图
SHOW TABLES[IN/FROM database_name][LIKE ‘pattern_with_wildcards’];
#查看视图的详细信息/查看视图定义方式/查询视图的方式
desc formatted customer_orders_view;
SHOW CREATE TABLE view_name;
select * from customer_orders_view limit 10;
4.DROP命令,删除视图
DROP VIEW [IF NOT EXISTS] [db_name].view_name;
5. ALTER命令,修改视图
#修改视图属性
#其中table_properties的格式为(property_name=property_value, property_name=property_value,…….)
ALTER VIEW [db_name].view_name SET TBLPROPERTIES table_properties;
#使用AS SELECT更改视图定义
ALTER VIEW [db_name].view_name AS select_statement;
6.练习
1.使用Hive Shell命令行完成操作
2.对零售商店数据库retail_db中表的视图管理操作
3.基于订单表、订单明细表和产品表的视图如下表
- 创建视图orders_products_items_view
create view orders_products_items_view( customer_fname, customer_lname, order_id, order_data, order_status) comment 'this is customer_orders_view' as select c.customer_fname,c.customer_lname,o.order_id,o.order_date,o.order_status from retail_db.customer c left join retail_db.orders o on c.customer_id = o.order_customer_id;
- 查询orders_products_items_view是否存在
SHOW TABLES LIKE 'orders_products_items_view';
- 为视图orders_products_items_view添加属性信息
ALTER VIEW orders_products_items_view SET TBLPROPERTIES ('comment' = 'This is the orders, products and order items view');
- 删除orders_products_items_view视图
DROP VIEW IF EXISTS orders_products_items_view;
- 查询视图orders_products_items_view是否存在
SHOW TABLES LIKE 'orders_products_items_view';