Hive - external table schema got dropped unfortunately, Is there a way to get it ?

[#1]

I have a Hive external table and Unfortunately, the schema of the table got dropped and i want to get back the schema. Is there any ways to get it back ?

I do understand that Hdfs is a file system, However, try to see if there are any possibilities.

[#2]

@PPR Reddy

I dont think there is anyway possible to retrive the table DDL after its dropped. You need to re-create it.

[#3]

@PPR Reddy

When you say schema was dropped, you mean, you dropped the table which means the metadata is gone. Your data still exists, right? You just have to run a create table again. Don't you have this saved somewhere already?

Why not just create the table. If not, then, assuming your metadata table is MySQL, you can use the following method:

https://twindb.com/recover-after-drop-table-innodb_file_per_table-is-off/

What is a little surprising is you have not lost the data so writing a create table statement again even if its 100 lines is not as bad as if your data was in mysql and you had dropped it without a backup. that would have been a much bigger issue.

[#4]

Hi @mqureshi

This question just popped on my mind. I know that table once dropped cant be retrieved. We do have a replication in hadoop. So if the data is deleted then I believe that all the replication will also be deleted. Is there a way to retrieve the data if it's deleted or a managed hive table is dropped? In case of failures or error with the help of replication we will be able to handle it. But whats the possibility of retrieving it when its deleted by human mistake ?

[#5]

@Bala Vignesh N V

Once you delete data, you lose all copies. Purpose of deletion majority of the time is reclaiming capacity. Now, what to do when you accidentally delete data? That's exactly why we have DR clusters or some backups in other places.

As for retrieving single copy, like in this case is to use the process in the link I shared or in the extreme cases when you delete something and I realize it right away, then shutdown everything and use a Forensic software.

[#6]

Replication is for the data-node failure, when Human deletes the data, data will lost where-ever it resides be it on any number of nodes. and this is moved into trash and if needed we can get it back within certain interval time.

[#7]

Thanks and Yes, i can re-write it, however i am looking options if there is any way to get it back and when i drop the table,immediately the commit will occur to the meta-store, which might be causing for not recovering the hive table schema back. Any other alternatives options ?

[#8]

@PPR Reddy At present there is no way of retrieving it. We need to re-create it again. Atleast you have still got your data.. That way I would say you are lucky! But I like your idea of retrieving it. But still hive doesnt have any options of commit. If we have then what you are expecting is possible. Now the only way is to re-create it.