2.2.3 Creating HeatWave Temporary Tables

As of MySQL 9.3.0, you can create temporary tables that are stored in the HeatWave Cluster. A temporary table is visible only within the current session, and is dropped automatically when you close the session.

Once created, you can use a HeatWave temporary table as an independent entity. After the HeatWave temporary table is created, you can unload the table that you used to create the temporary table. If you make updates in the original table after the temporary table is created, the updates are not reflected in the temporary table.

Unlike the MySQL temporary table that exists in DB System, HeatWave temporary tables are stored in the HeatWave Cluster by the HeatWave engine and are used for HeatWave processing.

This topic describes how to create a HeatWave temporary table.

Before You Begin

• To create a HeatWave 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.

• Load the original table based on which you want to create a temporary table into HeatWave Cluster.

Creating a Temporary Table

To create a HeatWave temporary table, use the CREATE TEMPORARY TABLE statement with the SECONDARY_ENGINE=RAPID option:

Press CTRL+C to copy
 
mysql>CREATE TEMPORARY TABLE tbl_name 
SECONDARY_ENGINE=rapid 
SELECT …;

For example:

Press CTRL+C to copy
 
mysql>CREATE TEMPORARY TABLE flight_temp
SECONDARY_ENGINE=rapid
SELECT * FROM flight;

The temporary table information is added to the rpd_tables performance schema table, and a new column MATERIALIZATION_QUERY is added to the rpd_table_id performance schema table. The information related to the query used to create the temporary table, is provided in the MATERIALIZATION_QUERY column.

Press CTRL+C to copy
 
mysql>USE performance_schema;
mysql>SELECT TABLE_NAME, MATERIALIZATION_QUERY FROM performance_schema.rpd_table_id;
+-------------------+--------------------------------------------------------------------------------+
| TABLE_NAME        | MATERIALIZATION_QUERY                                                          |
+-------------------+--------------------------------------------------------------------------------+
| flight_temp       | CREATE TEMPORARY TABLE flight_temp
SECONDARY_ENGINE=rapid
SELECT * FROM flight |
+-------------------+--------------------------------------------------------------------------------+