Bookings is a web application that, backed by the power of the MariaDB Connectors and the MariaDB X4 Platform, unleashes the power of smart transactions on hundreds of millions of records with lightning fast query performance without having to add any indexes!
This sample application, no matter which API project you target, will requires the following to be installed/enabled on your machine:
- MariaDB Client, used to connect to MariaDB instances.
- Bash (if you are using Windows 10, you will need to enable the Windows Subsystem for Linux), used to run the data download script
Introduction to MariaDB
MariaDB Platform integrates transactional and analytical products so developers can build modern applications by enriching transactions with real-time analytics and historical data, creating insightful experiences and compelling opportunities for customers – and for businesses, endless ways to monetize data.
To get started using MariaDB locally you can choose one of the following options:
MariaDB Hybrid Transactional-Analytical Processing (HTAP)
MariaDB Platform supports Hybrid Transactional-Analytical Processing (HTAP) through a combination of MariaDB Enterprise Server, MariaDB ColumnStore, and MariaDB MaxScale.
Here's a simple architecture diagram of MariaDB X4 Platform.
For more details on how to manually deploy MariaDB X4 please refer to the official documentation.
SkySQL is the first and only database-as-a-service (DBaaS) to bring the full power of MariaDB Platform to the cloud, including its support for transactional, analytical and hybrid workloads. Built on Kubernetes, and optimized for cloud infrastructure and services, SkySQL combines ease of use and self-service with enterprise reliability and world-class support – everything needed to safely run mission-critical databases in the cloud, and with enterprise governance.
Get the code
Download this code directly or use git (through CLI or a client) to retrieve the code using
$ git clone https://github.com/mariadb-corporation/dev-example-bookings.git
Set up the HTAP database instance
In order to run the Bookings application you will need to have a MariaDB instance to connect to. For more information please check out "Get Started with MariaDB".
Get the data, create the schema, and load the data
This application uses (US domestic) flight data freely available from the Bureau of Transportation on time performance dataset. The [get_flight_data.sh] shell script will be used to download the flight data (between 1990 and 2020) into a folder called data.
Complete the following steps.
- Download the flight data (approx. 180 million records, ~30 GB). Depending on your internet connection this may take some time. However, you can simply modify get_flight_data.sh script to adjust the amount of flight information that is downloaded. Doing so will not disrupt subsequent steps.
- Create the databases and tables load data. Be sure to include your database instance specific information (host url, port number, username, and password)
$ ./create_and_load.sh host_url port user password
Note: Remember to wrap argument values in single quotes if they contain special characters (e.g. !)
By default the create_and_load.shscript has ssl enabled and assumes a MariaDB SkySQL certificate authority chain file exists next to it. Feel free to modify accordingly.
Set up replication
Using MariaDB replication, MariaDB Enterprise Server replicates writes from InnoDB tables to the ColumnStore tables, ensuring that the application can perform analytical processing on current data.
Combining MariaDB replication with MariaDB MaxScale configured as a Binlog Server, MariaDB Enterprise Server can host InnoDB and ColumnStore on the same server.
This application uses replication on a single table called
flights, which exists
travel.flights (InnoDB) and
To set up replication on an HTAP instance you have direct access to add the following replication filter to the MaxScale configuration file (
[replication-filter] type = filter module = binlogfilter match = /[.]flights/ rewrite_src = innodb rewrite_dest = columnstore
For more information on configuring MariaDB HTAP please review the official Enterprise Documentation.
MariaDB SkySQL provides MariaDB Platform for Smart Transactions service, delivering HTAP capabilities. Simply connect to a MariaDB SkySQL (HTAP) instance and execute the following queries.
Create a replication filter.
Confirm a replication filter has been added.
For more information on configuring HTAP replication for SkySQL please check out the official documentation.
Create app data
This application is merely meant for demonstration purposes so you will need to provide relevant data within the following:
Creating searchable flights
You will need to supply future flights that can be booked. The process for this is to first add a flight and then create a ticket for that flight. Consider the following example.
An upcoming flight (option) from LAX to ORD on May 5th, 2020.
INSERT INTO `flights` (`year`, `month`, `day`, `day_of_week`, `fl_date`, `carrier`, `tail_num`, `fl_num`, `origin`, `dest`, `crs_dep_time`, `dep_time`, `dep_delay`, `taxi_out`, `wheels_off`, `wheels_on`, `taxi_in`, `crs_arr_time`, `arr_time`, `arr_delay`, `cancelled`, `cancellation_code`, `diverted`, `crs_elapsed_time`, `actual_elapsed_time`, `air_time`, `distance`, `carrier_delay`, `weather_delay`, `nas_delay`, `security_delay`, `late_aircraft_delay`) VALUES (2020, 5, 5, 5, '2020-05-05', 'DL', NULL, 1280, 'LAX', 'ORD', '0600', '0600', NULL, NULL, NULL, NULL, NULL, '0913', '0913', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO `tickets` (`id`, `fl_date`, `fl_num`, `carrier`, `origin`, `dest`, `price`) VALUES (1, '2020-05-05', 1280, 'DL', 'LAX', 'ORD', 240.00);
Creating upcoming trips
Currently you need to create upcoming trips manually. To do this you will need to have a flight, ticket, and trip record. Consider the following example.
An upcoming trip from ORD to LAX on July 4th, 2020.
INSERT INTO `flights` (`year`, `month`, `day`, `day_of_week`, `fl_date`, `carrier`, `tail_num`, `fl_num`, `origin`, `dest`, `crs_dep_time`, `dep_time`, `dep_delay`, `taxi_out`, `wheels_off`, `wheels_on`, `taxi_in`, `crs_arr_time`, `arr_time`, `arr_delay`, `cancelled`, `cancellation_code`, `diverted`, `crs_elapsed_time`, `actual_elapsed_time`, `air_time`, `distance`, `carrier_delay`, `weather_delay`, `nas_delay`, `security_delay`, `late_aircraft_delay`) VALUES (2020, 7, 4, 2, '2020-07-04', 'DL', NULL, 1170, 'ORD', 'LAX', '1420', '1420', NULL, NULL, NULL, NULL, NULL, '1730', '1730', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO `tickets` (`id`, `fl_date`, `fl_num`, `carrier`, `origin`, `dest`, `price`) VALUES (2, '2020-07-04', 1170, 'DL', 'ORD', 'LAX', 276.00); INSERT INTO `trips` (`id`, `ticket_id`) VALUES (1, 2);
Set up the web application
Anatomy of the app
This application is made of two parts:
- communicates with the API.
- is a React.js project located in the client folder.
Build and run the app
At this point you might be wondering, what are smart transactions?
At their core, smart transactions are the standard transactions that databases have been performing for decades – ultimately powering the online interactions we’ve become accustomed to. The difference with modern applications is the use of real-time analytics before, during and/or after these transactions.
This application uses real-time analytics before a flight is booked. Each flight ticket option contains information calculated from the historical records (average delay, average duration, flight score, etc.) within the
This application also uses real-time analytics after a flight has been booked, and a trip has been created.
This application uses cross-engine queries to maximize the potentials of the MariaDB X4 Platform. Cross-engine querying is the ability to access, via MaxScale, both the transactional and analytics data within a single query.