HeatWave Release Notes
You can use MySQL HeatWave Auto Unload to unload data automatically.
This topic contains the following sections:
Load structured data using Lakehouse Auto Parallel Load or manually.
Review the syntax for Auto Unload.
Unloading a table from MySQL HeatWave may be necessary to replace an existing table, to reload a table, to free up memory, or simply to remove a table that is no longer used.
Auto Unload facilitates the process of unloading data from MySQL HeatWave by automating many of the steps involved, including:
Excluding schemas and tables that cannot be unloaded.
Removes the secondary engine flag for tables that are to be unloaded.
Unloading data from MySQL HeatWave.
Unloading of automatically loaded tables if there is not enough memory to manually load a table. See Automatic Loading and Unloading of DB System Tables and Partitions.
Auto Unload unloads tables in sequence, ordered by schema and table name. It reports unload-time errors as they are encountered. If there is an error while unloading a table, Auto Unload does not terminate the operation. Auto Unload continues running, and moves on to the next table.
When Auto Unload finishes running, it checks if tables are unloaded and shows a summary with the number of tables that were unloaded and the number of tables that failed to unload.
Data is removed from MySQL HeatWave only. The table contents on the DB System are not affected.
To view command-line help in the terminal, run the following command:
mysql> CALL sys.heatwave_unload(JSON_ARRAY(),JSON_OBJECT("output","help"));
You can use the heatwave_unload command to
unload specific loaded tables in the DB System.
The following examples use the Auto Unload Syntax for MySQL 8.4.0 and Later. If you are on an earlier version than MySQL 8.4.0, use the Auto Unload Syntax for MySQL 8.3.0-u2 and Earlier.
To unload specific tables:
-
Run
heatwave_unloadwith the appropriate parameters to unload the desired tables.mysql> CALL sys.heatwave_unload (input_list,[options]);Replace
input_listand[options]with your own values. Review the syntax for Auto Unload.The following example unloads a single table in
dryrunmode. You can review the output before running the command innormalmode.mysql> CALL sys.heatwave_unload(JSON_ARRAY(JSON_OBJECT("db_name", "data_tables", "tables", JSON_ARRAY("data_table_1"))), JSON_OBJECT("mode", "dryrun")); +-----------------------------------+ | INITIALIZING HEATWAVE AUTO UNLOAD | +-----------------------------------+ | | | Unload Mode: dryrun | | Output Mode: normal | | | +-----------------------------------+ +--------------------------------------------------------------------------------------------------------------+ | HEATWAVE UNLOAD | +--------------------------------------------------------------------------------------------------------------+ | | | | | SCHEMA TABLES TABLES MEMORY GAIN MEMORY GAIN | | NAME TO UNLOAD TO REMAIN (HEATWAVE NODES) (HOST) | | ------- ----------- ----------- ------------------------------- -------------------- | | `data_tables` 1 0 68.24 KiB 0 bytes | | | | TOTAL 1 0 68.24 KiB 0 bytes | | | | Memory consumed by base relations after unload: 7.94 MiB | | | +--------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------+ | UNLOAD SCRIPT GENERATION | +---------------------------------------------------------------------------------------------------------------+ | Dryrun mode only generates the unload script | | Set mode to "normal" in options to unload tables | | Retrieve unload script containing 2 generated DDL commands using the query below: | | SELECT log->>"$.sql" AS "Unload Script" FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id; | | | | | | Caution: Executing the generated unload script will affect the secondary engine flags in the schema | | Caution: Executing the generated unload script will unload tables. Queries on these tables will | | no longer be offloaded to HeatWave. | | | +---------------------------------------------------------------------------------------------------------------+If Auto Unload fails with an error, run the following command to review the report.
mysql> SELECT log FROM sys.heatwave_autopilot_report WHERE type="error";Run the following command to review warnings.
mysql> SELECT log FROM sys.heatwave_autopilot_report WHERE type="warn"; -
Optionally, run the following command to review the unload script:
mysql> SELECT log->>"$.sql" AS "Unload Script" FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id; +-----------------------------------------------------------------+ | Unload Script | +-----------------------------------------------------------------+ | ALTER TABLE `data_tables`.`data_table_1` SECONDARY_UNLOAD; | | ALTER TABLE `data_tables`.`data_table_1` SECONDARY_ENGINE=NULL; | +-----------------------------------------------------------------+ -
If you are satisifed with the output and unload script, reissue the command in
normalmode.The following example runs the command previously run in
dryrunmode again innormalmode. Alternatively, including no options withNULLalso runs the command innormalmode.mysql> CALL sys.heatwave_unload(JSON_ARRAY(JSON_OBJECT("db_name", "data_tables", "tables", JSON_ARRAY("data_table_1"))), JSON_OBJECT("mode", "normal")); +-----------------------------------+ | INITIALIZING HEATWAVE AUTO UNLOAD | +-----------------------------------+ | | | Unload Mode: normal | | Output Mode: normal | | | +-----------------------------------+ +--------------------------------------------------------------------------------------------------------------+ | HEATWAVE UNLOAD | +--------------------------------------------------------------------------------------------------------------+ | | | | | SCHEMA TABLES TABLES MEMORY GAIN MEMORY GAIN | | NAME TO UNLOAD TO REMAIN (HEATWAVE NODES) (HOST) | | ------- ----------- ----------- ------------------------------- -------------------- | | `data_tables` 1 0 68.24 KiB 0 bytes | | | | TOTAL 1 0 68.24 KiB 0 bytes | | | | Memory consumed by base relations after unload: 7.94 MiB | | | +--------------------------------------------------------------------------------------------------------------+ +---------------------------------------------------------------------------------------------------------------+ | EXECUTING UNLOAD | +---------------------------------------------------------------------------------------------------------------+ | HeatWave Unload script generated | | Retrieve unload script containing 2 generated DDL commands using the query below: | | SELECT log->>"$.sql" AS "Unload Script" FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id; | | | | | | Proceeding to unload 1 tables from HeatWave | | | +---------------------------------------------------------------------------------------------------------------+ +----------------------------------------+ | UNLOAD EXECUTE | +----------------------------------------+ | Commands executed successfully: 2 of 2 | | Warnings encountered: 0 | +----------------------------------------+ -
Optionally, run the following commands to retrieve DDL statements.
mysql> SET SESSION group_concat_max_len = 1000000; mysql> SELECT GROUP_CONCAT(log->>"$.sql" SEPARATOR ' ') FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id;
You can use the heatwave_unload_all command
to unload all tables in the DB System. You have the option to
unload all user-loaded and system tables, or only user-loaded
tables.
Run heatwave_unload_all with the
appropriate options to unload all tables.
mysql> CALL sys.heatwave_unload_all ([options]);
Replace [options] with your own values.
Review the Auto Unload Syntax to Unload All Tables.
The following example unloads all tables with default options.
Only user-loaded tables are unloaded since the default value
for only_user_loaded_tables is
true.
mysql> CALL sys.heatwave_unload_all (NULL);-
Unload three specific tables from a single database with default options.
mysql> CALL sys.heatwave_unload(JSON_ARRAY(JSON_OBJECT("db_name", "db_1", "tables", JSON_ARRAY("data_table_1, data_table_2, data_table_3"))), NULL);The tables
data_table_1,data_table_2, anddata_table_3are unloaded from thedb_1database. -
Unload one table from two specific databases in
dryrunmode.mysql> CALL sys.heatwave_unload(JSON_ARRAY( JSON_OBJECT("db_name", "db_1", "tables", JSON_ARRAY("data_table_1")), JSON_OBJECT("db_name", "db_2", "tables", JSON_ARRAY("data_table_2"))), JSON_OBJECT("mode", "dryrun"));The table
data_table_1is unloaded from thedb_1database, and the tabledata_table_2is unloaded from thedb_2database. -
Unload all tables from the databases that have any tables with an
hwprefix.mysql> SELECT JSON_ARRAYAGG(schema_name) FROM performance_schema.rpd_table_id WHERE table_name LIKE 'hw%' INTO @input_list; mysql> CALL sys.heatwave_unload(@input_list, NULL); -
Unload tables that begin with an “hw” prefix from a schema named
db_1.mysql> SELECT JSON_ARRAY(JSON_OBJECT('db_name', 'db_1', 'tables', JSON_ARRAYAGG(table_name))) FROM performance_schema.rpd_table_id WHERE schema_name = 'db_1' AND table_name LIKE 'hw%' INTO @input_list; mysql> CALL sys.heatwave_unload(@input_list, NULL); -
Unload all user and system tables.
mysql> CALL sys.heatwave_unload_all (JSON_OBJECT("only_user_loaded_tables",false)); -
Unload user tables with the
silentoption.mysql> CALL sys.heatwave_unload_all (JSON_OBJECT("output","silent")); -
Unload all user and system tables with the
silentoption.mysql> CALL sys.heatwave_unload_all (JSON_OBJECT("only_user_loaded_tables",false,"output","silent")); -
Call Auto Unload from a stored procedure:
DROP PROCEDURE IF EXISTS auto_unload_wrapper; DELIMITER // CREATE PROCEDURE auto_unload_wrapper() BEGIN -- AUTOMATED INPUT SET @db_list = (SELECT JSON_ARRAYAGG(unique_schemas) FROM (SELECT DISTINCT(schema_name) as unique_schemas FROM performance_schema.rpd_table_id) AS loaded_schemas); SET @exc_list = (SELECT JSON_ARRAYAGG(CONCAT(schema_name,'.',table_name)) FROM performance_schema.rpd_table_id WHERE table_name NOT LIKE 'hw%'); CALL sys.heatwave_unload(@db_list, JSON_OBJECT("output","silent","exclude_list", CAST(@exc_list AS JSON))); -- CUSTOM OUTPUT SELECT log as 'Warnings' FROM sys.heatwave_autopilot_report WHERE type="warn"; SELECT Count(*) AS "Total Unload commands Generated" FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id; END // DELIMITER ; CALL auto_unload_wrapper();
-
Learn more about the following: