hive中的错误(missing EOF at )

来源：互联网发布：python 文本相似度编辑：程序博客网时间：2024/05/16 04:51

select cid as c from daily_conn where cid not in (select c from top_channel_log where v<>'5.8.7' and (objlabel='1' or objlabel is null or objlabel='13557')) and v<>'5.8.7' ;

这条语句执行时出现了下面这条错误

Error: Error while compiling statement: FAILED: SemanticException [Error 10249]: Line 1:38 Unsupported SubQuery Expression 'cid': Correlating expression cannot contain unqualified column references. (state=42000,code=10249)

找了好半天，甚至改写了sql，变成如下形式

select cid from daily_conn where v<>'5.8.7' except (select c from top_channel_log where v<>'5.8.7' and (objlabel=1 or objlabel is null or objlabel=13557)) as b;

Error: Error while compiling statement: FAILED: ParseException line 1:86 missing EOF at 'except' near '20170425' (state=42000,code=40000)
出现了这样的错误，最终改成了

select cid from daily_conn left outer join top_channel_log on daily_conn.cid=top_channel_log.c where daily_conn.v<>'5.8.7' and daily_conn.p_date>=20170423 and daily_conn.p_date<=20170425 and top_channel_log.v<>'5.8.7' and (top_channel_log.objlabel=1 or top_channel_log.objlabel is null or top_channel_log.objlabel=13557);

造成这样的原因是：本人使用的是hive1.3的版本，1.3的版本中支持not in 但是不支持not in的子查询；不支持except的使用。

6 0