This topic describes how to query tpch
data
in the HeatWave Cluster. After tables are loaded into the
HeatWave Cluster, queries that qualify are automatically offloaded
to the HeatWave Cluster for accelerated processing. To run queries:
If you are using MySQL HeatWave on AWS, you can also run queries from the Query Editor in the MySQL HeatWave Console. See Running Queries, in the MySQL HeatWave on AWS Service Guide.
-
Start MySQL Shell and connect to the MySQL DB System endpoint:
$> mysqlsh Username@DBSystem_IP_Address_or_Host_Name
MySQL Shell opens in JavaScript execution mode by default.
MySQL>JS>
-
Change the MySQL Shell execution mode to SQL:
MySQL>JS> \sql
-
Change to the
tpch
database:MySQL>SQL> USE tpch; Default schema set to `tpch`.Fetching table and column names from `tpch` for auto-completion... Press ^C to stop.
-
Before running a query, use
EXPLAIN
to verify that the query can be offloaded to the HeatWave Cluster. For example:MySQL>SQL> EXPLAIN SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_shipdate >= date '1994-01-01'; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: lineitem partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 56834662 filtered: 33.33 Extra: Using where; Using secondary engine RAPID
If the query can be offloaded, the
Extra
column in theEXPLAIN
output reportsUsing secondary engine RAPID
. -
After verifying that the query can be offloaded, run the query and note the execution time.
MySQL>SQL> SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_shipdate >= date '1994-01-01'; +------------------+ | revenue | +------------------+ | 82752894454.9036 | +------------------+ 1 row in set (0.04 sec)
-
To compare the HeatWave execution time with MySQL DB System execution time, disable the
use_secondary_engine
variable to see how long it takes to run the same query on the MySQL DB System. For example:MySQL>SQL> SET SESSION use_secondary_engine=OFF; MySQL>SQL> SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem WHERE l_shipdate >= date '1994-01-01'; +------------------+ | revenue | +------------------+ | 82752894454.9036 | +------------------+ 1 row in set (24.20 sec)
For other
tpch
sample database queries that you can run, see Section 7.1.6, “Additional tpch Queries”. For more information about running queries, refer to Section 2.3, “Running Queries”.