Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive, mail # user - Aggregrate Query Fails.


+
Matt Pestritto 2009-04-22, 22:22
Copy link to this message
-
Re: Aggregrate Query Fails.
Amr Awadallah 2009-04-22, 22:40

in the group by, try this instead:

*group by m.description, buyers* limit 40 ;

Matt Pestritto wrote:
> Hi - I'm having a problem with a query below.  When I try to run any
> aggregate function on a column from the sub-query, the job fails.
> The queries and output messages are below.
>
> Suggestions?
>
> thanks in advance.
>
> -- works:  2 map-reduces jobs.
> select m.description, o_buyers.num as buyers
> from clickstream_output o
>   join merchandise m on (o.merchandise_id = m.merchandise_id)
>   left outer join ( select o1.merchandise_id, count(distinct
> o1.consumer_id) as num from clickstream_output o1
>          where o1.file_date >= '20090216' and o1.file_date <=
> '20090217' and o1.event_id = 'buy' group by o1.merchandise_id ) o_buyers
>      on (o_buyers.merchandise_id = o.merchandise_id)
> where o.file_date >= '20090216' and o.file_date <= '20090217' limit 40;
>
> Successful output: PROD7362, 3
>
> -- fails: 3 map-reduce jobs - 2nd reduce fails.
> select  m.description, o_buyers.num as buyers*, count(1) as total*  --
> sum or max(o_buyers.num) and removing from group by also fails.
> from clickstream_output o
>   join merchandise m on (o.merchandise_id = m.merchandise_id)
>   left outer join ( select o1.merchandise_id, count(distinct
> o1.consumer_id) as num from clickstream_output o1
>          where o1.file_date >= '20090216' and o1.file_date <=
> '20090217' and o1.event_id = 'buy' group by o1.merchandise_id ) o_buyers
>      on (o_buyers.merchandise_id = o.merchandise_id)
> where o.file_date >= '20090216' and o.file_date <= '20090217'
> *group by m.description, o_buyers.num* limit 40 ;
>
>
> java.io.IOException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.LongWritable cannot be cast to org.apache.hadoop.io.Text
>
> at org.apache.hadoop.hive.ql.exec.ExecReducer.reduce(ExecReducer.java:172)
> at org.apache.hadoop.mapred.ReduceTask.run(ReduceTask.java:391)
> at org.apache.hadoop.mapred.TaskTracker$Child.main(TaskTracker.java:2122)
>
> Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassCastException: org.apache.hadoop.io.LongWritable cannot be cast to org.apache.hadoop.io.Text
> at org.apache.hadoop.hive.ql.exec.GroupByOperator.process(GroupByOperator.java:489)
>
> at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:353)
> at org.apache.hadoop.hive.ql.exec.FilterOperator.process(FilterOperator.java:69)
> at org.apache.hadoop.hive.ql.exec.Operator.forward(Operator.java:353)
>
> at org.apache.hadoop.hive.ql.exec.JoinOperator.createForwardJoinObject(JoinOperator.java:273)
> at org.apache.hadoop.hive.ql.exec.JoinOperator.genObject(JoinOperator.java:516)
> at org.apache.hadoop.hive.ql.exec.JoinOperator.genObject(JoinOperator.java:505)
>
> at org.apache.hadoop.hive.ql.exec.JoinOperator.genObject(JoinOperator.java:505)
> at org.apache.hadoop.hive.ql.exec.JoinOperator.genObject(JoinOperator.java:505)
> at org.apache.hadoop.hive.ql.exec.JoinOperator.checkAndGenObject(JoinOperator.java:546)
>
> at org.apache.hadoop.hive.ql.exec.JoinOperator.endGroup(JoinOperator.java:528)
> at org.apache.hadoop.hive.ql.exec.ExecReducer.reduce(ExecReducer.java:143)
>  
>
+
Matt Pestritto 2009-04-22, 22:43
+
Matt Pestritto 2009-04-22, 22:46
+
Prasad Chakka 2009-04-22, 22:49
+
Matt Pestritto 2009-04-22, 22:53
+
Ashish Thusoo 2009-04-23, 00:09
+
Zheng Shao 2009-04-23, 01:35
+
Jeff Hammerbacher 2009-04-23, 03:22
+
Zheng Shao 2009-04-23, 08:39
+
Matt Pestritto 2009-04-23, 03:38