spark-sql-1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a6d373a1-9457-4537-98bc-c2860b0fbd5e",
   "metadata": {},
   "source": [
    "# Ejemplo de Spark SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "908cc574-d7e6-4719-9b12-da1029e5a710",
   "metadata": {},
   "source": [
    "El objetivo de este notebook es presentar un ejemplo sobre el API de SQL, aplicando queries sobre los archivos <code>flights.csv</code>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2ca26249-c1f1-4546-98e9-a389783ce249",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cb911177-705f-403a-8d6d-060aa79d9d3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = SparkSession.builder.appName(\"App_spark_Sql\").getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bff4cfa6-44fa-473d-95d4-09f39dbc1a55",
   "metadata": {},
   "outputs": [],
   "source": [
    "flights = spark.read.option(\"header\",True).csv(\"./data/flights.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c26f8883-3b39-48a3-b7da-7f80eb56a319",
   "metadata": {},
   "source": [
    "Para el uso de la API estructurada, es primordial registrar todos los DataFrames que queramos usar commo tablas o vistas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4d941ff8-1deb-4a53-8c14-8b2d0ae8161a",
   "metadata": {},
   "outputs": [],
   "source": [
    "flights.createOrReplaceTempView(\"flights\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e4f15d30-1cee-42e0-96af-253e9162047d",
   "metadata": {},
   "source": [
    "Calcular los vuelos que llegan a cada aeropuerto de destino usando sentencia SQL "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "374678e8-eed8-4df1-ba3f-22d75d98ce87",
   "metadata": {},
   "outputs": [],
   "source": [
    "fl_dest_count = spark.sql(\"\"\"SELECT dest,count(dest) as dest_count \n",
    "                            FROM  flights GROUP BY dest ORDER BY dest_count DESC\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b3f42c00-0132-454a-ba24-cd7d63bb45d6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[dest: string, dest_count: bigint]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fl_dest_count"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb59e01d-183b-4d01-926c-8f9f2b5ea836",
   "metadata": {},
   "source": [
    "Nuevamente registramos el resultado como una vista. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f0461758-7d6a-4b51-a4f3-d999f1850019",
   "metadata": {},
   "outputs": [],
   "source": [
    "fl_dest_count.createOrReplaceTempView(\"flights_dest_count\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "222a6119-0fbc-458d-a864-e7dd5ab81283",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+----------+\n",
      "|dest|dest_count|\n",
      "+----+----------+\n",
      "| ORD|     17283|\n",
      "| ATL|     17215|\n",
      "| LAX|     16174|\n",
      "| BOS|     15508|\n",
      "| MCO|     14082|\n",
      "+----+----------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "fl_dest_count.show(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edb61107-58c3-4ee4-817d-82fb7f5184e1",
   "metadata": {},
   "source": [
    "Ahora cargaremos los datos del archivo de aeropuertos "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "55484340-eb34-4d4f-b60e-1ad50882552a",
   "metadata": {},
   "outputs": [],
   "source": [
    "airports_df = spark.read.option(\"header\",True).csv(\"./data/airport-codes.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "9a3bf284-6f47-47a9-ac07-3582d51dd8d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "airports_df.createOrReplaceTempView(\"airports\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fac3ad57-11fe-4d3b-b29b-75bcb2cf381d",
   "metadata": {},
   "source": [
    "Ejecutar una Unión (join) en Spark SQL  es muy similar a SQL:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "0758bf40-6054-4798-96f3-f50e6add363a",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql =\"\"\"SELECT\n",
    "            a.name,\n",
    "            f.dest_count \n",
    "        FROM   flights_dest_count f \n",
    "        JOIN airports a on a.iata_code = f.dest\n",
    "        ORDER BY dest_count DESC\n",
    "\"\"\"\n",
    "\n",
    "fl_dest_airports = spark.sql(sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5a1ec41b-e955-414f-98d7-6c4a2a051804",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+----------+\n",
      "|                name|dest_count|\n",
      "+--------------------+----------+\n",
      "|Chicago O'Hare In...|     17283|\n",
      "|Hartsfield Jackso...|     17215|\n",
      "|Los Angeles Inter...|     16174|\n",
      "|General Edward La...|     15508|\n",
      "|Orlando Internati...|     14082|\n",
      "|Charlotte Douglas...|     14064|\n",
      "|San Francisco Int...|     13331|\n",
      "|Fort Lauderdale H...|     12055|\n",
      "|Miami Internation...|     11728|\n",
      "|Ronald Reagan Was...|      9705|\n",
      "+--------------------+----------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "fl_dest_airports.show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e871a55-791b-4bdf-82f7-b9c75bf8cf07",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}