store-sales-forecasting-pysparkmllib.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7eb3b0db",
   "metadata": {
    "papermill": {
     "duration": 0.010964,
     "end_time": "2023-01-02T11:14:28.754074",
     "exception": false,
     "start_time": "2023-01-02T11:14:28.743110",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "**Store Sales Forecasting** an ongoing Kaggle competition. This note book is about the ML and predictive modeling of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4f1f2c78",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:14:28.776519Z",
     "iopub.status.busy": "2023-01-02T11:14:28.775322Z",
     "iopub.status.idle": "2023-01-02T11:15:34.228144Z",
     "shell.execute_reply": "2023-01-02T11:15:34.226874Z"
    },
    "papermill": {
     "duration": 65.46755,
     "end_time": "2023-01-02T11:15:34.231637",
     "exception": false,
     "start_time": "2023-01-02T11:14:28.764087",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pyspark\r\n",
      "  Downloading pyspark-3.3.1.tar.gz (281.4 MB)\r\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m281.4/281.4 MB\u001b[0m \u001b[31m3.3 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\r\n",
      "\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25l-\b \bdone\r\n",
      "\u001b[?25hCollecting py4j==0.10.9.5\r\n",
      "  Downloading py4j-0.10.9.5-py2.py3-none-any.whl (199 kB)\r\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m199.7/199.7 kB\u001b[0m \u001b[31m13.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\r\n",
      "\u001b[?25hBuilding wheels for collected packages: pyspark\r\n",
      "  Building wheel for pyspark (setup.py) ... \u001b[?25l-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \b-\b \b\\\b \b|\b \b/\b \bdone\r\n",
      "\u001b[?25h  Created wheel for pyspark: filename=pyspark-3.3.1-py2.py3-none-any.whl size=281845513 sha256=7508aae9ca99287c658e1f18a8fd3e330429c11c732f5aaa86d83f276fc7ffd0\r\n",
      "  Stored in directory: /root/.cache/pip/wheels/42/59/f5/79a5bf931714dcd201b26025347785f087370a10a3329a899c\r\n",
      "Successfully built pyspark\r\n",
      "Installing collected packages: py4j, pyspark\r\n",
      "  Attempting uninstall: py4j\r\n",
      "    Found existing installation: py4j 0.10.9.7\r\n",
      "    Uninstalling py4j-0.10.9.7:\r\n",
      "      Successfully uninstalled py4j-0.10.9.7\r\n",
      "Successfully installed py4j-0.10.9.5 pyspark-3.3.1\r\n",
      "\u001b[33mWARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv\u001b[0m\u001b[33m\r\n",
      "\u001b[0m"
     ]
    }
   ],
   "source": [
    "! pip install pyspark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "0e2e2b39",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:34.312120Z",
     "iopub.status.busy": "2023-01-02T11:15:34.311664Z",
     "iopub.status.idle": "2023-01-02T11:15:34.377457Z",
     "shell.execute_reply": "2023-01-02T11:15:34.376051Z"
    },
    "papermill": {
     "duration": 0.110332,
     "end_time": "2023-01-02T11:15:34.380790",
     "exception": false,
     "start_time": "2023-01-02T11:15:34.270458",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pyspark\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "91472037",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:34.456714Z",
     "iopub.status.busy": "2023-01-02T11:15:34.456308Z",
     "iopub.status.idle": "2023-01-02T11:15:34.462660Z",
     "shell.execute_reply": "2023-01-02T11:15:34.461254Z"
    },
    "papermill": {
     "duration": 0.046968,
     "end_time": "2023-01-02T11:15:34.465252",
     "exception": false,
     "start_time": "2023-01-02T11:15:34.418284",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#lets assign var names to the source files for easy references\n",
    "\n",
    "holidays = '/kaggle/input/store-sales-time-series-forecasting/holidays_events.csv'\n",
    "oil = '/kaggle/input/store-sales-time-series-forecasting/oil.csv'\n",
    "stores = '/kaggle/input/store-sales-time-series-forecasting/stores.csv'\n",
    "train = '/kaggle/input/store-sales-time-series-forecasting/train.csv'\n",
    "txn = '/kaggle/input/store-sales-time-series-forecasting/transactions.csv'\n",
    "#We wont be needing those for quite some time\n",
    "test = '/kaggle/input/store-sales-time-series-forecasting/test.csv'\n",
    "sample = '/kaggle/input/store-sales-time-series-forecasting/sample_submission.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "eba975ac",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:34.540552Z",
     "iopub.status.busy": "2023-01-02T11:15:34.539861Z",
     "iopub.status.idle": "2023-01-02T11:15:41.730547Z",
     "shell.execute_reply": "2023-01-02T11:15:41.728962Z"
    },
    "papermill": {
     "duration": 7.231951,
     "end_time": "2023-01-02T11:15:41.734058",
     "exception": false,
     "start_time": "2023-01-02T11:15:34.502107",
     "status": "completed"
    },
    "tags": []
   },
   "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"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "23/01/02 11:15:38 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n"
     ]
    }
   ],
   "source": [
    "#starting the spark session and getting the database setup.\n",
    "\n",
    "spark = SparkSession.builder.appName('sales_fc').getOrCreate()\n",
    "sparkql= spark.sql\n",
    "sparkreader = spark.read"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "db6ad0c7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:41.813048Z",
     "iopub.status.busy": "2023-01-02T11:15:41.812035Z",
     "iopub.status.idle": "2023-01-02T11:15:45.157991Z",
     "shell.execute_reply": "2023-01-02T11:15:45.156126Z"
    },
    "papermill": {
     "duration": 3.388912,
     "end_time": "2023-01-02T11:15:45.160917",
     "exception": false,
     "start_time": "2023-01-02T11:15:41.772005",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------+------------------------------------+\n",
      "|key                    |value                               |\n",
      "+-----------------------+------------------------------------+\n",
      "|spark.sql.warehouse.dir|file:/kaggle/working/spark-warehouse|\n",
      "+-----------------------+------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sparkql(\"SET spark.sql.warehouse.dir\").show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d030583a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:45.240274Z",
     "iopub.status.busy": "2023-01-02T11:15:45.239015Z",
     "iopub.status.idle": "2023-01-02T11:15:45.493371Z",
     "shell.execute_reply": "2023-01-02T11:15:45.492045Z"
    },
    "papermill": {
     "duration": 0.297195,
     "end_time": "2023-01-02T11:15:45.496753",
     "exception": false,
     "start_time": "2023-01-02T11:15:45.199558",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#creating local database, even though not having hive file system\n",
    "sparkql(\"CREATE DATABASE IF NOT EXISTS sales_forecast\")\n",
    "sparkql(\"USE sales_forecast\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2c6f9b9f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:45.581996Z",
     "iopub.status.busy": "2023-01-02T11:15:45.581456Z",
     "iopub.status.idle": "2023-01-02T11:15:55.733104Z",
     "shell.execute_reply": "2023-01-02T11:15:55.731834Z"
    },
    "papermill": {
     "duration": 10.193963,
     "end_time": "2023-01-02T11:15:55.737274",
     "exception": false,
     "start_time": "2023-01-02T11:15:45.543311",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "#Reading in the data\n",
    "holidays_data = sparkreader.csv(holidays,inferSchema=True,header=True)\n",
    "oil_data = sparkreader.csv(oil,inferSchema=True,header=True)\n",
    "stores_data = sparkreader.csv(stores,inferSchema=True,header=True)\n",
    "train_data = sparkreader.csv(train,inferSchema=True,header=True)\n",
    "txn_data = sparkreader.csv(txn,inferSchema=True,header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "03449942",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:55.851594Z",
     "iopub.status.busy": "2023-01-02T11:15:55.851053Z",
     "iopub.status.idle": "2023-01-02T11:15:56.271688Z",
     "shell.execute_reply": "2023-01-02T11:15:56.270380Z"
    },
    "papermill": {
     "duration": 0.482111,
     "end_time": "2023-01-02T11:15:56.274624",
     "exception": false,
     "start_time": "2023-01-02T11:15:55.792513",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "test_data = sparkreader.csv(test,inferSchema=True,header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd197e53",
   "metadata": {
    "papermill": {
     "duration": 0.0381,
     "end_time": "2023-01-02T11:15:56.352263",
     "exception": false,
     "start_time": "2023-01-02T11:15:56.314163",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Anything that is outside the database is data, once it is \n",
    "inside then it is a table. That will keep things separate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c78286c7",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:56.430511Z",
     "iopub.status.busy": "2023-01-02T11:15:56.429773Z",
     "iopub.status.idle": "2023-01-02T11:15:56.513978Z",
     "shell.execute_reply": "2023-01-02T11:15:56.512701Z"
    },
    "papermill": {
     "duration": 0.127439,
     "end_time": "2023-01-02T11:15:56.517704",
     "exception": false,
     "start_time": "2023-01-02T11:15:56.390265",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#Lets create temp views of the tables first. \n",
    "holidays_data.createOrReplaceTempView(\"holidays_table\")\n",
    "oil_data.createOrReplaceTempView(\"oil_table\")\n",
    "stores_data.createOrReplaceTempView(\"stores_table\")\n",
    "train_data.createOrReplaceTempView(\"train_table\")\n",
    "txn_data.createOrReplaceTempView(\"txn_table\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "ef7d2a78",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:56.636674Z",
     "iopub.status.busy": "2023-01-02T11:15:56.636147Z",
     "iopub.status.idle": "2023-01-02T11:15:56.656659Z",
     "shell.execute_reply": "2023-01-02T11:15:56.655323Z"
    },
    "papermill": {
     "duration": 0.083552,
     "end_time": "2023-01-02T11:15:56.659844",
     "exception": false,
     "start_time": "2023-01-02T11:15:56.576292",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "test_data.createOrReplaceTempView(\"test_table\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e5cb2ff2",
   "metadata": {
    "papermill": {
     "duration": 0.054283,
     "end_time": "2023-01-02T11:15:56.770076",
     "exception": false,
     "start_time": "2023-01-02T11:15:56.715793",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "The temp tables are dropped like the usual sql tables. sparkql(\"DROP TABLE holidays_data\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "199eae2f",
   "metadata": {
    "papermill": {
     "duration": 0.054177,
     "end_time": "2023-01-02T11:15:56.880259",
     "exception": false,
     "start_time": "2023-01-02T11:15:56.826082",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### Creating the date sequence that we want"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8cbaa10c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:56.957703Z",
     "iopub.status.busy": "2023-01-02T11:15:56.957142Z",
     "iopub.status.idle": "2023-01-02T11:15:57.198602Z",
     "shell.execute_reply": "2023-01-02T11:15:57.197239Z"
    },
    "papermill": {
     "duration": 0.284287,
     "end_time": "2023-01-02T11:15:57.202300",
     "exception": false,
     "start_time": "2023-01-02T11:15:56.918013",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "data_date_series = holidays_data.select(date_trunc(\"mm\", max(to_date(\"date\", \"dd/MM/yyyy\"))).\\\n",
    "            alias(\"max_date\"),\n",
    "            date_trunc(\"mm\", min(to_date(\"date\", \"dd/MM/yyyy\"))). \\\n",
    "            alias(\"min_date\")). \\\n",
    "    select(expr(\"sequence(min_date, max_date, interval 1 day)\").alias(\"date_seq\")). \\\n",
    "        withColumn(\"date_new\",explode(\"date_seq\")). \\\n",
    "        withColumn(\"date_form\",date_format(\"date_new\", \"yyyy-MM-dd\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f050ff70",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:57.322913Z",
     "iopub.status.busy": "2023-01-02T11:15:57.322394Z",
     "iopub.status.idle": "2023-01-02T11:15:58.299469Z",
     "shell.execute_reply": "2023-01-02T11:15:58.298162Z"
    },
    "papermill": {
     "duration": 1.037867,
     "end_time": "2023-01-02T11:15:58.302945",
     "exception": false,
     "start_time": "2023-01-02T11:15:57.265078",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+-------------------+\n",
      "|           max_date|           min_date|\n",
      "+-------------------+-------------------+\n",
      "|2017-12-01 00:00:00|2012-03-01 00:00:00|\n",
      "+-------------------+-------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "holidays_data.select(date_trunc(\"mm\", max(to_date(\"date\", \"yyyy-mm-dd\"))).\\\n",
    "            alias(\"max_date\"),\n",
    "            date_trunc(\"mm\", min(to_date(\"date\", \"yyyy-mm-dd\"))). \\\n",
    "            alias(\"min_date\")).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f288ba21",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:58.423903Z",
     "iopub.status.busy": "2023-01-02T11:15:58.423358Z",
     "iopub.status.idle": "2023-01-02T11:15:58.439833Z",
     "shell.execute_reply": "2023-01-02T11:15:58.438805Z"
    },
    "papermill": {
     "duration": 0.078073,
     "end_time": "2023-01-02T11:15:58.442594",
     "exception": false,
     "start_time": "2023-01-02T11:15:58.364521",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "date_series=data_date_series.drop(\"date_seq\",\"date_new\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a68795cc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:58.525075Z",
     "iopub.status.busy": "2023-01-02T11:15:58.524672Z",
     "iopub.status.idle": "2023-01-02T11:15:59.002931Z",
     "shell.execute_reply": "2023-01-02T11:15:59.001864Z"
    },
    "papermill": {
     "duration": 0.521748,
     "end_time": "2023-01-02T11:15:59.006256",
     "exception": false,
     "start_time": "2023-01-02T11:15:58.484508",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(date_form='2017-11-30'), Row(date_form='2017-12-01')]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_series.tail(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "d9d5ac2c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:59.090181Z",
     "iopub.status.busy": "2023-01-02T11:15:59.089365Z",
     "iopub.status.idle": "2023-01-02T11:15:59.415451Z",
     "shell.execute_reply": "2023-01-02T11:15:59.414155Z"
    },
    "papermill": {
     "duration": 0.369613,
     "end_time": "2023-01-02T11:15:59.419285",
     "exception": false,
     "start_time": "2023-01-02T11:15:59.049672",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2102"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_series.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "24205b8d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:59.537956Z",
     "iopub.status.busy": "2023-01-02T11:15:59.536857Z",
     "iopub.status.idle": "2023-01-02T11:15:59.557248Z",
     "shell.execute_reply": "2023-01-02T11:15:59.555704Z"
    },
    "papermill": {
     "duration": 0.082586,
     "end_time": "2023-01-02T11:15:59.561092",
     "exception": false,
     "start_time": "2023-01-02T11:15:59.478506",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "date_series.createOrReplaceTempView('date_table')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d1bea4c2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:15:59.669796Z",
     "iopub.status.busy": "2023-01-02T11:15:59.669160Z",
     "iopub.status.idle": "2023-01-02T11:16:00.014424Z",
     "shell.execute_reply": "2023-01-02T11:16:00.012546Z"
    },
    "papermill": {
     "duration": 0.399933,
     "end_time": "2023-01-02T11:16:00.019089",
     "exception": false,
     "start_time": "2023-01-02T11:15:59.619156",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1218"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * from oil_table\"\"\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e05a5b5c",
   "metadata": {
    "_kg_hide-input": true,
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:00.245963Z",
     "iopub.status.busy": "2023-01-02T11:16:00.245210Z",
     "iopub.status.idle": "2023-01-02T11:16:00.379731Z",
     "shell.execute_reply": "2023-01-02T11:16:00.378474Z"
    },
    "papermill": {
     "duration": 0.199154,
     "end_time": "2023-01-02T11:16:00.383872",
     "exception": false,
     "start_time": "2023-01-02T11:16:00.184718",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#Resorting to the Temp view creation route instead\n",
    "sparkql(\"\"\" SELECT date_form, COALESCE(dcoilwtico,0) as dcoilwtico\n",
    "        FROM date_table dt LEFT JOIN oil_table ot\n",
    "        ON dt.date_form = ot.date\"\"\"). \\\n",
    "    createOrReplaceTempView('full_oil_table')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "d54fab14",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:00.511249Z",
     "iopub.status.busy": "2023-01-02T11:16:00.510532Z",
     "iopub.status.idle": "2023-01-02T11:16:01.511665Z",
     "shell.execute_reply": "2023-01-02T11:16:01.510410Z"
    },
    "papermill": {
     "duration": 1.064808,
     "end_time": "2023-01-02T11:16:01.516234",
     "exception": false,
     "start_time": "2023-01-02T11:16:00.451426",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2102"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * \n",
    "            FROM full_oil_table\"\"\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "be2aa640",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:01.618871Z",
     "iopub.status.busy": "2023-01-02T11:16:01.618447Z",
     "iopub.status.idle": "2023-01-02T11:16:02.075188Z",
     "shell.execute_reply": "2023-01-02T11:16:02.073585Z"
    },
    "papermill": {
     "duration": 0.502965,
     "end_time": "2023-01-02T11:16:02.077806",
     "exception": false,
     "start_time": "2023-01-02T11:16:01.574841",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(date_form='2017-11-22', dcoilwtico=0.0),\n",
       " Row(date_form='2017-11-23', dcoilwtico=0.0),\n",
       " Row(date_form='2017-11-24', dcoilwtico=0.0),\n",
       " Row(date_form='2017-11-25', dcoilwtico=0.0),\n",
       " Row(date_form='2017-11-26', dcoilwtico=0.0),\n",
       " Row(date_form='2017-11-27', dcoilwtico=0.0),\n",
       " Row(date_form='2017-11-28', dcoilwtico=0.0),\n",
       " Row(date_form='2017-11-29', dcoilwtico=0.0),\n",
       " Row(date_form='2017-11-30', dcoilwtico=0.0),\n",
       " Row(date_form='2017-12-01', dcoilwtico=0.0)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * FROM full_oil_table\"\"\").tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ab045523",
   "metadata": {
    "_kg_hide-input": true,
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:02.182384Z",
     "iopub.status.busy": "2023-01-02T11:16:02.181919Z",
     "iopub.status.idle": "2023-01-02T11:16:02.298826Z",
     "shell.execute_reply": "2023-01-02T11:16:02.297282Z"
    },
    "papermill": {
     "duration": 0.163813,
     "end_time": "2023-01-02T11:16:02.302618",
     "exception": false,
     "start_time": "2023-01-02T11:16:02.138805",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "sparkql(\"\"\" SELECT ot.date_form, COALESCE(ht.type,'Working') as type, \n",
    "        COALESCE(ht.locale,'National') as locale,\n",
    "        COALESCE(ht.locale_name,'National') as locale_name,\n",
    "        COALESCE(ht.transferred,false) as transferred,\n",
    "        ot.dcoilwtico\n",
    "        FROM holidays_table ht RIGHT JOIN full_oil_table ot\n",
    "        ON date_format(ht.date,'yyyy-MM-dd') = ot.date_form\n",
    "\"\"\").createOrReplaceTempView('full_oil_with_holidays')\n",
    "## The tables are joining"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "4eacaeff",
   "metadata": {
    "_kg_hide-input": true,
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:02.421235Z",
     "iopub.status.busy": "2023-01-02T11:16:02.420663Z",
     "iopub.status.idle": "2023-01-02T11:16:03.300143Z",
     "shell.execute_reply": "2023-01-02T11:16:03.298998Z"
    },
    "papermill": {
     "duration": 0.944465,
     "end_time": "2023-01-02T11:16:03.304846",
     "exception": false,
     "start_time": "2023-01-02T11:16:02.360381",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-------+--------+-----------+-----------+----------+\n",
      "| date_form|   type|  locale|locale_name|transferred|dcoilwtico|\n",
      "+----------+-------+--------+-----------+-----------+----------+\n",
      "|2012-03-01|Working|National|   National|      false|       0.0|\n",
      "|2012-03-02|Holiday|   Local|      Manta|      false|       0.0|\n",
      "+----------+-------+--------+-----------+-----------+----------+\n",
      "only showing top 2 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * \n",
    "            FROM full_oil_with_holidays\"\"\").show(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8731d08c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:03.424380Z",
     "iopub.status.busy": "2023-01-02T11:16:03.423790Z",
     "iopub.status.idle": "2023-01-02T11:16:03.876043Z",
     "shell.execute_reply": "2023-01-02T11:16:03.874703Z"
    },
    "papermill": {
     "duration": 0.515816,
     "end_time": "2023-01-02T11:16:03.879741",
     "exception": false,
     "start_time": "2023-01-02T11:16:03.363925",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2138"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * \n",
    "            FROM full_oil_with_holidays\"\"\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "738fb03f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:03.995874Z",
     "iopub.status.busy": "2023-01-02T11:16:03.995466Z",
     "iopub.status.idle": "2023-01-02T11:16:04.025636Z",
     "shell.execute_reply": "2023-01-02T11:16:04.024308Z"
    },
    "papermill": {
     "duration": 0.09173,
     "end_time": "2023-01-02T11:16:04.030066",
     "exception": false,
     "start_time": "2023-01-02T11:16:03.938336",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "sparkql(\"\"\" SELECT tt.id, date_format(tt.date,'yyyy-MM-dd') as date,\n",
    "            tt.store_nbr, tt.family, \n",
    "            tt.sales, tt.onpromotion\n",
    "        FROM train_table tt\"\"\").createOrReplaceTempView('full_train_table')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "93d8b889",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:04.110841Z",
     "iopub.status.busy": "2023-01-02T11:16:04.110434Z",
     "iopub.status.idle": "2023-01-02T11:16:04.139287Z",
     "shell.execute_reply": "2023-01-02T11:16:04.137592Z"
    },
    "papermill": {
     "duration": 0.072914,
     "end_time": "2023-01-02T11:16:04.142948",
     "exception": false,
     "start_time": "2023-01-02T11:16:04.070034",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "sparkql(\"\"\" SELECT te.id, date_format(te.date,'yyyy-MM-dd') as date,\n",
    "            te.store_nbr, te.family, te.onpromotion\n",
    "        FROM test_table te\"\"\").createOrReplaceTempView('full_test_table')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "526faa37",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:04.263261Z",
     "iopub.status.busy": "2023-01-02T11:16:04.262818Z",
     "iopub.status.idle": "2023-01-02T11:16:05.555054Z",
     "shell.execute_reply": "2023-01-02T11:16:05.553827Z"
    },
    "papermill": {
     "duration": 1.357933,
     "end_time": "2023-01-02T11:16:05.558485",
     "exception": false,
     "start_time": "2023-01-02T11:16:04.200552",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "3000888"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * FROM full_train_table\"\"\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "82ca10a8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:05.679925Z",
     "iopub.status.busy": "2023-01-02T11:16:05.679367Z",
     "iopub.status.idle": "2023-01-02T11:16:05.904500Z",
     "shell.execute_reply": "2023-01-02T11:16:05.903280Z"
    },
    "papermill": {
     "duration": 0.286481,
     "end_time": "2023-01-02T11:16:05.907158",
     "exception": false,
     "start_time": "2023-01-02T11:16:05.620677",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28512"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * FROM full_test_table\"\"\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b96e58ac",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:05.988195Z",
     "iopub.status.busy": "2023-01-02T11:16:05.987810Z",
     "iopub.status.idle": "2023-01-02T11:16:06.047893Z",
     "shell.execute_reply": "2023-01-02T11:16:06.046678Z"
    },
    "papermill": {
     "duration": 0.104923,
     "end_time": "2023-01-02T11:16:06.051659",
     "exception": false,
     "start_time": "2023-01-02T11:16:05.946736",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# table short form used is ftt. and table created is train_store_oil_table\n",
    "sparkql(\"\"\" SELECT ftt.id, ftt.date,ftt.store_nbr,ftt.family,\n",
    "            ftt.sales, ftt.onpromotion, st.city, st.state,st.type,\n",
    "            st.cluster,fot.dcoilwtico, fot.type, fot.locale,fot.locale_name,\n",
    "            fot.transferred\n",
    "        FROM full_train_table ftt JOIN stores_table st\n",
    "        on ftt.store_nbr = st.store_nbr\n",
    "        LEFT JOIN full_oil_with_holidays fot\n",
    "        on fot.date_form = ftt.date\"\"\"). \\\n",
    "        createOrReplaceTempView(\"train_store_oil_table\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "b91b1530",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:06.151501Z",
     "iopub.status.busy": "2023-01-02T11:16:06.150138Z",
     "iopub.status.idle": "2023-01-02T11:16:18.827377Z",
     "shell.execute_reply": "2023-01-02T11:16:18.825975Z"
    },
    "papermill": {
     "duration": 12.721949,
     "end_time": "2023-01-02T11:16:18.830689",
     "exception": false,
     "start_time": "2023-01-02T11:16:06.108740",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[Row(id=3000886, date='2017-08-15', store_nbr=9, family='SCHOOL AND OFFICE SUPPLIES', sales=121.0, onpromotion=8, city='Quito', state='Pichincha', type='B', cluster=6, dcoilwtico=47.57, type='Holiday', locale='Local', locale_name='Riobamba', transferred=False),\n",
       " Row(id=3000887, date='2017-08-15', store_nbr=9, family='SEAFOOD', sales=16.0, onpromotion=0, city='Quito', state='Pichincha', type='B', cluster=6, dcoilwtico=47.57, type='Holiday', locale='Local', locale_name='Riobamba', transferred=False)]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * FROM train_store_oil_table\n",
    "                    ORDER BY date\"\"\").tail(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "b923bb3b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:18.917515Z",
     "iopub.status.busy": "2023-01-02T11:16:18.916632Z",
     "iopub.status.idle": "2023-01-02T11:16:26.683517Z",
     "shell.execute_reply": "2023-01-02T11:16:26.682058Z"
    },
    "papermill": {
     "duration": 7.818457,
     "end_time": "2023-01-02T11:16:26.692286",
     "exception": false,
     "start_time": "2023-01-02T11:16:18.873829",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Stage 76:>                                                         (0 + 4) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+----------+---------+----------+-----+-----------+-----+---------+----+-------+----------+-------+--------+-----------+-----------+\n",
      "| id|      date|store_nbr|    family|sales|onpromotion| city|    state|type|cluster|dcoilwtico|   type|  locale|locale_name|transferred|\n",
      "+---+----------+---------+----------+-----+-----------+-----+---------+----+-------+----------+-------+--------+-----------+-----------+\n",
      "|  2|2013-01-01|        1|    BEAUTY|  0.0|          0|Quito|Pichincha|   D|     13|       0.0|Holiday|National|    Ecuador|      false|\n",
      "|  0|2013-01-01|        1|AUTOMOTIVE|  0.0|          0|Quito|Pichincha|   D|     13|       0.0|Holiday|National|    Ecuador|      false|\n",
      "+---+----------+---------+----------+-----+-----------+-----+---------+----+-------+----------+-------+--------+-----------+-----------+\n",
      "only showing top 2 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * FROM train_store_oil_table\n",
    "                    ORDER BY date\"\"\").show(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "47125ff3",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:26.781279Z",
     "iopub.status.busy": "2023-01-02T11:16:26.780863Z",
     "iopub.status.idle": "2023-01-02T11:16:26.822763Z",
     "shell.execute_reply": "2023-01-02T11:16:26.821386Z"
    },
    "papermill": {
     "duration": 0.086874,
     "end_time": "2023-01-02T11:16:26.826142",
     "exception": false,
     "start_time": "2023-01-02T11:16:26.739268",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# table short form used is ftt. and table created is test_store_oil_table\n",
    "# There is no sales column.\n",
    "sparkql(\"\"\" SELECT ftt.id, ftt.date,ftt.store_nbr,ftt.family,\n",
    "            ftt.onpromotion, st.city, st.state,st.type,\n",
    "            st.cluster,fot.dcoilwtico, fot.type, fot.locale,fot.locale_name,\n",
    "            fot.transferred\n",
    "        FROM full_test_table ftt JOIN stores_table st\n",
    "        on ftt.store_nbr = st.store_nbr\n",
    "        LEFT JOIN full_oil_with_holidays fot\n",
    "        on fot.date_form = ftt.date\"\"\"). \\\n",
    "        createOrReplaceTempView(\"test_store_oil_table\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "dbb53eb9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:26.912132Z",
     "iopub.status.busy": "2023-01-02T11:16:26.911410Z",
     "iopub.status.idle": "2023-01-02T11:16:32.986729Z",
     "shell.execute_reply": "2023-01-02T11:16:32.985353Z"
    },
    "papermill": {
     "duration": 6.121992,
     "end_time": "2023-01-02T11:16:32.990114",
     "exception": false,
     "start_time": "2023-01-02T11:16:26.868122",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "3054348"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT tsot.*\n",
    "            FROM train_store_oil_table tsot\"\"\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "5cecd838",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:33.091888Z",
     "iopub.status.busy": "2023-01-02T11:16:33.091472Z",
     "iopub.status.idle": "2023-01-02T11:16:34.176823Z",
     "shell.execute_reply": "2023-01-02T11:16:34.175448Z"
    },
    "papermill": {
     "duration": 1.134766,
     "end_time": "2023-01-02T11:16:34.180676",
     "exception": false,
     "start_time": "2023-01-02T11:16:33.045910",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+----------+---------+----------+-----------+-----+---------+----+-------+----------+-------+--------+-----------+-----------+\n",
      "|     id|      date|store_nbr|    family|onpromotion| city|    state|type|cluster|dcoilwtico|   type|  locale|locale_name|transferred|\n",
      "+-------+----------+---------+----------+-----------+-----+---------+----+-------+----------+-------+--------+-----------+-----------+\n",
      "|3000888|2017-08-16|        1|AUTOMOTIVE|          0|Quito|Pichincha|   D|     13|      46.8|Working|National|   National|      false|\n",
      "|3000890|2017-08-16|        1|    BEAUTY|          2|Quito|Pichincha|   D|     13|      46.8|Working|National|   National|      false|\n",
      "+-------+----------+---------+----------+-----------+-----+---------+----+-------+----------+-------+--------+-----------+-----------+\n",
      "only showing top 2 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT tsot.*\n",
    "            FROM test_store_oil_table tsot\n",
    "                ORDER BY date\"\"\").show(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "415285a2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:34.309960Z",
     "iopub.status.busy": "2023-01-02T11:16:34.309421Z",
     "iopub.status.idle": "2023-01-02T11:16:35.080880Z",
     "shell.execute_reply": "2023-01-02T11:16:35.079396Z"
    },
    "papermill": {
     "duration": 0.840697,
     "end_time": "2023-01-02T11:16:35.084775",
     "exception": false,
     "start_time": "2023-01-02T11:16:34.244078",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28512"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT tsot.*\n",
    "            FROM test_store_oil_table tsot\n",
    "                ORDER BY date\"\"\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "6e40a03c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:35.195005Z",
     "iopub.status.busy": "2023-01-02T11:16:35.193549Z",
     "iopub.status.idle": "2023-01-02T11:16:35.274436Z",
     "shell.execute_reply": "2023-01-02T11:16:35.272984Z"
    },
    "papermill": {
     "duration": 0.130915,
     "end_time": "2023-01-02T11:16:35.278001",
     "exception": false,
     "start_time": "2023-01-02T11:16:35.147086",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "sparkql(\"\"\"SELECT tsot.*, \n",
    "            COALESCE(DATE_FORMAT(txt.date,'yyyy-MM-dd'),tsot.date) as txn_date,\n",
    "            COALESCE(txt.transactions,0) as store_txns \n",
    "            FROM train_store_oil_table tsot LEFT JOIN txn_table txt\n",
    "            on tsot.date = date_format(txt.date,'yyyy-MM-dd')\n",
    "            and tsot.store_nbr = txt.store_nbr\n",
    "        \"\"\").createOrReplaceTempView(\"all_data_joined_train_data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "43a584fc",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:35.371297Z",
     "iopub.status.busy": "2023-01-02T11:16:35.370877Z",
     "iopub.status.idle": "2023-01-02T11:16:35.752507Z",
     "shell.execute_reply": "2023-01-02T11:16:35.751189Z"
    },
    "papermill": {
     "duration": 0.42795,
     "end_time": "2023-01-02T11:16:35.755420",
     "exception": false,
     "start_time": "2023-01-02T11:16:35.327470",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(date=datetime.datetime(2017, 8, 15, 0, 0), store_nbr=53, transactions=932),\n",
       " Row(date=datetime.datetime(2017, 8, 15, 0, 0), store_nbr=54, transactions=802)]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * FROM txn_table\"\"\").tail(2)\n",
    "\n",
    "# There will be no txn data for the test_data time interval. So no need to join that \n",
    "# table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "c8a66b69",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:35.843772Z",
     "iopub.status.busy": "2023-01-02T11:16:35.842383Z",
     "iopub.status.idle": "2023-01-02T11:16:35.921863Z",
     "shell.execute_reply": "2023-01-02T11:16:35.920514Z"
    },
    "papermill": {
     "duration": 0.126956,
     "end_time": "2023-01-02T11:16:35.924860",
     "exception": false,
     "start_time": "2023-01-02T11:16:35.797904",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "sparkql(\"\"\"SELECT tsot.*, \n",
    "            COALESCE(DATE_FORMAT(txt.date,'yyyy-MM-dd'),tsot.date) as txn_date,\n",
    "            COALESCE(txt.transactions,0) as store_txns \n",
    "            FROM test_store_oil_table tsot LEFT JOIN txn_table txt\n",
    "            on tsot.date = date_format(txt.date,'yyyy-MM-dd')\n",
    "            and tsot.store_nbr = txt.store_nbr\n",
    "        \"\"\").createOrReplaceTempView(\"all_data_joined_train_data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "2feba078",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-01-02T11:16:36.020847Z",
     "iopub.status.busy": "2023-01-02T11:16:36.020394Z",
     "iopub.status.idle": "2023-01-02T11:16:37.323608Z",
     "shell.execute_reply": "2023-01-02T11:16:37.319839Z"
    },
    "papermill": {
     "duration": 1.362168,
     "end_time": "2023-01-02T11:16:37.337356",
     "exception": false,
     "start_time": "2023-01-02T11:16:35.975188",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+----------+---------+----------+-----------+-----+---------+----+-------+----------+-------+--------+-----------+-----------+----------+----------+\n",
      "|     id|      date|store_nbr|    family|onpromotion| city|    state|type|cluster|dcoilwtico|   type|  locale|locale_name|transferred|  txn_date|store_txns|\n",
      "+-------+----------+---------+----------+-----------+-----+---------+----+-------+----------+-------+--------+-----------+-----------+----------+----------+\n",
      "|3000888|2017-08-16|        1|AUTOMOTIVE|          0|Quito|Pichincha|   D|     13|      46.8|Working|National|   National|      false|2017-08-16|         0|\n",
      "|3000889|2017-08-16|        1| BABY CARE|          0|Quito|Pichincha|   D|     13|      46.8|Working|National|   National|      false|2017-08-16|         0|\n",
      "+-------+----------+---------+----------+-----------+-----+---------+----+-------+----------+-------+--------+-----------+-----------+----------+----------+\n",
      "only showing top 2 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "sparkql(\"\"\"SELECT * FROM all_data_joined_train_data adj\"\"\").show(2)"
   ]
  }
 ],
 "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.7.12"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 140.185688,
   "end_time": "2023-01-02T11:16:40.031259",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2023-01-02T11:14:19.845571",
   "version": "2.3.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}