HeatWave Release Notes
The following example demonstrates preparing and loading a table into HeatWave manually and executing a query.
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:
mysql> USE tpch;
mysql> SHOW CREATE TABLE orders\G
*************************** 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
# Exclude columns that you do not want to load, such as columns with unsupported data types
mysql> ALTER TABLE orders MODIFY `O_COMMENT` varchar(79) NOT NULL NOT SECONDARY;
# 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.
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';
# Define RAPID as the secondary engine for the table
mysql> ALTER TABLE orders SECONDARY_ENGINE RAPID;
# Verify the table definition changes
mysql> SHOW CREATE TABLE orders\G
*************************** 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
# Load the table into HeatWave
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.
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\G
*************************** 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
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 HeatWave query execution time
# with 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
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)