Hive Internal Table – With External Data

Have you ever wonder what will happen if you miss the “external” keyword while creating an external table? It will be an internal table at the end.

Let’s check it out.

Here is my sample data. It has three columns namely dno, dname, location.

11, marketing, hyd
12, hr, delhi
13, finance, bang
14, retail, madras
20, db, hyd
21, mg, madras

This file has been placed in /user/cloudera/NewEmpDir as dept.txt

Let us create the external table on top of this data without using “external” keyword.

CREATE TABLE newDept
(Dno INT, Dname String, loc String)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
LOCATION ‘/user/cloudera/NewEmpDir’;

Nothing to be surprised, the table will be created and the data will also be loaded without any errors.

This is an internal table but the data is not stored in Hive. The following things to be observed.

Unlike other tables, this table cannot be visible when you browse the files through Hue, though it is visible in Metastore Manager.
You can query and retrieve the data from the table.
If you drop this table, the source file and directory will also be deleted.