Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Aggregrate Query Fails.


Copy link to this message
-
Re: Aggregrate Query Fails.

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)
>  
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB