What is the difference between Internal Table and External Table in Hive ?

[DataFlair Team]

Internal Table :

• In Hive Internal Table can be created using command ‘CREATE TABLE’
• Through internal table data can be stored and queried.
• However, if you drop (delete) internal table, data stored in table will be deleted permanently.
• One can load the data in internal table either through local file system or load the data from HDFS.

If you have load the data from HDFS to Hive and if you delete (‘ DROP TABLE’) then data will be deleted from HDFS also. Same stand true for local file system. So one should be careful for using internal table.

Example:

CREATE TABLE emp(id INT, deparID INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION (path)
DROP TABLE emp

[DataFlair Team]

External Table :
In Hive External Table can be created using command ‘CREATE EXTERNAL TABLE’

Example
CREATE TABLE emp(id INT, deparID INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION (path)

By dropping the external table, data can not be deleted from HDFS so conclusion is, external table is point to the HDFS.

Example
DROP TABLE emp

[Author]

Posts