Column add or delete table add table

Question

I have an orc table in hive I want to remove a column from this table

ALTER TABLE table_name drop col_name; 

but i get the following exception

An error occurred while executing a bush request: OK FAILED: line ParseException 1:35 msgstr "Unable to execute request 'user_id1', expecting PARTITION near 'drop' in the abbreviation instruction

Can someone help me or give any idea to do this? Notice i am using hive 0.14

Answer 1

You cannot delete a column from a table with the ALTER TABLE table_name drop col_name;

The only way to remove a column is to use the replace command. Suppose I have an emp table with an identifier, name, and department column. I want to remove the emp column column column. Therefore, specify all the columns that you want to be part of the table, instead of the columns. The command below will remove the id column from the emp table.

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

Answer 2

Suppose you have an external table, namely. .employee organization like: (not including TBLPROPERTIES)

 hive> show create table organization.employee; OK CREATE EXTERNAL TABLE `organization.employee`( `employee_id` bigint, `employee_name` string, `updated_by` string, `updated_date` timestamp) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' LOCATION 'hdfs://getnamenode/apps/hive/warehouse/organization.db/employee' 

You want to remove the updated_by, updated_date columns from the table. Follow these steps:

create a replica of the temp table to organize .employee as:

 hive> create table organization.employee_temp as select * from organization.employee; 

Drop the main organization.employee table.

 hive> drop table organization.employee; 

remove basic data from HDFS (you must exit the hive shell)

 [ nameet@ip-80-108-1-111 myfile]$ hadoop fs -rm hdfs://getnamenode/apps/hive/warehouse/organization.db/employee/* 

create a table with deleted columns as needed:

 hive> CREATE EXTERNAL TABLE `organization.employee`( `employee_id` bigint, `employee_name` string) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' LOCATION 'hdfs://getnamenode/apps/hive/warehouse/organization.db/employee' 

insert source records into the source table.

 hive> insert into organization.employee select employee_id, employee_name from organization.employee_temp; 

finally discard the created temp table

 hive> drop table organization.employee_temp; 

Answer 3

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

The above statement can change the table layout, not the data. The solution to this problem is to copy the data to a new table.

 Insert <New Table> Select <selective columns> from <Old Table> 

Answer 4

ALTER TABLE is not yet supported for non-native tables; that is, what you get with CREATE TABLE when the STORED BY clause is specified.

check out https://cwiki.apache.org/confluence/display/Hive/StorageHandlers

Answer 5

There is also a "dumb" way to achieve the final goal, this is creating a new table without unnecessary columns. Using Hive regex matching will make this pretty easy.

Here is what I would do:

 -- make a copy of the old table ALTER TABLE table RENAME TO table_to_dump; -- make the new table without the columns to be deleted CREATE TABLE table AS SELECT '(col_to_remove_1|col_to_remove_2)?+.+' FROM table_to_dump; -- dump the table DROP TABLE table_to_dump; 

If the table in question is not too large, this should work just fine.

Answer 6

Thanks for the answer. Change table tbl_name drop col_name; I tried the above command, but it gives the following MismatchedTokenException (253! = 196)

Answer 7

Even below the request works for me.

 Alter table tbl_name drop col_name