question Re: Hive external table with semicolon delimiter in Archives of Support Questions (Read Only)

Hive external table with semicolon delimiter

lkoppenol — Wed, 16 Nov 2016 20:17:45 GMT

EDIT: FIELDS TERMINATED BY '\u0059' WORKS

I am trying to create an external table from a csv file with ; as delimiter. I have tried

FIELDS TERMINATED BY ';'

FIELDS TERMINATED BY '\;'

FIELDS TERMINATED BY '\\;'

Modifying the data is not an option.

This is the hive script:

CREATE EXTERNAL TABLE table1(
	f STRING
)
COMMENT 'table 1'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\;'
STORED AS TEXTFILE
LOCATION '/path/to/hdfs/'
tblproperties ("skip.header.line.count"="1");

I get the following error, in all cases:

Error while compiling statement: FAILED: ParseException line 72:23 mismatched input '<EOF>' expecting StringLiteral near 'BY' in table row format's field separator [ERROR_STATUS]

Re: Hive external table with semicolon delimiter

lkoppenol — Wed, 16 Nov 2016 20:28:45 GMT

Nevermind: this does work

FIELDS TERMINATED BY '\u0059'

Re: Hive external table with semicolon delimiter

sangam_aruns89 — Wed, 16 Nov 2016 20:40:22 GMT

Check this,

FIELDS TERMINATED BY ';'

Re: Hive external table with semicolon delimiter

lkoppenol — Wed, 16 Nov 2016 20:48:33 GMT

This is exactly what is causing the error.... Hive thinks the ; ends the statement, despite the quotes

Re: Hive external table with semicolon delimiter

kirill — Thu, 02 Nov 2017 21:39:10 GMT

According to the latest StackOverflow post, it should be:

FIELDS TERMINATED BY '\u003B'