Alter hive table add or drop column

hadoop hive
Aryan Singh picture Aryan Singh · Dec 10, 2015 · Viewed 75.5k times · Source

I have orc table in hive I want to drop column from this table

ALTER TABLE table_name drop  col_name;

but I am getting the following exception

Error occurred executing hive query: OK FAILED: ParseException line 1:35 mismatched input 'user_id1' expecting PARTITION near 'drop' in drop partition statement

Can any one help me or provide any idea to do this? Note, I am using hive 0.14

Answer

Reena Upadhyay picture Reena Upadhyay · Dec 10, 2015

You cannot drop column directly from a table using command ALTER TABLE table_name drop col_name;

The only way to drop column is using replace command. Lets say, I have a table emp with id, name and dept column. I want to drop id column of table emp. So provide all those columns which you want to be the part of table in replace columns clause. Below command will drop id column from emp table. 

 ALTER TABLE emp REPLACE COLUMNS( name string, dept string);

Related questions

How to delete and update a record in Hive

I have installed Hadoop, Hive, Hive JDBC. which are running fine for me. But I still have a problem. How to delete or update a single record using Hive because delete or update command of MySQL is not working in …

Read More
How to know Hive and Hadoop versions from command prompt?

How can I find which Hive version I am using from the command prompt. Below is the details- I am using Putty to connect to hive table and access records in the tables. So what I did is- I opened …

Read More
Hive insert query like SQL

I am new to hive, and want to know if there is anyway to insert data into hive table like we do in SQL. I want to insert my data into hive like INSERT INTO tablename VALUES (value1,value2..) I …

Read More