Documentation Home
HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.7Mb
PDF (A4) - 1.7Mb


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

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)