MySQL :: MySQL HeatWave User Guide :: 6.2 AirportDB Analytics Quickstart

Prerequisites

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.

Installing airportdb

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. The airportdb 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})
```
Note

The deferTableIndexes: "all" option defers creating secondary indexes until after the table data is loaded, which significantly reduces load times. If you intend to use airportdb with HeatWave, which does not use secondary indexes, you can avoid creating secondary indexes by specifying the loadIndexes: "false" option instead of deferTableIndexes: "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.

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”.

Running airportdb Queries

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 the EXPLAIN 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”.

Additional airportdb 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;

Unloading airportdb Tables

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;