Documentation Home
MySQL HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 0.9Mb
PDF (A4) - 0.9Mb


MySQL HeatWave User Guide  /  HeatWave Quickstarts  /  AirportDB Analytics Quickstart

6.2 AirportDB Analytics Quickstart

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:

Prerequisites

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:

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

  2. 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
  3. 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:

  1. Start MySQL Shell and connect to the MySQL DB System's Endpoint.

    mysqlsh Username@DBSystem_IP_Address_or_Host_Name
  2. 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:

  1. 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>
  2. Change the MySQL Shell execution mode to SQL:

    MySQL>JS> \sql
  3. 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.
  4. 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".

  5. 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)
  6. 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;