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
The problem could be because of the the Hive version, where the
CHAR
data type is supported from the Hive version 0.13.If you are using lower versions, then please try using the
string/varchar
data type.Please refer, https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-Char
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
andTBLPROPERTIES
andPARTITIONED BY
andLOCATION
is wrong. The correct order isPARTITIONED 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