Hive Indexing

[#1]

Can Indexes be created in hive?

[#2 · Accepted solution]

@saswati sahu

Yes, you can create index in Hive.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Indexing

Sample hive index creation:

0: jdbc:hive2://ssnode2.openstacklocal:10000/> create index test_index_table on table test_table(col1,col2) AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' WITH DEFERRED REBUILD;

No rows affected (0.351 seconds)

[#3]

@Sindhu

Thanku for the reply. I have tried in cloudera but couldn't get much information of creating hive Indexes in Hortonworks.

Could you please help me with hive indexing in Hortonworks.

[#4]

@saswati sahu

Sample hive index creation:

0: jdbc:hive2://ssnode2.openstacklocal:10000/> create index test_index_table on table test_table(col1,col2) AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' WITH DEFERRED REBUILD;

No rows affected (0.351 seconds)

[#5]

@saswati sahu

The link provided above (https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Indexing) by @Sindhu provides documentation for all aspects of standard open source (Hortonworks) Hive indexing. Can you let me know what are your specific concerns so we can try to more specifically address them?

[#6]

@sindhu

Thanku..It was really helpful.

@mjohnson

I got the answer to my query. However, I would also like to know how to check if the Index created in hive is utilized when a query is fired?

[#7]

@saswati sahu

just FYI.

ORC has a built-in index and not required to apply index if you are using ORC tables.