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 Plain View
Hive >> mail # user >> Hive Join-Query


+
prabhu k 2012-07-25, 14:51
Copy link to this message
-
Re: Hive Join-Query
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.
>
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