Hive（10）--Hive操作语句（6）Hive索引

Hive 在 0.7.0 引入了索引的功能，索引的设计目标是提高表某些列的查询速度。如果没有索引，带有谓词的查询（如'WHERE table1.column = 10'）会加载整个表或分区并处理所有行。但是如果 column 存在索引，则只需要加载和处理文件的一部分。

索引原理

在指定列上建立索引，会产生一张索引表（表结构如下），里面的字段包括：索引列的值、该值对应的 HDFS 文件路径、该值在文件中的偏移量。在查询涉及到索引字段时，首先到索引表查找索引列值对应的 HDFS 文件路径及偏移量，这样就避免了全表扫描。

+--------------+----------------+----------+--+
|   col_name   |   data_type    | comment     |
+--------------+----------------+----------+--+
| empno        | int            |  建立索引的列  |   
| _bucketname  | string         |  HDFS 文件路径  |
| _offsets     | array<bigint>  |  偏移量       |
+--------------+----------------+----------+--+

索引操作

创建索引

CREATE INDEX index_name     --索引名称
  ON TABLE base_table_name (col_name, ...)  --建立索引的列
  AS index_type    --索引类型
  [WITH DEFERRED REBUILD]    --重建索引
  [IDXPROPERTIES (property_name=property_value, ...)]  --索引额外属性
  [IN TABLE index_table_name]    --索引表的名字
  [
     [ ROW FORMAT ...] STORED AS ...  
     | STORED BY ...
  ]   --索引表行分隔符 、 存储格式
  [LOCATION hdfs_path]  --索引表存储位置
  [TBLPROPERTIES (...)]   --索引表表属性
  [COMMENT "index comment"];  --索引注释

案例：
--- 创建索引
create index status_index on table weather(status) as  
'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' 
with deferred rebuild in table weather_index_table;

**创建索引之后必须使用重建索引语句索引才能够生效

ALTER INDEX [索引名]**  **ON [表名] REBUILD;

案例：
ALTER INDEX** status_index  **ON** weather  **REBUILD;

之后查看创建的索引表可以看出有_offsets   

Hive 会启动 MapReduce 作业去建立索引，建立好后查看索引表数据如下。三个表字段分别代表：索引列的值、该值对应的 HDFS 文件路径、该值在文件中的偏移量。                                                                         

status|_bucketname                                                                 |_offsets                                                                            |
------+----------------------------------------------------------------------------+------------------------------------------------------------------------------------+
重度污染|hdfs://cluster/user/hive/warehouse/jacquesh_databases.db/weather/weather.csv|[73,1743]                                                                           |
轻度污染|hdfs://cluster/user/hive/warehouse/jacquesh_databases.db/weather/weather.csv|[489,1049,1375,1467,1559,1650,2383,2473]                                            |
优      |hdfs://cluster/user/hive/warehouse/jacquesh_databases.db/weather/weather.csv|[392,676,747,818]                                                                   |
中度污染|hdfs://cluster/user/hive/warehouse/jacquesh_databases.db/weather/weather.csv|[2804]                                                                              |
严重污染|hdfs://cluster/user/hive/warehouse/jacquesh_databases.db/weather/weather.csv|[176]                                                                               |
良 |hdfs://cluster/user/hive/warehouse/jacquesh_databases.db/weather/weather.csv|[0,289,601,889,972,1140,1217,1296,1835,1914,1993,2071,2149,2227,2305,2563,2638,2721]|**

默认情况下，虽然建立了索引，但是 Hive 在查询时候是不会自动去使用索引的，需要开启相关配置。开启配置后，涉及到索引列的查询就会使用索引功能去优化查询。

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;

查看索引

show index on 表名;

案例：
--- 查看索引

SHOW INDEX ON weather;

idx_name            |tab_name            |col_names           |idx_tab_name        |idx_type            |comment|
--------------------+--------------------+--------------------+--------------------+--------------------+-------+
status_index        |weather             |status              |weather_index_table |compact             |       |

idx_tab_name指向了这个索引所对应的索引表

删除索引

DROP INDEX [IF EXISTS] 索引名 ON 表名;

案例：
DROP INDEX [IF EXISTS] status_index   ON   weather ;