Re: Need help in joining 2 tables
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 mailto:ramasubramanian.naraya...@gmail.com>> Reply-To: "user@hive.apache.org<mailto:user@hive.apache.org>" mailto:user@hive.apache.org>> Date: Friday, July 26, 2013 1:17 AM To: "user@hive.apache.org<mailto:user@hive.apache.org>" mailto:user@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.
Re: Need help in joining 2 tables
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.naraya...@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 manishd...@gmail.com
Re: Need help in joining 2 tables
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.naraya...@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 >
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
