Managed Tables/Internal tables and External Tables

Managed Tables/Internal tables:

     1.     When we create a table in hive, by default Hive will take care of the data

     2.     It means that Hive engine will move the data into its warehouse directory.

CREATE TABLE managed_table (name STRING);

LOAD DATA INPATH '/user/file1.txt' INTO table managed_table ;

So here file1.txt file will be moved to default warehouse directory which we specify in

hive.metastore.warehouse.dir configuration

    3.     Here if we mistakenly or intentional remove the table, then data associated to that table also be removed and we can’t get back that file.

To avoid this data loss we have to go for External table. 

External Table:

    1.     In external table, Hive will refer to the existing file location and if we remove the table then it simply reference will be removed but not data

    2.     To create external table we simply need to place External keyword while creating the data 

hive>   CREATE EXTERNAL TABLE sample (id INT, name STRING) ROW FORMAT

              DELIMITED FIELDS TERMINATED BY ','

             STORED AS TEXTFILE

              LOCATION '/home/user/file1.txt';

    3.     Here /home/user/file1.txt file will not be moved to warehouse directory instead it will be just referenced to the Sample table

   4.     Hive strongly recommends External table