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 not delete column from hive table . But droping a table(if its external) in hive and the recreating table(with column excluded) ,wont delete ur data . so what can u do is(if u dont have table structure) run this command :

show create table database_name.table_name;

Then you can copy it and edit it (with column eliminated).Afterwards you can do as per invoke the shell

Answer 2

As mentioned before, you can't drop table using an alter statement.

Alter - replace is not guaranteed to work in all the cases.

I found the best answer for this here: https://stackoverflow.com/a/48921280/4385453

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.

Answer 4

table details are empid,name,dept,salary ,address. i want remove address column. Just write REPLACE COLUMNS like below query jdbc:hive2://> alter table employee replace columns(empid int, name string,dept string,salary int);

Answer 5

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 6

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