6

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

Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
Alex Brodov
  • 3,365
  • 18
  • 43
  • 66

1 Answers1

12

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");
Ahmed Ashour
  • 5,179
  • 10
  • 35
  • 56
ymonad
  • 11,710
  • 1
  • 38
  • 49
  • Thanks it's working. Is there any other way accessing the tables rather than using hive cli each time? Let's say that i want to integrate into my code so each time i would need to execute `hive -e "my query"` – Alex Brodov Aug 26 '15 at 16:16
  • @user3502786 you can search the web for keyword such as 'hive client' or 'hive webui' https://cwiki.apache.org/confluence/display/Hive/HiveClient – ymonad Aug 27 '15 at 00:48
  • without added quotes(') it will work and char(50) is not working instead of them we can use string. – Shyam Gupta Feb 16 '17 at 11:47
  • just fyi: date is a reserved keyword in hql. so we need to wrap it with `` if using in column names. – Akhilesh Aug 20 '22 at 17:58