这是我参与11月更文挑战的第29天，活动详情查看：2021最后一次更文挑战」
视图操作
Hive 中的视图和RDBMS中视图的概念一致，都是一组数据的逻辑表示，本质上就是一条SELECT语句的结果集。视图是纯粹的逻辑对象，没有关联的存储(Hive 3.0.0引入的物化视图除外)，当查询引用视图时，Hive可以将视图的定义与查询结合起来，例如将查询中的过滤器推送到视图中。
Hive视图特点
1、不支持物化视图
2、只能查询，不能做加载数据操作
3、视图的创建，只是保存一份元数据，查询视图时才执行对应的子查询
4、view定义中若包含了ORDER BY/LIMIT语句，当查询视图时也进行ORDER BY/LIMIT语句操作，view当中定义的优先级更高
5、view支持迭代视图
语法
--创建视图：
CREATE VIEW [IF NOT EXISTS] [数据库.]视图
[(字段名称[COMMENT 描述], ...) ]
[COMMENT 视图描述]
[TBLPROPERTIES (property_name = property_value, ...)]
AS SELECT ... ;
--查询视图：
select colums from view;
--删除视图：
DROP VIEW [IF EXISTS] [数据库.]视图;
案例
**案例1：**
###### 创建一张视图
CREATE VIEW IF NOT EXISTS jacquesh_databases.order_bucket_view AS SELECT * FROM jacquesh_databases.order_bucket;
###### 查看视图
select * from jacquesh_databases.order_bucket_view ;
order_id|order_money|pay_money|address |create_dates |cancel_dates |cancel_money|
--------+-----------+---------+-------------------+-----------------------+-----------------------+------------+
4| 157.0| 157.0|2020-02-20 23:58:34|2020-02-20 23:58:44.000|1970-01-01 08:00:00.000| |
8| 53.0| 53.0|2020-02-20 23:56:12|2020-02-20 23:56:16.000|1970-01-01 08:00:00.000| |
1| 178.8| 0.0|2020-02-21 00:00:00| |1970-01-01 08:00:00.000| |
5| 64.8| 0.0|2020-02-20 23:57:04|2020-02-20 23:57:11.000|1970-01-01 08:01:04.800| |
2| 21.0| 21.0|2020-02-20 23:59:54|2020-02-21 00:00:02.000|1970-01-01 08:00:00.000| |
6| 327.7| 148.9|2020-02-20 23:56:39|2020-02-20 23:56:53.000|1970-01-01 08:02:58.800| |
3| 37.0| 0.0|2020-02-20 23:59:35| |1970-01-01 08:00:00.000| |
7| 357.0| 357.0|2020-02-20 23:56:36|2020-02-20 23:56:40.000|1970-01-01 08:00:00.000| |
**案例2：**
###### 创建一张视图统计条数
CREATE VIEW IF NOT EXISTS jacquesh_databases.order_bucket_view_count AS SELECT COUNT(1) FROM jacquesh_databases.order_bucket;
###### 查看视图
select * from jacquesh_databases.order_bucket_view_count ;
_c0|
---+
8|
Hive Lateral View
Lateral View用于和UDTF函数（explode、split）结合来使用。
首先通过UDTF函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表。主要解决在select使用UDTF做查询过程中，查询只能包含单个UDTF，不能包含其他字段、以及多个UDTF的问题。
语法：
LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
案例：
### 表数据
id|game_id|types|name |functions|ident |oss |create_time|
--+-------+-----+------+---------+------+------------------------------------------------------------------------------------------------------------+-----------+
1| 0| 1|代理公共货币| 1|proxy |{} | |
2| | 2|游戏币 | 1|yxb |{"url":"<https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988321793-rc-upload-1630988274696-3.png>"}| |
3| 1003| 2|筹码 | 1|cm |{"url":"<https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988321793-rc-upload-1630988274696-3.png>"} | |
4| 1003| 2|金币 | 1|jb |{} | |
5| 1004| |金币 | 1|140005|{"url":"<https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988321793-rc-upload-1630988274696-3.png>"} | |
6| 1004| |筹码 | 1|140006|{"url":"<https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988349875-rc-upload-1630988274696-9.png>"} | |
7| 1003| |代理货币 | 2|dl |{"url":"<https://orye-admin.oss-cn-shenzhen.aliyuncs.com/Dawn/1631170011761-rc-upload-1631169960249-5.png>"} | |
#### 使用udtf语句进行查询
SELECT explode(oss) from jacquesh_databases.moneys
### 查询到数据
key|value |
---+--------------------------------------------------------------------------------------------------+
url|https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988321793-rc-upload-1630988274696-3.png|
url|https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988321793-rc-upload-1630988274696-3.png|
url|https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988321793-rc-upload-1630988274696-3.png|
url|https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988349875-rc-upload-1630988274696-9.png|
url|https://orye-admin.oss-cn-shenzhen.aliyuncs.com/Dawn/1631170011761-rc-upload-1631169960249-5.png |
**但是如果udtf函数加上普通的查询的话就会出现报错
如下：**
SELECT explode(oss),name from jacquesh_databases.moneys
报错信息：
SemanticException 1:21 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'name'
**如何解决->Lateral View**
select name , ossValue from jacquesh_databases.moneys
LATERAL VIEW explode(oss) ossTable AS osskey, ossValue;
结果：
name|ossvalue |
----+--------------------------------------------------------------------------------------------------+
游戏币 |https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988321793-rc-upload-1630988274696-3.png|
筹码 |https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988321793-rc-upload-1630988274696-3.png|
金币 |https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988321793-rc-upload-1630988274696-3.png|
筹码 |https://orye-admin.oss-cn-shenzhen.aliyuncs.com/junjie/1630988349875-rc-upload-1630988274696-9.png|
代理货币|https://orye-admin.oss-cn-shenzhen.aliyuncs.com/Dawn/1631170011761-rc-upload-1631169960249-5.png |
