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.
For example:
- 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
[pastacode lang=”markdown” manual=”SELECT%20*%20FROM%20x%3B%0A%0Ais%20translated%20to%0A%0ASELECT%20*%20FROM%20y%3B%0AThen%3A%0A%0ADROP%20TABLE%20abc%3B%0AIf%20the%20table%20is%20defined%20as%20external%3A%0A%0ACREATE%20EXTERNAL%20table%20abc%20(%E2%80%A6.)%3B” message=”” highlight=”” provider=”manual”/]- 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.
