HIVE Indexex 索引

Creating an Index -- 创建一个索引

CREATE TABLE employees (  name         STRING,  salary       FLOAT,  subordinates ARRAY<STRING>,  deductions   MAP<STRING, FLOAT>,  address      STRUCT<street:STRING, city:STRING, state:STRING, zip:INT>)PARTITIONED BY (country STRING, state STRING);

Let’s index on the country partition only:

CREATE INDEX employees_indexON TABLE employees (country)AS 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'WITH DEFERRED REBUILDIDXPROPERTIES ('creator = 'me', 'created_at' = 'some_time')IN TABLE employees_index_tablePARTITIONED BY (country, name)COMMENT 'Employees indexed by country and name.';

Bitmap Indexes

Hive v0.8.0 adds a built-in bitmap index handler. Bitmap indexes are commonly used
for columns with few distinct values. Here is our previous example rewritten to use the
bitmap index handler:

CREATE INDEX employees_indexON TABLE employees (country)AS 'BITMAP'WITH DEFERRED REBUILDIDXPROPERTIES ('creator = 'me', 'created_at' = 'some_time')IN TABLE employees_index_tablePARTITIONED BY (country, name)COMMENT 'Employees indexed by country and name.';

Rebuilding the Index

ALTER INDEX employees_indexON TABLE employeesPARTITION (country = 'US')REBUILD;

Showing an Index

SHOW FORMATTED INDEX ON employees;

Dropping an Index

DROP INDEX IF EXISTS employees_index ON TABLE employees;