2.2.3.2 Auto Parallel Load Syntax

mysql> CALL sys.heatwave_load (db_list,[options]);

db_list: {
     JSON_ARRAY(["schema_name","schema_name"] ...)
}

options: {
     JSON_OBJECT("key","value"[,"key","value"] ...)
          "key","value": {
          ["mode",{"normal"|"dryrun"}]
          ["output",{"normal"|"compact"|"silent"|"help"}]
          ["sql_mode","sql_mode"]
          ["policy",{"disable_unsupported_columns"|"not_disable_unsupported_columns"}]
          ["exclude_list",JSON_ARRAY(schema_name_1, schema_name_2.table_name_1, schema_name_3.table_name_2.column_name_1, ...)]
          ["set_load_parallelism",{TRUE|FALSE}]
          ["auto_enc",JSON_OBJECT("mode",{"off"|"check"}]
          ["external_tables": JSON_ARRAY(db_object [, db_object]... )]
          }
}
  
db_object: {
    "db_name": "name",
    "tables": JSON_ARRAY(table [, table] ...)
}
  
table: {
    "table_name": "name",
    "sampling": TRUE|FALSE,
    "file": JSON_ARRAY(file_section [, file_section]...), 
    "dialect": {dialect_section},
}

db_list specifies the schemas to load. The list is specified as a JSON_ARRAY and requires one or more valid schema names. An empty array is permitted to view the Auto Parallel Load command-line help, see Section 2.2.3.5, “Auto Parallel Load Command-Line Help”.

Use key-value pairs in JSON format to specify options. HeatWave uses the default setting if there is no option specification. Use NULL to specify no arguments.

For syntax examples, see Section 2.2.3.6, “Auto Parallel Load Examples”.

Auto Parallel Load options include:

• mode: Defines the Auto Parallel Load operational mode. Permitted values are:

  • normal: The default. Generates and executes the load script.

  • dryrun: Generates a load script only. Auto Parallel Load executes in dryrun mode automatically if the HeatWave Cluster is not active.

• output: Defines how Auto Parallel Load produces output. Permitted values are:

  • normal: The default. Produces summarized output and sends it to stdout and to the heatwave_autopilot_report table. See Section 6.1, “HeatWave Autopilot Report Table”. Before MySQL 8.0.32, it sends it to the heatwave_load_report table. See Section 2.2.3.7, “The Auto Parallel Load Report Table”.

  • silent: Sends output to the heatwave_autopilot_report table only. See Section 6.1, “HeatWave Autopilot Report Table”. Before MySQL 8.0.32, it sends it to the heatwave_load_report table. See Section 2.2.3.7, “The Auto Parallel Load Report Table”. The silent output type is useful if human-readable output is not required; when the output is consumed by a script, for example. For an example of a stored procedure with an Auto Parallel Load call that uses the silent output type, see Section 2.2.3.6, “Auto Parallel Load Examples”.

  • compact: Produces compact output.

  • help: Displays Auto Parallel Load command-line help. See Section 2.2.3.5, “Auto Parallel Load Command-Line Help”.

• sql_mode: Defines the SQL mode used while loading tables. Auto Parallel Load does not support the MySQL global or session sql_mode variable. To run Auto Parallel Load with a non-oci-default SQL mode configuration, specify the configuration using the Auto Parallel Load sql_mode option as a string value. If no SQL modes are specified, the default OCI SQL mode configuration is used.

  For information about SQL modes, see Server SQL Modes.

• policy: Defines the policy for handling of tables containing columns with unsupported data types. Permitted values are:

  • disable_unsupported_columns: The default. Disable columns with unsupported data types and include the table in the load script. Columns that are explicitly pre-defined as NOT SECONDARY are ignored (they are neither disabled or enabled).

    Auto Parallel Load does not generate statements to disable columns that are explicitly defined as NOT SECONDARY.

  • not_disable_unsupported_columns: Exclude the table from the load script if the table contains a column with an unsupported data type.

    A column with an unsupported data type that is explicitly defined as a NOT SECONDARY column does not cause the table to be excluded. For information about defining columns as NOT SECONDARY, see Section 2.2.2.1, “Excluding Table Columns”.

• exclude_list: Defines a list of schemas, tables, and columns to exclude from the load script. Names must be fully qualified without backticks.

  Auto Parallel Load automatically excludes database objects that cannot be offloaded, according to the default policy setting. These objects need not be specified explicitly in the exclude list. System schemas, non-InnoDB tables, tables that are already loaded in HeatWave, and columns explicitly defined as NOT SECONDARY are automatically excluded.

• set_load_parallelism: Enabled by default. Optimizes load parallelism based on machine-learning models by optimizing the innodb_parallel_read_threads variable setting before loading each table.

• auto_enc: Checks if there is enough memory for string column encoding. Settings include:

  • mode: Defines the auto_enc operational mode. Permitted values are:

    • off: Disables the auto_enc option. No memory checks are performed.

    • check: The default. Checks if there is enough memory on the MySQL node for dictionary-encoded columns and if there is enough root heap memory for variable-length column encoding overhead. Dictionary-encoded columns require memory on the MySQL node for dictionaries. For each loaded table, 4MB of memory (the default heap segment size) must be allocated from the root heap for variable-length column encoding overhead. As of MySQL 8.0.30, the default heap segment size is reduced from 4MB to a default of 64KB per table. If there is not enough memory, Auto Parallel Load executes in dryrun mode and prints a warning about insufficient memory. The auto_enc option runs check mode if it is not specified explicitly and set to off. For more information, see Section 2.2.3.4, “Memory Estimation for String Column Encoding”.

• external_tables: non-InnoDB tables which do not store any data, but refer to data stored externally, such as in Oracle Cloud Infrastructure Object Storage, in file formats such as Avro, CSV or Parquet.

  The external table stores the location of the data, and uses a pre-authenticated request, PAR, or a resource principal. See Section 4.3, “Pre-Authenticated Requests”, and Section 4.4, “Resource Principals”.

  External tables are part of Heatwave Lakehouse, see Chapter 4, HeatWave Lakehouse.