Search code examples
hivehiveqlderbyhadoop2

Apache Hive: FAILED: ParseException line 4:1 character '' not supported here

I'm trying to create a new table in Apache Hive, which loads the data from a CSV file. I wrote this script

CREATE SCHEMA IF NOT EXISTS practica2;

CREATE EXTERNAL TABLE IF NOT EXISTS practica2.station_data 
(IDPROVINCIA string,
SPROVINCIA string,
IDESTACION string, 
SESTACION string,
FECHA string,
DIA string,
TEMPMAX string,
HORMINTEMPMAX string,
TEMPMIN string,
HORMINTEMPMIN string,
TEMPMEDIA string,
HUMEDADMAX string,
HUMEDADMIN string,
HUMEDADMEDIA string,
VELVIENTO string,
DIRVIENTO string, 
RADIACION string, 
PRECIPITACION string) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ';'
STORED AS TEXTFILE
LOCATION 'hdfs://0.0.0.0:9000/user/hive';

But, when I execute the script, I get this error

FAILED: ParseException line 4:1 character '' not supported here

I tested with another alternative, with the same results:

CREATE SCHEMA IF NOT EXISTS practica2;

CREATE TABLE IF NOT EXISTS practica2.station_data 
(IDPROVINCIA string,
SPROVINCIA string,
IDESTACION string, 
SESTACION string,
FECHA string,
DIA string,
TEMPMAX string,
HORMINTEMPMAX string,
TEMPMIN string,
HORMINTEMPMIN string,
TEMPMEDIA string,
HUMEDADMAX string,
HUMEDADMIN string,
HUMEDADMEDIA string,
VELVIENTO string,
DIRVIENTO string, 
RADIACION string, 
PRECIPITACION string) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ';';

LOAD DATA LOCAL INPATH './RIA_exportacion_datos_diarios_Huelva_20140206.csv' INTO TABLE practica2.station_data;

The full report is this:

almu@debian:~/Practicas_BigData/Practica2/Hive$ hive -f practica2.hql 
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/local/hive/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/local/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]

Logging initialized using configuration in jar:file:/usr/local/hive/lib/hive-common-2.3.7.jar!/hive-log4j2.properties Async: true
OK
Time taken: 3.894 seconds
FAILED: ParseException line 4:1 character '' not supported here

When I create a table from the Hive command line, It is created without problems. But, when I execute the script, It always fails.

Where is the error?

Update: About the suggestions about if the ';' could be the cause of the error, I replaced this line like this:

FIELDS TERMINATED BY ',';

But the error continues

Solution

  • Try removing the new lines?

    CREATE SCHEMA IF NOT EXISTS practica2;

CREATE EXTERNAL TABLE IF NOT EXISTS practica2.station_data (IDPROVINCIA string, SPROVINCIA string, IDESTACION string, SESTACION string, FECHA string, DIA string, TEMPMAX string, HORMINTEMPMAX string, TEMPMIN string, HORMINTEMPMIN string, TEMPMEDIA string, HUMEDADMAX string, HUMEDADMIN string, HUMEDADMEDIA string, VELVIENTO string, DIRVIENTO string, RADIACION string, PRECIPITACION string) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ';'
STORED AS TEXTFILE
LOCATION 'hdfs://0.0.0.0:9000/user/hive';

    Or maybe you have full-width space in your sql file. See this post. If you want to get rid of full-width spaces, type the sql file manually, and avoid copy/paste from somewhere else.