MismatchedTokenException on hive create table query

Question

I'm trying to create a Hive table with the following query:

CREATE TABLE IF NOT EXISTS BXDataSet 
    (ISBN STRING, 
    BookTitle STRING, 
    BookAuthor STRING, 
    YearOfPublication STRING, 
    Publisher STRING, 
    ImageURLS STRING, 
    ImageURLM STRING, 
    ImageURLL STRING) 
COMMENT 'BX-Books Table' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ';' LINE TERMINATED BY '\n'
STORED AS TEXTFILE;

How when I submitted to Hive I got the following exception:

MismatchedTokenException(-1!=301)
    at org.antlr.runtime.BaseRecognizer.recoverFromMismatchedToken(BaseRecognizer.java:617)
    at org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)
    at org.apache.hadoop.hive.ql.parse.HiveParser.tableRowFormatFieldIdentifier(HiveParser.java:31433)
    at org.apache.hadoop.hive.ql.parse.HiveParser.rowFormatDelimited(HiveParser.java:30386)
    at org.apache.hadoop.hive.ql.parse.HiveParser.tableRowFormat(HiveParser.java:30662)
    at org.apache.hadoop.hive.ql.parse.HiveParser.createTableStatement(HiveParser.java:4683)
    at org.apache.hadoop.hive.ql.parse.HiveParser.ddlStatement(HiveParser.java:2144)
    at org.apache.hadoop.hive.ql.parse.HiveParser.execStatement(HiveParser.java:1398)
    at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1036)
    at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:199)
    at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
    at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:404)
    at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:322)
    at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:975)
    at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1040)
    at org.apache.hadoop.hive.ql.Driver.run(Driver.java:911)
    at org.apache.hadoop.hive.ql.Driver.run(Driver.java:901)
    at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:268)
    at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:220)
    at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:423)
    at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:792)
    at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:686)
    at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.apache.hadoop.util.RunJar.main(RunJar.java:212)
FAILED: ParseException line 10:43 mismatched input '<EOF>' expecting StringLiteral near 'BY' in table row format's field separator

Hive is correctly installed, I'm able to run things like show tables. So, what is the issue with this query?

I found this due to a similar error, and it turned out to be caused by single-quotes in my query that were actually left- and right-quotes. — Noah, Sep 17 '15 at 21:36
you copy pasted command from some site where single quote is not actually single quote. Re-write query by typing single quotes '\n' — Manav Patadia, Aug 30 '20 at 19:27

score 16 · Accepted Answer · answered Sep 05 '14 at 11:55

16

You need to specify delimiter's OCT code .

That is for ; OCT code is 073

  CREATE TABLE IF NOT EXISTS BXDataSet 
(ISBN STRING, 
BookTitle STRING, 
BookAuthor STRING, 
YearOfPublication STRING, 
Publisher STRING, 
ImageURLS STRING, 
ImageURLM STRING, 
ImageURLL STRING) 
COMMENT 'BX-Books Table' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\073' LINE TERMINATED BY '\n'
 STORED AS TEXTFILE;

answered Sep 05 '14 at 11:55

K S Nidhin

2,622
2
22
44

I've a similar error for '\n' `ParseException line 11:49 cannot recognize input near 'LINE' 'TERMINATED' 'BY' in serde properties specification` even when I try with the corresponding code '\012'!! – bachr Sep 05 '14 at 12:50
refer https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Create/Drop/TruncateTable – K S Nidhin Sep 05 '14 at 13:10
LINE -> LINES correct one – Rachid Saidane Dec 25 '21 at 22:31

score 4 · Answer 2 · answered Jan 26 '15 at 16:47

4

WRONGLINE TERMINATED BY '\n'

RIGHTLINES TERMINATED BY '\n'

'LINES' NOT 'LINE'

answered Jan 26 '15 at 16:47

Jimmy Zhang

939
1
10
15

score 2 · Answer 3 · answered Dec 23 '15 at 11:08

2

Simple answer is to escape the ";" like this "\;" so your line would be ROW FORMAT DELIMITED FIELDS TERMINATED BY '\;' LINE TERMINATED BY '\n'

answered Dec 23 '15 at 11:08

Paul

29
1

Sanyog Tiwari · Answer 4 · 2017-01-14T05:23:14.793

0

CREATE TABLE IF NOT EXISTS BXDataSetNew (ISBN STRING, BookTitle STRING, BookAuthor STRING, YearOfPublication STRING, Publisher STRING, ImageURLS STRING, ImageURLM STRING, ImageURLL STRING) COMMENT 'BX-Books Table' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE;

edited Jan 14 '17 at 05:23

answered Nov 30 '16 at 20:24

Sanyog Tiwari

33
6

MismatchedTokenException on hive create table query

4 Answers4

Linked