CODEWITHSUNDEEP
hive
M. T.
M. T.

Reputation: 33

Error while creating table in Hive

I am new to hadoop. I need a help regarding error encountered in Hive while creating a new table. I have gone through this Hive FAILED: ParseException line 2:0 cannot recognize input near ''macaddress'' 'CHAR' '(' in column specification

My question: Is it necessary to write a location of the table in the script? because I am writing table location at starting and I am afraid about writing the location because it should not disturb my rest of the databases by any mulfunction operation.

Here is my query:

CREATE TABLE meta_statistics.tank_items (
   shop_offers_history_before bigint,
   shop_offers_temp bigint,
   videos_distinct_temp bigint,
   deleted_temp bigint,
   t_stamp timestamp )
   CLUSTERED BY (
   tank_items_id)
   INTO 8 BUCKETS
   ROW FORMAT SERDE
   TBLPROPERTIES (transactional=true)
   STORED AS ORC;

The error I am getting is-

ParseException line 1:3 cannot recognize input near 'TBLPROPERTIES' '(' 'transactional'

What would be the other possibilities of errors and how can I remove those?

Upvotes: 0

Views: 7049

Answers (2)

Reena Upadhyay
Reena Upadhyay

Reputation: 2017

There is a syntax error in your create query. Error which you have shared says that hive cannot recognize input near 'TBLPROPERTIES'.

Solution:

As per hive syntax, the key value passed in TBLPROPERTIES should be in double quotes. it should be like this: TBLPROPERTIES ("transactional"="true")

So if I correct your query it will be:

 CREATE TABLE meta_statistics.tank_items ( 
     shop_offers_history_before bigint, 
     shop_offers_temp bigint, 
     videos_distinct_temp bigint, 
     deleted_temp bigint, 
     t_stamp timestamp 
  ) CLUSTERED BY (tank_items_id) INTO 8 BUCKETS 
    ROW FORMAT SERDE TBLPROPERTIES ("transactional"="true") STORED AS ORC;

Execute above query, then if you get any other syntax error them make sure that the order of STORED AS , CLUSTERED BY , TBLPROPERTIES is as per the hive syntax.

Refer this for more details: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable

Upvotes: 1

Nirmal Ram
Nirmal Ram

Reputation: 1210

1) ROW FORMAT SERDE -> you should pass some serde

2) TBLPROPERTIES key value should be in double quotes

3) if you give CLUSTERED BY value should be there in the columns given

replace as follows

CREATE TABLE meta_statistics.tank_items ( shop_offers_history_before bigint, shop_offers_temp bigint, videos_distinct_temp bigint, deleted_temp bigint, t_stamp timestamp ) CLUSTERED BY (shop_offers_history_before) INTO 8 BUCKETS ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'  STORED AS ORC TBLPROPERTIES ("transactional"="true");

hope this helps

Upvotes: 0

Related Questions