hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From manish dunani <manishd...@gmail.com>
Subject Re: Need help in joining 2 tables
Date Fri, 26 Jul 2013 08:53:20 GMT
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

Mime
View raw message