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.

score 19 · Answer 1 · answered Jun 29 '15 at 07:25

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

@BalajiBoggaramRamanarayan What is the correct workaround? Please answer the question as you've left later people having no idea how to do what the original question asks. — Alan, Nov 09 '17 at 18:13

score 3 · Answer 2 · answered Jun 25 '15 at 19:45

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.

Can you explain you regex. why you regex end with "+.+" not ".+" — Samuel, May 10 '16 at 05:41

score 1 · Answer 3 · edited Jul 06 '18 at 09:41

1

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.

edited Jul 06 '18 at 09:41

Sergey Bushmanov

23,310
7
53
72

answered Nov 30 '17 at 06:31

Sujit Fulse

73
7

score 0 · Answer 4 · edited Mar 21 '16 at 10:02

0

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

edited Mar 21 '16 at 10:02

Nishu Tayal

20,106
8
49
101

answered Jun 28 '15 at 11:38

Ankit Agrahari

349
9
22

I don't think this is true. Dropping a table does delete the data. – DashK Jul 28 '15 at 23:19
@DashK: i updated answer.On dropping internal table data is deleted but its not the case in external table. – Ankit Agrahari Jul 29 '15 at 12:25
Could you elaborate on what you meant by external table? Is it a table that does not exist in Hive? – DashK Aug 12 '15 at 14:46
@DashK [http://stackoverflow.com/questions/17038414/difference-between-hive-internal-tables-and-external-tables] refer this link – Ankit Agrahari Aug 12 '15 at 16:11

score 0 · Answer 5 · answered Dec 01 '16 at 14:51

0

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);

answered Dec 01 '16 at 14:51

ravi

1

score 0 · Answer 6 · answered Sep 18 '18 at 11:34

0

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

answered Sep 18 '18 at 11:34

Abdulhafeth Sartawi

1,086
1
11
20

Delete column in hive table

6 Answers6

Linked