Documentation Home
HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 2.2Mb
PDF (A4) - 2.2Mb


2.5.3.4 Auto Unload Examples

  • Unload a single schema with default options.

    Press CTRL+C to copy
    mysql> CALL sys.heatwave_unload(JSON_ARRAY("tpch"),NULL);
  • Unload multiple schemas with default options.

    Press CTRL+C to copy
    mysql> CALL sys.heatwave_unload(JSON_ARRAY("tpch","airportdb","employees","sakila"),NULL);
  • Unload multiple schemas, excluding specified tables:

    Press CTRL+C to copy
    mysql> CALL sys.heatwave_unload(JSON_ARRAY("tpch","airportdb"), JSON_OBJECT("exclude_list",JSON_ARRAY("tpch.orders")));
  • Unload tables that begin with an hw prefix from a schema named schema_customer_1.

    Press CTRL+C to copy
    mysql> SET @exc_list = (SELECT JSON_OBJECT('exclude_list', JSON_ARRAYAGG(CONCAT(table_schema,'.',table_name))) FROM performance_schema.rpd_table_id WHERE schema_name = 'schema_customer_1' AND table_name NOT LIKE 'hw%'); mysql> CALL sys.heatwave_unload(JSON_ARRAY('schema_customer_1'), @exc_list);
  • Unload all schemas with tables that start with an hw prefix.

    Press CTRL+C to copy
    mysql> 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); mysql> SET @exc_list = (SELECT JSON_OBJECT('exclude_list', JSON_ARRAYAGG(CONCAT(table_schema,'.',table_name))) FROM performance_schema.rpd_table_id WHERE table_name NOT LIKE 'hw%'); mysql> CALL sys.heatwave_unload(@db_list, @exc_list);

    Check db_list and exc_list with SELECT JSON_PRETTY(@db_list); and SELECT JSON_PRETTY(@exc_list);

  • Call Auto Unload from a stored procedure:

    Press CTRL+C to copy
    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();