Hive索引使用

1.创建索引：

hive> create index 1index_leon on table ipslog(nstimestamp)
> as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
> with deferred rebuild
> IN TABLE index_table_student_3
OK
Time taken: 12.219 seconds
hive>

//org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler ：创建索引需要的实现类
//1index_leon:索引名称
//ipslog:表名
//index_table_student_3:创建索引后的表名

2.加载索引数据：

0: jdbc:hive2://localhost:10000/default> SELECT * from index_table_student_3;
+--------------+--------------+-----------+----------+-------+--+
| nstimestamp  | _bucketname  | _offsets  | ns_date  | hash  |
+--------------+--------------+-----------+----------+-------+--+
+--------------+--------------+-----------+----------+-------+--+
No rows selected (0.166 seconds)

hive> alter index 11index_leon on ipslog rebuild;
//索引表装在数据

hive> select * from index_table_student_3 limit 3;
OK
1530279304      hdfs://10.65.123.103:9000/user/hive/warehouse/ipslog/ns_date=20180629/hash=8626-95A0-7FA1-CFB8/part-00000_copy_1 [13724] 20180629        8626-95A0-7FA1-CFB8
1530281171      hdfs://10.65.123.103:9000/user/hive/warehouse/ipslog/ns_date=20180629/hash=8626-95A0-7FA1-CFB8/part-00001        [6369]  20180629        8626-95A0-7FA1-CFB8
1530281755      hdfs://10.65.123.103:9000/user/hive/warehouse/ipslog/ns_date=20180629/hash=8626-95A0-7FA1-CFB8/part-00000        [14473] 20180629        8626-95A0-7FA1-CFB8
Time taken: 0.082 seconds, Fetched: 3 row(s)

3.查看索引：

hive>  SHOW INDEX on ipslog;
OK
11index_leon            ipslog                  nstimestamp             index_table_student_3   compact  
1index_leon             ipslog                  nstimestamp             default__ipslog_1index_leon__   compact
Time taken: 0.029 seconds, Fetched: 2 row(s)

4.删除索引：

hive> DROP INDEX 1index_leon on ipslog;
OK
Time taken: 0.639 seconds

5.建立索引前：

6.建立索引后：