Hive Error:Failed to recognize predicate Failed rule: ‘identifier’ column specification

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 :

  1. Do not use reserved keywords in the queries
  2. 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;

Leave a Reply

Your email address will not be published. Required fields are marked *