Overview
Real-time data processing, visualizations, and dashboards are essential in logistics and aviation. They boost supply chain performance, optimize movement, and improve efficiency. In business aviation, especially in flight operations, real-time analytics enhances efficiency, sustainability, and customer focus, leading to better decision-making and cost savings. It also ensures safe operations, optimizes airspace use, and helps air traffic controllers. Overall, real-time data processing improves the aviation industry by enhancing customer experiences and business operations.
In this blog, we set up a real-time flight tracking system using RisingWave, Upstash, and Metabase. We leverage the Aviationstack API to get real-time flight data, and then transmit this data into a Kafka topic in Upstash. These streams are then ingested into RisingWave, enabling us to create materialized views (MVs) for thorough flight data analysis. MVs maintain the latest results and are instantly queryable. We also use Metabase to create charts, tables and a unified dashboard for real-time flights tracking.
Upstash is a serverless platform that offers Redis, Kafka, and Qstash, providing the benefits of scalability, advanced security options, and dedicated support. Upstash Kafka uses Apache Kafka for deployments and provides a serverless Kafka platform with connectors, a schema registry, and monitoring, offering various plans for customers with advanced needs.
Sign up for an Upstash account
Sign up for a free Upstash Cloud account, which provides access to Kafka services. To create an account, visit Upstash Cloud Account.
Once you are logged in, create your Kafka cluster with the following details:
- Cluster Name: Give your Kafka cluster a unique name for identification.
- Region: Choose the region where your Kafka cluster will be hosted.
- Cluster Type: Select the cluster type that suits your needs.
After creating your Kafka cluster, set up a Kafka topic. Upstash Kafka provides default configurations for the number of partitions and retention policy, simplifying the setup process.
After creating a Kafka cluster and then a Kafka topic, we are ready to leverage the capabilities of Upstash Kafka and RisingWave to build stream processing applications and pipelines. For more information, please refer to Upstash Kafka Documentation.
The sample data that we ingest into an Upstash Kafka topic contains information regarding real-time data from an aviation API that includes airport name, flight status, flight location, etc.
24-05-16",
"flight_status": "scheduled",
"departure_airport": "Auckland International",
"departure_timezone": "Pacific/Auckland",
"departure_iata": "AKL",
"departure_icao": "NZAA",
"departure_terminal": "D",
"departure_gate": "28",
"departure_delay": null,
"departure_scheduled": "2024-05-16T06:30:00+00:00",
"departure_estimated": "2024-05-16T06:30:00+00:00",
"departure_actual": null,
"departure_estimated_runway": null,
"departure_actual_runway": null,
"arrival_airport": "Wellington International",
"arrival_timezone": "Pacific/Auckland",
"arrival_iata": "WLG",
"arrival_icao": "NZWN",
"arrival_terminal": null,
"arrival_gate": "15",
"arrival_baggage": null,
"arrival_delay": null,
"arrival_scheduled": "2024-05-16T07:40:00+00:00",
"arrival_estimated": "2024-05-16T07:40:00+00:00",
"arrival_actual": null,
"arrival_estimated_runway": null,
"arrival_actual_runway": null,
"airline_name": "Singapore Airlines",
"airline_iata": "SQ",
"airline_icao": "SIA",
"flight_number": "SQ4438",
"flight_iata": "SQ4438",
"flight_icao": "SIA4438",
"codeshared_airline_name": "air new zealand",
"codeshared_airline_iata": "nz",
"codeshared_airline_icao": "anz",
"codeshared_flight_number": "401",
"codeshared_flight_iata": "nz401",
"flight_info": "Singapore Airlines flight SQ4438 is currently in the air, flying from Auckland International (AKL) to Wellington International (WLG)"
}
Ingest data from Upstash Kafka into RisingWave
For ingesting and processing streaming data, there are two options available: the open-source RisingWave and the managed service, RisingWave Cloud. In this blog, we will focus on using RisingWave Cloud, which provides a user-friendly experience and simplifies the operational aspects of managing and utilizing RisingWave for our flight-tracking solution.
To create a RisingWave cluster in RisingWave Cloud and explore the various features it offers, you can sign up for the free plan available. The free plan allows you to test the functionalities of RisingWave without any cost. For detailed instructions on how to create a RisingWave cluster and get started, you can refer to the official RisingWave documentation. It will provide you with step-by-step guidance to set up and explore the features of RisingWave. If you need additional help with setting up this integration, join our active Slack community.
Ingest data streams into RisingWave
Now that we have set up the data stream in Kafka (in JSON) in Upstash, we can connect to the streams with the following SQL statement. For more information, to ingest data from Upstash Kafka into RisingWave, refer to Upstash Kafka integration in RisingWave documentation.
CREATE SOURCE flight_tracking_source(
flight_date VARCHAR,
flight_status VARCHAR,
departure_airport VARCHAR,
departure_timezone VARCHAR,
departure_iata VARCHAR,
departure_icao VARCHAR,
departure_terminal VARCHAR,
departure_gate VARCHAR,
departure_delay INTERVAL,
departure_scheduled TIMESTAMP WITH TIME ZONE ,
departure_estimated TIMESTAMP WITH TIME ZONE,
departure_actual TIMESTAMP WITH TIME ZONE,
departure_estimated_runway TIMESTAMP WITH TIME ZONE,
departure_actual_runway TIMESTAMP WITH TIME ZONE,
arrival_airport VARCHAR,
arrival_timezone VARCHAR,
arrival_iata VARCHAR,
arrival_icao VARCHAR,
arrival_terminal VARCHAR,
arrival_gate VARCHAR,
arrival_baggage VARCHAR,
arrival_delay INTERVAL,
arrival_scheduled TIMESTAMP WITH TIME ZONE,
arrival_estimated TIMESTAMP WITH TIME ZONE,
arrival_actual TIMESTAMP WITH TIME ZONE,
arrival_estimated_runway TIMESTAMP WITH TIME ZONE,
arrival_actual_runway TIMESTAMP WITH TIME ZONE,
airline_name VARCHAR,
airline_iata VARCHAR,
airline_icao VARCHAR,
flight_number VARCHAR,
flight_iata VARCHAR,
flight_icao VARCHAR,
codeshared_airline_name VARCHAR,
codeshared_airline_iata VARCHAR,
codeshared_airline_icao VARCHAR,
codeshared_flight_number VARCHAR,
codeshared_flight_iata VARCHAR,
flight_info VARCHAR
)
WITH(
connector='kafka',
topic ='flights_tracking',
properties.bootstrap.server ='delicate-herring-9260-us1-kafka.upstash.io:9092',
properties.sasl.mechanism = 'SCRAM-SHA-256',
properties.security.protocol = 'SASL_SSL',
properties.sasl.username = 'xxxxxx',
properties.sasl.password = 'xxxxxx',
scan.startup.mode ='earliest'
)FORMAT PLAIN ENCODE JSON;
With a CREATE SOURCE statement, RisingWave is connected to the streams but has not started to consume data yet. For data to be processed and stored incrementally, we need to define materialized views. After a materialized view is created, RisingWave will consume data from the specified offset.
Set up materialized views for analyzing flight data
We’ll create different materialized views that keep track of and extract various attributes related to flight information from flight_tracking**_source**. These attributes include flight date, status, departure and arrival details (airport, timezone, IATA code, ICAO code, scheduled and estimated times), airline information (name, IATA code, ICAO code), flight number and identifiers (IATA and ICAO codes), and general flight information.
The reason why we use materialized views is that they always maintain the latest results. The query creates a materialized view called Airline_Flight_Counts that counts the number of flights for each airline within hourly intervals. It uses the flight_tracking_source and groups the data by airline name and time windows of one hour.
CREATE MATERIALIZED VIEW Airline_Flight_Counts
SELECT airline_name,
COUNT(airline_name) AS total_flights,
window_start, window_end
FROM TUMBLE (flight_tracking_source, arrival_scheduled, INTERVAL '1 hour')
GROUP BY airline_name,window_start, window_end
ORDER BY total_flights desc;
This query creates a materialized view named Airport_Summary that counts the total flights arriving and departing at each airport within hourly intervals from the flight_tracking_source. The results are grouped by airport and time windows of one hour, and they're ordered by the total flight count in descending order.
CREATE MATERIALIZED VIEW Airport_Summary
WITH ArrivalCounts AS (
SELECT
arrival_airport,
COUNT(arrival_airport) AS total_flights_arrival,
window_start,
window_end
FROM
TUMBLE (flight_tracking_source, arrival_scheduled, INTERVAL '1 hour')
GROUP BY
arrival_airport,
window_start,
window_end
),
DepartureCounts AS (
SELECT
departure_airport,
COUNT(departure_airport) AS total_flights_departure,
window_start,
window_end
FROM
TUMBLE (flight_tracking_source, arrival_scheduled, INTERVAL '1 hour')
GROUP BY
departure_airport,
window_start,
window_end
)
SELECT
ArrivalCounts.arrival_airport,
ArrivalCounts.total_flights_arrival,
DepartureCounts.departure_airport,
DepartureCounts.total_flights_departure,
ArrivalCounts.window_start,
ArrivalCounts.window_end
FROM
ArrivalCounts
INNER JOIN
DepartureCounts ON ArrivalCounts.window_start = DepartureCounts.window_start
AND ArrivalCounts.window_end = DepartureCounts.window_end
AND ArrivalCounts.arrival_airport = DepartureCounts.departure_airport
ORDER BY
ArrivalCounts.total_flights_arrival DESC,
DepartureCounts.total_flights_departure DESC;
This query creates a materialized view called Timezone_Summary that calculates the total number of flights arriving and departing from each timezone within hourly intervals. It uses the flight_tracking_source, grouping the data by timezone and one-hour time windows, then orders the results by the total flight count in descending order.
CREATE MATERIALIZED VIEW Timezone_Summary
WITH ArrivalCounts AS (
SELECT
arrival_timezone,
COUNT(arrival_timezone) AS total_flights_arrival,
window_start,
window_end
FROM
TUMBLE (flight_tracking_source, arrival_scheduled, INTERVAL '1 hour')
GROUP BY
arrival_timezone,
window_start,
window_end
),
DepartureCounts AS (
SELECT
departure_timezone,
COUNT(departure_timezone) AS total_flights_departure,
window_start,
window_end
FROM
TUMBLE (flight_tracking_source, arrival_scheduled, INTERVAL '1 hour')
GROUP BY
departure_timezone,
window_start,
window_end
)
SELECT
ArrivalCounts.arrival_timezone,
ArrivalCounts.total_flights_arrival,
DepartureCounts.departure_timezone,
DepartureCounts.total_flights_departure,
ArrivalCounts.window_start,
ArrivalCounts.window_end
FROM
ArrivalCounts
INNER JOIN
DepartureCounts ON ArrivalCounts.window_start = DepartureCounts.window_start
AND ArrivalCounts.window_end = DepartureCounts.window_end
AND ArrivalCounts.arrival_timezone = DepartureCounts.departure_timezone
ORDER BY
ArrivalCounts.total_flights_arrival DESC,
DepartureCounts.total_flights_departure DESC;
Visualization using Metabase
Metabase is an open-source business intelligence tool that lets you visualize and share data insights. It provides an easy way to create charts, dashboards, and metrics on top of databases.
Connect RisingWave to Metabase
Since RisingWave is compatible with PostgreSQL, you can connect Metabase to RisingWave as a data source and build analytics on streaming data.
You can use RisingWave as a data source in Metabase for creating visualizations and dashboards using the tables and materialized views in RisingWave. To understand the process, follow the instructions in Configure Metabase to read data from RisingWave.
After successfully connecting RisingWave to Metabase, we add the materialized views in RisingWave as data sources to create tables, various charts, and a unified dashboard.
Visualizing data with Metabase: table, charts, and dashboard
We create these tables, charts, and dashboards using the materialized views and a source in RisingWave, such as flight_tracking_source,Airline_Flight_Counts, Airport_Summary, and Timezone_Summary.
This unified dashboard presents a collection of charts for real-time flight tracking. It provides a holistic view of flight operations, offering insights into total flights categorized by airline, airport, and timezone. Additionally, it features detailed information on the current flight, empowering users with comprehensive monitoring capabilities and actionable insights.
> In this blog, we developed a real-time flight tracking system using Upstash, RisingWave, and Metabase. Configuration and connection were straightforward, thanks to RisingWave's wide range of source and destination connectors. We ingested real-time flight data into a Kafka topic in Upstash, sent it to RisingWave, and created materialized views for in-depth analysis. Finally, we used Metabase to create visualizations and a real-time dashboard, enabling users to monitor flight operations and make informed decisions.
