Using CREATE TEMPORARY TABLE statement we can create a temporary table in Hive which is used to store the data temporarily within an active session and the temporary tables get automatically removed when the active session end.
In this article, I will explain what is a temporary table, how to create one, and what are the differences between regular vs temporary table and their limitations.
Table of Contents
- What is Hive Temporary Table
- Temporary Table vs Regular Table
- Create a Temporary Table
- Drop Temporary Table
- Temporary Table Limitations
1. What is Hive Temporary Tables?
Hive temporary tables are similar to temporary tables that exist in SQL Server or any RDBMS databases, As the name suggests these tables are created temporarily within an active session.
Usually, temporary tables are created at the run time to store the intermediate data that are used to perform further data processing. once the processing is done either you can explicitly drop the temporary table or session termination will drop these tables.
Temporary tables don’t store data in the Hive warehouse directory instead the data get stored in the user’s scratch directory /tmp/hive/<user>/* on HDFS.
If you create a temporary table in Hive with the same name as a permanent table that already exists in the database, then within that session any references to that permanent table will resolve to the temporary table, rather than to the permanent table.
2. Hive Temporary Table vs Regular Table
Below are the differences between Hive Temporary table vs Regular permanent table
| Temporary Table | Regular Table (Internal/External) |
|---|---|
| Creates a table within a session | Creates globally |
| Can be accessed only from a session it created | Table could be accessed from different session right after created |
Stores at users scratch directory /tmp/hive/<user>/* | Stores at Hive warehouse directory /user/hive/warehouse |
| Automatically removed when session terminated | Persist until explicitly dropped |
| Doesn’t support partitions | Supports partitions |
| Indexes cannot be created | You can create Indexes |
3. Hive Create a Temporary Table
By using CREATE TEMPORARY TABLE you can create a temporary table in Hive, The TEMPORARY a clause is optional, not using it will not return an error instead it creates a regular managed table.
3.1 Hive Create Temporary Table Examples
3.1.1 Below is a simple example of creating a temporary table.
CREATE TEMPORARY TABLE emp.employee_tmp (
id int,
name string,
age int,
gender string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
DESCRIBE emp.employee_tmp returns the following.
3.1.2 Create a temporary table using LIKE existing table without copying data.
CREATE TEMPORARY TABLE emp.similar_tmp LIKE emp.employee;
3.1.3 Creating a temporary table from the results of the select query.
CREATE TEMPORARY TABLE emp.filter_tmp AS SELECT id,name FROM emp.employee WHERE gender = 'F';
3.1.4 Creating temporary external table
CREATE TEMPORARY external TABLE emp.employee_tmp2(id int);
3.2 Loading Files to a Temporary Table
- Have the data file (data.txt) on HDFS. Note: you can also load the data from
LOCAL DATAwithout uploading it to HDFS. - Use the Hive
LOAD DATAcommand to upload the file.
LOAD DATA INPATH '/user/hive/data/data.txt' INTO TABLE emp.employee_tmp;
3.3 Insert Data into Temporary Table
Like SQL, you can also use INSERT INTO to insert rows into table.
INSERT INTO emp.employee VALUES (7,'scott',23,'M');
INSERT INTO emp.employee VALUES (8,'raman',50,'M');
4. Hive Drop Temporary Table
Use DROP TABLE statement to drop a temporary table. Below is an example of how to drop a temporary table.
DROP TABLE IF NOT EXISTS emp.employee_temp
5. Hive Temporary Table Limitations
As mentioned in the differences, Hive temporary table have few limitation compared with regular tables.
- On temporary tables, you cannot create partitions.
- Indexes are not supported on temporary tables
- If you create a temporary table name same as the permanent table name, you cannot access the permanent table until you drop a temporary table or rename it to a different name.
Related Articles
- How to Connect to Hive Using Beeline
- How to Set Variables in Hive Scripts Examples
- How to connect to Hive from Java & Scala Examples
- What are the Different Types of Tables present in Apache Hive
- How to Create Partitioned Hive Table
- Hive – How to Show All Partitions of a Table?
- Hive Load CSV File into Table
- Hive Load Partitioned Table with Examples
- How to Connect to Hive Using Beeline
- Connect to Hive using JDBC connection
Reference
Hope you like it.
Happy Learning !!
