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


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