Delete column in hive table

Question

I am working with hive version 0.9 and I need delete columns of a hive table. I have searched in several manuals of hive commands but I only I have found commands to version 0.14. Is possible to delete a column of a hive table in hive version 0.9? What is the command? Thanks.

Answer 1

We can’t simply drop a table column from a hive table using the below statement like sql.

ALTER TABLE tbl_name drop column column_name ---- it will not work.

So there is a shortcut to drop columns from a hive table.

Let’s say we have a hive table.

From this table I want to drop the column Dob. You can use the ALTER TABLE REPLACE statement to drop a column.

ALTER TABLE test_tbl REPLACE COLUMNS(ID STRING,NAME STRING,AGE STRING);   you have to give the column names which you want to keep in the table

Answer 2

There isn't a drop column or delete column in Hive.

A SELECT statement can take regex-based column specification in Hive releases prior to 0.13.0, or in 0.13.0 and later releases if the configuration property hive.support.quoted.identifiers is set to none.

That being said you could create a new table or view using the following:

drop table if       exists database.table_name;
create table if not exists database.table_name as
select `(column_to_remove_1|...|column_to_remove_N)?+.+`
    from database.some_table
    where 
    ...
;

This will create a table that has all the columns from some_table except the columns named column_to_remove_1, ... , to column_to_remove_N. You can also choose to create a view instead.

Answer 3

ALTER TABLE table_name REPLACE COLUMNS ( c1 int, c2 String);

NOTE: eliminate column from column list. It will keep matched columns and removed unmentioned columns from table schema.