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

Live instructor-led & Self-paced Online Certification Training Courses (Big Data, Hadoop, Spark) Forums Hive What is the difference between Internal Table and External Table in Hive ?

Viewing 1 reply thread
  • Author
    Posts
    • #6634
      DataFlair Team
      Spectator

      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
    • #6635
      DataFlair Team
      Spectator

      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

Viewing 1 reply thread
  • You must be logged in to reply to this topic.