error while creating hive table

[#1]

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

[#2]

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.

[#3]

Hi @Bala Vignesh N V,

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

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

[#4]

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

[#5]

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.

[#6]

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

[#7]

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

[#8]

@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

[#9]

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

[#10]

@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;