Index Types in Hive

[tony martin <to...@gmail.com>]

Hi,

Im new to this forum as well as to Hive.

In RDBMS we talk of multiple index types such as Btree, BitMap and so. When
an index is created in HIVE using CREATE INDEX, what sort of index is
created?

Could anyone guide me on the indexing stratergies used in Hive.

Best regards,
Tony M

Re: Index Types in Hive

[Marquis Wang <ma...@hmc.edu>]

Hi Tony,

We're still working on the index implementation in Hive, so index
support is very limited. When you use CREATE INDEX in Hive, you must
specify the index type. Currently, the only built-in index type is the
Compact index, though we are working to add bitmap indexes and others.

Suppose you want to do the query
> SELECT key, value FROM src WHERE key=100 ORDER BY key;
many times and you want to create an index for it.

You can create a compact index with the command

> CREATE INDEX src_index ON TABLE src(key) as 'COMPACT' WITH DEFERRED REBUILD;

However, the current indexing implementation also does not support
automatic index rebuilding and usage.

Build the index with the command

> ALTER INDEX src_index ON src REBUILD;

And use the index with the sequence of commands:

> # Query the underlying compact index table and save the result in a temp file
> INSERT OVERWRITE DIRECTORY "/tmp/index_result" SELECT `_bucketname` ,
> `_offsets` FROM default__src_src_index__ WHERE key=100;
>
> # Switch to using the compact index input format for the real query and tell it
> # where the temp file is.
> SET hive.index.compact.file=/tmp/index_result;
> SET
> hive.input.format=org.apache.hadoop.hive.ql.index.compact.HiveCompactIndexInputFormat;
>
> # Make the query you want to do
> SELECT key, value FROM src WHERE key=100 ORDER BY key;
>
> # Change the input format back to normal
> SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;

More information about the ongoing development of indexing in Hive can
be found on the Hive Wiki at
http://wiki.apache.org/hadoop/Hive/IndexDev

Hope that helps,
Marquis

On Tue, Feb 15, 2011 at 1:24 PM, tony martin <to...@gmail.com> wrote:
> Hi,
>
> Im new to this forum as well as to Hive.
>
> In RDBMS we talk of multiple index types such as Btree, BitMap and so. When
> an index is created in HIVE using CREATE INDEX, what sort of index is
> created?
>
> Could anyone guide me on the indexing stratergies used in Hive.
>
> Best regards,
> Tony M