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
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");