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;