You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@hive.apache.org by prabhu k <pr...@gmail.com> on 2012/07/25 16:51:45 UTC

Hive Join-Query

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.

Re: Hive Join-Query

Posted by Lefty Leverenz <le...@hortonworks.com>.

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 <pr...@gmail.com> 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.
>