先说一个0.7.1和0.8.1的Metastore不兼容
一。外部表的索引。
因为怕麻烦,就继续用之前的table02的数据,在new meta里也叫table02,不过改成了external表。最后的结论是Hive的索引也是支持外部表的。
建立索引,运行。还是6个mapper,不行。
二。建立内部表
CTAS from table02,建立一个内部表table03;
重点是,换了0.8.1,
<property>
<name>hive.optimize.index.filter</name>
<value>true</value>
<description>Whether to enable automatic use of indexes</description>
</property>
建立了索引:
1. hive> create index table03_index on table
2. as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
3. with
4. OK
5. Time
6. hive> show tables;
7. OK
8. default__table02_compact_index__
9. default__table03_table03_index__
10. table02
11. table03
12. Time
13. user/hive/warehouse/default__table03_table03_index__;
14. user/hive/warehouse/default__table02_compact_index__;
15. Found 1 items
16. --r-- 1 allen supergroup 74701985 2012-03-12 17:25 /user/hive/warehouse/default__table02_compact_index__/000000_0
17. alter index table03_index on
现在重新query:
1. hive> select * from table03 where
2. Total MapReduce jobs = 1
3. out of
4. of reduce tasks is set to 0 since there's no
5. Starting Job = job_201203122135_0003, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201203122135_0003
6. Kill Command = /home/allen/Hadoop/hadoop-0.20.2/bin/../bin/hadoop job -Dmapred.job.tracker=localhost:9001 -kill job_201203122135_0003
7. for Stage-1: number of mappers: 2; number of
8. 2012-03-12 21:58:02,283 Stage-1 map = 0%, reduce = 0%
9. 2012-03-12 21:58:35,932 Stage-1 map = 50%, reduce = 0%
10. 2012-03-12 21:58:39,057 Stage-1 map = 63%, reduce = 0%
11. 2012-03-12 21:58:45,216 Stage-1 map = 75%, reduce = 0%
12. 2012-03-12 21:58:51,407 Stage-1 map = 88%, reduce = 0%
13. 2012-03-12 21:58:57,667 Stage-1 map = 100%, reduce = 0%
14. 2012-03-12 21:59:03,798 Stage-1 map = 100%, reduce = 100%
15. Ended Job = job_201203122135_0003
16. MapReduce Jobs Launched:
17. Read: 356889161 HDFS Write: 357 SUCESS
18. Time
19. OK
20. and would flaunt his wares and skills at All-Star weekends like the recent aerial show in Orlando, Fla. There was a time, however fleeting, when he was more heralded, or perhaps merely hyped, than any other high school player in
21. Time
s还是不行,再设置一个参数:
<property>
<name>hive.optimize.index.filter.compact.minsize</name>
<value>5368</value>
<description>Minimum size (in bytes) of the inputs on which a compact index is automatically used.</description>原来是5368709120
</property>
再次尝试:好了
1. hive> select * from table02 where
2. Total MapReduce jobs = 3
3. out of
4. of reduce tasks is set to 0 since there's no
5. Starting Job = job_201203122135_0010, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201203122135_0010
6. Kill Command = /home/allen/Hadoop/hadoop-0.20.2/bin/../bin/hadoop job -Dmapred.job.tracker=localhost:9001 -kill job_201203122135_0010
7. for Stage-3: number of mappers: 1; number of
8. 2012-03-12 22:12:18,347 Stage-3 map = 0%, reduce = 0%
9. 2012-03-12 22:12:33,834 Stage-3 map = 100%, reduce = 0%
10. 2012-03-12 22:12:40,078 Stage-3 map = 100%, reduce = 100%
11. Ended Job = job_201203122135_0010
12. is filtered out (removed at
13. to: hdfs://localhost:9000/tmp/hive-allen/hive_2012-03-12_22-12-07_069_3484347533083360337/-ext-10000
14. to: hdfs://localhost:9000/tmp/hive-allen/hive_2012-03-12_22-12-06_413_2863963861844056912/-mr-10002
15. out of
16. of reduce tasks is set to 0 since there's no
17. Starting Job = job_201203122135_0011, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201203122135_0011
18. Kill Command = /home/allen/Hadoop/hadoop-0.20.2/bin/../bin/hadoop job -Dmapred.job.tracker=localhost:9001 -kill job_201203122135_0011
19. for Stage-1: number of mappers: 1; number of
20. 2012-03-12 22:12:53,124 Stage-1 map = 0%, reduce = 0%
21. 2012-03-12 22:13:04,590 Stage-1 map = 100%, reduce = 0%
22. 2012-03-12 22:13:10,897 Stage-1 map = 100%, reduce = 100%
23. Ended Job = job_201203122135_0011
24. MapReduce Jobs Launched:
25. Read: 74706082 HDFS Write: 68 SUCESS
26. Read: 33554431 HDFS Write: 357 SUCESS
27. Time
28. OK
29. and would flaunt his wares and skills at All-Star weekends like the recent aerial show in Orlando, Fla. There was a time, however fleeting, when he was more heralded, or perhaps merely hyped, than any other high school player in
30. Time
31. hive>