Hive FAILED: ParseException line 2:0 cannot recognize input near ''macaddress'' 'CHAR' '(' in column specification

Alex Brodov picture Alex Brodov · Aug 24, 2015 · Viewed 55.4k times · Source

I've tried running hive -v -f sqlfile.sql

Here is the content of the file

CREATE TABLE UpStreamParam (
'macaddress' CHAR(50),
'datats' BIGINT,
'cmtstimestamp' BIGINT,
'modulation' INT,
'chnlidx'   INT,
'severity' BIGINT,
'rxpower'  FLOAT,
'sigqnoise' FLOAT,
'noisedeviation'  FLOAT,
'prefecber'  FLOAT,
'postfecber'  FLOAT,
'txpower'  FLOAT,
'txpowerdrop' FLOAT,
'nmter'  FLOAT,
'premtter'  FLOAT,
'postmtter'  FLOAT,
'unerroreds'  BIGINT,
'corrected'  BIGINT,
'uncorrectables'  BIGINT)
STORED AS ORC TBLPROPERTIES ("orc.compress"="SNAPPY","orc.bloom.filters.columns"="macaddress")
PARTITIONED BY ('cmtsid' CHAR(50),' date' INT)
LOCATION '/usr/hive/warehouse/UpStreamParam' ;

And i'm getting the following error:

FAILED: ParseException line 2:0 cannot recognize input near ''macaddress'' 'CHAR' '(' in column specification

Answer

ymonad picture ymonad · Aug 25, 2015

First, the column name must be surrounded by ` (backticks), not ' (single quote).

Therefore you have to replace 'macaddress' to `macaddress`, as well as all other column names.

Second, the order of STORED AS and TBLPROPERTIES and PARTITIONED BY and LOCATION is wrong. The correct order is PARTITIONED BY, STORED AS, LOCATION, TBLPROPERTIES.

See the hive language manual for detail. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable

So the correct code is

CREATE TABLE UpStreamParam (
`macaddress` CHAR(50),
`datats` BIGINT,
`cmtstimestamp` BIGINT,
`modulation` INT,
`chnlidx`   INT,
`severity` BIGINT,
`rxpower`  FLOAT,
`sigqnoise` FLOAT,
`noisedeviation`  FLOAT,
`prefecber`  FLOAT,
`postfecber`  FLOAT,
`txpower`  FLOAT,
`txpowerdrop` FLOAT,
`nmter`  FLOAT,
`premtter`  FLOAT,
`postmtter`  FLOAT,
`unerroreds`  BIGINT,
`corrected`  BIGINT,
`uncorrectables`  BIGINT)
PARTITIONED BY (`cmtsid` CHAR(50), `date` INT)
STORED AS ORC
LOCATION '/usr/hive/warehouse/UpStreamParam'
TBLPROPERTIES ("orc.compress"="SNAPPY","orc.bloom.filters.columns"="macaddress");