Hive Create Table: Internal Table, Alter, Drop with Examples
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
Dropping table guru_sampleNew:
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
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
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 |