Difference between hive view and a hive external table When should it be used

using-apache-hive-with-high-performance
  • 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

SELECT * FROM x;

is translated to

SELECT * FROM y;
Then:

DROP TABLE abc;
If the table is defined as external:

CREATE EXTERNAL table abc (….);
  • 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.

Categorized in:

Hive

Tagged in:

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,