Re: order by having no effect?!
You have attributevalue in quotes which makes it a constant literal. igor decide.com On Tue, Mar 13, 2012 at 1:54 PM, Keith Wiley kwi...@keithwiley.com wrote: Um, this is weird. It simply isn't modifying the order of the returned rows at all. I get the same result with no 'order by' clause as with one. Adding a limit or specifying 'asc' has no effect. Using 'sort by' also has no effect. The column used for ordering is type INT. In the example below, I was hoping to sort or order the results according to the third column. Like I said, I also tried adding 'limit 10' and/or 'asc' to the end of the query; they had no effect. The jobtracker shows a single mapper and a single reducer being used for the job incidentally, so it can't be some sort of multi-reducer sort discrepancy. hive describe stringmap; OK objecttypecode int attributename string attributevalue int langid int value string displayorderint Time taken: 0.074 seconds hive hive select * from stringmap where attributename='foo' order by 'attributevalue'; Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 1 In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=number In order to limit the maximum number of reducers: set hive.exec.reducers.max=number In order to set a constant number of reducers: set mapred.reduce.tasks=number Starting Job = job_201202221500_0114, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201202221500_0114 Kill Command = /media/sdb1/kwiley/hadoop/hadoop-0.20.2-cdh3u3/bin/hadoop job -Dmapred.job.tracker=localhost:9001 -kill job_201202221500_0114 2012-03-13 14:45:28,418 Stage-1 map = 0%, reduce = 0% 2012-03-13 14:45:31,428 Stage-1 map = 100%, reduce = 0% 2012-03-13 14:45:39,459 Stage-1 map = 100%, reduce = 33% 2012-03-13 14:45:40,463 Stage-1 map = 100%, reduce = 100% Ended Job = job_201202221500_0114 OK 3 foo 6 10336 - aaa 6 3 foo 3 10333 - bbb 3 3 foo 4 10334 - ccc 4 4 foo 1 1033Default Value 1 3 foo 2 10332 - ddd 2 3 foo 1 10331 - eee 1 3 foo 5 10335 - fff 5 Time taken: 17.954 seconds hive Keith Wiley kwi...@keithwiley.com keithwiley.com music.keithwiley.com Luminous beings are we, not this crude matter. -- Yoda
Re: order by having no effect?!
This syntax is wrong for both hive and SQL: hive select * from stringmap where attributename='foo' order by 'attributevalue'; This is right. hive select * from stringmap where attributename='foo' order by attributevalue; On Tue, Mar 13, 2012 at 4:54 PM, Keith Wiley kwi...@keithwiley.com wrote: Um, this is weird. It simply isn't modifying the order of the returned rows at all. I get the same result with no 'order by' clause as with one. Adding a limit or specifying 'asc' has no effect. Using 'sort by' also has no effect. The column used for ordering is type INT. In the example below, I was hoping to sort or order the results according to the third column. Like I said, I also tried adding 'limit 10' and/or 'asc' to the end of the query; they had no effect. The jobtracker shows a single mapper and a single reducer being used for the job incidentally, so it can't be some sort of multi-reducer sort discrepancy. hive describe stringmap; OK objecttypecode int attributename string attributevalue int langid int value string displayorder int Time taken: 0.074 seconds hive hive select * from stringmap where attributename='foo' order by 'attributevalue'; Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 1 In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=number In order to limit the maximum number of reducers: set hive.exec.reducers.max=number In order to set a constant number of reducers: set mapred.reduce.tasks=number Starting Job = job_201202221500_0114, Tracking URL = http://localhost:50030/jobdetails.jsp?jobid=job_201202221500_0114 Kill Command = /media/sdb1/kwiley/hadoop/hadoop-0.20.2-cdh3u3/bin/hadoop job -Dmapred.job.tracker=localhost:9001 -kill job_201202221500_0114 2012-03-13 14:45:28,418 Stage-1 map = 0%, reduce = 0% 2012-03-13 14:45:31,428 Stage-1 map = 100%, reduce = 0% 2012-03-13 14:45:39,459 Stage-1 map = 100%, reduce = 33% 2012-03-13 14:45:40,463 Stage-1 map = 100%, reduce = 100% Ended Job = job_201202221500_0114 OK 3 foo 6 1033 6 - aaa 6 3 foo 3 1033 3 - bbb 3 3 foo 4 1033 4 - ccc 4 4 foo 1 1033 Default Value 1 3 foo 2 1033 2 - ddd 2 3 foo 1 1033 1 - eee 1 3 foo 5 1033 5 - fff 5 Time taken: 17.954 seconds hive Keith Wiley kwi...@keithwiley.com keithwiley.com music.keithwiley.com Luminous beings are we, not this crude matter. -- Yoda
Re: order by having no effect?!
On Mar 13, 2012, at 13:57 , Igor Tatarinov wrote: You have attributevalue in quotes which makes it a constant literal. igor decide.com Argh! You are correct good sir! thanks Keith Wiley kwi...@keithwiley.com keithwiley.commusic.keithwiley.com And what if we picked the wrong religion? Every week, we're just making God madder and madder! -- Homer Simpson