Indexes in hive

[saquib khan <sk...@gmail.com>]

Dear Friends,

I have created the indexes but I am not able to use them. Does hive have an
index optimizer?

For a simple query in Oracle/Postgres, when we do EXPLAIN it gives the
Query Plan with indexes but with Hive it does not show me the indexes. How
can we make sure that the indexes are being used?


Table:
CREATE TABLE VT_DEMOGRAPHY_INFO (PID int, HID int, AGE int, GENDER int,
ZIPCODE string, BLOCKGROUPID string, LONGITUDE string, LATTITUDE string,
COUNTY string) PARTITIONED BY (COUNTYID string) row format delimited fields
terminated by '|' stored as textfile;

I have created indexes on PID and BLOCKGROUPID

CREATE INDEX se_vt_pid ON TABLE se_vt(pid) AS
'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' WITH DEFERRED
REBUILD;
ALTER INDEX se_vt_pid ON se_vt REBUILD;

CREATE INDEX se_vt_blockgroupid ON TABLE se_vt(blockgroupid) AS
'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' WITH DEFERRED
REBUILD;
ALTER INDEX se_vt_blockgroupid ON se_vt REBUILD;


Thanks and Regards,
Saky