Apache Hive Temporary Tables and Examples

  • Post author:
  • Post last modified:March 21, 2018
  • Post category:BigData
  • Reading time:3 mins read

A temporary table is a convenient way for an application to automatically manage intermediate data generated during a large or complex query execution. Hive 0.14 onward supports temporary tables. You can use them as a normal table within a user session. In this article, we will check Apache Hive Temporary tables, examples on how to create and usage restrictions.

Apache Hive Temporary Tables

Apache Hive Temporary Tables

Hive temporary tables are local to the user session. You can use temporary table repeatedly within a user session for multiple times. Hive automatically deletes all temporary tables at the end of the Hive session in which they are created.

The data in temporary tables is stored in the user’s scratch directory rather than in the Hive warehouse directory. Usually, the location will be /tmp/hive/<user>/*.

Read:

Hive Create Temporary Tables

You can create Hive temporary tables using the TEMPORARY keyword along with CREATE TABLE. Below is the syntax for various ways to create temporary tables:

CREATE TEMPORARY TABLE temp1(col1 string);
CREATE TEMPORARY TABLE temp2 AS Select * from table_name; 
CREATE TEMPORARY TABLE temp3 LIKE table_name;

Related Reading:

Hive Create Temporary Tables Examples

Below are the examples for creating temporary tables;

hive> create temporary table t3(col1 int, col2 string);
OK
Time taken: 0.061 seconds

hive> create temporary table t2 like t1;
OK
Time taken: 0.281 seconds

hive> create temporary table t3(col1 int, col2 string);
OK
Time taken: 0.061 seconds

hive> create temporary table t4 as select * from t1;
Query ID = admin_20180104145739_0a142fcb-22da-4edc-b1c1-27dc152423a7
Total jobs = 1
…
Table ds_tbl_db.t4 stats: [numFiles=1, numRows=8, totalSize=54, rawDataSize=46]
OK
Time taken: 8.334 seconds

Hive Drop Temporary Tables Examples

You can drop the Hive temporary table using DROP TABLE command. While deleting hive temporary table, no need to use DROP TEMPORARY TABLE:

hive> drop table t3;OKTime taken: 0.060 seconds

Hive Temporary Tables Limitations

Below are the some of the limitations of Hive temporary tables:

  • Partition columns option is not supported on temporary tables.
  • Creating indexes on temporary table is not allowed.
  • A temporary table with the same name as a permanent table will cause all references to that table name to resolve to the temporary table.
  • The user cannot access the permanent table with same name as temporary tables during that session without dropping or renaming the temporary table.
Tags: