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

Switch to Threaded View
Hive, mail # user - Hive Join-Query


Copy link to this message
-
Re: Hive Join-Query
Lefty Leverenz 2012-07-26, 01:08
Your query says "JOIN supplier s ON (s.supplierid=v.supplier)" but
s.supplierid should be s.supplier_id.

Also, the vender schema shows a "quantiry" column which might be just a
message typo, but if you cut-&-pasted the schema data into the message then
you should change the name to "quantity".

– Lefty

On Wed, Jul 25, 2012 at 7:51 AM, prabhu k <[EMAIL PROTECTED]> wrote:

> Hi Users,
>
> I have 3 table's vender,supplier and date, by using these table Im trying
> to generate a report like below
>
> *Vendor Name,  Supplier Name, Year, Quarter, Sum ( quantity )*
>
>
> I have executed the below query, after execute the query,I'm not getting
> any result on my console
>
>  hive>select v.vender,s.supplier_name,d.quarter,sum(v.quantity) from
> vender1k v
>     > JOIN dim_date d ON (v.order_date = d.ddate)
>     > JOIN supplier s ON (s.supplierid=v.supplier)
>     > group by v.quantity,d.quarter,v.vender,s.supplier_name;
>
> vender
> =======>
> vender    supplier   order_date quantiry
> ven_1     supp_1    2010-03-03 5000
> ven_2     supp_2    2010-03-03 6000
> ven_3     supp_1   2010-03-03 6000
>
> supplier
> =========> supplier_id supplier_name
>
> supp_1          Nokia
> supp_2          HLL
> supp_1          NOKIA
>
> DIM_DATE
> =======> ddate                  year          quarter
> 2010-03-03          2010            2
> 2010-03-03          2010            2
> 2010-03-03          2010            2
>
>
> Please suggest and help me on this query.
>
> Thanks,
> Prabhu.
>