What is Manage tables and External tables in hive?

By | April 17, 2017

Manage tables in hive:

Manage tables in hive also called internal tables, this table uses hive default warehouse directory to store the table data. (hive.metastore.warehouse.dir)

One disadvantage is, hive doesn’t allow another tools to share the data that is hive has the ownership of data and that’s where the external tables are came into picture.

External tables in hive:

External tables in hive allows data to be shared with other Hadoop ecosystem tools like apache pig.

How to create external table in hive?

To create external table in hive we need to specify the EXTERNAL keyword after CREATE statement.

Hive> CREATE EXTERNAL TABLE IF NOT EXISTS emp ( Name STRING, Salary STRING LOCATION ‘/my/external/hive/emptable’

One important difference between the hive managed table or internal table and external hive table is, if you delete the managed table the table schema and data will be deleted and if you delete the external table only table schema gets deleted because of hive schema on read nature.

How to make new external hive table from existing external hive table?

Hive> CREATE EXTERNAL TABLE IF NOT EXISTS employee1 LIKE employee LOCATION ‘/my/external/hive/emptable2’