The MySQL HeatWave airportdb Quickstart shows how to import data into the DB System using the MySQL Shell Dump Load utility, loading data into MySQL HeatWave Cluster using Auto Parallel Load, and running queries.
For an online workshop that demonstrates MySQL HeatWave using the
airportdb
sample database, see
Turbocharge
Business Insights with MySQL HeatWave Service and MySQL HeatWave.
This quickstart contains the following sections:
Review the requirements to setup and use MySQL HeatWave.
Confirm you have the latest version of MySQL Shell installed. To learn more about downloading and installing MySQL Shell, see Installing MySQL Shell.
-
You will need to connect to the DB System in one of the following ways:
Through a VPN
Through a compute instance
To learn more about connecting to your DB system, see Networking.
Download the airportdb
sample database. The
airportdb
sample database is provided for
download as a compressed tar or Zip archive.
The download is approximately 640 MBs in size.
To run these commands, make sure you have the
wget
utility installed. Alternatively, you
can download the files by pasting the URL in a browser window.
$> wget https://downloads.mysql.com/docs/airport-db.tar.gz
or
$> wget https://downloads.mysql.com/docs/airport-db.zip
Depending on how you connect to your DB system, follow the
appropriate steps to load the airportdb
database into the DB system. To learn more about connecting to
your DB system, see
Networking.
To load the data, you will need to use the MySQL Shell Dump Loading utility. See MySQL Shell Dump Loading Utility.
VPN Connection
If you connect to the DB system through a VPN connection, you
can load airportdb
locally from your
computer.
To load the airportdb
database through a VPN
connection:
-
Unpack the compressed tar or Zip archive you downloaded previously. It creates a single directory named
airport-db
, which contains the data files.$> tar xvzf airport-db.tar.gz
or
$> unzip airport-db.zip
Copy the file path to the unpacked
airport-db
folder. For example, on Windows it might beC:\\Temp\\airport-db
, or on Mac it might be/Users/johnsmith/airport-db
.-
From a terminal window, start MySQL Shell and connect to the DB System IP address. You can review the IP address (private IP address) and other connection details in the Connections tab for the DB system. For additional information about connecting to a DB System, see Connecting to a DB System in the MySQL HeatWave on OCI Service Guide.
$> mysqlsh Username@DBSystem_IP_Address_or_Host_Name
-
Enable
js
mode.mysql-sql> \js
-
Load the
airportdb
database into the DB system using MySQL Shell Dump Loading Utility. In the command, replaceairport-db
with the file path you copied in the previous step.mysql-js> util.loadDump("file path to the unpacked airport-db folder", {threads: 16, deferTableIndexes: "all", ignoreVersion: true})
The command uses the following options:
threads
: Specifies the number of parallel threads to use to upload chunks of data to the target MySQL instance.deferTableIndexes
: This option is set to "all", so it defers the creation of secondary indexes until after the table data is loaded. This can reduce loading time. If you intend to useairportdb
with only MySQL 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.ignoreVersion
: This option is enabled, which imports the dump even if the major version number of the MySQL instance from which the data was dumped is non-consecutive to the major version number of the MySQL instance to which the data will be uploaded.
Compute Instance Connection
If you connect to the DB system through a compute instance, you
first need to upload the airportdb
database
to the compute instance before running the MySQL Shell
Dump Loading Utility.
To load the airportdb
database through a
compute instance connection:
In the terminal window, go to the local directory containing the
airportdb
files.-
Upload the contents of the
airport-db
folder to the appropriate directory of the compute instance.$> scp -v -i ssh-key.key airport-db.zip opc@ComputeInstancePublicIP:/home/opc/
Replace the following:
ssh-key.key
: The full file path to the SSH key file (.key) for the compute instance.airport-db.zip
: The full file path to theairport-db.zip
file on your device.opc@ComputeInstancePublicIP
: The appropriate username and public IP for the compute instance./home/opc/
: The appropriate file path to upload the airport-db folder to in the compute instance.
-
Once the upload successfully completes, SSH into the Compute instance using the public IP address of the Compute instance.
$> ssh -i ssh-key.key opc@computeInstancePublicIP
Replace the following:
ssh-key.key
: The full file path to the SSH key file (.key) for the compute instance.opc@ComputeInstancePublicIP
: The appropriate username and public IP for the compute instance.
-
Unpack the compressed tar or Zip archive you downloaded previously. It creates a single directory named
airport-db
, which contains the data files.$> tar xvzf airport-db.tar.gz
or
$> unzip airport-db.zip
-
From a terminal window, start MySQL Shell and connect to the DB System IP address. You can review the IP address (private IP address) and other connection details in the Connections tab for the DB system. For additional information about connecting to a DB System, see Connecting to a DB System in the MySQL HeatWave on OCI Service Guide.
$> mysqlsh Username@DBSystem_IP_Address_or_Host_Name
-
Enable
js
mode.mysql-sql> \js
-
Load the
airportdb
database into the DB system using MySQL Shell Dump Loading Utility. If needed, replaceairport-db
to the appropriate file path in the compute instance.mysql-js> util.loadDump("airport-db", {threads: 16, deferTableIndexes: "all", ignoreVersion: true})
The command uses the following options:
threads
: Specifies the number of parallel threads to use to upload chunks of data to the target MySQL instance.deferTableIndexes
: This option is set to "all", so it defers the creation of secondary indexes until after the table data is loaded. This can reduce loading time. If you intend to useairportdb
with only MySQL 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.ignoreVersion
: This option is enabled, which imports the dump even if the major version number of the MySQL instance from which the data was dumped is non-consecutive to the major version number of the MySQL instance to which the data will be uploaded.
To load the airportdb
from the DB System into
MySQL HeatWave Cluster:
For MySQL HeatWave on AWS, load data into MySQL HeatWave Cluster using the MySQL HeatWave Console. See Manage Data in MySQL HeatWave with Workspaces in the MySQL HeatWave on AWS Service Guide.
-
From a terminal window, start MySQL Shell and connect to the DB System IP address. You can review the IP address (private IP address) and other connection details in the Connections tab for the DB system.
$> mysqlsh Username@DBSystem_IP_Address_or_Host_Name
-
Run the following Auto Parallel Load command to load the
airportdb
tables into MySQL HeatWave Cluster.mysql-sql> CALL sys.heatwave_load(JSON_ARRAY('airportdb'), NULL);
For information about the Auto Parallel Load utility, see Section 4.2.5, “Load Data Using Auto Parallel Load”.
After airportdb sample database tables are loaded into the MySQL HeatWave Cluster, queries that qualify are automatically offloaded to the MySQL HeatWave Cluster for accelerated processing. To run queries:
For MySQL HeatWave on AWS, run queries from the Query Editor in the MySQL HeatWave Console. See Running Queries in the MySQL HeatWave on AWS Service Guide.
-
From a terminal window, start MySQL Shell and connect to the DB System IP address. You can review the IP address (private IP address) and other connection details in the Connections tab for the DB system.
$> mysqlsh Username@DBSystem_IP_Address_or_Host_Name
-
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 MySQL 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\G *************************** 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 reportsUsing 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 MySQL HeatWave Cluster execution time with DB System execution time, disable the
use_secondary_engine
variable to see how long it takes to run the same query on the 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 5.4, “Run Queries”.
This topic provides additional airportdb queries that you can run to test the MySQL HeatWave Cluster.
-
Query 1: Number of Tickets > $500.00, Grouped By Price
mysql-sql> 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
mysql-sql> 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
mysql-sql> 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
MySQL HeatWave Cluster using the following statements:
For MySQL HeatWave on AWS, unload data from MySQL HeatWave Cluster using the MySQL HeatWave Console. See Manage Data in MySQL HeatWave with Workspaces in the MySQL HeatWave on AWS Service Guide.
mysql-sql> 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;