You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Ramasubramanian Narayanan <ra...@gmail.com> on 2013/07/26 10:17:59 UTC

Need help in joining 2 tables

Hi,

Need to create a view in HIVE as per the below query (written in Oracle).
Need help to provide the equivalent query in HIVE to create view.


*EMP_ADDRESS*

Emp_Id
Address1
Address2
Address3
Address4
City
State
Pin
Country
Alternate_Address1
Alternate_Address2
Alternate_Address3
Alternate_Address4
Alternate_City
Alternate_State
Alternate_Pin
Alternate_Country
row_create_date


*EMP_MASTER*

Emp_Id
Name
DOB
Department
Sex
FathersName
row_create_date


*View Query*

select
MAST.Emp_Id,
MAST.Name,
MAST.DOB,
ADDR.Address1,
ADDR.Address2,
ADDR.Address3,
ADDR.Address4,
ADDR.City,
ADDR.State,
ADDR.Pin,
ADDR.Country
from EMP_MASTER MAST, EMP_ADDRESS ADDR
where
MAST.row_create_date = (select max(row_create_date) from EMP_MASTER where
Emp_Id = MAST.Emp_Id)
and ADDR.row_create_date = (select max(row_create_date) from EMP_ADDRESS
where Emp_Id = ADDR.Emp_Id)


regards,
Rams

Re: Need help in joining 2 tables

Posted by manish dunani <ma...@gmail.com>.
Hi Rama,

Have a look !!

Both sql and hiveql looks similar..
try this one..

*How to create view??*
*
*
*Ans:*
*
*
create view v1 as
select m.Emp_id,
m.Name,
m.Dob,
a.address1,
a.address2,
a.address3,
a.address4,
a.city,
a.state,
a.zip,
a.country from EMP_MASTER m join EMP_ADDRESS a on (m.Emp_id=a.Emp_id)
where m.row_create_date=(select MAX(row_create_date) from EMP_MASTER where
Emp_id = m.Emp_id)
and a.row_create_date = (select MAX(row_create_date) from EMP_ADDRESS where
Emp_id = a.Emp_id)




On Fri, Jul 26, 2013 at 1:47 PM, Ramasubramanian Narayanan <
ramasubramanian.narayanan@gmail.com> wrote:

> Hi,
>
> Need to create a view in HIVE as per the below query (written in Oracle).
> Need help to provide the equivalent query in HIVE to create view.
>
>
> *EMP_ADDRESS*
>
> Emp_Id
> Address1
> Address2
> Address3
> Address4
> City
> State
> Pin
> Country
> Alternate_Address1
> Alternate_Address2
> Alternate_Address3
> Alternate_Address4
> Alternate_City
> Alternate_State
> Alternate_Pin
> Alternate_Country
> row_create_date
>
>
> *EMP_MASTER*
>
> Emp_Id
> Name
> DOB
> Department
> Sex
> FathersName
> row_create_date
>
>
> *View Query*
>
> select
> MAST.Emp_Id,
> MAST.Name,
> MAST.DOB,
> ADDR.Address1,
> ADDR.Address2,
> ADDR.Address3,
> ADDR.Address4,
> ADDR.City,
> ADDR.State,
> ADDR.Pin,
> ADDR.Country
> from EMP_MASTER MAST, EMP_ADDRESS ADDR
> where
> MAST.row_create_date = (select max(row_create_date) from EMP_MASTER where
> Emp_Id = MAST.Emp_Id)
> and ADDR.row_create_date = (select max(row_create_date) from EMP_ADDRESS
> where Emp_Id = ADDR.Emp_Id)
>
>
> regards,
> Rams
>



-- 
MANISH DUNANI
-THANX
+91 9426881954,+91 8460656443
manishd207@gmail.com

Re: Need help in joining 2 tables

Posted by Richa Sharma <ma...@gmail.com>.
Hi

Have a look at link below. They have enough examples to clarify usage

https://cwiki.apache.org/confluence/display/Hive/Tutorial#Tutorial-Joins

Richa


On Fri, Jul 26, 2013 at 1:47 PM, Ramasubramanian Narayanan <
ramasubramanian.narayanan@gmail.com> wrote:

> Hi,
>
> Need to create a view in HIVE as per the below query (written in Oracle).
> Need help to provide the equivalent query in HIVE to create view.
>
>
> *EMP_ADDRESS*
>
> Emp_Id
> Address1
> Address2
> Address3
> Address4
> City
> State
> Pin
> Country
> Alternate_Address1
> Alternate_Address2
> Alternate_Address3
> Alternate_Address4
> Alternate_City
> Alternate_State
> Alternate_Pin
> Alternate_Country
> row_create_date
>
>
> *EMP_MASTER*
>
> Emp_Id
> Name
> DOB
> Department
> Sex
> FathersName
> row_create_date
>
>
> *View Query*
>
> select
> MAST.Emp_Id,
> MAST.Name,
> MAST.DOB,
> ADDR.Address1,
> ADDR.Address2,
> ADDR.Address3,
> ADDR.Address4,
> ADDR.City,
> ADDR.State,
> ADDR.Pin,
> ADDR.Country
> from EMP_MASTER MAST, EMP_ADDRESS ADDR
> where
> MAST.row_create_date = (select max(row_create_date) from EMP_MASTER where
> Emp_Id = MAST.Emp_Id)
> and ADDR.row_create_date = (select max(row_create_date) from EMP_ADDRESS
> where Emp_Id = ADDR.Emp_Id)
>
>
> regards,
> Rams
>

Re: Need help in joining 2 tables

Posted by Sanjay Subramanian <Sa...@wizecommerce.com>.
Hi Rams

Please don't think I am sermonizing or preaching and please don't mind what I am saying :-)

This community is there is help u and there is no doubt about that.

However I am assuming you tried out a few options by yourself before you reached out to the community with your question.

Since the whole community is opensource and everyone is busy in projects , I think it will be better utilization of everyones time if you spend a few minutes first playing around a few options, juxtapose syntax if needed and see what errors you get (if any) and then ask your question.
There are many  times an error for a syntax might give new ideas to community developers.

If this makes sense, I am happy…..if not, I don't mind :-)

Good luck with Hive…its a great technology and there are many people working very hard to make this better every day

Regards

sanjay


From: Ramasubramanian Narayanan <ra...@gmail.com>>
Reply-To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Date: Friday, July 26, 2013 1:17 AM
To: "user@hive.apache.org<ma...@hive.apache.org>" <us...@hive.apache.org>>
Subject: Need help in joining 2 tables

Hi,

Need to create a view in HIVE as per the below query (written in Oracle). Need help to provide the equivalent query in HIVE to create view.


EMP_ADDRESS

Emp_Id
Address1
Address2
Address3
Address4
City
State
Pin
Country
Alternate_Address1
Alternate_Address2
Alternate_Address3
Alternate_Address4
Alternate_City
Alternate_State
Alternate_Pin
Alternate_Country
row_create_date


EMP_MASTER

Emp_Id
Name
DOB
Department
Sex
FathersName
row_create_date


View Query

select
MAST.Emp_Id,
MAST.Name,
MAST.DOB,
ADDR.Address1,
ADDR.Address2,
ADDR.Address3,
ADDR.Address4,
ADDR.City,
ADDR.State,
ADDR.Pin,
ADDR.Country
from EMP_MASTER MAST, EMP_ADDRESS ADDR
where
MAST.row_create_date = (select max(row_create_date) from EMP_MASTER where Emp_Id = MAST.Emp_Id)
and ADDR.row_create_date = (select max(row_create_date) from EMP_ADDRESS where Emp_Id = ADDR.Emp_Id)


regards,
Rams

CONFIDENTIALITY NOTICE
======================
This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.