创建索引
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"]
注意:
1.index的partition默认和数据表一致
2.视图上不能创建index
3. index可以通过stored as配置存储格式
重建索引
ALTER INDEX index_name ON table_name [PARTITION (...)] REBUILD
假如在创建索引是我们使用“ WITH DEFERRED REBUILD”语句,则索引创建是为空,可以通过“Alter index ... REBUILD”在一个partition上或所有partition上构建索引。
注意:
1.当hive数据更新时,必须调用该语句更新索引。
2. index rebuild操作时一个原子操作,因此,当rebuild失败时,先前构建的索引也无法使用!
删除索引
DROP INDEX index_name ON table_name