How to use external table in hive?

Question

Can anyone please explain why and where do we use external tables in hive?

Please explain a scenario to understand easily.

Answer 1

We use external table when our underlying dataset pointed by hive table is shared by many purpose i.e for map reduce job, pig etc and use managed table in hive when our dataset pointed by hive table is used only by hive application.

Actually in hive managed table has full control on dataset i.e in managed table if you will drop the table dataset will also be deleted from hive warehouse(/usr/hive/warehouse) present in HDFS, but in case of external table when you drop the table, dataset are not deleted from hive warehouse in HDFS.

Suppose take an example you have 50 gb data set now if you create multiple copies of dataset for different purpose it will simply take more space so the better option is to use external table so that when you drop the table dataset are not deleted and you can use it further by any other application like by pig or by any other purpose.

Answer 2

As a rule of thumb: use external table if you plan to work with those data not only from Hive but from other frameworks as well. Otherwise make it internal.

Answer 3

The only difference between External and Managed table in Hive is Drop table or Drop partition behavior. For Managed it will drop data as well, for External table the data will remain untouched in the table/partition location.

Use External in most cases. External table allows you to change table definition easily. Also you can create few tables on top of the same location. Use Managed table if the table is temporary/intermediate and data should be deleted to free space.

Managed table can be converted to external and vice-versa using

alter table table_name SET TBLPROPERTIES('EXTERNAL'='TRUE');