what is an internal and external table in Hive

Question

Accepted Answer

Internal tables store data within Hive's warehouse directory while external tables store data outside of it.

Internal tables are managed by Hive and are deleted when the table is dropped
External tables are not managed by Hive and data is not deleted when the table is dropped
Internal tables are faster for querying as data is stored within Hive's warehouse directory
External tables are useful for sharing data between different systems
Example: CREATE TABLE my_table (col1 INT, col2 STRING) STORED AS PARQUET; (internal table)
Example: CREATE EXTERNAL TABLE my_table (col1 INT, col2 STRING) LOCATION '/path/to/data'; (external table)

Accepted Answer

Hi, An internal table is manged by hive (the partitions will be created in the default hive location, unless specified) An external table will not have complete control over the partitions. The partitions can be deleted and added as needed and these partitions can be dropped from the table. EXTERNAL keyword needs to be specified while creating an external table.

Accepted Answer

managed/default table is the internal table(Derby) while the external table is formed in any RDBMS(MySQL).On deletion internal lose everything including data while the external table loses only metadata.

what is an internal and external table in Hive

Top TCS Data Engineer interview questions & answers

Popular interview questions of Data Engineer

Top HR questions asked in TCS Data Engineer