HIVE 创建索引

4.索引的建立与使用

语法如下：

CREATE INDEX index_name 
ON TABLE base_table_name (col_name, ...)
AS 'index.handler.class.name'
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name=property_value, ...)]
[IN TABLE index_table_name]
[PARTITIONED BY (col_name, ...)]
[
   [ ROW FORMAT ...] STORED AS ...
   | STORED BY ...
]
[LOCATION hdfs_path]
[TBLPROPERTIES (...)]
[COMMENT "index comment"]
AS ...语句指定了 索引处理器，也就是一个实现了索引接口的Java类。

5.建立完索引之后 需要重建索引数据，会触发一个mr job

ALTER INDEX employee_index
ON TABLE employee
PARTITION (country = 'US')
REBUILD;

6.建立完可以通过显示命令显示索引

SHOW FORMATTED INDEX ON employees;

7.想要索引在查询时，生效，还得设置使用索引：默认是不使用的。

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
SET hive.optimize.index.filter=true;
SET hive.optimize.index.filter.compact.minsize=0;

8. 删除索引

DROP INDEX IF EXISTS employees_index ON TABLE employees;