hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mich Talebzadeh <mich.talebza...@gmail.com>
Subject Re: Hive indexes without improvement of performance
Date Thu, 16 Jun 2016 20:54:45 GMT
Nothing.

Hive does not support external indexes even in version 2.

In other words, although you create indexes, they are not visible to Hive
optimizer as you have found out.

I wrote an article on this hoping that we should have external indexes
being used .

HTH

Dr Mich Talebzadeh



LinkedIn * https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUrV8Pw>*



http://talebzadehmich.wordpress.com



On 16 June 2016 at 21:50, Vadim Dedkov <dedkovva@gmail.com> wrote:

> Hello!
>
> I use Hive 1.1.0-cdh5.5.0 and try to use indexes support.
>
> My index creation:
> *CREATE INDEX doc_id_idx on TABLE my_schema_name.doc_t (id) AS 'COMPACT'
> WITH DEFERRED REBUILD;*
> *ALTER INDEX doc_id_idx ON my_schema_name.doc_t REBUILD;*
>
> Then I set configs:
> *set hive.optimize.autoindex=true;*
> *set hive.optimize.index.filter=true;*
> *set hive.optimize.index.filter.compact.minsize=0;*
> *set hive.index.compact.query.max.size=-1;*
> *set hive.index.compact.query.max.entries=-1; *
>
> And my query is:
> *select count(*) from my_schema_name.doc_t WHERE id = '3723445235879';*
>
> Sometimes I have improvement of performance, but most of cases - not.
>
> In cases when I have improvement:
> 1. my query is
> *select count(*) from my_schema_name.doc_t WHERE id = '3723445235879';*
> give me NullPointerException (in logs I see that Hive doesn't find my
> index table)
> 2. then I write:
> *USE my_schema_name;*
> *select count(*) from doc_t WHERE id = '3723445235879';*
> and have result with improvement
> (172 sec)
>
> In case when I don't have improvement, I can use either
> *select count(*) from my_schema_name.doc_t WHERE id = '3723445235879';*
> without exception, either
> *USE my_schema_name;*
> *select count(*) from doc_t WHERE id = '3723445235879';*
> and have result
> (1153 sec)
>
> My table is about 6 billion rows.
> I tried various combinations on index configs, including only these two:
> *set hive.optimize.index.filter=true;*
> *set hive.optimize.index.filter.compact.minsize=0;*
> My hadoop version is 2.6.0-cdh5.5.0
>
> What I do wrong?
>
> Thank you.
>
> --
> _______________             _______________
> Best regards,                    С уважением
> Vadim Dedkov.                  Вадим Дедков.
>

Mime
View raw message