CODEWITHSUNDEEP
sqlhivehiveql
AbtPst
AbtPst

Reputation: 8008

Hive : FAILED: SemanticException Column Found in more than One Tables/Subqueries

I am trying to run a query as follows

hive -e "set hive.execution.engine=mr;set hive.strict.checks.cartesian.product=false;
set hive.mapred.mode=nonstrict;use db1; select col1,col2 from tb1 where col_date='2020-08-15' and col3='Y' 
and col4='val4' and col1 not in 
( select distinct col1 from db2.tb2 where col_date='2020-08-15' and 
col5='val5' and col6='val6' and col3='Y' and col4='val4') "

but i keep getting

FAILED: SemanticException Column col1 Found in more than One Tables/Subqueries

what am i doing wrong ? how can i fix this ?

columns in db1.tb1

col1
col2
col_date
col3
col4

columns in db2.tb2

col1
col2
col_date
col3
col4
col5
col6

Upvotes: 2

Views: 2408

Answers (1)

leftjoin
leftjoin

Reputation: 38290

Add aliases to tables and use them for all columns:

hive -e "set hive.execution.engine=mr;set hive.strict.checks.cartesian.product=false;
set hive.mapred.mode=nonstrict;
use db1; 
select t1.col1, t1.col2 
  from tb1 t1
 where t1.col_date='2020-08-15' and t1.col3='Y' and t1.col4='val4' 
   and t1.col1 not in 
( select distinct t2.col1 from db2.tb2 t2 
   where t2.col_date='2020-08-15' and t2.col5='val5' and t2.col6='val6' and t2.col3='Y' and t2.col4='val4' ) "

Alternatiively, if your Hive version does not support NOT IN subquery, you can use LEFT JOIN + filter for the same

hive -e "set hive.execution.engine=mr;set hive.strict.checks.cartesian.product=false;
set hive.mapred.mode=nonstrict;
use db1; 
select t1.col1, t1.col2 
  from tb1 t1
       left join 
        ( select distinct t2.col1 from db2.tb2 t2 
           where t2.col_date='2020-08-15' 
             and t2.col5='val5' 
             and t2.col6='val6' 
             and t2.col3='Y' 
             and t2.col4='val4' 
        ) s on t1.col1 = s.col1 
 where t1.col_date='2020-08-15' and t1.col3='Y' and t1.col4='val4' 
   and s.col1 is null --filter out joined records
"

Upvotes: 2

Related Questions