hive報錯Unsupported SubQuery Expression Invalid subquery. Subquery in UDAF is not allowed.

Error while compiling statement: FAILED: SemanticException org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSubquerySemanticException: Unsupported SubQuery Expression Invalid subquery. Subquery in UDAF is not allowed.

在hive中in、not in不支援子查詢

例子

select DISTINCT userid FROM TABLE_A AS a WHERE a.dt >= '20200209' AND a.userid 
NOT IN  (SELECT DISTINCT userid FROM TABLE_B AS b WHERE b.dt >= '20200209');      

　　

改寫方法1 ：用left join連接配接

select DISTINCT a.userid FROM TABLE_A AS  a left JOIN  TABLE_B AS b
on a.userid=b.userid
WHERE b.userid is NULL;      

使用left jion示例一下，左聯以後，加上b表userid為空的條件，就可以實作例子的需求

改寫方法2: 用exists 

select DISTINCT a.userid FROM TABLE_A AS a WHERE a.dt >= '20200209' 
AND NOT EXISTS  
(SELECT DISTINCT b.userid FROM TABLE_B AS b WHERE b.dt >= '20200209' AND a.userid = b.userid);