I don't have a hive shell under my hands. What's the planning? It should be doable with a single MapReduce if the cross join is constructed as a 'HashJoin'. Is that the case? I am curious.
(You can use 'explain' on your query to know the planning.) Bertrand On Thu, Aug 9, 2012 at 11:08 PM, <richin.j...@nokia.com> wrote: > Thanks Guys, it worked.**** > > ** ** > > *From:* ext Bertrand Dechoux [mailto:decho...@gmail.com] > *Sent:* Thursday, August 09, 2012 5:03 PM > *To:* user@hive.apache.org > *Subject:* Re: Nested Select Statements**** > > ** ** > > Basically a cross join. You would have the same issue with SQL. > > Bertrand**** > > On Thu, Aug 9, 2012 at 10:41 PM, shrikanth shankar <sshan...@qubole.com> > wrote:**** > > This should work**** > > ** ** > > Select ts,id,sum(metric/usage_count) from usage join (select count(*) > usage_count from usage) V on ( 1 = 1) group by ts,id;**** > > ** ** > > thanks,**** > > Shrikanth**** > > ** ** > > On Aug 9, 2012, at 1:33 PM, <richin.j...@nokia.com> wrote:**** > > > > **** > > Hi (vers),**** > > **** > > This might be a very basic question for most of you but I am stuck at it > for quite some time now. I have a table with three columns : ** > ** > > *Describe usage;***** > > *ts string***** > > *id string***** > > *metric double***** > > **** > > I am trying to do a query like**** > > *Select ts,id,sum(metric/(select count(*) from usage)) from usage group > by ts,id;***** > > **** > > This throws a parse error- Can’t recognize input near ‘select’ ‘count’ > ‘(‘ in expression specification.**** > > I tried setting the output in a temp variable and use it in the query like > **** > > *Set totalrows = select count(*) from usage;***** > > *Select ts, id, sum(metric/${hiveconf:totalrows}) from usage group by > ts,id;***** > > **** > > This also throws a parse error as the variable gets substituted by > variable. So I have three questions.**** > > 1. What is wrong with the above queries?**** > > 2. Is there another way to find number of rows in a table?**** > > 3. Is there a better way for what I am trying to do?**** > > **** > > Thanks,**** > > Richin**** > > ** ** > > > > > -- > Bertrand Dechoux**** > -- Bertrand Dechoux
