Hive ParseException - cannot recognize input near 'end' 'string'

Question

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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

43

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 5, 2013 at 15:47

Jens Roland

27.8k15 gold badges86 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rockie Yang Over a year ago

I meet similar issue when using column _1. change to another name solves the problem. Thanks for the answer.

MoG Over a year ago

"div" was another keyword. Replaced with backticks and started working.. !! Error Message: FAILED: ParseException line 52:16 cannot recognize input near 'div' 'STRING' ',' in column specification

naren · Accepted Answer · 2018-10-04 09:21:24Z

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");

answered Oct 4, 2018 at 9:21

naren

15.3k5 gold badges42 silver badges47 bronze badges

Comments

Sat · Accepted Answer · 2016-10-03 23:03:54Z

1

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

answered Oct 3, 2016 at 23:03

Sat

738 bronze badges

1 Comment

Masih Over a year ago

Do you know how we can partition on /20161003 ?

Dieudonné · Accepted Answer · 2020-02-05 19:40:04Z

0

I solved this issue by doing like that:

insert into my_table(my_field_0, ..., my_field_n) values(my_value_0, ..., my_value_n)

answered Feb 5, 2020 at 19:40

Dieudonné

631 silver badge6 bronze badges

Collectives™ on Stack Overflow

Hive ParseException - cannot recognize input near 'end' 'string'

4 Answers 4

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

2 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

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

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related