drop table command in hive

Question

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 

Answer 1

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.