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.

(Dno INT, Dname String, loc String)
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.


One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s