Spark-SQL.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "6fdec79e-9823-4add-927c-a48411db873b",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# 1. Load the given data file and create a Spark data frame.\n",
    "\n",
    "# fire_df = spark.read\\ #spark is spark session object which is entry point to spark programming APIs this .read gives DFs reader\n",
    "#     .format(\"csv\")\\ # these below all are attributes of DFs reader\n",
    "#         .option(\"header\",\"true\")\\ # data comes with header\n",
    "#             .option(\"inferSchema\",\"true\")\\ # DF should infer schema from data file itself\n",
    "#                 .load(\"/databricks-datasets/learning-spark-v2/sf-fire/sf-fire-calls.csv\") # load file from given location\n",
    "fire_df = spark.read\\\n",
    "    .format(\"csv\")\\\n",
    "        .option(\"header\",\"true\")\\\n",
    "            .option(\"inferSchema\",\"true\")\\\n",
    "                .load(\"/databricks-datasets/learning-spark-v2/sf-fire/sf-fire-calls.csv\") \n",
    "\n",
    "# Another way -\n",
    "# fire_df = spark.read\\\n",
    "#     .csv(\"/databricks-datasets/learning-spark-v2/sf-fire/sf-fire-calls.csv\", header='true', inferSchema='true')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "9ea8825d-a896-4eba-90bc-7ff9e06bff64",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "#fire_df.show()\n",
    "display(fire_df) # to get data in structured table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "46da72d0-c31b-4ca6-b051-1a2cd8207069",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "fire_df.createGlobalTempView(\"fireview\") #converts dataframe to table view"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "9abb0e0b-bea7-4cfa-ab85-86fa1ec715f6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "SELECT * FROM global_temp.fireview -- # temp tables are created in global database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "9d0522b0-0b7a-4257-8b24-2f3f421babc9",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "-- # 1. How many distinct types of calls were made to the fire department?\n",
    "SELECT COUNT(DISTINCT CallType) FROM global_temp.fireview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "dbf39905-0707-4d85-b08a-061af962f745",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "-- # 2. What are distinct types of calls made to the fire department?\n",
    "SELECT DISTINCT CallType FROM global_temp.fireview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "67704be1-e975-4db3-8d14-7c431c88ea72",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "-- # 3. Find out all responses or delayed times greater than 5 mins?\n",
    "SELECT * FROM global_temp.fireview WHERE Delay > 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "97bcc81f-5c78-4cc4-bd50-1709527caf48",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "-- # 4. What were the most common call types?\n",
    "SELECT CallType,COUNT(CallType) AS cnt FROM global_temp.fireview GROUP BY CallType ORDER BY cnt DESC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "024858aa-627b-424c-9eb0-26c41dec7bbc",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "-- # 5. What zip codes accounted for the most common calls?\n",
    "SELECT `Zipcode of Incident`,COUNT(*) AS cnt FROM global_temp.fireview GROUP BY `Zipcode of Incident` ORDER BY cnt DESC "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "fad9fb7e-81bc-4019-8c31-f6ce407e3cc3",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "-- # 6.What San Francisco neighborhoods are in the zip codes 94102 and 94103\n",
    "SELECT Neighborhood FROM global_temp.fireview WHERE `Zipcode of Incident` = 94102 OR `Zipcode of Incident`= 94103 AND City='SF'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "dff66380-ac31-49a8-a074-6bf83f9cc124",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "-- # 7. What was the sum of all calls, average, min, and max of the call response times?\n",
    "SELECT SUM(Delay) AS Sum_of_Calls, AVG(Delay) As Avg_Delay, MIN(Delay) As Min_Delay, MAX(Delay) AS Max_Delay FROM global_temp.fireview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "f037a91e-7e55-42bc-addc-a10d0e669c23",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "-- # 8. How many distinct years of data are in the CSV file?\n",
    "SELECT COUNT(DISTINCT YEAR(`Call Date`)) FROM global_temp.fireview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "63fb6327-f1c4-4269-80d0-ec108c464190",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "-- # 9. What week of the year in 2018 had the most fire calls?\n",
    "SELECT EXTRACT(WEEK FROM `Call Date`) AS week_of_year, COUNT(*) AS call_count\n",
    "FROM global_temp.fireview\n",
    "WHERE EXTRACT(YEAR FROM `Call Date`) = 2018\n",
    "GROUP BY EXTRACT(WEEK FROM `Call Date`)\n",
    "ORDER BY call_count DESC\n",
    "LIMIT 1;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "a8e35d78-013b-4b6c-b649-6bc1cdee9771",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "-- # 10. What neighborhoods in San Francisco had the worst response time in 2018?\n",
    "SELECT Neighborhood, AVG(Delay) AS Avg_response_time\n",
    "FROM global_temp.fireview\n",
    "WHERE EXTRACT(YEAR FROM `Call Date`) = 2018\n",
    "  AND Neighborhood IS NOT NULL\n",
    "GROUP BY Neighborhood\n",
    "ORDER BY Avg_response_time DESC;"
   ]
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "environmentMetadata": null,
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 1189576252987062,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 4
   },
   "notebookName": "Spark-SQL",
   "widgets": {}
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}