Hive ParseException - cannot recognize input near

问题:

I am getting the following error when trying to create a Hive table from an existing DynamoDB table:

NoViableAltException(88@[])
at org.apache.hadoop.hive.ql.parse.HiveParser_IdentifiersParser.identifier(HiveParser_IdentifiersParser.java:9123)
at org.apache.hadoop.hive.ql.parse.HiveParser.identifier(HiveParser.java:30750)
...more stack trace...
FAILED: ParseException line 1:77 cannot recognize input near 'end' 'string' ',' in column specification

The query looks like this (simplified to protect the innocent):

CREATE EXTERNAL TABLE moveProjects (cid string, end string, category string)
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "Projects",
    "dynamodb.column.mapping" = "cid:cid,end:end,category:category");

Basically, I am trying to create a Hive table containing the contents of the Projects DynamoDB table, but the create statement is throwing a parse error from Hive / Hadoop.

回答1:

The issue isn't actually a syntax error, the Hive ParseException is just caused by a reserved keyword in Hive (in this case, end).

The solution: use backticks around the offending column name:

CREATE EXTERNAL TABLE moveProjects (cid string, `end` string, category string)
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "Projects",
    "dynamodb.column.mapping" = "cid:cid,end:end,category:category");

With the added backticks around end, the query works as expected.

Reserved words in Amazon Hive (as of February 2013):

IF, HAVING, WHERE, SELECT, UNIQUEJOIN, JOIN, ON, TRANSFORM, MAP, REDUCE, TABLESAMPLE, CAST, FUNCTION, EXTENDED, CASE, WHEN, THEN, ELSE, END, DATABASE, CROSS

Source: This Hive ticket from the Facebook Phabricator tracker

回答2:

I was using /Date=20161003 in the folder path while doing an insert overwrite and it was failing. I changed it to /Dt=20161003 and it worked

回答3:

You can always escape the reserved keyword if you still want to make your query work!!

Just replace end with `end`

Here is the list of reserved keywords https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

CREATE EXTERNAL TABLE moveProjects (cid string, `end` string, category string)
STORED BY 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler'
TBLPROPERTIES ("dynamodb.table.name" = "Projects",
    "dynamodb.column.mapping" = "cid:cid,end:end,category:category");