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, <[email protected]> wrote: > Thanks Guys, it worked.**** > > ** ** > > *From:* ext Bertrand Dechoux [mailto:[email protected]] > *Sent:* Thursday, August 09, 2012 5:03 PM > *To:* [email protected] > *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 <[email protected]> > 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, <[email protected]> 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
