MySQL :: HeatWave User Guide :: 2.7 Table Load and Query Example

HeatWave User Guide / HeatWave MySQL / Table Load and Query Example

2.7 Table Load and Query Example

The following example demonstrates preparing a table, manually loading the table into HeatWave, executing a query, unloading the table, and then preventing Auto Parallel Load from loading the table.

It is assumed that HeatWave is enabled and the MySQL DB System has a schema named tpch with a table named orders. The example shows how to exclude a table column, encode string columns, define RAPID as the secondary engine, and load the table. The example also shows how to use EXPLAIN to verify that the query can be offloaded, and how to force query execution on the MySQL DB System to compare MySQL DB System and HeatWave query execution times.

The table used in this example:

Press CTRL+C to copy
 
mysql>USE tpch;
mysql>SHOW CREATE TABLE orders;
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `O_ORDERKEY` int NOT NULL,
  `O_CUSTKEY` int NOT NULL,
  `O_ORDERSTATUS` char(1) COLLATE utf8mb4_bin NOT NULL,
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) COLLATE utf8mb4_bin NOT NULL,
  `O_CLERK` char(15) COLLATE utf8mb4_bin NOT NULL,
  `O_SHIPPRIORITY` int NOT NULL,
  `O_COMMENT` varchar(79) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`O_ORDERKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Encode individual string columns as necessary. For example, apply dictionary encoding to string columns with a low number of distinct values. Variable-length encoding is the default if no encoding is specified.

Press CTRL+C to copy
 
mysql>ALTER TABLE orders MODIFY `O_ORDERSTATUS` char(1) NOT NULL 
          COMMENT 'RAPID_COLUMN=ENCODING=SORTED';

mysql>ALTER TABLE orders MODIFY `O_ORDERPRIORITY` char(15) NOT NULL 
          COMMENT 'RAPID_COLUMN=ENCODING=SORTED';

mysql>ALTER TABLE orders MODIFY `O_CLERK` char(15) NOT NULL 
          COMMENT 'RAPID_COLUMN=ENCODING=SORTED';

Verify the table definition changes:

Press CTRL+C to copy
 
mysql>SHOW CREATE TABLE orders;
*************************** 1. row ***************************
       Table: orders
Create Table: CREATE TABLE `orders` (
  `O_ORDERKEY` int NOT NULL,
  `O_CUSTKEY` int NOT NULL,
  `O_ORDERSTATUS` char(1) COLLATE utf8mb4_bin NOT NULL COMMENT 
    'RAPID_COLUMN=ENCODING=SORTED',
  `O_TOTALPRICE` decimal(15,2) NOT NULL,
  `O_ORDERDATE` date NOT NULL,
  `O_ORDERPRIORITY` char(15) COLLATE utf8mb4_bin NOT NULL COMMENT 
    'RAPID_COLUMN=ENCODING=SORTED',
  `O_CLERK` char(15) COLLATE utf8mb4_bin NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED',
  `O_SHIPPRIORITY` int NOT NULL,
  `O_COMMENT` varchar(79) COLLATE utf8mb4_bin NOT NULL NOT SECONDARY,
  PRIMARY KEY (`O_ORDERKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin SECONDARY_ENGINE=RAPID

As of MySQL 9.1.0, if the secondary engine is set to something other than RAPID, set the secondary engine to RAPID:
```
Press CTRL+C to copy
 
mysql>ALTER TABLE orders SECONDARY_ENGINE = RAPID;
```

Load the table into HeatWave.

Press CTRL+C to copy
 
mysql>ALTER TABLE orders SECONDARY_LOAD;

Use EXPLAIN to determine if a query on the orders table can be offloaded. Using secondary engine RAPID in the Extra column indicates that the query can be offloaded.

Press CTRL+C to copy
 
mysql>EXPLAIN SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT
          FROM orders 
          WHERE O_ORDERDATE >= DATE '1994-03-01' 
          GROUP BY O_ORDERPRIORITY 
          ORDER BY O_ORDERPRIORITY;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 33.33
        Extra: Using where; Using temporary; Using filesort; Using secondary 
        engine RAPID
1 row in set, 1 warning (0.00 sec)

Execute the query and note the execution time:

Press CTRL+C to copy
 
mysql>SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT
          FROM orders 
          WHERE O_ORDERDATE >= DATE '1994-03-01'
          GROUP BY O_ORDERPRIORITY 
          ORDER BY O_ORDERPRIORITY;
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     2017573 |
| 2-HIGH          |     2015859 |
| 3-MEDIUM        |     2013174 |
| 4-NOT SPECIFIED |     2014476 |
| 5-LOW           |     2013674 |
+-----------------+-------------+
5 rows in set (0.04 sec)

To compare the HeatWave query execution time with the MySQL DB System execution time, disable use_secondary_engine and run the query again to see how long it takes to run on the MySQL DB System

Press CTRL+C to copy
 
mysql>SET SESSION use_secondary_engine=OFF;

mysql>SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT 
          FROM orders 
          WHERE O_ORDERDATE >= DATE '1994-03-01' 
          GROUP BY O_ORDERPRIORITY 
          ORDER BY O_ORDERPRIORITY;
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     2017573 |
| 2-HIGH          |     2015859 |
| 3-MEDIUM        |     2013174 |
| 4-NOT SPECIFIED |     2014476 |
| 5-LOW           |     2013674 |
+-----------------+-------------+
5 rows in set (8.91 sec)

Unload the table from HeatWave:

Press CTRL+C to copy
 
mysql>ALTER TABLE orders SECONDARY_UNLOAD;

As of MySQL 9.1.0, after unloading a table, and to prevent Auto Parallel Load from automatically loading the table into HeatWave, set the secondary engine to something other than RAPID:
```
Press CTRL+C to copy
 
mysql>ALTER TABLE orders SECONDARY_ENGINE = NOT_AUTO;
```

PREV HOME UP NEXT