CODEWITHSUNDEEP
hadoophivebigdata
Chandrika
Chandrika

Reputation: 33

drop table command in hive

I am trying to drop a table and recreate it in Hive. After dropping the table if I run select query on the table it shows old rows which were in the table before dropping. How is this possible when the table is already dropped ? Why does it retain rows even after table is dropped and recreated ?

hive> select * from abc;

A 30

B 40

hive> drop table abc;

hive> create external table abc ( name string, qty int);

hive> select * from abc;

A 30

B 40

Upvotes: 3

Views: 12279

Answers (2)

Sahil Desai
Sahil Desai

Reputation: 3696

The problem is you are dropping the external table so whenever we dropped this table at that time source file of that table is still exist on that path so whenever we are going to create a new external table with same table name then data can directly extract from source path, for resolving this issue First get path of the table using following command :

hive> describe formatted database_name.table_name;

Then copy entire location which appear in description, for example :

/user/hive/warehouse/database_name.db/table_name

After this use following command to truncate all the data from given table :

hive> dfs -rmr /user/hive/warehouse/database_name.db/table_name;

OR

hive> dfs -rm -r /user/hive/warehouse/database_name.db/table_name;

Then you can wipe it completely using DROP TABLE command.

Upvotes: 8

Littlefoot
Littlefoot

Reputation: 142788

I don't know Hive, but if it is anything like Oracle (which I, kind of, know), then external table points to a file stored on your disk.

Therefore, once you dropped it you couldn't use it (of course). But then you created another EXTERNAL TABLE (see? 5th line in your example) and of course that you were able to select from it once again.

Because, you didn't delete the FILE that is a data source for that external table.

Upvotes: 2

Related Questions