Hive Lateral View、视图、索引

一、Hive Lateral View

• 1.Lateral View用于和UDTF函数（explode、split）结合来使用。
• 2.首先通过UDTF函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
• 3.主要解决在select使用UDTF做查询过程中，查询只能包含单个UDTF，不能包含其他字段、以及多个UDTF的问题

语法：

select explode(likes) from psn2;命令可将likes数组拆分。explode拆分，只能接一个字段。

案例：

统计人员表中一共有多少种爱好、城市？

从psn2表中查询，并将结果输入到虚拟表中：

拆分likes数组，定义别名，结果放在myCol1中。

拆分address集合，定义别名，k、v分别放在myCol2和myCol3中

select count(distinct(myCol1)), count(distinct(myCol2)) from psn2 
LATERAL VIEW explode(likes) myTable1 AS myCol1 
LATERAL VIEW explode(address) myTable2 AS myCol2, myCol3;
           

二、Hive视图

特点：

• 1.不支持物化视图
• 2.只能查询，不能做加载数据操作（写入）
• 3.视图的创建，只是保存一份元数据，查询视图时才执行对应的子查询
• 4.view定义中若包含了ORDER BY/LIMIT语句，当查询视图时也进行ORDER BY/LIMIT语句操作，view当中定义的优先级更高
• 5.view支持迭代视图

创建视图语法：

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name 
  [(column_name [COMMENT column_comment], ...) ]
  [COMMENT view_comment]
  [TBLPROPERTIES (property_name = property_value, ...)]
  AS SELECT ... ;
           

查询视图：

删除视图：

三、Hive索引

解决查询问题，避免全表扫描，提高检索的性能。索引具体的添加，要根据数据来定。

1.创建索引：

as：指定索引器；

in table：指定索引表，若不指定默认生成在default__psn2_t1_index__表中

create index t1_index on table psn2(name) 
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild 
in table t1_index_table;
           

或者可以这样写，不指定索引表，即默认生成default__psn2_t1_index__：

create index t2_index on table psn2(name) 
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild;

           

此时索引表中都是空的，还要生成索引信息。

2.重建索引：

创建索引之后，必须重建索引才能生效。

索引表查询结果：

hive> select * from t1_index_table;
OK
t1_index_table.name	t1_index_table._bucketname	t1_index_table._offsets	t1_index_table.age
小明1	hdfs://mycluster/user/hive/warehouse/psn2/age=10/data1	[0]	10
小明2	hdfs://mycluster/user/hive/warehouse/psn2/age=10/data1	[56]	10
小明3	hdfs://mycluster/user/hive/warehouse/psn2/age=10/data1	[112]	10
小明4	hdfs://mycluster/user/hive/warehouse/psn2/age=10/data1	[168]	10
小明5	hdfs://mycluster/user/hive/warehouse/psn2/age=10/data1	[224]	10
小明6	hdfs://mycluster/user/hive/warehouse/psn2/age=10/data1	[275]	10
小明7	hdfs://mycluster/user/hive/warehouse/psn2/age=10/data1	[331]	10
小明8	hdfs://mycluster/user/hive/warehouse/psn2/age=10/data1	[381]	10
小明9	hdfs://mycluster/user/hive/warehouse/psn2/age=10/data1	[431]	10
张三1	hdfs://mycluster/user/hive/warehouse/psn2/age=20/data2	[0]	20
张三2	hdfs://mycluster/user/hive/warehouse/psn2/age=20/data2	[58]	20
张三3	hdfs://mycluster/user/hive/warehouse/psn2/age=20/data2	[116]	20
张三4	hdfs://mycluster/user/hive/warehouse/psn2/age=20/data2	[174]	20
张三5	hdfs://mycluster/user/hive/warehouse/psn2/age=20/data2	[232]	20
张三6	hdfs://mycluster/user/hive/warehouse/psn2/age=20/data2	[285]	20
张三7	hdfs://mycluster/user/hive/warehouse/psn2/age=20/data2	[343]	20
张三8	hdfs://mycluster/user/hive/warehouse/psn2/age=20/data2	[395]	20
张三9	hdfs://mycluster/user/hive/warehouse/psn2/age=20/data2	[447]	20
Time taken: 0.127 seconds, Fetched: 18 row(s)

           

有了索引，查询才会更快：

hive> select * from psn2 where name='小明2';
OK
psn2.id	psn2.name	psn2.likes	psn2.address	psn2.age
2	小明2	["lol","book","movie"]	{"beijing":"longze","shanghai":"pudong"}	10
Time taken: 0.227 seconds, Fetched: 1 row(s)

           

3.查询索引

hive> show index on psn2;
OK
idx_name	tab_name	col_names	idx_tab_name	idx_type	comment
t1_index            	psn2                	name                	t1_index_table      	compact             	
t2_index            	psn2                	name                	default__psn2_t2_index__ compact             	
Time taken: 0.146 seconds, Fetched: 2 row(s)
           

4.删除索引