目录
2、Hive表行转列,列转行以及Hive Lateral View在列转行中的使用
一、Hive Lateral View
1、基本介绍
Lateral View用于和UDTF函数(explode、split)结合来使用。 首先通过UDTF函数拆分成多行,再将多行结果组合成一个支持别名的虚拟表。主要解决在select使用UDTF做查询过程中,查询只能包含单个UDTF,不能包含其他字段、以及多个UDTF的问题。
语法:
LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
2、Hive表行转列,列转行以及Hive Lateral View在列转行中的使用
- 表1:cityInfo
cityname | regionname |
上海 | 四川北路 |
上海 | 虹梅路 |
上海 | 音乐学院 |
上海 | 徐家汇 |
上海 | 陆家嘴 |
上海 | 古北 |
北京 | 东四 |
北京 | 复兴门 |
北京 | 新街口 |
北京 | 地安门 |
- 表2:cityInfoSet
cityname | regionname |
上海 | 四川北路,虹梅路,音乐学院,徐家汇,陆家嘴,古北 |
北京 | 地安门,复兴门,东四,新街口 |
表1和表2的结构如上所示。
现在需要在 hive 中使用 Hql 语句对表1和表2进行互相转化。
(1)行转列
使用的函数说明:
concat_ws(string SEP,string array<String>):函数返回字符串连接后的结果,SEP表示各个字符串连接的分割符;
collect_set(col): 函数将col字段进行去重,并合并成一个数组。
表1=>表2 可以使用 hive 的内置函数 concat_ws() 和 collect_set()进行转换,执行hql如下所示:
select cityname,concat_ws(',',collect_set(regionname)) as address_set from cityInfo group by cityname;
(2)列转行
使用到的函数
函数split(String str,String pat) 将字符串按照pat分割;
函数explode(array) 将数组中的元素拆分为多行显示。
表2=>表1 可以使用 hive 的内置函数 lateral view explode()进行转化。执行hql如下:
select cityname, regionname from cityInfoSet lateral view explode(split(address_set, ',')) aa as region;
二、Hive视图
1、Hive视图基本介绍
视图是一个虚拟表,其内容由查询定义。Hive 中的视图和RDBMS中视图的概念一致,都是一组数据的逻辑表示,本质上就是一条SELECT语句的结果集。视图是纯粹的逻辑对象,没有关联的存储(Hive 3.0.0引入的物化视图除外),当查询引用视图时,Hive可以将视图的定义与查询结合起来,例如将查询中的过滤器推送到视图中。
2、Hive视图特点
(1)不支持物化视图 ;
(2)只能查询,不能做加载数据操作 ;
(3)视图的创建,只是保存一份元数据,查询视图时才执行对应的子查询 ;
(4)view定义中若包含了ORDER BY/LIMIT语句,当查询视图时也进行了ORDER BY/LIMIT语句操作,view当中定义的优先级更高 ;
(5)Hive视图支持迭代视图。
3、Hive视图的意义
(1) 使用视图使数据操作更简便,相当于封装了sql语句,类似于关系型数据库中的存储过程。
(2) 使用视图,基表中的数据有了一定的安全性,使用视图可以基于限制条件过滤数据,通过视图限制数据访问可以保护信息不被随意查询。
4、Hive视图的创建和删除
--创建视图:
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name
[(column_name [COMMENT column_comment], ...) ]
[COMMENT view_comment]
[TBLPROPERTIES (property_name = property_value, ...)]
AS SELECT ... ;
--查询视图:
select colums from view;
--删除视图:
DROP VIEW [IF EXISTS] [db_name.]view_name;
案例:
CREATE VIEW gal_view IF NOT EXISTS AS SELECT * FROM product1 p LEFT JOIN logistic l ON p.sku=l.lsku;
5、视图的使用
SELECT sku,price FROM gal_view;
6、查询所有的hive视图
(1) show tables可以查看到新增的视图
查看结构 :
(2) Hive的元数据信息一般存储在mysql,Hive的视图信息存储在mysql hive数据库中的 TBLS 表中,通过如下查询语句可以查询得到Hive中的所有视图:
SELECT * FROM TBLS WHERE TBL_TYPE='VIRTUAL_VIEW';
结果:
三、Hive索引
hive的索引可以提高数据的检索效率,避免全表扫描和资源浪费,对于含有group by的语句,对group by的字段添加索引可以提高查询速度。
1、Hive索引的实现原理
在指定列上建立索引,会产生一张索引表(Hive的一张物理表),里面的字段包括,索引列的值、该值对应的HDFS文件路径、该值在文件中的偏移量。
在执行索引字段查询时候,首先额外生成一个MR job,根据对索引列的过滤条件,从索引表中过滤出索引列的值对应的hdfs文件路径及偏移量,输出到hdfs上的一个文件中,然后根据这些文件中的hdfs路径和偏移量,筛选原始input文件,生成新的split,作为整个job的split,这样就达到不用全表扫描的目的。
2、创建索引
创建索引的字段最好使用不更新的字段,以免频繁重建索引数据。每次建立、更新数据后,都要重建索引以构建索引表。
现在有一张表名为product1的表,需要将该表中的sku字段建立一个索引,案例:
create index test_index on table product1(sku)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
with deferred rebuild
in table product1_index;
create index test_index2 on table product1(sku) as 'bitmap' with deferred rebuild in table product1_index2;
两种创建方式 as 后表示两种不同的索引处理器。
创建示例如下(这里采用的bitmap索引处理器):
0: jdbc:hive2://cdh-001:10000/test> show tables;
+-----------+--+
| tab_name |
+-----------+--+
| gal_view |
| logistic |
| product1 |
| stocks |
+-----------+--+
4 rows selected (0.027 seconds)
//创建索引
0: jdbc:hive2://cdh-001:10000/test> create index test_index2 on table product1(sku) as 'bitmap' with deferred rebuild in table product1_index2;
0: jdbc:hive2://cdh-001:10000/test> show tables;
+------------------+--+
| tab_name |
+------------------+--+
| gal_view | //这是之前创建的视图
| logistic |
| product1 |
| product1_index2 | //可以看到这里多了一张索引表
| stocks |
+------------------+--+
5 rows selected (0.033 seconds)
//重建索引生成索引数据(建立索引之后必须重建索引才能生效)
0: jdbc:hive2://cdh-001:10000/test> alter index test_index2 on product1 rebuild;
//创建完成后通过索引命令显示索引
0: jdbc:hive2://cdh-001:10000/test> SHOW FORMATTED INDEX ON product1;
+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+--+
| idx_name | tab_name | col_names | idx_tab_name | idx_type | comment |
+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+--+
| idx_name | tab_name | col_names | idx_tab_name | idx_type | comment |
| | NULL | NULL | NULL | NULL | NULL |
| | NULL | NULL | NULL | NULL | NULL |
| test_index2 | product1 | sku | product1_index2 | bitmap | |
+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+--+
3、使用索引
hive默认不使用索引,需要设置才能使索引生效:
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
SET hive.optimize.index.filter=true;
SET hive.optimize.index.filter.compact.minsize=0;
//执行查询
select * from product1 where sku=272480720;
4、删除索引
DROP INDEX IF EXISTS product1_index2 ON product1;