Flights App with MariaDB Connector/Node.js

Overview

Flights is a web application for analyzing flight information. The application is compatible with MariaDB Connector/Node.js and the Analytics topologies on MariaDB SkySQL.

Flights demonstrates the power of the Single Node Analytics topology by reading 180 million flight records in real time using lightning fast queries without indexes.

Flights Application

Flights has three components:

  • React.js front-end web application

  • Node.js-based back-end application service

  • Single Node Analytics back-end database service running on MariaDB SkySQL

Prerequisites

Clone the Repository

Flights is available from a MariaDB Corporation repository on GitHub. The repository contains the SQL code to initialize the database, the API code in Node.js and other languages, and the React.js front-end web application that connects to the API.

To download the Flights application, clone the repository from GitHub:

$ git clone https://github.com/mariadb-corporation/dev-example-flights.git

$ cd dev-example-flights

This creates a dev-example-flights directory on your file system.

Provision Database Service

Flights requires a database back-end service using the MariaDB Enterprise ColumnStore storage engine. It is compatible with MariaDB SkySQL Analytics topologies. For the purposes of demonstration, this guide uses the Single Node Analytics topology on MariaDB SkySQL.

For additional information, see "Launch".

Load Flights Data

The repository provides scripts for retrieving data and configuring the database service:

  • gen_flights_data.sh retrieves data from the US Department of Transportation. The data is saved in the data directory as a series of CSV files with the necessary flight information.

    The script downloads three decades of flight data, which requires approximately 30G of disk space. It can take a long time to download onto your system and a long time to upload to MariaDB SkySQL, depending on your internet speeds.

  • create_and_load.sh creates a set of MariaDB ColumnStore tables on the database service, then imports each of the CSV files into the database.

    The script is configured to connect to MariaDB SkySQL and expects to find the skysql_chain.pem Certificate Authority (CA) file in the same directory.

To load data into the Single Node Analytics database service:

  1. Download flight data:

    $ ./get_flight_data.sh
    

    The script downloads the flight data from the US Department of Transportation as a series of CSV files in the data directory.

  2. Copy the skysql_chain.pem file into the dev-example-flights directory:

    $ cp ~/Downloads/skysql_chain.pem .
    
  3. Use the create_and_load.sh with your credentials to initialize the database on your SkySQL database service:

    $ ./create_and_load.sh example.skysql.net 5001 db_user 'db_passwd'
    

    Note that default passwords set for MariaDB SkySQL may require single quotes to prevent the shell from misreading special characters.

Install Back-end Application Service

Flight connects to MariaDB SkySQL using a back-end application service. The repository contains the different versions of the application service, enabling you to test and experiment with different languages and different MariaDB Connectors. These instructions are for Node.js using MariaDB Connector/Node.js.

Navigate to the correct api directory:

$ cd /path/to/dev-example-flights/api/nodejs

Configure the Back-end Application Service

To connect the back-end app to your MariaDB SkySQL database service, you must first configure it to use the appropriate credentials.

  1. Create a .env file in the api/nodejs directory, set the variables to the login credentials for your MariaDB SkySQL service:

    DB_HOST_1 = example.skysql.net
    DB_USER_1 = db_user
    DB_PASS_1 = db_user_passwd
    DB_PORT_1 = 5001
    DB_NAME_1 = orders
    
  2. Edit the db.js file, uncomment the configuration variables for TLS connections:

// SSL (e.g. SkySQL) connections
// * Remember to change the location of "skysql_chain.pem" to wherever you placed it!
// * To use just uncomment the two lines below and the 'ssl' property (and value) within the connection pool configuration

const fs = require("fs");
const serverCert = [fs.readFileSync("skysql_chain.pem", "utf8")];
  1. In setting the serverCert constant, adjust the path to point to the location of the skysql_chain.pem file on your file system:

    const serverCert = [fs.readFileSync("/path/to/skysql_chain.pem", "utf8")];
    
  2. In the mariadb.createPool() configuration, uncomment the ssl entry, so that MariaDB Connector/Node.js uses the CA certificate:

    var pools = [
      mariadb.createPool({
        host: process.env.DB_HOST_1,
        user: process.env.DB_USER_1,
        password: process.env.DB_PASS_1,
        port: process.env.DB_PORT_1,
        database: process.env.DB_NAME_1,
        multipleStatements: true,
        connectionLimit: 5,
        ssl: {
          ca: serverCert
        }
      })
    ];
    

Build the Back-end Application Service

To build the Node.js back-end application service:

$ npm install

Start the Back-end Application Service

Once you have the back-end application service configured and installed, you can start the service:

$ npm start

The back-end application service is now running. Install and start the React.js web application to use Flights.

Install Front-end

Flights uses a React.js front-end web application. The front-end connects to the back-end application service and provides a web interface to the user.

To install and start the front-end:

  1. Navigate to the client directory:

    $ cd /path/to/dev-example-flights/client/
    
  2. Install the web application:

    $ npm install
    
  3. Start the web application:

    $ npm start
    

The React.js web application is now running. You can access it through your web browser at http://localhost:3000.

Live Demonstration

Flights provides a web interface for analyzing flight data. The data comes from United States Department of Transportation and contains 180 million records of flights that were made over the course of thirty years.

Orders Application

Using the interface, enter the origin and destination airports for the flight, then the airline and the range of dates that interest you. Flights queries the data and generates a table listing flight information, and charts noting delays and average minutes delayed.

Flights queries this data from the Single Node Analytics database service, which uses MariaDB Enterprise ColumnStore tables. The columnar table format used by the ColumnStore storage engine enables the application to quickly retrieve the selected rows without using indexes.