Hive Create Table: Internal Table, Alter, Drop with Examples

David TaylorByDavid Taylor Hours Updated

Table Operations such as Creation, Altering, and Dropping tables in Hive can be observed in this tutorial.

In the below screenshot, we are creating a table with columns and altering the table name.

1. Creating table guru_sample with two column names such as “empid” and “empname”

2. Displaying tables present in guru99 database

3. Guru_sample displaying under tables

4. Altering table “guru_sample” as “guru_sampleNew”

5. Again, when you execute “show” command, it will display the new name Guru_sampleNew

Data operations in Hive

Dropping table guru_sampleNew:

Data operations in Hive

Table types and its Usage:

Coming to Tables it’s just like the way that we create in traditional Relational Databases. The functionalities such as filtering, joins can be performed on the tables.

Hive deals with two types of table structures like Internal and External tables depending on the loading and design of schema in Hive.

Internal tables

  • Internal Table is tightly coupled in nature. In this type of table, first we have to create table and load the data.
  • We can call this one as data on schema.
  • By dropping this table, both data and schema will be removed.
  • The stored location of this table will be at /user/hive/warehouse.

When to Choose Internal Table?

  • If the processing data available in local file system
  • If we want Hive to manage the complete lifecycle of data including the deletion

Sample code Snippet for Internal Table

1. To create the internal table

 Hive>CREATE TABLE guruhive_internaltable (id INT,Name STRING);
	 Row format delimited 
	 Fields terminated by '\t';

2. Load the data into internal table

    Hive>LOAD DATA INPATH '/user/guru99hive/data.txt' INTO table guruhive_internaltable;

3. Display the content of the table

    Hive>select * from guruhive_internaltable;

4. To drop the internal table

    Hive>DROP TABLE guruhive_internaltable;

If you dropped the guruhive_internaltable, including its metadata and its data will be deleted from Hive.

From the following screenshot, we can observe the output

Data operations in Hive

In above code and from screenshot we do following things,

  • Create the internal table
  • Load the data into internal table
  • Display the content of the table
  • To drop the internal table

External tables

  • External Table is loosely coupled in nature. Data will be available in HDFS.The table is going to create on HDFS data.
  • In other way, we can say like its creating schema on data.
  • At the time of dropping the table it drops only schema, the data will be still available in HDFS as before.
  • External tables provide an option to create multiple schemas for the data stored in HDFS instead of deleting the data every time whenever schema updates

When to Choose External Table?

  • If processing data available in HDFS
  • Useful when the files are being used outside of Hive

Sample code Snippet for External Table

1. Create External table

Hive>CREATE EXTERNAL TABLE guruhive_external(id INT,Name STRING)
	 Row format delimited
	 Fields terminated by '\t'
	 LOCATION '/user/guru99hive/guruhive_external;

2. If we are not specifying the location at the time of table creation, we can load the data manually

    Hive>LOAD DATA INPATH '/user/guru99hive/data.txt' INTO TABLE guruhive_external;

3. Display the content of the table

  Hive>select * from guruhive_external;

4. To drop the internal table

  Hive>DROP TABLE guruhive_external;

From the following screenshot, we can observe the output

Data operations in Hive

In above code, we do following things

  • Create the External table
  • Load the data into External table
  • Display the content of the table
  • Dropping external table

Difference between Internal Vs External tables

Feature Internal External
Schema Data on Schema Schema on Data
Storage Location /usr/hive/warehouse HDFS location
Data availability Within local file system Within HDFS

You Might Like: