Earlier I was using Hive 0.13 and the query which was running fine in this version of Hive started misbehaving once I upgraded to Hive 1.2.My query was something looked something like below
SELECT count(*) as rows,userId FROM USERTABLE GROUP BY userId;
Below is the snippet of the error.
FailedPredicateException(identifier,{useSQL11ReservedKeywordsForIdentifier()}?)
at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.identifier(HiveParser_IdentifiersParser.java:10937)
at org.apache.hadoop.hive.ql.parse.HiveParser.identifier(HiveParser.java:47741)
at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectItem(HiveParser_SelectClauseParser.java:2954)
at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectList(HiveParser_SelectClauseParser.java:1414)
at org.apache.hadoop.hive.ql.parse.HiveParser_SelectClauseParser.selectClause(HiveParser_SelectClauseParser.java:1141)
at org.apache.hadoop.hive.ql.parse.HiveParser.selectClause(HiveParser.java:47833)
at org.apache.hadoop.hive.ql.parse.HiveParser.selectStatement(HiveParser.java:42337)
at org.apache.hadoop.hive.ql.parse.HiveParser.regularBody(HiveParser.java:42077)
at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpressionBody(HiveParser.java:41260)
at org.apache.hadoop.hive.ql.parse.HiveParser.queryStatementExpression(HiveParser.java:41130)
at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1622)
at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1122)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:202)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:531)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:326)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1273)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1327)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1210)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1200)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:242)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:194)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:409)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:340)
at org.apache.hadoop.hive.cli.CliDriver.processReader(CliDriver.java:442)
at org.apache.hadoop.hive.cli.CliDriver.processFile(CliDriver.java:488)
at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:843)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:795)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:706)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.apache.hadoop.util.RunJar.run(RunJar.java:223)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
FAILED: ParseException line 3:32 Failed to recognize predicate 'rows'. Failed rule: 'rows' in selection target
Upon investigating I realized that “rows” is a reserved keyword in Hive 1.2 and hence cannot be used as column names. Click here for the full list of reserved keywords.
There are 2 ways to resolve this :
- Do not use reserved keywords in the queries
- If the user does not want to change the queries and wants backward compatibility, then add the below before running the queries
set hive.support.sql11.reserved.keywords=false;