The HeatWave airportdb Quickstart walks you through importing data into the DB System using the MySQL Shell Dump Load utility, loading data into HeatWave using Auto Parallel Load, and running queries.
The following topics are described:
An operational MySQL DB System. See Creating a DB System, in the MySQL Database Service Guide.
An operational HeatWave Cluster. See Adding a HeatWave Cluster, in the MySQL Database Service Guide.
MySQL Shell 8.0.22 or higher. See Connecting to the MySQL DB System with SSH and MySQL Shell.
The installation procedure involves downloading the
airportdb
database to a Compute instance
and importing the data from the Compute instance into the
MySQL DB System using the MySQL Shell Dump Loading utility.
For information about this utility, see
Dump Loading Utility.
To install the airportdb
database:
-
Download the
airportdb
sample database and unpack it. Theairportdb
sample database is provided for download as a compressed tar or Zip archive. The download is approximately 640 MBs in size.wget https://downloads.mysql.com/docs/airport-db.tar.gz tar xvzf airport-db.tar.gz
or
wget https://downloads.mysql.com/docs/airport-db.zip unzip airport-db.zip
Unpacking the compressed tar or Zip archive results in a single directory named
airport-db
, which contains the data files. -
Start MySQL Shell and connect to the MySQL DB System Endpoint. For additional information about connecting to a DB System, see Connecting to the MySQL DB System with SSH and MySQL Shell.
mysqlsh Username@DBSystem_IP_Address_or_Host_Name
-
Load the
airportdb
database into the MySQL DB System using the MySQL Shell Dump Loading Utility.MySQL>JS> util.loadDump("airport-db", {threads: 16, deferTableIndexes: "all", ignoreVersion: true})
NoteThe
deferTableIndexes: "all"
option defers creating secondary indexes until after the table data is loaded, which significantly reduces load times. If you intend to useairportdb
with HeatWave, which does not use secondary indexes, you can avoid creating secondary indexes by specifying theloadIndexes: "false"
option instead ofdeferTableIndexes: "all"
. For more information about MySQL Dump Load options, see Dump Loading Utility.
After the data is imported into the MySQL DB System, you can load the tables into HeatWave. For instructions, see Loading airportdb into HeatWave.
To load the airportdb
from the MySQL DB
System into HeatWave:
-
Start MySQL Shell and connect to the MySQL DB System's Endpoint.
mysqlsh Username@DBSystem_IP_Address_or_Host_Name
-
Change the MySQL Shell execution mode to SQL and run the following Auto Parallel Load command to load the
airportdb
tables into HeatWave.MySQL>JS> \sql MySQL>SQL> CALL sys.heatwave_load(JSON_ARRAY('airportdb'), NULL);
For information about the Auto Parallel Load utility, see Section 2.2.3, “Loading Data Using Auto Parallel Load”.
After airportdb sample database tables are loaded into the HeatWave Cluster, queries that qualify are automatically offloaded to the HeatWave Cluster for accelerated processing. To run queries:
-
Start MySQL Shell and connect to the MySQL DB System's 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
airportdb
database.MySQL>SQL> USE airportdb; Default schema set to `airportdb`.Fetching table and column names from `airportdb` 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 booking.price, count(*) FROM booking WHERE booking.price > 500 GROUP BY booking.price ORDER BY booking.price LIMIT 10; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: booking partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 54081693 filtered: 33.32999801635742 Extra: Using where; Using temporary; Using filesort; Using secondary engine RAPID
If the query can be offloaded, the
Extra
column in theEXPLAIN
output reports"Using secondary engine RAPID"
. -
After verifying that the query can be offloaded, run the query and note the execution time.
MySQL>SQL> SELECT booking.price, count(*) FROM booking WHERE booking.price > 500 GROUP BY booking.price ORDER BY booking.price LIMIT 10; +--------+----------+ | price | count(*) | +--------+----------+ | 500.01 | 860 | | 500.02 | 1207 | | 500.03 | 1135 | | 500.04 | 1010 | | 500.05 | 1016 | | 500.06 | 1039 | | 500.07 | 1002 | | 500.08 | 1095 | | 500.09 | 1117 | | 500.10 | 1106 | +--------+----------+ 10 rows in set (0.0537 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 booking.price, count(*) FROM booking WHERE booking.price > 500 GROUP BY booking.price ORDER BY booking.price LIMIT 10; +--------+----------+ | price | count(*) | +--------+----------+ | 500.01 | 860 | | 500.02 | 1207 | | 500.03 | 1135 | | 500.04 | 1010 | | 500.05 | 1016 | | 500.06 | 1039 | | 500.07 | 1002 | | 500.08 | 1095 | | 500.09 | 1117 | | 500.10 | 1106 | +--------+----------+ 10 rows in set (9.3859 sec)
For other airportdb
sample database queries
that you can run, see
Additional airportdb Queries. For
more information about running queries, see
Section 2.3, “Running Queries”.
This topic provides additional airportdb queries that you can run to test the HeatWave Cluster.
-
Query 1: Number of Tickets > $500.00, Grouped By Price
SELECT booking.price, count(*) FROM booking WHERE booking.price > 500 GROUP BY booking.price ORDER BY booking.price LIMIT 10;
-
Query 2: Average Age of Passengers By Country, Per Airline
SELECT airline.airlinename, AVG(datediff(departure,birthdate)/365.25) as avg_age, count(*) as nb_people FROM booking, flight, airline, passengerdetails WHERE booking.flight_id=flight.flight_id AND airline.airline_id=flight.airline_id AND booking.passenger_id=passengerdetails.passenger_id AND country IN ("SWITZERLAND", "FRANCE", "ITALY") GROUP BY airline.airlinename ORDER BY airline.airlinename, avg_age LIMIT 10;
-
Query 3: Most Tickets Sales by Airline for Departures from US Airports
SELECT airline.airlinename, SUM(booking.price) as price_tickets, count(*) as nb_tickets FROM booking, flight, airline, airport_geo WHERE booking.flight_id=flight.flight_id AND airline.airline_id=flight.airline_id AND flight.from=airport_geo.airport_id AND airport_geo.country = "UNITED STATES" GROUP BY airline.airlinename ORDER BY nb_tickets desc, airline.airlinename LIMIT 10;
airportdb
tables can be unloaded from
HeatWave using the following statements:
USE airportdb;
ALTER TABLE booking SECONDARY_UNLOAD;
ALTER TABLE flight SECONDARY_UNLOAD;
ALTER TABLE flight_log SECONDARY_UNLOAD;
ALTER TABLE airport SECONDARY_UNLOAD;
ALTER TABLE airport_reachable SECONDARY_UNLOAD;
ALTER TABLE airport_geo SECONDARY_UNLOAD;
ALTER TABLE airline SECONDARY_UNLOAD;
ALTER TABLE flightschedule SECONDARY_UNLOAD;
ALTER TABLE airplane SECONDARY_UNLOAD;
ALTER TABLE airplane_type SECONDARY_UNLOAD;
ALTER TABLE employee SECONDARY_UNLOAD;
ALTER TABLE passenger SECONDARY_UNLOAD;
ALTER TABLE passengerdetails SECONDARY_UNLOAD;
ALTER TABLE weatherdata SECONDARY_UNLOAD;