HPL/SQL - Procedural SQL on Hadoop, NoSQL and RDBMS

This is an old revision of the document!

---

====== Native and Managed Temporary Tables - PL/HQL ====== PL/HQL provides you with two options to work with temporary tables: native and managed. Use the [[configuration#plhqltemptables|plhql.temp.tables]] option to define how to handle temporary tables, the default value is **native**. ===== Native Temporary Tables ===== When native temporary tables are used PL/HQL relies on the underlying database to manage temporary tables. PL/HQL converts DECLARE TEMPORARY TABLE statement to CREATE TEMPORARY TABLE in Hive. Note that Hive supports temporary tables since version 0.14 only. ===== Managed Temporary Tables ===== When [[configuration#plhqltemptables|plhql.temp.tables]] is set to **managed**, PL/HQL creates a regular table in the database and automatically drops it at the end of the session. Note that the schema name and location are defined by [[configuration#plhqltemptablesschema|plhql.temp.tables.schema]] and [[configuration#plhqltemptableslocation|plhql.temp.tables.location]] options, respectively. Also UUID is added to the table name to prevent name conflicts between multiple sessions. For example, if you declare temporary table //temp1//, PL/HQL will actually create something like //temp1_3fc162e0590f4e17ae141385cc0e8447//. **Example**: Create a managed temporary table and use it in other SQL statements: <code language=sql> SET plhql.temp.tables = managed; DECLARE TEMPORARY TABLE temp1 ( c1 INT, c2 STRING ); INSERT INTO temp1 SELECT 1, 'A' FROM dual; SELECT * FROM temp1; </code> See also: * [[create-local-temporary-table|CREATE LOCAL TEMPORARY TABLE]] * [[create-volatile-table|CREATE VOLATILE TABLE]] * [[declare-temporary-table|DECLARE TEMPORARY TABLE]] ~~NOTOC~~