Ticket Meta — Hive Data Warehouse for Flight Search

Overview

This repository contains the data-warehouse foundation of the Ticket Meta flight search and recommendation project, which later grew into the Data Turbulence analytics platform. It defines, in ordered HiveQL scripts, how raw aviation data is loaded, optimised and summarised on a Hadoop cluster.

How it works

The scripts run in sequence:

• Database: creates the project database.
• External tables: stage the raw CSV data for itineraries, airports, airport frequencies, countries, regions, runways and navigation aids.
• Managed tables: copy each dataset into Hive-managed tables. The itineraries fact table, about 30 GB of raw data, is stored as ORC with Snappy compression for much faster scans.
• Aggregated tables: pre-compute the queries the application needs most, including cheapest destinations, price by month, airline comparison, airline market share and distance versus price.
• Verification: a final script checks that every table loaded correctly.

Why it matters

Splitting the work into staging, managed and aggregated layers keeps raw data untouched and lets the web application answer common questions from small summary tables instead of scanning the full fact table each time. Numbered scripts make the warehouse easy to rebuild from scratch on any cluster.