CODEWITHSUNDEEP
hadoopmapreducehivebigdataamazon-dynamodb
Jens Roland
Jens Roland

Reputation: 27770

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

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.

Upvotes: 16

Views: 163862

Answers (4)

Dieudonné
Dieudonné

Reputation: 63

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)

Upvotes: 0

naren
naren

Reputation: 15243

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

Upvotes: 3

Sat
Sat

Reputation: 73

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

Upvotes: 1

Jens Roland
Jens Roland

Reputation: 27770

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

Upvotes: 43

Related Questions