Hive Temporary Tables are used to store intermediate or Temporary complex query results which we don’t want to store it inside database tables permanently, the Temporary table exists only on the particular session or Terminal window, where it is being created and used, once you close the session/terminal you will not be able to see the temp table in the Database or any where else and we cannot locate the temporary table once we close our session/terminal.
How to Create Temporary Table?
We have to use Temporary keyword with Hive Create Table.
CREATE TEMPORARY TABLE Temp_TableName (ColumnName string);
CREATE TEMPORARY TABLE Temp_Table_Name AS ...
CREATE TEMPORARY TABLE Temp_Table_Name LIKE ...
hive> create temporary table student01 as select * from student;
Query ID = cloudera_20160814204040_cae7d0d6-8e4a-40fc-9792-4aaf19e8e90d
Total jobs = 3
Launching Job 1 out of 3
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_1471212511715_0003, Tracking URL = http://quickstart.cloudera:8088/proxy/application_1471212511715_0003/
Kill Command = /usr/lib/hadoop/bin/hadoop job -kill job_1471212511715_0003
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2016-08-14 20:40:58,228 Stage-1 map = 0%, reduce = 0%
2016-08-14 20:41:07,275 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.27 sec
MapReduce Total cumulative CPU time: 1 seconds 270 msec
Ended Job = job_1471212511715_0003
Stage-4 is selected by condition resolver.
Stage-3 is filtered out by condition resolver.
Stage-5 is filtered out by condition resolver.
Moving data to: hdfs://quickstart.cloudera:8020/user/hive/warehouse/studentdb.db/.hive-staging_hive_2016-08-14_20-40-47_555_4456940049284778571-1/-ext-10001
Moving data to: hdfs://quickstart.cloudera:8020/tmp/hive/cloudera/69a04e68-f8ac-4233-b703-86725be1b143/_tmp_space.db/23200ee3-472b-4fc7-8d55-2476376aaee2
Table studentdb.student01 stats: [numFiles=1, numRows=5, totalSize=154, rawDataSize=149]
MapReduce Jobs Launched:
Stage-Stage-1: Map: 1 Cumulative CPU: 1.27 sec HDFS Read: 3308 HDFS Write: 230 SUCCESS
Total MapReduce CPU Time Spent: 1 seconds 270 msec
Time taken: 20.994 seconds
In the following video i have demonstrated how to create and use the temporary table,
Let us create and see the difference between normal table and temp table in hive.