Hive - Indexing

When we need to search for a particular record in the database ,instead of searching the whole database ,we can find that particular record with the help of index that can be termed as the reference to that record.
In fact ,an index is a pointer to a particular column in the table.

Syntax for creating index :-
create index index_name on table_name column;

We will create a managed table indextest .


Then , we will create the index on the table indextest on the column c. The "with deferred rebuild" clause instruct hive to create the empty indexes. org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' is a compact index handler



The index can be built the first time or rebuilt using the ALTER INDEX statement:

After triggering the ALTER command , the map reduce job will get invoked. A separate table for the index column will also get created .Hive doesn’t provide automatic index maintenance, so you need to rebuild the index if you overwrite or append data to the table.The rebuild can also be limited to the partitions.

Syntax for Showing Index :-
show formatted index on tablename;


The show formatted command provides us below information :-
a) index name
b)table name
c)column name
d)index table name
e)index type

Syntax for dropping an index :-
drop index if exists index_name on tablename;


If we check for the index table , we will not find any information regarding this one.


Types Of Indexes in Hive :- 

Hive supports two type of indexing
1) Compact Indexing
2) Bitmap Indexing

We should note that the creation of index will occupy some extra space in the disk and also take create some processing. The indexes should not be created unnecessarily and the column which are used in filter condition should be taken as the index column.

Further Reading :- https://hive.apache.org/