【数据库】HIVE SQL索引及其使用

       最近在用一张8亿数据量表作为主表去关联一个千万量级的表时遇到一个问题，JOB运行的特别慢，而且大量的时间花费在了大表的查询上。如何解决这个问题，首先想到是不是由于数据偏移造成的，对应了各种数据偏移的场景，最后认定不是数据偏移造成的。那怎么办呢？后来想到用索引！由于对于索引不是特别了解，查了各种资料，这里做一个总结，加深对索引的理解。

      HIVE中如何创建索引？

第一步

最最最重要的，是要先建表，并且对表的存储方式有要求（如果你所建的表本身是如下方式创建的，那么忽略这一步）：

use dw_htldatadb;
drop table table;
create table table(
aa string comment 'asd',
bb int  comment 'asd'
) COMMENT 'table comment'
PARTITIONED BY(d STRING COMMENT 'date')
row format delimited fields terminated by ',' 
stored as textfile;

如果你按照下面这种方式建表，那么，你之后可能会遇到，再重建索引时，报错的情况（这里我真的不知道为啥子，只是在尝试过程中，发现了这个坑）

use dw_htldatadb;
drop table table;
create table table(
aa string comment 'asd',
bb int  comment 'asd'
) COMMENT 'table comment'
PARTITIONED BY(d STRING COMMENT 'date')
row format delimited fields terminated by ',' 
 STORED AS ORC;

第二步

创建索引，这里注意的是，我在查资料时，看到有小伙伴用了分区创建索引的方法，但是我并没有试验成功，不知道是否和hive版本有关，所以，我用的是最常用的方式创建的索引。

成功版本：对表table的aa列创建索引

use database;
create index idx_table on table table(aa)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 
with deferred rebuild
IN TABLE tablen_index;

未成功版本：

use database;
create index idx_table on table table(eid)
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 
with deferred rebuild
IN TABLE table_index
PARTITIONED BY (d,eid)
;

第三步

重建索引，这里，如果你的表已经有分区有数据，那么你可以对单个分区重建索引，也可以对整个表建立索引，但通过实践，是在表中未产生数据时，创建索引比较快，而且在你的表再次插入数据时，也不需要再执行此操作。

对表重建索引：

use database;
ALTER INDEX idx_table REBUILD;

对表的分区重建建索引：

use database;
ALTER INDEX idx_table partition (d = '2019-04-01') REBUILD ;

第四步

创建好之后，就可以对表进行操作了。

查看索引

重建之后可以显示索引：

SHOW FORMATTED INDEX ON table;

案例结果：

idx_name	tab_name	col_names	idx_tab_name	idx_type	comment
idx_tmp_ceshi	tmp_ceshi	eid	tmp_ceshi_index	compact

注意

想要索引在查询时生效，还需要设置参数，默认情况下不使用索引。

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;

这个部分也解决了我大表查询时间长的问题，再把大表建成索引表，再去关联时，和之间相比，时间缩短了一半，不过，索引有自身的试用情况，用时还是需要了解使用的场景的。下个小节会对分桶进行探讨，这也是优化job的一个方向。