lec2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c8dca847-54af-4284-97d8-0682e88a6e8d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n",
      "25/03/12 16:10:35 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "spark = (SparkSession.builder.appName(\"cs544\")\n",
    "         .master(\"spark://boss:7077\")\n",
    "         .config(\"spark.executor.memory\", \"2G\")\n",
    "         .config(\"spark.sql.warehouse.dir\", \"hdfs://nn:9000/user/hive/warehouse\")\n",
    "         .enableHiveSupport()\n",
    "         .getOrCreate())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2294e4e0-ab19-496c-980f-31df757e7837",
   "metadata": {},
   "outputs": [],
   "source": [
    "!hdfs dfs -cp sf.csv hdfs://nn:9000/sf.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cb54bacc-b52a-4c25-93d2-2ba0f61de9b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "df = (spark.read.format(\"csv\")\n",
    "      .option(\"header\", True)\n",
    "      .option(\"inferSchema\", True)\n",
    "      .load(\"hdfs://nn:9000/sf.csv\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c1298818-83f6-444b-b8a0-4be5b16fd6fb",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "23/10/27 01:43:57 WARN SparkStringUtils: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.\n",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import col, expr\n",
    "cols = [col(c).alias(c.replace(\" \", \"_\")) for c in df.columns]\n",
    "df.select(cols).write.format(\"parquet\").save(\"hdfs://nn:9000/sf.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37d1ded3-ed8a-4e39-94cb-dd3a3272af91",
   "metadata": {},
   "outputs": [],
   "source": [
    "!hdfs dfs -rm hdfs://nn:9000/sf.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "abea48b5-e012-4ae2-a53a-e40350f94e20",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                "
     ]
    }
   ],
   "source": [
    "df = spark.read.format(\"parquet\").load(\"hdfs://nn:9000/sf.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "bff0f592-1ba2-4812-b09d-16a3b169d41a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[Call_Number: int, Unit_ID: string, Incident_Number: int, Call_Type: string, Call_Date: string, Watch_Date: string, Received_DtTm: string, Entry_DtTm: string, Dispatch_DtTm: string, Response_DtTm: string, On_Scene_DtTm: string, Transport_DtTm: string, Hospital_DtTm: string, Call_Final_Disposition: string, Available_DtTm: string, Address: string, City: string, Zipcode_of_Incident: int, Battalion: string, Station_Area: string, Box: string, Original_Priority: string, Priority: string, Final_Priority: int, ALS_Unit: boolean, Call_Type_Group: string, Number_of_Alarms: int, Unit_Type: string, Unit_sequence_in_call_dispatch: int, Fire_Prevention_District: string, Supervisor_District: string, Neighborhooods_-_Analysis_Boundaries: string, RowID: string, case_location: string, Analysis_Neighborhoods: int]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "7f6d52b9-f3a9-4448-bcd3-86fd3aac4611",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.withColumnRenamed(\"Neighborhooods_-_Analysis_Boundaries\", \"area\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "be5964cf-a72c-4f4c-9aff-f85f8e90c5c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.createOrReplaceTempView(\"calls\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "49606e81-7d7a-405d-b38e-6e798c4cf2a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Call_Number</th>\n",
       "      <th>Unit_ID</th>\n",
       "      <th>Incident_Number</th>\n",
       "      <th>Call_Type</th>\n",
       "      <th>Call_Date</th>\n",
       "      <th>Watch_Date</th>\n",
       "      <th>Received_DtTm</th>\n",
       "      <th>Entry_DtTm</th>\n",
       "      <th>Dispatch_DtTm</th>\n",
       "      <th>Response_DtTm</th>\n",
       "      <th>...</th>\n",
       "      <th>Call_Type_Group</th>\n",
       "      <th>Number_of_Alarms</th>\n",
       "      <th>Unit_Type</th>\n",
       "      <th>Unit_sequence_in_call_dispatch</th>\n",
       "      <th>Fire_Prevention_District</th>\n",
       "      <th>Supervisor_District</th>\n",
       "      <th>Neighborhooods_-_Analysis_Boundaries</th>\n",
       "      <th>RowID</th>\n",
       "      <th>case_location</th>\n",
       "      <th>Analysis_Neighborhoods</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",