{
 "cells": [
  {
   "cell_type": "code",
   "metadata": {
    "cell_style": "center"
   },
   "outputs": [],
   "source": [
    "# Copyright 2026 Google LLC\n",
    "#\n",
    "# Licensed under the Apache License, Version 2.0 (the \"License\");\n",
    "# you may not use this file except in compliance with the License.\n",
    "# You may obtain a copy of the License at\n",
    "#\n",
    "#     https://www.apache.org/licenses/LICENSE-2.0\n",
    "#\n",
    "# Unless required by applicable law or agreed to in writing, software\n",
    "# distributed under the License is distributed on an \"AS IS\" BASIS,\n",
    "# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n",
    "# See the License for the specific language governing permissions and\n",
    "# limitations under the License.\n"
   ],
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# RMI Tutorial 03: BigQuery Data Verification\n",
    "\n",
    "Once your routes are active and historical data begins to accumulate, you can access this information directly in BigQuery via your **Linked Dataset**.\n",
    "\n",
    "### Objectives:\n",
    "1. **Locate** your RMI linked dataset.\n",
    "2. **Verify** your registered routes in the `routes_status` table.\n",
    "3. **Query** historical traffic data using BigQuery magics.\n",
    "4. **Analyze** performance metrics like the Travel Time Index (TTI)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[![Open In Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/googlemaps-samples/insights-samples/blob/main/roads_management_insights/tutorials/03_bq_verification.ipynb) \n",
    "[![Open In Colab Enterprise](https://img.shields.io/badge/Open%20in-Colab%20Enterprise-blue?logo=google-cloud&logoColor=white)](https://console.cloud.google.com/vertex-ai/colab/import/https%3A%2F%2Fraw.githubusercontent.com%2Fgooglemaps-samples%2Finsights-samples%2Fmain%2Froads_management_insights%2Ftutorials%2F03_bq_verification.ipynb) \n",
    "[![Open In BigQuery Notebooks](https://img.shields.io/badge/Open%20in-BigQuery%20Notebooks-blue?logo=google-cloud&logoColor=white)](https://console.cloud.google.com/bigquery/import?url=https%3A%2F%2Fraw.githubusercontent.com%2Fgooglemaps-samples%2Finsights-samples%2Fmain%2Froads_management_insights%2Ftutorials%2F03_bq_verification.ipynb) \n",
    "[![View on GitHub](https://img.shields.io/badge/View%20on-GitHub-lightgrey?logo=github&logoColor=white)](https://github.com/googlemaps-samples/insights-samples/blob/main/roads_management_insights/tutorials/03_bq_verification.ipynb)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from google.colab import auth\n",
    "auth.authenticate_user()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Environment Setup\n",
    "Set your Project ID and the standard RMI dataset name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "PROJECT_ID = 'YOUR_PROJECT_ID' # @param {type:\"string\"}\n",
    "DATASET_ID = 'historical_roads_data' # @param {type:\"string\"}\n",
    "os.environ['PROJECT_ID'] = PROJECT_ID\n",
    "os.environ['DATASET_ID'] = DATASET_ID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Check Route Status\n",
    "The `routes_status` table contains your route definitions and their current RMI state. We use the `%%bigquery` magic for direct SQL execution."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "SELECT \n",
    "  selected_route_id, \n",
    "  status, \n",
    "  validation_error \n",
    "FROM `historical_roads_data.routes_status` \n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Query Historical Performance\n",
    "Using the **Gold Standard** pattern: temporal filtering, geometry type checks, and joining with status for attributes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "DECLARE start_date DEFAULT TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR);\n",
    "\n",
    "WITH base_history AS (\n",
    "  SELECT\n",
    "    h.selected_route_id,\n",
    "    h.record_time,\n",
    "    h.duration_in_seconds,\n",
    "    h.static_duration_in_seconds,\n",
    "    s.route_attributes\n",
    "  FROM `historical_roads_data.historical_travel_time` h\n",
    "  JOIN `historical_roads_data.routes_status` s\n",
    "    ON h.selected_route_id = s.selected_route_id\n",
    "  WHERE h.record_time >= start_date\n",
    "    -- Early Path Integrity Filter\n",
    "    AND ST_GEOMETRYTYPE(h.route_geometry) = 'ST_LineString'\n",
    ")\n",
    "SELECT\n",
    "  *,\n",
    "  -- Metric Calculation (Travel Time Index)\n",
    "  SAFE_DIVIDE(duration_in_seconds, static_duration_in_seconds) as travel_time_index\n",
    "FROM base_history\n",
    "ORDER BY record_time DESC\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Visualize Traffic Trends\n",
    "Finally, let's plot the **Travel Time Index (Ratio)** over time to see congestion patterns. We'll use `plotly` for a quick interactive chart."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery df_trends --project $PROJECT_ID\n",
    "DECLARE start_date DEFAULT TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR);\n",
    "\n",
    "SELECT\n",
    "  record_time,\n",
    "  selected_route_id,\n",
    "  SAFE_DIVIDE(duration_in_seconds, static_duration_in_seconds) as travel_time_index\n",
    "FROM `historical_roads_data.historical_travel_time` \n",
    "WHERE record_time >= start_date\n",
    "ORDER BY record_time ASC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "\n",
    "fig = px.line(df_trends, x='record_time', y='travel_time_index', color='selected_route_id',\n",
    "              title='Travel Time Index Over Last 24 Hours',\n",
    "              labels={'travel_time_index': 'Congestion Ratio (Actual/Static)', 'record_time': 'Time (UTC)'})\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Weekly Congestion Heatmap\n",
    "Let's look at long-term trends by analyzing the average congestion ratio by **Hour of Day** and **Day of Week** over the last 30 days."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery df_heatmap --project $PROJECT_ID\n",
    "DECLARE start_date DEFAULT TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);\n",
    "\n",
    "SELECT\n",
    "  EXTRACT(DAYOFWEEK FROM record_time) as day_of_week,\n",
    "  EXTRACT(HOUR FROM record_time) as hour_of_day,\n",
    "  AVG(SAFE_DIVIDE(duration_in_seconds, static_duration_in_seconds)) as avg_tti\n",
    "FROM `historical_roads_data.historical_travel_time` \n",
    "WHERE record_time >= start_date\n",
    "GROUP BY 1, 2\n",
    "ORDER BY 1, 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.graph_objects as go\n",
    "\n",
    "days = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']\n",
    "\n",
    "# Pivot the data for the heatmap\n",
    "pivot_df = df_heatmap.pivot(index='day_of_week', columns='hour_of_day', values='avg_tti')\n",
    "\n",
    "fig = go.Figure(data=go.Heatmap(\n",
    "        z=pivot_df.values,\n",
    "        x=pivot_df.columns,\n",
    "        y=[days[i-1] for i in pivot_df.index],\n",
    "        colorscale='Viridis',\n",
    "        hoverongaps=False))\n",
    "\n",
    "fig.update_layout(\n",
    "    title='Average Congestion Heatmap (Last 30 Days)',\n",
    "    xaxis_title='Hour of Day (UTC)',\n",
    "    yaxis_title='Day of Week')\n",
    "\n",
    "fig.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Audit Table Partitions\n",
    "To manage costs and understand data retention, you can audit the physical partitions of your RMI tables using the `INFORMATION_SCHEMA`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID$PROJECT_ID\n",
    "SELECT\n",
    "  table_schema as dataset,\n",
    "  table_name as table, \n",
    "  partition_id, \n",
    "  total_rows, \n",
    "  total_logical_bytes, \n",
    "  total_billable_bytes, \n",
    "  last_modified_time, \n",
    "  storage_tier\n",
    "FROM\n",
    "  `historical_roads_data.INFORMATION_SCHEMA.PARTITIONS`\n",
    "ORDER BY 1, 2, 3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Advanced Partition Pruning\n",
    "For maximum efficiency, you can dynamically identify the latest available partition before running your main query. This is the most cost-effective way to audit the freshest RMI data.\n"
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery --project $PROJECT_ID\n",
    "DECLARE last_partition TIMESTAMP;\n",
    "\n",
    "SET last_partition = (\n",
    "  SELECT\n",
    "    PARSE_TIMESTAMP(\"%Y%m%d\", MAX(partition_id))\n",
    "  FROM\n",
    "    `historical_roads_data.INFORMATION_SCHEMA.PARTITIONS`\n",
    "  WHERE\n",
    "    table_name = \"historical_travel_time\"\n",
    ");\n",
    "\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  `historical_roads_data.historical_travel_time`\n",
    "WHERE\n",
    "  record_time >= last_partition\n",
    "LIMIT 10\n"
   ],
   "execution_count": null
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Summary & Next Steps\n",
    "You have successfully verified your RMI data in BigQuery using native magics.\n",
    "\n",
    "**Next**: In the next tutorial, we will explore real-time data flow via **Pub/Sub**.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "**For more advanced RMI query patterns, visit the official [RMI Sample Queries Repository](https://github.com/googlemaps-samples/insights-samples/tree/main/roads_management_insights/rmi_sample_queries).**"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}