在公司用Hive实现个规则的时候,遇到了要查询某个字段是否在另一张表中,大概情况就是
A表:
id | value1 | value2 |
---|---|---|
1 | 100 | 0 |
2 | 101 | 1 |
3 | 102 | 1 |
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