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.
This question is related to
hadoop
mapreduce
hive
bigdata
amazon-dynamodb
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.
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
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");
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)
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
Source: Stackoverflow.com