在公司用Hive实现个规则的时候,遇到了要查询某个字段是否在另一张表中,大概情况就是

A表:

idvalue1value2
11000
21011
31021

B表:

value1
100
102
104

我要查询A表中当value2为0的时候直接输出0,为1的时候,判断value1是否在B表的value1中,如果在那么便输出0,不在便输出1,拿到第一反映是:

select 
case
when value2 = 0 then 0
when value2 = 1 then
    case
    when value1 in (select value1 from B) then 0
    else 1
    end
end as value3
from A 

结果Hive就报错了

Error: Error while compiling statement: FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression 'value1': Currently SubQuery expressions are only allowed as Where Clause predicates.

大概意思就是:目前的子查询表达式只允许为Where条件谓词

于是我们就必须将其改为使用left join来解决。

select
case 
when a.value2 = 0 then 0
when a.value2 = 1 then 
    case when
    b.value1 is not null then 0
    else 1
    END
END as value3 
from A a
left join 
B b
on a.value1 = b.value1;

大功告成,对了,使用的Hive版本为1.1.0