Hive SQL: Both left and right aliases encountered

问题:

I have this valid T-SQL query:

select t1.*
    ,case when s1.period is not null then 'Y' else 'N' end as flag_cur
    ,case when s2.period is not null then 'Y' else 'N' end as flag_prev
    ,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur 
    ,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev 
into #tmp_leads2
from #tmp_leads t1
left join #param s1 on s1.period = '(a) Current' and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date 
left join #param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date 

I tried to re-write it for Hive (v0.13) as:

create table tmp_leads2 as  
select t1.*
    ,case when s1.period is not null then 'Y' else 'N' end as flag_cur
    ,case when s2.period is not null then 'Y' else 'N' end as flag_prev
    ,s1.cutoff_date as cutoff_date_cur ,s1.cutoff_dtkey as cutoff_dtkey_cur 
    ,s2.cutoff_date as cutoff_date_prev ,s2.cutoff_dtkey as cutoff_dtkey_prev 
from tmp_leads t1
left join param s1 on s1.period = '(a) Current'  and s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date 
left join param s2 on s2.period = '(b) Previous' and s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date ; 

But I get the error:

Error occurred executing hive query: OK FAILED: SemanticException [Error 10017]: Line 8:53 Both left and right aliases encountered in JOIN 'CreatedDate'

I see the fields it's talking about, but I'm not sure how to re-write this while keeping the query results identical.

回答1:

The problem comes from the inequality conditions in the joins. This poses a problem. The following is probably sufficient for your purposes:

create table tmp_leads2 as  
    select t1.*,
           (case when s1.period is not null then 'Y' else 'N' end) as flag_cur,
           (case when s2.period is not null then 'Y' else 'N' end) as flag_prev,
           s1.cutoff_date as cutoff_date_cur, s1.cutoff_dtkey as cutoff_dtkey_cur ,
           s2.cutoff_date as cutoff_date_prev, s2.cutoff_dtkey as cutoff_dtkey_prev 
    from tmp_leads t1 left join
         param s1
         on s1.period = '(a) Current' left join  
         param s2
         on s2.period = '(b) Previous'
    where (s1.begin_date is null or s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date) or
          (s2.begin_date is null or s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date);

This is not exactly equivalent. It makes the assumption that if a parameter is in the table, then it is in the table for all dates. That might be a reasonable assumption. If not, then a more complex query would be needed.

回答2:

Here is something which would not cause inner join or alias issue and give you the expected results in Hive

   create table tmp_leads2 as  
   select final.*
       ,case when s1period is not null then 'Y' else 'N' end as flag_cur
       ,case when s2period is not null then 'Y' else 'N' end as flag_prev

    from
    (select t1.*,
       max(case when  s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date then s1.peroid else null end) as s1period,
       max(case when  s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date then s1.cutoff_date else null end) as cutoff_date_cur,
       max(case when  s1.begin_date <= t1.CreatedDate and t1.CreatedDate < s1.end_date then s1.cutoff_dtkey else null end) as cutoff_dtkey_cur,

       max(case when  s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date then s2.peroid else null end) as s2period,
       max(case when  s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date then s2.cutoff_date else null end) as cutoff_date_prev,
       max(case when  s2.begin_date <= t1.CreatedDate and t1.CreatedDate < s2.end_date then s2.cutoff_dtkey else null end) as cutoff_dtkey_prev,

   from tmp_leads t1
   left join param s1 on s1.period = '(a) Current'  
   left join param s2 on s2.period = '(b) Previous' 
   group by t1.* /* type all column names required from t1*/
   ) final ;