4.5.1 View Secondary Engine Definitions

To identify tables in the DB System that are defined with a secondary engine, query the CREATE_OPTIONS column in the INFORMATION_SCHEMA.TABLES table. If the CREATE_OPTIONS column contains both SECONDARY_ENGINE=RAPID and SECONDARY_LOAD=1, the table is offloaded and loaded into the MySQL HeatWave Cluster

Before You Begin

• Ensure that the enable_secondary_engine_statistics system variables is set to ON.

• Check if you have the SELECT and INSERT privileges for the table that you want to analyze. If not, ask your admin user to grant you the required privileges.

Viewing table Offload Status

Use the following SELECT TABLE to verify the offload status of tables in a specific schema:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE CREATE_OPTIONS LIKE '%SECONDARY_ENGINE%'
AND TABLE_SCHEMA LIKE 'your_schema_name';

If the table is not loaded to the DB System, you can load tables with SECONDARY_ENGINE="RAPID" into MySQL HeatWave either explicitly using ALTER TABLE with SECONDARY_LOAD or load using Lakehouse Auto Parallel Load using the MySQL HeatWave Autopilot subsystem.

mysql> CREATE TABLE airline_info (
    airline_id INT,
    flight_id INT,
    PRIMARY KEY (airline_id)
) SECONDARY_ENGINE = RAPID;
Query OK, 0 rows affected (0.025 sec)  
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%SECONDARY_ENGINE%' AND TABLE_SCHEMA LIKE 'airportdb';

+--------------+-------------------+---------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME        | CREATE_OPTIONS                              |
+--------------+-------------------+---------------------------------------------+
| airportdb    | airline_info      | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="0" |
| ...          |                   |                                             |
+--------------+-------------------+---------------------------------------------+
mysql> ALTER TABLE airline_info SECONDARY_LOAD;
Query OK, 0 rows affected (3.9098 sec)
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE CREATE_OPTIONS LIKE '%SECONDARY_ENGINE%' AND TABLE_SCHEMA LIKE 'airportdb';

+--------------+-------------------+---------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME        | CREATE_OPTIONS                              |
+--------------+-------------------+---------------------------------------------+
| airportdb    | airline_info      | SECONDARY_ENGINE="RAPID" SECONDARY_LOAD="1" |
| ...          |                   |                                             |
+--------------+-------------------+---------------------------------------------+

You can also view create options for an individual table using SHOW CREATE TABLE.

The INFORMATION_SCHEMA.PARTITIONS view includes a SECONDARY_LOAD column that indicates whether a partition has been loaded. The possible values are:

• 1: The partition is loaded.

• 0: The partition is not loaded.

• NULL: The table does not have partitions.

A successfully completed load or unload operation updates INFORMATION_SCHEMA.PARTITIONS . If an operation loads or unloads the whole table, and does not specify individual partitions, then it updates all the SECONDARY_LOAD columns when successfully completed.

MySQL HeatWave persists the partition information. During a restart, only the previously loaded partitions are loaded.

What's Next

• Learn to view the excluded columns.