2.5.3.4 Auto Unload Examples

• Unload a single schema with default options.

  mysql> CALL sys.heatwave_unload(JSON_ARRAY("tpch"),NULL);

• Unload multiple schemas with default options.

  mysql> CALL sys.heatwave_unload(JSON_ARRAY("tpch","airportdb","employees","sakila"),NULL);

• Unload multiple schemas, excluding specified tables:

  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.

  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.

  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:

  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();