Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Managed & External table

SOLVED Go to solution
Highlighted

Managed & External table

Explorer

Hi...I am not able to get the difference between managed & external table.

 

I know the difference comes when dropping the table. I don't understand what you mean by the data and metadata is deleted in internal and only metadata is deleted in external tables. Can anyone please explain me, how to check it in backend.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Managed & External table

Guru
A hive table consists the following:

1. metadata info (all table and column definitions and HDFS location)
2. actual HDFS data stored in HDFS

If you delete a managed table, both 1 and 2 will be deleted.

However, if you delete an external table, then only 1 will be deleted, meaning, the table reference will be removed in Hive's backend database (show tables will not return the table and you can't query the table any more). The underlining HDFS file will remain on HDFS path untouched.

To confirm this, you can check where the backend database is stored. If it is mysql, simply login and check the table under TBLS and check if you can query the table (mysql table, not hive table):

SELECT * FROM TBLS WHERE TBL_NAME = "{your_table_name}";

Hope above helps.
1 REPLY 1

Re: Managed & External table

Guru
A hive table consists the following:

1. metadata info (all table and column definitions and HDFS location)
2. actual HDFS data stored in HDFS

If you delete a managed table, both 1 and 2 will be deleted.

However, if you delete an external table, then only 1 will be deleted, meaning, the table reference will be removed in Hive's backend database (show tables will not return the table and you can't query the table any more). The underlining HDFS file will remain on HDFS path untouched.

To confirm this, you can check where the backend database is stored. If it is mysql, simply login and check the table under TBLS and check if you can query the table (mysql table, not hive table):

SELECT * FROM TBLS WHERE TBL_NAME = "{your_table_name}";

Hope above helps.