2.6 Table Load and Query Example

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.

1. The table used in this example:

   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

2. 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;

3. 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';

4. Define RAPID as the secondary engine for the table.

   mysql> ALTER TABLE orders SECONDARY_ENGINE RAPID;

5. Verify the table definition changes:

   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

6. Load the table into HeatWave.

   mysql> ALTER TABLE orders SECONDARY_LOAD;

7. 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;
   *************************** 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)

8. 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)

9. 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)