Difference between hive view and a hive external table When should it be used
- A view is a query which is defined as a table.
- CREATE VIEW x AS SELECT * FROM y;
- When the user queries table x the query planner combines the queries and executes a single one.
This is something like
- An external table is something totally different. An external TABLE is a table that when DROPPED will NOT remove the physical data.
- This is a alternative that affects how data is loaded, controlled, and managed.
Use EXTERNAL tables:
- The data is also used outside of Hive.
- For example, the data files are browse and processed by an existing program that doesn’t lock the files.
- Data needs to stay within the underlying location even after a DROP TABLE.
- Pointing multiple patterns at a single data it sets repeats via possible patterns.User can use custom location like ASV.
- Hive does not contain own data and control settings, dirs, etc.In Hive existing table (i.e) not modify.
Use INTERNAL tables:
- Temporary data needs Hive to manage the table and data.