MySQL :: MySQL HeatWave User Guide :: 2.4 Modifying Tables

MySQL HeatWave User Guide / HeatWave / Modifying Tables

2.4 Modifying Tables

Before MySQL 8.0.31, DDL operations are not permitted on tables that are loaded in HeatWave, with SECONDARY_ENGINE = RAPID. In those releases, to alter the definition of a table, you must unload the table and remove the SECONDARY_ENGINE attribute before performing the DDL operation, then reload the table. As of MySQL 8.0.31, you do not need to do this, and can change the table without unloading it.

Before MySQL 8.0.31, follow this procedure to modify a table that is loaded in HeatWave:

Unload the table from HeatWave; for example:
```
mysql> ALTER TABLE orders SECONDARY_UNLOAD;
```
Set the SECONDARY_ENGINE attribute to NULL; for example:
```
mysql> ALTER TABLE orders SECONDARY_ENGINE = NULL;
```
Modify the table. The following examples demonstrate adding a previously excluded column, modifying or removing a column encoding, and modifying or removing a data placement key. Examples are based on the orders table describes in Section 2.6, “Table Load and Query Example”.
- Adding a previously excluded column
  
  Columns are excluded by specifying the NOT SECONDARY column attribute in a CREATE TABLE or ALTER TABLE statement; for example:
```
mysql> ALTER TABLE orders MODIFY `O_COMMENT` varchar(79) NOT NULL NOT SECONDARY;
```
  To include a previously excluded column the next time the table is loaded, modify the column definition to remove the NOT SECONDARY column attribute; for example:
```
mysql> ALTER TABLE orders MODIFY `O_COMMENT` varchar(79) NOT NULL;
```
- Modifying or removing a column encoding
  
  String column encoding is defined by specifying the RAPID_COLUMN=ENCODING={SORTED|VARLEN} keyword string in a column comment; for example:
```
mysql> ALTER TABLE orders MODIFY `O_COMMENT` VARCHAR(79) COLLATE utf8mb4_bin NOT NULL 
          COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
```
  To modify the column encoding, alter the column comment; for example:
```
mysql> ALTER TABLE orders MODIFY `O_COMMENT` VARCHAR(79) COLLATE utf8mb4_bin NOT NULL 
          COMMENT 'RAPID_COLUMN=ENCODING=VARLEN';
```
  To remove a column encoding, remove the RAPID_COLUMN=ENCODING={SORTED|VARLEN} keyword string from the column comment. The next time the table is loaded, the string column encoding defaults to VARLEN encoding, which is the default string column encoding.
  
  The following example removes the column comment entirely, but if there are other column comments that you want to keep, you need only remove the encoding keyword string.
```
mysql> ALTER TABLE orders MODIFY `O_COMMENT` VARCHAR(79)
          COLLATE utf8mb4_bin NOT NULL;
```
- Modifying or removing a data placement key
  
  A data placement key is defined by specifying the RAPID_COLUMN=DATA_PLACEMENT_KEY=N keyword string in a column comment; for example:
```
mysql> ALTER TABLE orders MODIFY `O_ORDERDATE` DATE NOT NULL 
          COMMENT 'RAPID_COLUMN=DATA_PLACEMENT_KEY=1';
```
  To modify a data placement key, modify the data placement keyword string:
```
mysql> ALTER TABLE orders MODIFY `O_ORDERDATE` DATE NOT NULL 
          COMMENT 'RAPID_COLUMN=DATA_PLACEMENT_KEY=2';
```
  To remove a data placement key, modify the column comment to remove the RAPID_COLUMN=DATA_PLACEMENT_KEY=N keyword string. The following example removes the column comment entirely, but if there are other column comments that you want to keep, you need only remove the data placement keyword string.
```
mysql> ALTER TABLE orders MODIFY `O_ORDERDATE` DATE NOT NULL;
```
After making the desired changes to the table, set the SECONDARY_ENGINE attribute back to RAPID; for example:
```
mysql> ALTER TABLE orders SECONDARY_ENGINE = RAPID;
```

Reload the table; for example:

mysql> ALTER TABLE orders SECONDARY_LOAD;

PREV HOME UP NEXT