Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create temporary table in Hive?

Tags:

Does Hive support temporary tables? I can't find it in the apache docs.

like image 871
CMaury Avatar asked Mar 21 '11 23:03

CMaury


People also ask

Can we create temporary table in Hive?

Temporary table data persists only during the current Apache Hive session. Hive drops the table at the end of the session. If you use the name of a permanent table to create the temporary table, the permanent table is inaccessible during the session unless you drop or rename the temporary table.

What is the use of temporary table in Hive?

Apache Hive A temporary table is a convenient way for an application to automatically manage intermediate data generated during a complex query.

How do I view a temporary table in Hive?

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>/*.

How do you create a temporary database table?

To create a temporary table, you must have the CREATE TEMPORARY TABLES privilege. After a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as DROP TABLE , INSERT , UPDATE , or SELECT .


1 Answers

As of Hive 0.14.0 Thanks to @hwrdprkns for commenting. As of Hive 0.14.0 there is support for temporary tables https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-TemporaryTables
Link to the JIRA: https://issues.apache.org/jira/browse/HIVE-7090

The syntax is CREATE TEMPORARY TABLE TABLE_NAME_HERE (key string, value string)

Pre Hive 0.14.0
I don't think Hive has temporary tables the same as something like SQL Server does. You can do something like temporary tables with Hive though.

If you create a table with the schema your temporary table needs, then do a query populating the table before you run the query needing the data, it will act like a temporary table.

The steps would be:

  1. Create Table
  2. Fill Table: INSERT OVERWRITE TABLE temptbl <select_query>
  3. Run query

When you run your query you can use temptbl like any other table. The INSERT OVERWRITE will overwrite all data in the table so it will only be populated with data for that run. The data persists, so if you re-use the table without re-populating it, you will be using the data from whatever the last run was.

This can definitely run into issues if the same table will be needed at the same time but for different data...

From what I've been able to find, this is the only solution to a 'temporary' table in Hive right now.

like image 150
QuinnG Avatar answered Oct 11 '22 00:10

QuinnG