Indexing in hive

["Raghunath, Ranjith" <Ra...@usaa.com>]

I am currently using hive 0.7.1 and creating indexes based on columns in the where clause. However, when I run the explain plan I do not see the index being leveraged. The syntax that I am using to build the index is as follows:


CREATE INDEX x ON TABLE t(j)
AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
WITH DEFERRED REBUILD

The query is as follows:

Select a,count(*) from x where j='and' group by a

Is there anything I am doing incorrectly?

Thanks,
Ranjith

Re: Indexing in hive

[Carl Steinbach <ca...@cloudera.com>]

Hi Ranjith,

Hive 0.7 supports the ability to build indexes, but the query compiler in
0.7 doesn't know how to optimize queries with these indexes. Hive 0.8 was
the first release to include some support for optimizing query plans with
indexes, and that only applies to GROUP BY and WHERE clauses under certain
specific circumstances. At this point index support in Hive is very much a
work in progress.

Thanks.

Carl

On Wed, May 16, 2012 at 1:06 PM, Raghunath, Ranjith <
Ranjith.Raghunath1@usaa.com> wrote:

>  I am currently using hive 0.7.1 and creating indexes based on columns in
> the where clause. However, when I run the explain plan I do not see the
> index being leveraged. The syntax that I am using to build the index is as
> follows:
>
> CREATE INDEX x ON TABLE t(j)
> AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
> WITH DEFERRED REBUILD
>
> The query is as follows:
>
> Select a,count(*) from x where j=’and’ group by a
>
> Is there anything I am doing incorrectly?
>
> Thanks,
> Ranjith
>
>
>
>

RE: Indexing in hive

["Hezhiqiang (Ransom)" <ra...@huawei.com>]

“hive.optimize.index.filter” is the conf automatically use indexes
If u set hive.optimize.index.groupby  = true.
It will set hive.optimize.index.filter=false.
See your configurations.

And you need to build index  after create index.

Best regards
Ransom.