MySQL HeatWave User Guide

10.1.2 HEATWAVE_UNLOAD

Review the appropriate syntax windows to unload data automatically.

This topic contains the following sections:

Auto Unload Syntax for MySQL 8.4.0 and Later

MySQL 8.4.0 adds an input_list JSON array that replaces the db_list JSON array. This adds an include list to exactly specify the tables to unload, which eliminates the need to include a database and exclude unnecessary tables. The input_list is backwards compatible with db_list. 

mysql> CALL sys.HEATWAVE_UNLOAD (input_list,[options]);

input_list: {
 JSON_ARRAY(input [,input] ...)
}

options: {
 JSON_OBJECT("key","value"[,"key","value"] ...)
      "key","value": {
      ["mode",{"normal"|"dryrun"}]
      ["output",{"normal"|"silent"|"help"}]
      }
}

input: {
 'db_name' | db_object
}

db_object: {
 JSON_OBJECT("key","value"[,"key","value"] ...)
      "key","value": {
      "db_name": "db_name",
      ["tables": JSON_ARRAY(table [, table] ...)]
      ["exclude_tables": JSON_ARRAY(table [, table] ...)]
      }
}

table: {
 'table_name'
}

Auto Unload Syntax to Unload All Tables

The following syntax options allow you to automatically unload all tables. 

mysql> CALL sys.HEATWAVE_UNLOAD_all ([options]);

options: {
 JSON_OBJECT("key","value"[,"key","value"] ...)
      "key","value": {
      ["only_user_loaded_tables",{true|false}]
      ["output",{"normal"|"silent"}]
      }
}

Auto Unload Syntax for MySQL 8.3.0-u2 and Earlier

The following syntax options only apply to MySQL 8.3.0-u2 and earlier. 

mysql> CALL sys.HEATWAVE_UNLOAD (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"|"silent"|"help"}]
      ["exclude_list",JSON_ARRAY(schema_name_1, schema_name_2.table_name_1, ...)]
}

Parameter Descriptions

As of MySQL 8.4.0 use input_list to define what to unload. input_list is a JSON array and requires one or more valid input which can be either a valid schema name or a db_object. An empty array is permitted to view the Auto Unload command-line help. This is backwards compatible with db_list.

Before MySQL 8.4.0, db_list specifies the schemas to unload. The list is a JSON array and requires one or more valid schema names. An empty array is permitted to view the Auto Unload command-line help.

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

For syntax examples, see Auto Unload Examples.

Auto Unload options include:

  • mode: Defines the Auto Unload operational mode. Permitted values are:

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

    • dryrun: Generates an unload script only. Auto Unload executes in dryrun mode automatically if the MySQL HeatWave Cluster is not active.

  • output: Defines how Auto Unload 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 5.8.8, “Autopilot Report Table”.

    • silent: Sends output to the heatwave_autopilot_report table only. See Section 5.8.8, “Autopilot 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 Unload call that uses the silent output type, see Auto Unload Examples.

    • help: Displays Auto Unload command-line help.

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

    Do not use as of MySQL 8.4.0. Use db_object with tables or exclude_tables instead. exclude_list will be deprecated in a future release.

    Auto Unload automatically excludes tables that are loading, unloading or in recovery. This is when the load_status is one of the following: NOLOAD_RPDGSTABSTATE, LOADING_RPDGSTABSTATE, UNLOADING_RPDGSTABSTATE or INRECOVERY_RPDGSTABSTATE.

  • only_user_loaded_tables: Only available to unload all tables. It is enabled by default. The procedure only unloads user tables. If false, the procedure unloads user and system tables.

The db_object is a JSON object literal that includes:

  • db_name: The name of the database to load.

  • Use one or other of the following, but not both. The use of both parameters causes an error.

    • tables: An optional JSON array of table to include in the load.

    • exclude_tables: As of MySQL 8.4.0, an optional JSON array of table to exclude from the load.

  • table: A valid table name.

See Also

MySQL Documentation Library