Support Questions
Find answers, ask questions, and share your expertise

error while creating hive table

Highlighted

error while creating hive table

Hi,

I was following the tutorial (i'm not using the sandbox) and everything was clear, until "FORMAT THE DATA WITH HIVE" I pasted the code :

CREATE TABLE FIREWALL_LOGS(
  time STRING,
  ip STRING,
  country STRING,
  status INT
)
CLUSTERED BY (time) into 25 buckets
STORED AS ORC
TBLPROPERTIES("transactional"="true")
FIELDS TERMINATED BY '|'
LOCATION '/tmp/server-logs';

and got this error :

"org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 10:0 missing EOF at 'FIELDS' near ')'

This seems to be a common error with hive but everything I tried doesn't help. If someone has an idea it would be very usefull ! Thanks in advance

9 REPLIES 9
Highlighted

Re: error while creating hive table

Hi Félicien Catherin

You have missed row format delimited. Please use the below in your DDL.

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '|'

It should work. I hope it helps.

Highlighted

Re: error while creating hive table

Hi @Bala Vignesh N V,

Thanks for your fast answer ! I tried to add ROW format delimited but it didin't change the error

16155-chrome-2017-06-08-14-54-40.png

I also tried to change the order of the keywords (some say that "LOCATION" has to be before "TBLPROPERTIES") but it didn't change anything either

Highlighted

Re: error while creating hive table

Félicien Catherin

Please use the below DDL.

CREATE TABLE FIREWALL_LOGS( time STRING, ip STRING, country STRING, status INT ) CLUSTERED BY (time) into 25 buckets STORED AS ORC ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' TBLPROPERTIES("transactional"="true")

Highlighted

Re: error while creating hive table

CREATE TABLE FIREWALL_LOGS( time STRING, ip STRING, country STRING, status INT ) CLUSTERED BY (time) into 25 buckets STORED AS ORC ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' LOCATION '/tmp/server-logs' TBLPROPERTIES("transactional"="true");

Missed the location in the previous answer.

Highlighted

Re: error while creating hive table

@Bala Vignesh N V Still not, I realy don't understand where this problem come from...

Highlighted

Re: error while creating hive table

Félicien Catherin

Could please share the screen shot with error after executing this code.

CREATE TABLE FIREWALL_LOGS( time STRING, ip STRING, country STRING, status INT ) CLUSTERED BY (time) into 25 buckets STORED AS ORC ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' TBLPROPERTIES("transactional"="true");

Highlighted

Re: error while creating hive table

@Bala Vignesh N V The error is the same as shown in my previous screenshot, here is the error stackTrace if it can help :

errorhive.txt

Highlighted

Re: error while creating hive table

Master Collaborator

There are two problems here with the DDL:

  1. Use of delimiter with ORC. ORC is in itself a format so you don't have to provide a delimiter.
  2. You would define an external table with a location, also ACID tables cannot be external tables, see here.

The proper definition for this table would be:

CREATE TABLE FIREWALL_LOGS(
  time STRING,
  ip STRING,
  country STRING,
  status INT
)
CLUSTERED BY (time) into 25 buckets
STORED AS ORC
TBLPROPERTIES("transactional"="true");
Highlighted

Re: error while creating hive table

New Contributor

@Félicien Catherin The tutorial has typo...

you need to create normal table first using following sytax:

CREATE TABLE FIREWALL_LOGS( time STRING, ip STRING, country STRING, status INT ) 
CLUSTERED BY (time) into 25 buckets 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
LOCATION '/tmp/server-logs' TBLPROPERTIES("transactional"="true");

Once the above table is created you can convert it to ORC

CREATE TABLE FIREWALL AS STORED AS ORC SELECT * FROM FIREWALL_LOGS;