hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Markovitz, Dudu" <dmarkov...@paypal.com>
Subject RE: Question on Implementing CASE in Hive Join
Date Tue, 19 Apr 2016 13:08:54 GMT
Please try the following two options.
Option 2 might be better, performance wise (depending of the data volume and characteristics).

P.s.
I didn’t understand the explanation about the LEFT JOIN


Dudu

1.

select      b.code
           ,b.value

from                    b

            left join   a

            on          a.type      = b.type
                    and a.code      like case b.code       when 'ALL' then '%' else b.code
      end
                    and a.indicator like case b.indicatior when 'ALL' then '%' else b.indicatior
end
;



2.

select      b.code
           ,b.value

from                    b

            left join   a

            on          a.type      = b.type
                    and a.code      = b.code
                    and a.indicator = b.indicatior

where       b.code       != 'ALL'
        and b.indicatior != 'ALL'

union all

select      b.code
           ,b.value

from                    b

            left join   a

            on          a.type      = b.type
                    and a.indicator = b.indicatior

where       b.code        = 'ALL'
        and b.indicatior != 'ALL'

union all

select      b.code
           ,b.value

from                    b

            left join   a

            on          a.type      = b.type
                    and a.code      = b.code

where       b.code       != 'ALL'
        and b.indicatior  = 'ALL'

union all

select      b.code
           ,b.value

from                    b

            left join   a

            on          a.type      = b.type

where       b.code       = 'ALL'
        and b.indicatior = 'ALL'
;


From: Kishore A [mailto:kishore.atmakuri@gmail.com]
Sent: Tuesday, April 19, 2016 3:51 PM
To: user@hive.apache.org
Subject: Re: Question on Implementing CASE in Hive Join

Hi Dudu,

Actually we use both fields from left and right tables, I mentioned right table just for my
convenience to check whether ALL from right table can be pulled as per join condition match.

One more reason why we use left join is we should not have extra columns after join.

Kishore



On Tue, Apr 19, 2016 at 5:46 AM, Markovitz, Dudu <dmarkovitz@paypal.com<mailto:dmarkovitz@paypal.com>>
wrote:
Before dealing with the technical aspect, can you please explain what is the point of using
LEFT JOIN without selecting any field from table A?

Thanks

Dudu

From: Kishore A [mailto:kishore.atmakuri@gmail.com<mailto:kishore.atmakuri@gmail.com>]
Sent: Tuesday, April 19, 2016 2:29 PM
To: user@hive.apache.org<mailto:user@hive.apache.org>
Subject: Question on Implementing CASE in Hive Join

Hi,

I have a scenario to implement to cases in Hive Joins. I need to implement case on the value
on which join condition to be applied.

Table A
Code// Type// Indicator// Value//
A      1      XYZ         John
B      1      PQR         Smith
C      2      XYZ         John
C      2      PQR         Smith
D      3      PQR         Smith
E      3      XYZ         Smith
F      4      MNO         Smith
G      3      MNO         Smith
D      1      XYZ         John
N      3      STR         Smith


Table B
Code// Type// Indicator// Value//
ALL    1      XYZ         John
D        3      ALL         Smith
ALL    1      PQR         Smith

I need to stamp Value from TableB by joining TableA and I am writing join condition as below.
Note : No instance of ALL for Type column, a value for Type will be provided.

Select b.Code,b.Value from B
LEFT JOIN A a ON
a.Code = (case when b.Code = 'ALL' then a.Code else b.Code END)
AND
a.Type = b.Type
AND
a.Indicator = (case when b.Indicatior = 'ALL' then a.Inidicator else b.Inidicator END)

When I run this in hive this query is failing with below error
Error while compiling statement: FAILED: SemanticException [Error 10017]: Line 4:0 Both left
and right aliases encountered in JOIN 'Code'.


Please let me know if more details are needed

Thanks,
Kishore


Mime
View raw message