HIVE报错原因与修改(持续更新)

Error1：Unsupported SubQuery Expression 'xxx': Correlating expression cannot contain unqualified column references.

ANS : 子查询使用不当造成
hive中是支持not in 语法的 ，只是在not in 中不能接一个子查询

user.id not in ('01','02','03') 这种的查询是可以的
user.id not in (select id from null_user) 是不支持

Error2:cannot recognize input near '<EOF>' '<EOF>' '<EOF>' in subquery source

HQL子查询别名问题
https://blog.csdn.net/dxl342/article/details/50896046

HQL的书写

select * from (select * from table) ;

执行此HQL，应该会报错：ql.Driver (SessionState.java:printError(960)) - FAILED: ParseException line 48:52 cannot recognize input near '<EOF>' '<EOF>' '<EOF>' in subquery source
此时，修改HQL为

select * from (select * from table) a

执行成功。

Error 3:In strict mode, if ORDER BY is specified, LIMIT must also be specified.

ANS: 与数据库中 order by 的区别在于在hive.mapred.mode = strict 模式下 必须指定 limit 否则执行会报错。

-- 设定 
set hive.mapred.mode=nonstrict;

Error 4: Warning: Value had a \n character in it.
ANS: 缺了个分号 “；”

Error 5: No partition predicate found for Alias
需要制定分区，减少hive消耗
https://blog.csdn.net/qq_29232943/article/details/79644614

Error6: 找不到变量xxx
SET hivevar: xxx = 20; x
SET hivevar:xxx = 20; √ 注意:号后面不能加空格

Error 7: 需要3个参数，但只给出了2个参数
hive中IF(expr1, expr2, expr3) 必须有三个参数，不能省略最后一个
presto可以省略最后一个，默认为NULL