Table of Contents |
---|
Indexing Is Removed since 3.0
There are alternate options which might work similarily to indexing:
- Materialized views with automatic rewriting can result in very similar results. Hive 2.3.0 adds support for materialzed views.
- Using columnar file formats (Parquet, ORC) – they can do selective scanning; they may even skip entire files/blocks.
Note |
---|
Indexing has been removed in version 3.0 (HIVE-18448). |
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.
...
...
- Tutorial: SQL-like join and index with MapReduce using Hadoop and Hive – blog by Ashish Garg, April 2012
Configuration Parameters for Hive Indexes
The Configuration Properties document describes parameters that configure Hive indexes.
Simple Examples
This section gives some indexing examples adapted from the Hive test suite.
Note | ||
---|---|---|
| ||
In Hive 0.12.0 and earlier releases, the index name is case-sensitive for CREATE INDEX and DROP INDEX statements. HoweverHowever, ALTER INDEX requires an index name that was created with lowercase letters (see HIVE-2752). This This bug will be fixed in a future release by is fixed in Hive 0.13.0 by making index names case-insensitive for all HiveQL statements; in the meantime. For releases prior to 0.13.0, the best practice is to use lowercase letters for all index names. |
...
No Format |
---|
DROP INDEX IF EXISTS table09_index ON table09; |
Rebuild index on a partition:
No Format |
---|
ALTER INDEX table10_index ON table10 PARTITION (columnX='valueQ', columnY='valueR') REBUILD; |