0

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?

Community
  • 1
  • 1
M. T.
  • 33
  • 1
  • 7

2 Answers2

1

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

Naveen
  • 425
  • 12
  • 28
Reena Upadhyay
  • 1,977
  • 20
  • 35
  • Hello Reena, I have created a new database meta_statistics and excecute d corrected query and facing another error SemanticException [Error 10002]: Invalid column reference but I don't understand which column reference the error is and what to do with that? thanks for your time – M. T. Nov 16 '16 at 09:30
  • Yes I got it ! I just replaced cluster column reference with other existing column reference and it works fine now. Thank you very much for your suggestions. – M. T. Nov 16 '16 at 09:35
0

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

Nirmal Ram
  • 1,180
  • 2
  • 9
  • 18
  • Hello Nirmal, I have implemented what you have suggested but shwoing me another type of error- ParseException line 40:20 mismatched input 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' expecting StringLiteral near 'SERDE' in serde format specification – M. T. Nov 15 '16 at 15:14
  • I also have change the postion of "stored as ORC" and "tblproperties" and got error Database does not exist: meta_statistics. So need to create the database or use another database. Thanks a lot. – M. T. Nov 15 '16 at 15:25