LanguageManual Indexing

Overview of Hive Indexes

The goal of Hive indexing is to improve the speed of query lookup on certain columns of a table. Without an index, queries with predicates like 'WHERE tab1.col1 = 10' load the entire table or partition and process all the rows. But if an index exists for col1, then only a portion of the file needs to be loaded and processed.

The improvement in query speed that an index can provide comes at the cost of additional processing to create the index and disk space to store the index.

Indexing Resources

Documentation and examples of how to use Hive indexes can be found here:

• Indexes – design document (lists indexing JIRAs with current status, starting with HIVE-417)

• Create/Drop Index – HiveQL Language Manual DDL

• Show Indexes – HiveQL Language Manual DDL

• Bitmap indexes – added in Hive version 0.8.0 (HIVE-1803)

• Indexed Hive – overview and examples by Prafulla Tekawade and Nikhil Deshpande, October 2010

• Tutorial: SQL-like join and index with MapReduce using Hadoop and Hive – blog by Ashish Garg, April 2012