3.2 Quickstart: AirportDB Analytics

AirportDB Requirements

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 Download MySQL Shell and 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 AirportDB Data

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

Load AirportDB into DB system

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 be C:\\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, replace airport-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 use airportdb with only MySQL 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.
- 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 the airport-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, replace airport-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 use airportdb with only MySQL 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.
- 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.

Load AirportDB into MySQL HeatWave Cluster

To load the airportdb from the DB System into MySQL HeatWave Cluster:

Note

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

Run AirportDB Queries

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:

Note

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

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

Unload AirportDB Tables

airportdb tables can be unloaded from MySQL HeatWave Cluster using the following statements:

Note

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;