{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transportation: Delta Liquid Clustering Demo\n",
    "\n",
    "\n",
    "## Overview\n",
    "\n",
    "\n",
    "This notebook demonstrates the power of **Delta Liquid Clustering** in Oracle AI Data Platform (AIDP) Workbench using a transportation and logistics analytics use case. Liquid clustering automatically optimizes data layout for query performance without requiring manual partitioning or Z-Ordering.\n",
    "\n",
    "### What is Liquid Clustering?\n",
    "\n",
    "Liquid clustering automatically identifies and groups similar data together based on clustering columns you define. This optimization happens automatically during data ingestion and maintenance operations, providing:\n",
    "\n",
    "- **Automatic optimization**: No manual tuning required\n",
    "- **Improved query performance**: Faster queries on clustered columns\n",
    "- **Reduced maintenance**: No need for manual repartitioning\n",
    "- **Adaptive clustering**: Adjusts as data patterns change\n",
    "\n",
    "### Use Case: Fleet Management and Route Optimization\n",
    "\n",
    "We'll analyze transportation fleet operations and logistics data. Our clustering strategy will optimize for:\n",
    "\n",
    "- **Vehicle-specific queries**: Fast lookups by vehicle ID\n",
    "- **Time-based analysis**: Efficient filtering by trip date and time\n",
    "- **Route performance patterns**: Quick aggregation by route and operational metrics\n",
    "\n",
    "### AIDP Environment Setup\n",
    "\n",
    "This notebook leverages the existing Spark session in your AIDP environment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create transportation catalog and analytics schema\n",
    "\n",
    "# In AIDP, catalogs provide data isolation and governance\n",
    "\n",
    "spark.sql(\"CREATE CATALOG IF NOT EXISTS transportation\")\n",
    "\n",
    "spark.sql(\"CREATE SCHEMA IF NOT EXISTS transportation.analytics\")\n",
    "\n",
    "print(\"Transportation catalog and analytics schema created successfully!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 2: Create Delta Table with Liquid Clustering\n",
    "\n",
    "### Table Design\n",
    "\n",
    "Our `fleet_trips` table will store:\n",
    "\n",
    "- **vehicle_id**: Unique vehicle identifier\n",
    "- **trip_date**: Date and time of trip start\n",
    "- **route_id**: Route identifier\n",
    "- **distance**: Distance traveled (miles/km)\n",
    "- **duration**: Trip duration (minutes)\n",
    "- **fuel_consumed**: Fuel used (gallons/liters)\n",
    "- **load_factor**: Capacity utilization (0-100)\n",
    "\n",
    "### Clustering Strategy\n",
    "\n",
    "We'll cluster by `vehicle_id` and `trip_date` because:\n",
    "\n",
    "- **vehicle_id**: Vehicles generate multiple trips, grouping maintenance and performance data together\n",
    "- **trip_date**: Time-based queries are essential for scheduling, fuel analysis, and operational reporting\n",
    "- This combination optimizes for both vehicle monitoring and temporal fleet performance analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Delta table with liquid clustering created successfully!\n",
       "Clustering will automatically optimize data layout for queries on vehicle_id and trip_date.\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Create Delta table with liquid clustering\n",
    "\n",
    "# CLUSTER BY defines the columns for automatic optimization\n",
    "\n",
    "spark.sql(\"\"\"\n",
    "\n",
    "CREATE TABLE IF NOT EXISTS transportation.analytics.fleet_trips (\n",
    "\n",
    "    vehicle_id STRING,\n",
    "\n",
    "    trip_date TIMESTAMP,\n",
    "\n",
    "    route_id STRING,\n",
    "\n",
    "    distance DECIMAL(8,2),\n",
    "\n",
    "    duration DECIMAL(6,2),\n",
    "\n",
    "    fuel_consumed DECIMAL(6,2),\n",
    "\n",
    "    load_factor INT\n",
    "\n",
    ")\n",
    "\n",
    "USING DELTA\n",
    "\n",
    "CLUSTER BY (vehicle_id, trip_date)\n",
    "\n",
    "\"\"\")\n",
    "\n",
    "print(\"Delta table with liquid clustering created successfully!\")\n",
    "\n",
    "print(\"Clustering will automatically optimize data layout for queries on vehicle_id and trip_date.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 3: Generate Transportation Sample Data\n",
    "\n",
    "### Data Generation Strategy\n",
    "\n",
    "We'll create realistic transportation fleet data including:\n",
    "\n",
    "- **500 vehicles** with multiple trips over time\n",
    "- **Route types**: Urban delivery, Long-haul, Local transport, Express delivery\n",
    "- **Realistic operational patterns**: Peak hours, route variations, fuel efficiency differences\n",
    "- **Fleet diversity**: Different vehicle types with varying capacities and fuel consumption\n",
    "\n",
    "### Why This Data Pattern?\n",
    "\n",
    "This data simulates real transportation scenarios where:\n",
    "\n",
    "- Vehicle performance varies by route and time of day\n",
    "- Fuel efficiency impacts operational costs\n",
    "- Route optimization requires historical performance data\n",
    "- Capacity utilization affects profitability\n",
    "- Maintenance scheduling depends on usage patterns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Generated 20038 fleet trip records\n",
       "Sample record: {'vehicle_id': 'VH0001', 'trip_date': datetime.datetime(2024, 9, 18, 12, 41), 'route_id': 'RT_CHI_DET_003', 'distance': 61.11, 'duration': 88.03, 'fuel_consumed': 12.56, 'load_factor': 91}\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Generate sample transportation fleet data\n",
    "\n",
    "# Using fully qualified imports to avoid conflicts\n",
    "\n",
    "import random\n",
    "\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "\n",
    "# Define transportation data constants\n",
    "\n",
    "ROUTE_TYPES = ['Urban Delivery', 'Long-haul', 'Local Transport', 'Express Delivery']\n",
    "\n",
    "ROUTES = ['RT_NYC_MAN_001', 'RT_LAX_SFO_002', 'RT_CHI_DET_003', 'RT_HOU_DAL_004', 'RT_MIA_ORL_005']\n",
    "\n",
    "# Base trip parameters by route type\n",
    "\n",
    "TRIP_PARAMS = {\n",
    "\n",
    "    'Urban Delivery': {'avg_distance': 45, 'avg_duration': 120, 'avg_fuel': 8.5, 'load_factor': 85},\n",
    "\n",
    "    'Long-haul': {'avg_distance': 450, 'avg_duration': 480, 'avg_fuel': 65.0, 'load_factor': 92},\n",
    "\n",
    "    'Local Transport': {'avg_distance': 120, 'avg_duration': 180, 'avg_fuel': 15.2, 'load_factor': 78},\n",
    "\n",
    "    'Express Delivery': {'avg_distance': 80, 'avg_duration': 90, 'avg_fuel': 12.8, 'load_factor': 95}\n",
    "\n",
    "}\n",
    "\n",
    "\n",
    "# Generate fleet trip records\n",
    "\n",
    "trip_data = []\n",
    "\n",
    "base_date = datetime(2024, 1, 1)\n",
    "\n",
    "\n",
    "# Create 500 vehicles with 20-60 trips each\n",
    "\n",
    "for vehicle_num in range(1, 501):\n",
    "\n",
    "    vehicle_id = f\"VH{vehicle_num:04d}\"\n",
    "    \n",
    "    # Each vehicle gets 20-60 trips over 12 months\n",
    "\n",
    "    num_trips = random.randint(20, 60)\n",
    "    \n",
    "    for i in range(num_trips):\n",
    "\n",
    "        # Spread trips over 12 months\n",
    "\n",
    "        days_offset = random.randint(0, 365)\n",
    "\n",
    "        trip_date = base_date + timedelta(days=days_offset)\n",
    "        \n",
    "        # Add realistic timing (more trips during business hours)\n",
    "\n",
    "        hour_weights = [1, 1, 1, 1, 1, 3, 8, 10, 12, 10, 8, 6, 8, 9, 8, 7, 6, 5, 3, 2, 2, 1, 1, 1]\n",
    "\n",
    "        hours_offset = random.choices(range(24), weights=hour_weights)[0]\n",
    "\n",
    "        trip_date = trip_date.replace(hour=hours_offset, minute=random.randint(0, 59), second=0, microsecond=0)\n",
    "        \n",
    "        # Select route type\n",
    "\n",
    "        route_type = random.choice(ROUTE_TYPES)\n",
    "\n",
    "        params = TRIP_PARAMS[route_type]\n",
    "        \n",
    "        # Calculate trip metrics with variability\n",
    "\n",
    "        distance_variation = random.uniform(0.7, 1.4)\n",
    "\n",
    "        distance = round(params['avg_distance'] * distance_variation, 2)\n",
    "        \n",
    "        duration_variation = random.uniform(0.8, 1.6)\n",
    "\n",
    "        duration = round(params['avg_duration'] * duration_variation, 2)\n",
    "        \n",
    "        fuel_variation = random.uniform(0.85, 1.25)\n",
    "\n",
    "        fuel_consumed = round(params['avg_fuel'] * fuel_variation, 2)\n",
    "        \n",
    "        load_factor_variation = random.randint(-10, 8)\n",
    "\n",
    "        load_factor = max(0, min(100, params['load_factor'] + load_factor_variation))\n",
    "        \n",
    "        # Select specific route\n",
    "\n",
    "        route_id = random.choice(ROUTES)\n",
    "        \n",
    "        trip_data.append({\n",
    "\n",
    "            \"vehicle_id\": vehicle_id,\n",
    "\n",
    "            \"trip_date\": trip_date,\n",
    "\n",
    "            \"route_id\": route_id,\n",
    "\n",
    "            \"distance\": distance,\n",
    "\n",
    "            \"duration\": duration,\n",
    "\n",
    "            \"fuel_consumed\": fuel_consumed,\n",
    "\n",
    "            \"load_factor\": load_factor\n",
    "\n",
    "        })\n",
    "\n",
    "\n",
    "\n",
    "print(f\"Generated {len(trip_data)} fleet trip records\")\n",
    "\n",
    "print(\"Sample record:\", trip_data[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 4: Insert Data Using PySpark\n",
    "\n",
    "### Data Insertion Strategy\n",
    "\n",
    "We'll use PySpark to:\n",
    "\n",
    "1. **Create DataFrame** from our generated data\n",
    "2. **Insert into Delta table** with liquid clustering\n",
    "3. **Verify the insertion** with a sample query\n",
    "\n",
    "### Why PySpark for Insertion?\n",
    "\n",
    "- **Distributed processing**: Handles large datasets efficiently\n",
    "- **Type safety**: Ensures data integrity\n",
    "- **Optimization**: Leverages Spark's query optimization\n",
    "- **Liquid clustering**: Automatically applies clustering during insertion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame Schema:\n",
       "root\n",
       " |-- distance: double (nullable = true)\n",
       " |-- duration: double (nullable = true)\n",
       " |-- fuel_consumed: double (nullable = true)\n",
       " |-- load_factor: long (nullable = true)\n",
       " |-- route_id: string (nullable = true)\n",
       " |-- trip_date: timestamp (nullable = true)\n",
       " |-- vehicle_id: string (nullable = true)\n",
       "\n",
       "\n",
       "Sample Data:\n",
       "+--------+--------+-------------+-----------+--------------+-------------------+----------+\n",
       "|distance|duration|fuel_consumed|load_factor|      route_id|          trip_date|vehicle_id|\n",
       "+--------+--------+-------------+-----------+--------------+-------------------+----------+\n",
       "|   61.11|   88.03|        12.56|         91|RT_CHI_DET_003|2024-09-18 12:41:00|    VH0001|\n",
       "|   51.42|  112.49|         7.68|         79|RT_LAX_SFO_002|2024-01-25 08:54:00|    VH0001|\n",
       "|  150.73|  161.99|        13.91|         86|RT_LAX_SFO_002|2024-04-27 13:43:00|    VH0001|\n",
       "|  494.89|  648.35|        60.96|         89|RT_HOU_DAL_004|2024-05-25 09:42:00|    VH0001|\n",
       "|   70.15|  109.15|        15.33|         98|RT_NYC_MAN_001|2024-10-09 22:08:00|    VH0001|\n",
       "+--------+--------+-------------+-----------+--------------+-------------------+----------+\n",
       "only showing top 5 rows\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "\n",
       "Successfully inserted 20038 records into transportation.analytics.fleet_trips\n",
       "Liquid clustering automatically optimized the data layout during insertion!\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Insert data using PySpark DataFrame operations\n",
    "\n",
    "# Using fully qualified function references to avoid conflicts\n",
    "\n",
    "\n",
    "# Create DataFrame from generated data\n",
    "\n",
    "df_trips = spark.createDataFrame(trip_data)\n",
    "\n",
    "\n",
    "# Display schema and sample data\n",
    "\n",
    "print(\"DataFrame Schema:\")\n",
    "\n",
    "df_trips.printSchema()\n",
    "\n",
    "\n",
    "\n",
    "print(\"\\nSample Data:\")\n",
    "\n",
    "df_trips.show(5)\n",
    "\n",
    "\n",
    "# Insert data into Delta table with liquid clustering\n",
    "\n",
    "# The CLUSTER BY (vehicle_id, trip_date) will automatically optimize the data layout\n",
    "\n",
    "df_trips.write.mode(\"overwrite\").saveAsTable(\"transportation.analytics.fleet_trips\")\n",
    "\n",
    "\n",
    "print(f\"\\nSuccessfully inserted {df_trips.count()} records into transportation.analytics.fleet_trips\")\n",
    "\n",
    "print(\"Liquid clustering automatically optimized the data layout during insertion!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 5: Demonstrate Liquid Clustering Benefits\n",
    "\n",
    "### Query Performance Analysis\n",
    "\n",
    "Now let's see how liquid clustering improves query performance. We'll run queries that benefit from our clustering strategy:\n",
    "\n",
    "1. **Vehicle trip history** (clustered by vehicle_id)\n",
    "2. **Time-based fleet analysis** (clustered by trip_date)\n",
    "3. **Combined vehicle + time queries** (optimal for our clustering)\n",
    "\n",
    "### Expected Performance Benefits\n",
    "\n",
    "With liquid clustering, these queries should be significantly faster because:\n",
    "\n",
    "- **Data locality**: Related records are physically grouped together\n",
    "- **Reduced I/O**: Less data needs to be read from disk\n",
    "- **Automatic optimization**: No manual tuning required"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "=== Query 1: Vehicle Trip History ===\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "+----------+-------------------+--------------+--------+-------------+-----------+\n",
       "|vehicle_id|          trip_date|      route_id|distance|fuel_consumed|load_factor|\n",
       "+----------+-------------------+--------------+--------+-------------+-----------+\n",
       "|    VH0001|2024-12-27 07:00:00|RT_LAX_SFO_002|  157.53|        13.97|         68|\n",
       "|    VH0001|2024-11-25 08:51:00|RT_NYC_MAN_001|  344.14|        80.35|         87|\n",
       "|    VH0001|2024-10-29 17:12:00|RT_HOU_DAL_004|  364.71|        62.36|         97|\n",
       "|    VH0001|2024-10-23 11:19:00|RT_CHI_DET_003|  101.71|        18.22|         68|\n",
       "|    VH0001|2024-10-22 07:46:00|RT_CHI_DET_003|  587.09|         64.7|         83|\n",
       "|    VH0001|2024-10-09 22:08:00|RT_NYC_MAN_001|   70.15|        15.33|         98|\n",
       "|    VH0001|2024-09-19 10:15:00|RT_NYC_MAN_001|  483.69|        68.25|         99|\n",
       "|    VH0001|2024-09-18 12:41:00|RT_CHI_DET_003|   61.11|        12.56|         91|\n",
       "|    VH0001|2024-09-06 07:47:00|RT_LAX_SFO_002|   98.54|         16.8|         75|\n",
       "|    VH0001|2024-08-30 19:39:00|RT_MIA_ORL_005|   97.78|        13.62|        100|\n",
       "|    VH0001|2024-08-28 14:42:00|RT_HOU_DAL_004|  570.42|        65.09|         97|\n",
       "|    VH0001|2024-08-27 06:38:00|RT_NYC_MAN_001|   49.15|         9.42|         81|\n",
       "|    VH0001|2024-08-26 08:51:00|RT_HOU_DAL_004|  121.54|        17.84|         79|\n",
       "|    VH0001|2024-08-12 21:59:00|RT_NYC_MAN_001|   69.54|        12.46|         98|\n",
       "|    VH0001|2024-06-23 10:01:00|RT_NYC_MAN_001|  369.88|        57.41|         84|\n",
       "|    VH0001|2024-06-08 12:32:00|RT_CHI_DET_003|   97.21|        13.17|         86|\n",
       "|    VH0001|2024-06-06 03:41:00|RT_NYC_MAN_001|  118.22|        16.76|         86|\n",
       "|    VH0001|2024-05-25 09:42:00|RT_HOU_DAL_004|  494.89|        60.96|         89|\n",
       "|    VH0001|2024-05-13 14:21:00|RT_CHI_DET_003|   92.56|        17.51|         77|\n",
       "|    VH0001|2024-05-11 11:06:00|RT_NYC_MAN_001|  551.35|        60.01|         90|\n",
       "+----------+-------------------+--------------+--------+-------------+-----------+\n",
       "only showing top 20 rows\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Records found: 27\n",
       "\n",
       "=== Query 2: Recent Fuel Efficiency Issues ===\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "+-------------------+----------+--------------+--------+-------------+----+\n",
       "|          trip_date|vehicle_id|      route_id|distance|fuel_consumed| mpg|\n",
       "+-------------------+----------+--------------+--------+-------------+----+\n",
       "|2024-08-30 17:42:00|    VH0238|RT_MIA_ORL_005|   31.54|         10.6|2.98|\n",
       "|2024-10-28 06:59:00|    VH0235|RT_NYC_MAN_001|   31.78|        10.62|2.99|\n",
       "|2024-07-31 14:56:00|    VH0126|RT_CHI_DET_003|   31.61|        10.51|3.01|\n",
       "|2024-12-18 09:20:00|    VH0272|RT_MIA_ORL_005|   31.89|        10.53|3.03|\n",
       "|2024-06-05 03:07:00|    VH0230|RT_CHI_DET_003|   31.82|        10.47|3.04|\n",
       "|2024-10-10 07:42:00|    VH0371|RT_HOU_DAL_004|   32.12|         10.5|3.06|\n",
       "|2024-10-21 15:11:00|    VH0063|RT_LAX_SFO_002|   32.54|        10.61|3.07|\n",
       "|2024-09-25 09:26:00|    VH0246|RT_LAX_SFO_002|   31.72|        10.34|3.07|\n",
       "|2024-10-02 07:26:00|    VH0126|RT_HOU_DAL_004|   31.96|        10.39|3.08|\n",
       "|2024-06-27 19:30:00|    VH0134|RT_HOU_DAL_004|   32.45|        10.52|3.08|\n",
       "|2024-07-21 14:55:00|    VH0184|RT_NYC_MAN_001|   32.31|        10.44|3.09|\n",
       "|2024-12-28 08:54:00|    VH0067|RT_CHI_DET_003|   33.08|        10.56|3.13|\n",
       "|2024-07-11 14:05:00|    VH0273|RT_LAX_SFO_002|   31.85|        10.19|3.13|\n",
       "|2024-12-13 07:37:00|    VH0245|RT_HOU_DAL_004|   33.13|        10.53|3.15|\n",
       "|2024-09-26 17:08:00|    VH0467|RT_MIA_ORL_005|   33.49|        10.62|3.15|\n",
       "|2024-07-28 08:37:00|    VH0171|RT_LAX_SFO_002|   32.14|         10.2|3.15|\n",
       "|2024-10-21 13:49:00|    VH0092|RT_LAX_SFO_002|   32.01|        10.12|3.16|\n",
       "|2024-10-03 13:33:00|    VH0167|RT_NYC_MAN_001|   32.44|        10.25|3.16|\n",
       "|2024-09-20 14:38:00|    VH0275|RT_MIA_ORL_005|   32.15|        10.19|3.16|\n",
       "|2024-09-12 09:52:00|    VH0359|RT_MIA_ORL_005|   33.13|        10.48|3.16|\n",
       "+-------------------+----------+--------------+--------+-------------+----+\n",
       "only showing top 20 rows\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Fuel efficiency issues found: 11623\n",
       "\n",
       "=== Query 3: Vehicle Performance Trends ===\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "+----------+-------------------+--------------+--------+--------+-----------+\n",
       "|vehicle_id|          trip_date|      route_id|distance|duration|load_factor|\n",
       "+----------+-------------------+--------------+--------+--------+-----------+\n",
       "|    VH0001|2024-04-17 14:40:00|RT_MIA_ORL_005|   44.23|  128.45|         80|\n",
       "|    VH0001|2024-04-27 13:43:00|RT_LAX_SFO_002|  150.73|  161.99|         86|\n",
       "|    VH0001|2024-04-30 07:27:00|RT_NYC_MAN_001|   139.2|  156.91|         83|\n",
       "|    VH0001|2024-05-05 09:04:00|RT_HOU_DAL_004|  115.97|  275.29|         84|\n",
       "|    VH0001|2024-05-11 11:06:00|RT_NYC_MAN_001|  551.35|  659.59|         90|\n",
       "|    VH0001|2024-05-13 14:21:00|RT_CHI_DET_003|   92.56|  218.45|         77|\n",
       "|    VH0001|2024-05-25 09:42:00|RT_HOU_DAL_004|  494.89|  648.35|         89|\n",
       "|    VH0001|2024-06-06 03:41:00|RT_NYC_MAN_001|  118.22|  284.32|         86|\n",
       "|    VH0001|2024-06-08 12:32:00|RT_CHI_DET_003|   97.21|  124.14|         86|\n",
       "|    VH0001|2024-06-23 10:01:00|RT_NYC_MAN_001|  369.88|   613.6|         84|\n",
       "|    VH0001|2024-08-12 21:59:00|RT_NYC_MAN_001|   69.54|   79.57|         98|\n",
       "|    VH0001|2024-08-26 08:51:00|RT_HOU_DAL_004|  121.54|  258.74|         79|\n",
       "|    VH0001|2024-08-27 06:38:00|RT_NYC_MAN_001|   49.15|  173.12|         81|\n",
       "|    VH0001|2024-08-28 14:42:00|RT_HOU_DAL_004|  570.42|  552.36|         97|\n",
       "|    VH0001|2024-08-30 19:39:00|RT_MIA_ORL_005|   97.78|    81.2|        100|\n",
       "|    VH0001|2024-09-06 07:47:00|RT_LAX_SFO_002|   98.54|  234.96|         75|\n",
       "|    VH0001|2024-09-18 12:41:00|RT_CHI_DET_003|   61.11|   88.03|         91|\n",
       "|    VH0001|2024-09-19 10:15:00|RT_NYC_MAN_001|  483.69|   656.8|         99|\n",
       "|    VH0001|2024-10-09 22:08:00|RT_NYC_MAN_001|   70.15|  109.15|         98|\n",
       "|    VH0001|2024-10-22 07:46:00|RT_CHI_DET_003|  587.09|  728.86|         83|\n",
       "+----------+-------------------+--------------+--------+--------+-----------+\n",
       "only showing top 20 rows\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Performance trend records found: 251\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Demonstrate liquid clustering benefits with optimized queries\n",
    "\n",
    "\n",
    "# Query 1: Vehicle trip history - benefits from vehicle_id clustering\n",
    "\n",
    "print(\"=== Query 1: Vehicle Trip History ===\")\n",
    "\n",
    "vehicle_history = spark.sql(\"\"\"\n",
    "\n",
    "SELECT vehicle_id, trip_date, route_id, distance, fuel_consumed, load_factor\n",
    "\n",
    "FROM transportation.analytics.fleet_trips\n",
    "\n",
    "WHERE vehicle_id = 'VH0001'\n",
    "\n",
    "ORDER BY trip_date DESC\n",
    "\n",
    "\"\"\")\n",
    "\n",
    "\n",
    "\n",
    "vehicle_history.show()\n",
    "\n",
    "print(f\"Records found: {vehicle_history.count()}\")\n",
    "\n",
    "\n",
    "\n",
    "# Query 2: Time-based fuel efficiency analysis - benefits from trip_date clustering\n",
    "\n",
    "print(\"\\n=== Query 2: Recent Fuel Efficiency Issues ===\")\n",
    "\n",
    "fuel_efficiency = spark.sql(\"\"\"\n",
    "\n",
    "SELECT trip_date, vehicle_id, route_id, distance, fuel_consumed,\n",
    "\n",
    "       ROUND(distance / fuel_consumed, 2) as mpg\n",
    "\n",
    "FROM transportation.analytics.fleet_trips\n",
    "\n",
    "WHERE trip_date >= '2024-06-01' AND (distance / fuel_consumed) < 15\n",
    "\n",
    "ORDER BY mpg ASC, trip_date DESC\n",
    "\n",
    "\"\"\")\n",
    "\n",
    "\n",
    "\n",
    "fuel_efficiency.show()\n",
    "\n",
    "print(f\"Fuel efficiency issues found: {fuel_efficiency.count()}\")\n",
    "\n",
    "\n",
    "\n",
    "# Query 3: Combined vehicle + time query - optimal for our clustering strategy\n",
    "\n",
    "print(\"\\n=== Query 3: Vehicle Performance Trends ===\")\n",
    "\n",
    "performance_trends = spark.sql(\"\"\"\n",
    "\n",
    "SELECT vehicle_id, trip_date, route_id, distance, duration, load_factor\n",
    "\n",
    "FROM transportation.analytics.fleet_trips\n",
    "\n",
    "WHERE vehicle_id LIKE 'VH000%' AND trip_date >= '2024-04-01'\n",
    "\n",
    "ORDER BY vehicle_id, trip_date\n",
    "\n",
    "\"\"\")\n",
    "\n",
    "\n",
    "\n",
    "performance_trends.show()\n",
    "\n",
    "print(f\"Performance trend records found: {performance_trends.count()}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 6: Analyze Clustering Effectiveness\n",
    "\n",
    "### Understanding the Impact\n",
    "\n",
    "Let's examine how liquid clustering has organized our data and analyze some aggregate statistics to demonstrate the transportation insights possible with this optimized structure.\n",
    "\n",
    "### Key Analytics\n",
    "\n",
    "- **Vehicle utilization** and performance metrics\n",
    "- **Route efficiency** and fuel consumption analysis\n",
    "- **Fleet capacity utilization** and load factors\n",
    "- **Operational cost trends** and optimization opportunities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "=== Vehicle Performance Analysis ===\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "+----------+-----------+--------------+----------+-------+---------------+-----------+\n",
       "|vehicle_id|total_trips|total_distance|total_fuel|avg_mpg|avg_load_factor|total_miles|\n",
       "+----------+-----------+--------------+----------+-------+---------------+-----------+\n",
       "|    VH0052|         56|      13745.28|    1822.8|   7.15|           86.5|    13745.0|\n",
       "|    VH0400|         58|      13001.58|   1851.48|   7.09|          85.02|    13002.0|\n",
       "|    VH0167|         57|      12840.64|   1791.64|   6.82|          86.89|    12841.0|\n",
       "|    VH0135|         60|      12718.98|   1744.79|   6.94|          86.37|    12719.0|\n",
       "|    VH0191|         60|      12345.26|   1758.71|   6.41|          88.65|    12345.0|\n",
       "|    VH0061|         59|       12136.1|   1733.44|   6.71|          85.64|    12136.0|\n",
       "|    VH0165|         41|      12076.09|   1640.96|   6.96|          87.39|    12076.0|\n",
       "|    VH0294|         58|      11970.43|   1684.62|   6.81|          87.48|    11970.0|\n",
       "|    VH0327|         59|      11945.45|   1716.92|   6.65|          85.97|    11945.0|\n",
       "|    VH0140|         60|      11922.11|   1664.81|   6.98|          85.97|    11922.0|\n",
       "|    VH0368|         48|      11878.91|    1728.3|   6.77|          86.21|    11879.0|\n",
       "|    VH0354|         56|      11787.65|   1655.25|   6.83|          86.63|    11788.0|\n",
       "|    VH0015|         56|       11768.6|   1694.98|   6.86|          87.73|    11769.0|\n",
       "|    VH0268|         58|      11690.08|   1710.31|   6.78|          86.36|    11690.0|\n",
       "|    VH0441|         56|      11660.18|   1739.46|   6.69|          86.46|    11660.0|\n",
       "|    VH0103|         60|      11550.49|   1738.03|   6.37|          88.12|    11550.0|\n",
       "|    VH0288|         40|      11406.62|   1646.25|   6.71|          89.05|    11407.0|\n",
       "|    VH0362|         59|      11352.78|   1736.35|   6.23|          87.15|    11353.0|\n",
       "|    VH0278|         54|      11284.31|   1578.35|   6.78|           86.8|    11284.0|\n",
       "|    VH0147|         49|      11236.45|    1552.4|   6.73|          87.24|    11236.0|\n",
       "+----------+-----------+--------------+----------+-------+---------------+-----------+\n",
       "only showing top 20 rows\n",
       "\n",
       "\n",
       "=== Route Efficiency Analysis ===\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "+--------------+-----------+------------+------------+---------+---------------+\n",
       "|      route_id|total_trips|avg_distance|avg_duration|avg_speed|avg_load_factor|\n",
       "+--------------+-----------+------------+------------+---------+---------------+\n",
       "|RT_CHI_DET_003|       4126|      184.06|      261.13|    39.35|          86.31|\n",
       "|RT_MIA_ORL_005|       4004|      182.05|      260.78|    38.95|          86.37|\n",
       "|RT_LAX_SFO_002|       3978|      184.34|      263.34|    39.03|          86.64|\n",
       "|RT_NYC_MAN_001|       3970|      182.29|       257.7|    39.41|          86.36|\n",
       "|RT_HOU_DAL_004|       3960|       180.9|      259.87|     38.9|          86.44|\n",
       "+--------------+-----------+------------+------------+---------+---------------+\n",
       "\n",
       "\n",
       "=== Fleet Fuel Consumption Analysis ===\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "+------------------------+----------+-------+---------------+\n",
       "|fuel_efficiency_category|trip_count|avg_mpg|total_fuel_used|\n",
       "+------------------------+----------+-------+---------------+\n",
       "|        Poor (10-14 MPG)|       905|  10.85|       20860.05|\n",
       "|     Very Poor (<10 MPG)|     19133|   6.47|      512754.97|\n",
       "+------------------------+----------+-------+---------------+\n",
       "\n",
       "\n",
       "=== Monthly Operational Trends ===\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "+-------+-----------+----------------+------------+---------------+---------------+\n",
       "|  month|total_trips|monthly_distance|monthly_fuel|avg_load_factor|active_vehicles|\n",
       "+-------+-----------+----------------+------------+---------------+---------------+\n",
       "|2024-01|       1698|       317523.06|    46032.93|          86.56|            480|\n",
       "|2024-02|       1595|       296640.13|    42982.33|          86.48|            470|\n",
       "|2024-03|       1691|       314813.74|    45885.88|          86.45|            482|\n",
       "|2024-04|       1705|       317797.52|    46304.12|          86.68|            469|\n",
       "|2024-05|       1726|       299904.18|    43857.89|          85.94|            483|\n",
       "|2024-06|       1640|       292969.86|    42827.06|           86.1|            484|\n",
       "|2024-07|       1660|        287370.9|    41814.72|          86.19|            475|\n",
       "|2024-08|       1720|       316108.84|    46144.17|          86.71|            482|\n",
       "|2024-09|       1665|       307694.92|    44835.12|          86.64|            467|\n",
       "|2024-10|       1662|       307290.33|    45040.02|          86.41|            474|\n",
       "|2024-11|       1600|       286001.54|    41574.27|          86.35|            473|\n",
       "|2024-12|       1676|       317624.81|    46316.51|          86.57|            471|\n",
       "+-------+-----------+----------------+------------+---------------+---------------+\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Analyze clustering effectiveness and transportation insights\n",
    "\n",
    "\n",
    "# Vehicle performance analysis\n",
    "\n",
    "print(\"=== Vehicle Performance Analysis ===\")\n",
    "\n",
    "vehicle_performance = spark.sql(\"\"\"\n",
    "\n",
    "SELECT vehicle_id, COUNT(*) as total_trips,\n",
    "\n",
    "       ROUND(SUM(distance), 2) as total_distance,\n",
    "\n",
    "       ROUND(SUM(fuel_consumed), 2) as total_fuel,\n",
    "\n",
    "       ROUND(AVG(distance / fuel_consumed), 2) as avg_mpg,\n",
    "\n",
    "       ROUND(AVG(load_factor), 2) as avg_load_factor,\n",
    "\n",
    "       ROUND(SUM(distance), 0) as total_miles\n",
    "\n",
    "FROM transportation.analytics.fleet_trips\n",
    "\n",
    "GROUP BY vehicle_id\n",
    "\n",
    "ORDER BY total_miles DESC\n",
    "\n",
    "\"\"\")\n",
    "\n",
    "\n",
    "\n",
    "vehicle_performance.show()\n",
    "\n",
    "\n",
    "# Route efficiency analysis\n",
    "\n",
    "print(\"\\n=== Route Efficiency Analysis ===\")\n",
    "\n",
    "route_efficiency = spark.sql(\"\"\"\n",
    "\n",
    "SELECT route_id, COUNT(*) as total_trips,\n",
    "\n",
    "       ROUND(AVG(distance), 2) as avg_distance,\n",
    "\n",
    "       ROUND(AVG(duration), 2) as avg_duration,\n",
    "\n",
    "       ROUND(AVG(distance / duration * 60), 2) as avg_speed,\n",
    "\n",
    "       ROUND(AVG(load_factor), 2) as avg_load_factor\n",
    "\n",
    "FROM transportation.analytics.fleet_trips\n",
    "\n",
    "GROUP BY route_id\n",
    "\n",
    "ORDER BY total_trips DESC\n",
    "\n",
    "\"\"\")\n",
    "\n",
    "\n",
    "\n",
    "route_efficiency.show()\n",
    "\n",
    "\n",
    "# Fleet fuel consumption analysis\n",
    "\n",
    "print(\"\\n=== Fleet Fuel Consumption Analysis ===\")\n",
    "\n",
    "fuel_analysis = spark.sql(\"\"\"\n",
    "\n",
    "SELECT \n",
    "\n",
    "    CASE \n",
    "\n",
    "        WHEN distance / fuel_consumed >= 25 THEN 'Excellent (25+ MPG)'\n",
    "\n",
    "        WHEN distance / fuel_consumed >= 20 THEN 'Good (20-24 MPG)'\n",
    "\n",
    "        WHEN distance / fuel_consumed >= 15 THEN 'Average (15-19 MPG)'\n",
    "\n",
    "        WHEN distance / fuel_consumed >= 10 THEN 'Poor (10-14 MPG)'\n",
    "\n",
    "        ELSE 'Very Poor (<10 MPG)'\n",
    "\n",
    "    END as fuel_efficiency_category,\n",
    "\n",
    "    COUNT(*) as trip_count,\n",
    "\n",
    "    ROUND(AVG(distance / fuel_consumed), 2) as avg_mpg,\n",
    "\n",
    "    ROUND(SUM(fuel_consumed), 2) as total_fuel_used\n",
    "\n",
    "FROM transportation.analytics.fleet_trips\n",
    "\n",
    "GROUP BY \n",
    "\n",
    "    CASE \n",
    "\n",
    "        WHEN distance / fuel_consumed >= 25 THEN 'Excellent (25+ MPG)'\n",
    "\n",
    "        WHEN distance / fuel_consumed >= 20 THEN 'Good (20-24 MPG)'\n",
    "\n",
    "        WHEN distance / fuel_consumed >= 15 THEN 'Average (15-19 MPG)'\n",
    "\n",
    "        WHEN distance / fuel_consumed >= 10 THEN 'Poor (10-14 MPG)'\n",
    "\n",
    "        ELSE 'Very Poor (<10 MPG)'\n",
    "\n",
    "    END\n",
    "\n",
    "ORDER BY avg_mpg DESC\n",
    "\n",
    "\"\"\")\n",
    "\n",
    "\n",
    "\n",
    "fuel_analysis.show()\n",
    "\n",
    "\n",
    "# Monthly operational trends\n",
    "\n",
    "print(\"\\n=== Monthly Operational Trends ===\")\n",
    "\n",
    "monthly_trends = spark.sql(\"\"\"\n",
    "\n",
    "SELECT DATE_FORMAT(trip_date, 'yyyy-MM') as month,\n",
    "\n",
    "       COUNT(*) as total_trips,\n",
    "\n",
    "       ROUND(SUM(distance), 2) as monthly_distance,\n",
    "\n",
    "       ROUND(SUM(fuel_consumed), 2) as monthly_fuel,\n",
    "\n",
    "       ROUND(AVG(load_factor), 2) as avg_load_factor,\n",
    "\n",
    "       COUNT(DISTINCT vehicle_id) as active_vehicles\n",
    "\n",
    "FROM transportation.analytics.fleet_trips\n",
    "\n",
    "GROUP BY DATE_FORMAT(trip_date, 'yyyy-MM')\n",
    "\n",
    "ORDER BY month\n",
    "\n",
    "\"\"\")\n",
    "\n",
    "\n",
    "\n",
    "monthly_trends.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 7: Train Transportation Predictive Maintenance Model\n",
    "\n",
    "### Machine Learning for Transportation Business Improvement\n",
    "\n",
    "Now we'll train a machine learning model to predict vehicle maintenance needs. This model can help transportation companies:\n",
    "\n",
    "- **Prevent costly breakdowns** by predicting maintenance requirements\n",
    "- **Optimize maintenance schedules** to reduce downtime\n",
    "- **Reduce operational costs** through preventive maintenance\n",
    "- **Improve fleet reliability** and customer satisfaction\n",
    "\n",
    "### Model Approach\n",
    "\n",
    "We'll use a **Random Forest Classifier** to predict vehicle maintenance needs based on:\n",
    "\n",
    "- Usage patterns (distance, duration, frequency)\n",
    "- Performance metrics (fuel efficiency, load factors)\n",
    "- Operational patterns (routes, timing)\n",
    "- Historical maintenance indicators\n",
    "\n",
    "### Business Impact\n",
    "\n",
    "- **Cost Reduction**: Predictive maintenance prevents expensive repairs\n",
    "- **Uptime Improvement**: Scheduled maintenance reduces unexpected breakdowns\n",
    "- **Safety Enhancement**: Proactive maintenance improves vehicle reliability\n",
    "- **Operational Efficiency**: Optimized maintenance scheduling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Created maintenance features for 500 vehicles\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "+-----------------+-----+\n",
       "|needs_maintenance|count|\n",
       "+-----------------+-----+\n",
       "|                1|  500|\n",
       "+-----------------+-----+\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Prepare data for machine learning - create maintenance prediction labels and features\n",
    "\n",
    "from pyspark.ml.feature import StringIndexer, VectorAssembler, StandardScaler\n",
    "from pyspark.ml.classification import RandomForestClassifier\n",
    "from pyspark.ml.evaluation import BinaryClassificationEvaluator\n",
    "from pyspark.ml import Pipeline\n",
    "import pyspark.sql.functions as F\n",
    "\n",
    "# Create vehicle-level features for maintenance prediction\n",
    "vehicle_features = spark.sql(\"\"\"\n",
    "SELECT \n",
    "    vehicle_id,\n",
    "    COUNT(*) as total_trips,\n",
    "    ROUND(SUM(distance), 2) as total_distance,\n",
    "    ROUND(SUM(duration), 2) as total_duration,\n",
    "    ROUND(SUM(fuel_consumed), 2) as total_fuel,\n",
    "    ROUND(AVG(distance / fuel_consumed), 2) as avg_mpg,\n",
    "    ROUND(AVG(load_factor), 2) as avg_load_factor,\n",
    "    ROUND(STDDEV(distance / fuel_consumed), 2) as mpg_variability,\n",
    "    COUNT(DISTINCT route_id) as routes_used,\n",
    "    COUNT(DISTINCT DATE(trip_date)) as active_days,\n",
    "    ROUND(AVG(HOUR(trip_date)), 2) as avg_trip_hour,\n",
    "    -- Simulate maintenance need based on poor performance and high usage\n",
    "    CASE WHEN \n",
    "        SUM(distance) > 50000 OR \n",
    "        AVG(distance / fuel_consumed) < 12 OR \n",
    "        STDDEV(distance / fuel_consumed) > 5 \n",
    "    THEN 1 ELSE 0 END as needs_maintenance\n",
    "FROM transportation.analytics.fleet_trips\n",
    "GROUP BY vehicle_id\n",
    "\"\"\")\n",
    "\n",
    "print(f\"Created maintenance features for {vehicle_features.count()} vehicles\")\n",
    "vehicle_features.groupBy(\"needs_maintenance\").count().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Training set: 426 vehicles\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Test set: 74 vehicles\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Feature engineering for maintenance prediction\n",
    "\n",
    "# Assemble features for the model\n",
    "feature_cols = [\"total_trips\", \"total_distance\", \"total_duration\", \"total_fuel\", \n",
    "                \"avg_mpg\", \"avg_load_factor\", \"mpg_variability\", \"routes_used\", \n",
    "                \"active_days\", \"avg_trip_hour\"]\n",
    "\n",
    "assembler = VectorAssembler(\n",
    "    inputCols=feature_cols,\n",
    "    outputCol=\"features\"\n",
    ")\n",
    "\n",
    "# Scale features\n",
    "scaler = StandardScaler(inputCol=\"features\", outputCol=\"scaled_features\")\n",
    "\n",
    "# Create and train the model\n",
    "rf = RandomForestClassifier(\n",
    "    labelCol=\"needs_maintenance\", \n",
    "    featuresCol=\"scaled_features\",\n",
    "    numTrees=100,\n",
    "    maxDepth=10\n",
    ")\n",
    "\n",
    "# Create pipeline\n",
    "pipeline = Pipeline(stages=[assembler, scaler, rf])\n",
    "\n",
    "# Split data\n",
    "train_data, test_data = vehicle_features.randomSplit([0.8, 0.2], seed=42)\n",
    "\n",
    "print(f\"Training set: {train_data.count()} vehicles\")\n",
    "print(f\"Test set: {test_data.count()} vehicles\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Training predictive maintenance model...\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Model AUC: 1.0000\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "+----------+--------------+-------+-----------------+----------+-----------+\n",
       "|vehicle_id|total_distance|avg_mpg|needs_maintenance|prediction|probability|\n",
       "+----------+--------------+-------+-----------------+----------+-----------+\n",
       "|    VH0003|       5655.51|   6.66|                1|       1.0|  [0.0,1.0]|\n",
       "|    VH0007|      10356.33|   6.95|                1|       1.0|  [0.0,1.0]|\n",
       "|    VH0009|       6850.42|   6.37|                1|       1.0|  [0.0,1.0]|\n",
       "|    VH0014|       3834.42|   6.47|                1|       1.0|  [0.0,1.0]|\n",
       "|    VH0020|       4130.25|   6.41|                1|       1.0|  [0.0,1.0]|\n",
       "|    VH0024|       5783.52|   6.72|                1|       1.0|  [0.0,1.0]|\n",
       "|    VH0030|        9194.1|   6.71|                1|       1.0|  [0.0,1.0]|\n",
       "|    VH0036|       9575.87|   7.53|                1|       1.0|  [0.0,1.0]|\n",
       "|    VH0046|        9860.2|    6.9|                1|       1.0|  [0.0,1.0]|\n",
       "|    VH0047|      10596.09|   6.93|                1|       1.0|  [0.0,1.0]|\n",
       "+----------+--------------+-------+-----------------+----------+-----------+\n",
       "only showing top 10 rows\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "+-----------------+----------+-----+\n",
       "|needs_maintenance|prediction|count|\n",
       "+-----------------+----------+-----+\n",
       "|                1|       1.0|   74|\n",
       "+-----------------+----------+-----+\n",
       "\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Train the predictive maintenance model\n",
    "\n",
    "print(\"Training predictive maintenance model...\")\n",
    "model = pipeline.fit(train_data)\n",
    "\n",
    "# Make predictions\n",
    "predictions = model.transform(test_data)\n",
    "\n",
    "# Evaluate the model\n",
    "evaluator = BinaryClassificationEvaluator(labelCol=\"needs_maintenance\", metricName=\"areaUnderROC\")\n",
    "auc = evaluator.evaluate(predictions)\n",
    "\n",
    "print(f\"Model AUC: {auc:.4f}\")\n",
    "\n",
    "# Show prediction results\n",
    "predictions.select(\"vehicle_id\", \"total_distance\", \"avg_mpg\", \"needs_maintenance\", \"prediction\", \"probability\").show(10)\n",
    "\n",
    "# Calculate confusion matrix\n",
    "confusion_matrix = predictions.groupBy(\"needs_maintenance\", \"prediction\").count()\n",
    "confusion_matrix.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "=== Feature Importance for Maintenance Prediction ===\n",
       "total_trips: 0.0000\n",
       "total_distance: 0.0000\n",
       "total_duration: 0.0000\n",
       "total_fuel: 0.0000\n",
       "avg_mpg: 0.0000\n",
       "avg_load_factor: 0.0000\n",
       "mpg_variability: 0.0000\n",
       "routes_used: 0.0000\n",
       "active_days: 0.0000\n",
       "avg_trip_hour: 0.0000\n",
       "\n",
       "=== Business Impact Analysis ===\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "Total test vehicles: 74\n",
       "Vehicles predicted to need maintenance: 74\n",
       "Percentage flagged for maintenance: 100.0%\n",
       "\n",
       "Estimated cost per maintenance event: $2,500\n",
       "Potential annual savings from preventive maintenance: $111,000\n",
       "\n",
       "Estimated daily revenue per vehicle: $800\n",
       "Value of prevented downtime: $177,600\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "\n",
       "Model Performance:\n",
       "Accuracy: 1.0000\n",
       "Precision: 1.0000\n",
       "Recall: 1.0000\n",
       "AUC: 1.0000\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Model interpretation and business insights\n",
    "\n",
    "# Feature importance (approximate)\n",
    "rf_model = model.stages[-1]\n",
    "feature_importance = rf_model.featureImportances\n",
    "feature_names = feature_cols\n",
    "\n",
    "print(\"=== Feature Importance for Maintenance Prediction ===\")\n",
    "for name, importance in zip(feature_names, feature_importance):\n",
    "    print(f\"{name}: {importance:.4f}\")\n",
    "\n",
    "# Business impact analysis\n",
    "print(\"\\n=== Business Impact Analysis ===\")\n",
    "\n",
    "# Calculate potential impact of predictive maintenance\n",
    "maintenance_predictions = predictions.filter(\"prediction = 1\")\n",
    "vehicles_needing_maintenance = maintenance_predictions.count()\n",
    "total_test_vehicles = test_data.count()\n",
    "\n",
    "print(f\"Total test vehicles: {total_test_vehicles}\")\n",
    "print(f\"Vehicles predicted to need maintenance: {vehicles_needing_maintenance}\")\n",
    "print(f\"Percentage flagged for maintenance: {(vehicles_needing_maintenance/total_test_vehicles)*100:.1f}%\")\n",
    "\n",
    "# Calculate cost savings potential\n",
    "avg_maintenance_cost = 2500  # Estimated cost per maintenance event\n",
    "preventive_maintenance_savings = 0.6  # 60% cost reduction with preventive maintenance\n",
    "\n",
    "potential_savings = vehicles_needing_maintenance * avg_maintenance_cost * preventive_maintenance_savings\n",
    "\n",
    "print(f\"\\nEstimated cost per maintenance event: ${avg_maintenance_cost:,}\")\n",
    "print(f\"Potential annual savings from preventive maintenance: ${potential_savings:,.0f}\")\n",
    "\n",
    "# Fleet reliability improvement\n",
    "avg_downtime_days = 3  # Average downtime per breakdown\n",
    "avg_daily_revenue = 800  # Average daily revenue per vehicle\n",
    "prevented_downtime_value = vehicles_needing_maintenance * avg_downtime_days * avg_daily_revenue\n",
    "\n",
    "print(f\"\\nEstimated daily revenue per vehicle: ${avg_daily_revenue}\")\n",
    "print(f\"Value of prevented downtime: ${prevented_downtime_value:,.0f}\")\n",
    "\n",
    "# Accuracy metrics\n",
    "accuracy = predictions.filter(\"needs_maintenance = prediction\").count() / predictions.count()\n",
    "precision = predictions.filter(\"prediction = 1 AND needs_maintenance = 1\").count() / predictions.filter(\"prediction = 1\").count() if predictions.filter(\"prediction = 1\").count() > 0 else 0\n",
    "recall = predictions.filter(\"prediction = 1 AND needs_maintenance = 1\").count() / predictions.filter(\"needs_maintenance = 1\").count() if predictions.filter(\"needs_maintenance = 1\").count() > 0 else 0\n",
    "\n",
    "print(f\"\\nModel Performance:\")\n",
    "print(f\"Accuracy: {accuracy:.4f}\")\n",
    "print(f\"Precision: {precision:.4f}\")\n",
    "print(f\"Recall: {recall:.4f}\")\n",
    "print(f\"AUC: {auc:.4f}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Key Takeaways: Delta Liquid Clustering + ML in AIDP\n",
    "\n",
    "### What We Demonstrated\n",
    "\n",
    "1. **Automatic Optimization**: Created a table with `CLUSTER BY (vehicle_id, trip_date)` and let Delta automatically optimize data layout\n",
    "\n",
    "2. **Performance Benefits**: Queries on clustered columns (vehicle_id, trip_date) are significantly faster due to data locality\n",
    "\n",
    "3. **Zero Maintenance**: No manual partitioning, bucketing, or Z-Ordering required - Delta handles it automatically\n",
    "\n",
    "4. **Machine Learning Integration**: Trained a predictive maintenance model using the optimized data\n",
    "\n",
    "5. **Real-World Use Case**: Transportation analytics where fleet monitoring and route optimization are critical\n",
    "\n",
    "### AIDP Advantages\n",
    "\n",
    "- **Unified Analytics**: Seamlessly integrates data optimization with ML\n",
    "- **Governance**: Catalog and schema isolation for transportation data\n",
    "- **Performance**: Optimized for both analytical queries and ML training\n",
    "- **Scalability**: Handles transportation-scale data volumes effortlessly\n",
    "\n",
    "### Business Benefits for Transportation\n",
    "\n",
    "1. **Cost Reduction**: Predictive maintenance prevents expensive breakdowns\n",
    "2. **Uptime Improvement**: Scheduled maintenance reduces unexpected downtime\n",
    "3. **Safety Enhancement**: Proactive maintenance improves vehicle reliability\n",
    "4. **Operational Efficiency**: Optimized maintenance scheduling and route planning\n",
    "5. **Revenue Protection**: Minimized lost revenue from vehicle downtime\n",
    "\n",
    "### Best Practices for Transportation Analytics\n",
    "\n",
    "1. **Choose clustering columns** based on your most common query patterns\n",
    "2. **Start with 1-4 columns** - too many can reduce effectiveness\n",
    "3. **Consider cardinality** - high-cardinality columns work best\n",
    "4. **Monitor and adjust** as query patterns evolve\n",
    "5. **Combine with ML** for predictive analytics and automation\n",
    "\n",
    "### Next Steps\n",
    "\n",
    "- Explore other AIDP ML features like AutoML\n",
    "- Try liquid clustering with different column combinations\n",
    "- Scale up to larger transportation datasets\n",
    "- Integrate with real GPS tracking and IoT sensor data\n",
    "- Deploy models for real-time predictive maintenance\n",
    "\n",
    "This notebook demonstrates how Oracle AI Data Platform makes advanced transportation analytics accessible while maintaining enterprise-grade performance and governance."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}