4.2.6.1 Exclude Table Columns

Before loading a table into MySQL HeatWave, exclude the columns with unsupported data types. Otherwise, the table cannot be loaded. For a list of data types that MySQL HeatWave supports, see Section 4.2.1, “Supported Data Types for MySQL HeatWave”.

With MySQL version 8.2.0 or higher, this step is not required as the guided load feature automatically excludes all unsupported columns when you load the table.

Optionally, exclude columns that are not relevant to the intended queries. Excluding irrelevant columns is not required but doing so reduces load time and the amount of memory required to store table data.

Before You Begin

• Import Data into MySQL HeatWave DB System.

Excluding Table Columns

The NOT SECONDARY column attribute prevents a column from being loaded into MySQL HeatWave when executing a table load operation. To exclude a column, specify the NOT SECONDARY column attribute in an ALTER TABLE or CREATE TABLE statement, as shown below.

mysql> ALTER TABLE tbl_name MODIFY column_name datatype NOT SECONDARY;

mysql> CREATE TABLE tbl_name (
column_1 datatype, 
column_2 datatype,
...
column_n datatype NOT SECONDARY);

The following example creates a table orders with 2 columns order_ID (INT datatype) and Description (BLOB datatype)

mysql> CREATE TABLE orders (
order_ID  INT, 
Description BLOB);

BLOB is not supported by MySQL HeatWave and hence you can exclude this column while loading to MySQL HeatWave, using the following statement :

mysql> ALTER TABLE orders MODIFY Description BLOB NOT SECONDARY;

Note

If a query accesses a column defined with the NOT SECONDARY attribute, the query is executed on the DB System by default.

To include the Description column that was previously excluded, remove the NOT SECONDARY column attribute using the ALTER TABLE statement and reload the table.

What's Next

• Learn how to define secondary engine.