2.2.2 Loading Data Manually

Loading data manually involves the following steps:

1. Excluding columns with unsupported data types. See Excluding Table Columns.

2. Defining RAPID as the secondary engine for tables you want to load. See Defining the Secondary Engine.

3. Optionally, applying string column encoding and data placement workload optimizations. For more information, see Section 2.7, “Workload Optimization”.

4. Loading tables using ALTER TABLE ... SECONDARY_LOAD statements. See Loading Tables.

Excluding Table Columns

Before loading a table into HeatWave, columns with unsupported data types must be excluded; otherwise, the table cannot be loaded. For a list of data types that HeatWave supports, see Section 2.9, “Supported Data Types”.

Optionally, you can also exlude columns that are not relevant to the queries you intend to run. Excluding irrelevant columns is not required but doing so reduces load time and the amount of memory required to store table data.

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

mysql> ALTER TABLE orders MODIFY description BLOB NOT SECONDARY;

mysql> CREATE TABLE orders (id INT, description BLOB NOT SECONDARY);

Note

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

To include a column that was previously excluded, refer to the procedure described in Section 2.4, “Modifying Tables”.

Defining the Secondary Engine

For each table that you want to load into HeatWave, you must define the HeatWave query processing engine (RAPID) as the secondary engine for the table. To define RAPID as the secondary engine, specify the SECONDARY_ENGINE table option in an ALTER TABLE or CREATE TABLE statement:

mysql> ALTER TABLE orders SECONDARY_ENGINE = RAPID;

mysql> CREATE TABLE orders (id INT) SECONDARY_ENGINE = RAPID;

Loading Tables

To load a table into HeatWave, specify the SECONDARY_LOAD option in an ALTER TABLE statement.

mysql> ALTER TABLE orders SECONDARY_LOAD;

The SECONDARY_LOAD clause has these properties:

• It is considered a local operation and is therefore omitted from the binary log.

• Data is read using the READ COMMITTED isolation level.