2.2.3.6 Auto Parallel Load Examples

• Load a single schema with default options.

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

• Load multiple schemas with default options.

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

• Load multiple schemas with the not_disable_unsupported_columns policy, which causes tables with unsupported columns to be excluded from the load operation. Unsupported columns are those with unsupported data types.

  mysql> CALL sys.heatwave_load(JSON_ARRAY("tpch","airportdb","employees","sakila"), 
            JSON_OBJECT("policy","not_disable_unsupported_columns"));

• Load multiple schemas, excluding specified tables and a particular column:

  mysql> CALL sys.heatwave_load(JSON_ARRAY("tpch","airportdb"), 
            JSON_OBJECT("exclude_list",JSON_ARRAY("tpch.orders","airportdb.employee.salary")));

• Load 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 information_schema.tables 
                   WHERE table_schema = 'schema_customer_1' 
                   AND table_name NOT LIKE 'hw%');
  mysql> CALL sys.heatwave_load(JSON_ARRAY('schema_customer_1'), @exc_list);

• Load all schemas with tables that start with an “hw” prefix.

  mysql> SET @db_list = (SELECT json_arrayagg(schema_name) FROM information_schema.schemata);
  mysql> SET @exc_list = (SELECT JSON_OBJECT('exclude_list',
            JSON_ARRAYAGG(CONCAT(table_schema,'.',table_name))) 
            FROM information_schema.tables 
            WHERE table_schema NOT IN
            ('mysql','information_schema', 'performance_schema','sys') 
            AND table_name NOT LIKE 'hw%');
  mysql> CALL sys.heatwave_load(@db_list, @exc_list);

  You can check db_list and exc_list using SELECT JSON_PRETTY(@db_list); and SELECT JSON_PRETTY(@exc_list);

• Call Auto Parallel Load from a stored procedure:

  DROP PROCEDURE IF EXISTS auto_load_wrapper;
  DELIMITER //
  CREATE PROCEDURE auto_load_wrapper()
  BEGIN
    -- AUTOMATED INPUT
    SET @db_list = (SELECT JSON_ARRAYAGG(schema_name) FROM information_schema.schemata);
    SET @exc_list = (SELECT JSON_ARRAYAGG(CONCAT(table_schema,'.',table_name)) 
    FROM information_schema.tables WHERE table_schema = "db0");
  
    CALL sys.heatwave_load(@db_list, JSON_OBJECT("output","silent","exclude_list", 
    CAST(@exc_list AS JSON)));
  
    -- CUSTOM OUTPUT
    SELECT log as 'Unsupported objects' FROM sys.heatwave_autopilot_report WHERE type="warn" 
    AND stage="VERIFICATION" and log like "%Unsupported%";
    SELECT Count(*) AS "Total Load commands Generated" 
    FROM sys.heatwave_autopilot_report WHERE type = "sql" ORDER BY id;
  
  END //
  DELIMITER ;
  
  CALL auto_load_wrapper();

  Before MySQL 8.0.32, use heatwave_load_report.