HIVE创建索引

参考链接：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Indexing#LanguageManualIndexing-ConfigurationParametersforHiveIndexes

1.创建HIVE索引官方语法：

Create/build, show, and drop index:

CREATE INDEX table01_index ON TABLE table01 (column2) AS 'COMPACT';SHOW INDEX ON table01;DROP INDEX table01_index ON table01;

Create then build, show formatted (with column names), and drop index:

CREATE INDEX table02_index ON TABLE table02 (column3) AS 'COMPACT' WITH DEFERRED REBUILD;ALTER INDEX table02_index ON table2 REBUILD;SHOW FORMATTED INDEX ON table02;DROP INDEX table02_index ON table02;

Create bitmap index, build, show, and drop:

CREATE INDEX table03_index ON TABLE table03 (column4) AS 'BITMAP' WITH DEFERRED REBUILD;ALTER INDEX table03_index ON table03 REBUILD;SHOW FORMATTED INDEX ON table03;DROP INDEX table03_index ON table03;

Create index in a new table:

CREATE INDEX table04_index ON TABLE table04 (column5) AS 'COMPACT' WITH DEFERRED REBUILD IN TABLE table04_index_table;

Create index stored as RCFile:

CREATE INDEX table05_index ON TABLE table05 (column6) AS 'COMPACT' STORED AS RCFILE;

Create index stored as text file:

CREATE INDEX table06_index ON TABLE table06 (column7) AS 'COMPACT' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE;

Create index with index properties:

CREATE INDEX table07_index ON TABLE table07 (column8) AS 'COMPACT' IDXPROPERTIES ("prop1"="value1", "prop2"="value2");

Create index with table properties:

CREATE INDEX table08_index ON TABLE table08 (column9) AS 'COMPACT' TBLPROPERTIES ("prop3"="value3", "prop4"="value4");

Drop index if exists:

DROP INDEX IF EXISTS table09_index ON table09;

Rebuild index on a partition:

ALTER INDEX table10_index ON table10 PARTITION (columnX='valueQ', columnY='valueR') REBUILD;

2. HIVE创建索引测试：

CREATE INDEX index_persona_idCard2 ON TABLE persona (idCard) AS 'COMPACT' WITH DEFERRED REBUILD;

ALTER INDEX index_persona_idCard2 ON persona REBUILD;

3. 添加索引查询前后对比

未添加索引查询：

jdbc:hive2://hadoop01:10015/> select count (*) from test1;

添加索引查询：

jdbc:hive2://hadoop01:10015/> select count (*) from test1;