1、在hive中创建索引所在表
create table if not exists h_odse.hxy(
id int,
name string,
hobby array<string>,
add map<string,string>
)
partitioned by (age int,sex string)
row format delimited
fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
2、在服务器内创建数据文件并导入数据
load data local inpath '/home/hetl/data' into table h_odse.hxy partition (age=10,sex='f');
3、从其他表中导入数据
load data local inpath '/home/hetl/data' into table h_odse.hxy partition(age=10,sex='boy') ;
from h_odse.hxy1
insert into h_odse.hxy partition(age,sex)
select id,name,hobby,add,age,sex distribute by age,sex;
4、创建指定索引表的索引
create index hxy_ind on table h_odse.hxy(id) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild in table h_odse.h_ind_table;
-----当in table h_odse.h_ind_table不加时 系统会自动创建一个索引表
5，使索引生效的话，必须执行
alter index hxy_ind on h_odse.hxy rebuild;
6,测试索引效果
select * from h_odse.hxy where id=1
未加索引时：
Time taken: 0.676 seconds, Fetched: 24 row(s)
添加索引后：
Time taken: 0.494 seconds, Fetched: 24 row(s)
7,、查询当前表中有哪些索引
use h_odse;
show index on hxy;
8、删除索引
drop index hxy_ind on h_odse.hxy;