Hive中text格式和orc格式表的索引性能相关总结

Hive中text格式和orc格式表的索引性能相关总结
text表索引的使用：
建表：
create table aa (O_ORDERKEY int, O_CUSTKEY int, O_ORDERSTATUS string, O_TOTALPRICE string, O_ORDERDATE string, O_ORDERPRIORITY string, O_CLERK string, O_SHIPPRIORITY int, O_COMMENT string)
row format delimited fields terminated by ‘|’ stored as textfile;
导数据(2100万条)：
swhdfs dfs -put aa.txt /user/hive/warehouse/aa
创建索引表：
create index idx on table aa(O_ORDERKEY) as ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’ with deferred rebuild in table aa_tmp;
导数据到索引表：
alter index idx on aa rebuild;
生效索引需要设置的参数：
SET hive.fetch.task.conversion=minimal;
SET hive.optimize.index.filter.compact.minsize=0;
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
SET hive.optimize.index.filter=true;
text表有无索引性能对比：
通过hive client 查询不走索引：

通过h