project_1021_002.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "%matplotlib notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession.builder \\\n",
    "        .master(\"local\") \\\n",
    "        .appName(\"SalesPrediction\") \\\n",
    "        .getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pyspark\n",
    "sc = pyspark.SparkContext(appName=\"sales_prediction\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'pyspark' from '/usr/local/opt/apache-spark/libexec/python/pyspark/__init__.py'>"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pyspark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "holiday_events = spark.read.csv(\"holidays_events.csv\", header=True)\n",
    "items = spark.read.csv(\"items.csv\", header=True)\n",
    "oil = spark.read.csv(\"oil.csv\", header=True)\n",
    "stores = spark.read.csv(\"stores.csv\", header=True)\n",
    "test = spark.read.csv(\"test.csv\", header=True)\n",
    "train = spark.read.csv(\"train.csv\", header=True)\n",
    "transactions = spark.read.csv(\"transactions.csv\", header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# holiday_events.createOrReplaceTempView(\"holiday_events_sql\")\n",
    "# items.createOrReplaceTempView(\"items_sql\")\n",
    "# oil.createOrReplaceTempView(\"oil_sql\")\n",
    "# stores.createOrReplaceTempView(\"stores_sql\")\n",
    "# #test.createOrReplaceTempView(\"test_sql\")\n",
    "# train.createOrReplaceTempView(\"train_sql\")\n",
    "# transactions.createOrReplaceTempView(\"transactions_sql\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+----------+---------+--------+-----------+\n",
      "| id|      date|store_nbr|item_nbr|onpromotion|\n",
      "+---+----------+---------+--------+-----------+\n",
      "|  0|2013-01-01|       25|  103665|       null|\n",
      "|  1|2013-01-01|       25|  105574|       null|\n",
      "|  2|2013-01-01|       25|  105575|       null|\n",
      "|  3|2013-01-01|       25|  108079|       null|\n",
      "|  4|2013-01-01|       25|  108701|       null|\n",
      "|  5|2013-01-01|       25|  108786|       null|\n",
      "|  6|2013-01-01|       25|  108797|       null|\n",
      "|  7|2013-01-01|       25|  108952|       null|\n",
      "|  8|2013-01-01|       25|  111397|       null|\n",
      "|  9|2013-01-01|       25|  114790|       null|\n",
      "| 10|2013-01-01|       25|  114800|       null|\n",
      "| 11|2013-01-01|       25|  115267|       null|\n",
      "| 12|2013-01-01|       25|  115611|       null|\n",
      "| 13|2013-01-01|       25|  115693|       null|\n",
      "| 14|2013-01-01|       25|  115720|       null|\n",
      "| 15|2013-01-01|       25|  115850|       null|\n",
      "| 16|2013-01-01|       25|  115891|       null|\n",
      "| 17|2013-01-01|       25|  115892|       null|\n",
      "| 18|2013-01-01|       25|  115894|       null|\n",
      "| 19|2013-01-01|       25|  119024|       null|\n",
      "+---+----------+---------+--------+-----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "train_without_target = train.select(\"id\", 'date', 'store_nbr', 'item_nbr', 'onpromotion')\n",
    "train_without_target.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import lit\n",
    "train_without_target = train_without_target.withColumn('source', lit('train'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+----------+---------+--------+-----------+------+\n",
      "| id|      date|store_nbr|item_nbr|onpromotion|source|\n",
      "+---+----------+---------+--------+-----------+------+\n",
      "|  0|2013-01-01|       25|  103665|       null| train|\n",
      "|  1|2013-01-01|       25|  105574|       null| train|\n",
      "|  2|2013-01-01|       25|  105575|       null| train|\n",
      "|  3|2013-01-01|       25|  108079|       null| train|\n",
      "|  4|2013-01-01|       25|  108701|       null| train|\n",
      "|  5|2013-01-01|       25|  108786|       null| train|\n",
      "|  6|2013-01-01|       25|  108797|       null| train|\n",
      "|  7|2013-01-01|       25|  108952|       null| train|\n",
      "|  8|2013-01-01|       25|  111397|       null| train|\n",
      "|  9|2013-01-01|       25|  114790|       null| train|\n",
      "| 10|2013-01-01|       25|  114800|       null| train|\n",
      "| 11|2013-01-01|       25|  115267|       null| train|\n",
      "| 12|2013-01-01|       25|  115611|       null| train|\n",
      "| 13|2013-01-01|       25|  115693|       null| train|\n",
      "| 14|2013-01-01|       25|  115720|       null| train|\n",
      "| 15|2013-01-01|       25|  115850|       null| train|\n",
      "| 16|2013-01-01|       25|  115891|       null| train|\n",
      "| 17|2013-01-01|       25|  115892|       null| train|\n",
      "| 18|2013-01-01|       25|  115894|       null| train|\n",
      "| 19|2013-01-01|       25|  119024|       null| train|\n",
      "+---+----------+---------+--------+-----------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "train_without_target.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test = test.withColumn('source', lit('test'))\n",
    "test.createOrReplaceTempView(\"test_sql\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+----------+---------+--------+-----------+------+\n",
      "|       id|      date|store_nbr|item_nbr|onpromotion|source|\n",
      "+---------+----------+---------+--------+-----------+------+\n",
      "|125497040|2017-08-16|        1|   96995|      False|  test|\n",
      "|125497041|2017-08-16|        1|   99197|      False|  test|\n",
      "|125497042|2017-08-16|        1|  103501|      False|  test|\n",
      "|125497043|2017-08-16|        1|  103520|      False|  test|\n",
      "|125497044|2017-08-16|        1|  103665|      False|  test|\n",
      "|125497045|2017-08-16|        1|  105574|      False|  test|\n",
      "|125497046|2017-08-16|        1|  105575|      False|  test|\n",
      "|125497047|2017-08-16|        1|  105576|      False|  test|\n",
      "|125497048|2017-08-16|        1|  105577|      False|  test|\n",
      "|125497049|2017-08-16|        1|  105693|      False|  test|\n",
      "|125497050|2017-08-16|        1|  105737|      False|  test|\n",
      "|125497051|2017-08-16|        1|  105857|      False|  test|\n",
      "|125497052|2017-08-16|        1|  106716|      False|  test|\n",
      "|125497053|2017-08-16|        1|  108079|      False|  test|\n",
      "|125497054|2017-08-16|        1|  108634|      False|  test|\n",
      "|125497055|2017-08-16|        1|  108696|      False|  test|\n",
      "|125497056|2017-08-16|        1|  108698|      False|  test|\n",
      "|125497057|2017-08-16|        1|  108701|       True|  test|\n",
      "|125497058|2017-08-16|        1|  108786|      False|  test|\n",
      "|125497059|2017-08-16|        1|  108797|       True|  test|\n",
      "+---------+----------+---------+--------+-----------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "test.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from pyspark.sql import functions\n",
    "train_test_set = train_without_target.union(test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+----------+---------+--------+-----------+------+\n",
      "| id|      date|store_nbr|item_nbr|onpromotion|source|\n",
      "+---+----------+---------+--------+-----------+------+\n",
      "|  0|2013-01-01|       25|  103665|       null| train|\n",
      "|  1|2013-01-01|       25|  105574|       null| train|\n",
      "|  2|2013-01-01|       25|  105575|       null| train|\n",
      "|  3|2013-01-01|       25|  108079|       null| train|\n",
      "|  4|2013-01-01|       25|  108701|       null| train|\n",
      "|  5|2013-01-01|       25|  108786|       null| train|\n",
      "|  6|2013-01-01|       25|  108797|       null| train|\n",
      "|  7|2013-01-01|       25|  108952|       null| train|\n",
      "|  8|2013-01-01|       25|  111397|       null| train|\n",
      "|  9|2013-01-01|       25|  114790|       null| train|\n",
      "| 10|2013-01-01|       25|  114800|       null| train|\n",
      "| 11|2013-01-01|       25|  115267|       null| train|\n",
      "| 12|2013-01-01|       25|  115611|       null| train|\n",
      "| 13|2013-01-01|       25|  115693|       null| train|\n",
      "| 14|2013-01-01|       25|  115720|       null| train|\n",
      "| 15|2013-01-01|       25|  115850|       null| train|\n",
      "| 16|2013-01-01|       25|  115891|       null| train|\n",
      "| 17|2013-01-01|       25|  115892|       null| train|\n",
      "| 18|2013-01-01|       25|  115894|       null| train|\n",
      "| 19|2013-01-01|       25|  119024|       null| train|\n",
      "+---+----------+---------+--------+-----------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "train_test_set.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train_test_set_002 = train_test_set.join(holiday_events, ['date'], 'outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-------+---------+--------+-----------+------+----+------+-----------+-----------+-----------+\n",
      "|      date|     id|store_nbr|item_nbr|onpromotion|source|type|locale|locale_name|description|transferred|\n",
      "+----------+-------+---------+--------+-----------+------+----+------+-----------+-----------+-----------+\n",
      "|2013-03-14|2924449|        1|  103520|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924450|        1|  103665|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924451|        1|  105574|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924452|        1|  105575|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924453|        1|  105577|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924454|        1|  105737|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924455|        1|  105857|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924456|        1|  106716|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924457|        1|  108696|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924458|        1|  108698|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924459|        1|  108701|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924460|        1|  108786|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924461|        1|  108831|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924462|        1|  108952|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924463|        1|  111223|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924464|        1|  112830|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924465|        1|  114778|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924466|        1|  114790|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924467|        1|  115611|       null| train|null|  null|       null|       null|       null|\n",
      "|2013-03-14|2924468|        1|  115720|       null| train|null|  null|       null|       null|       null|\n",
      "+----------+-------+---------+--------+-----------+------+----+------+-----------+-----------+-----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "train_test_set_002.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "109301859"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# train_test_set_002.where(train_test_set_002.locale.isNull()).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#train_test_set_002.createOrReplaceTempView(\"train_test_set_002_sql\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#spark.sql(\"select * from train_test_set_002_sql where locale is not null\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#train_test_set_002.join(items, ['item_nbr'], 'outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+----------+--------+---------+-----------+------+----+------+-----------+-----------+-----------+------------+-----+----------+\n",
      "|item_nbr|      date|      id|store_nbr|onpromotion|source|type|locale|locale_name|description|transferred|      family|class|perishable|\n",
      "+--------+----------+--------+---------+-----------+------+----+------+-----------+-----------+-----------+------------+-----+----------+\n",
      "| 1040170|2013-03-14| 2925286|        1|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2013-03-14| 2927457|        3|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2013-03-14| 2931493|        7|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2013-03-14| 2932591|        8|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2013-03-14| 2941363|       23|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2013-03-14| 2942376|       24|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2013-03-14| 2943231|       25|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2013-03-14| 2944765|       27|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-16|19016379|        1|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-16|19024345|        7|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-16|19025728|        8|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-16|19036969|       23|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-16|19038171|       24|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-16|19040188|       26|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-16|19066698|       51|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-22|19310458|        1|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-22|19331198|       23|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-22|19333438|       25|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-02-22|19360444|       51|       null| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "| 1040170|2014-05-27|24432001|        1|      False| train|null|  null|       null|       null|       null|FROZEN FOODS| 2222|         0|\n",
      "+--------+----------+--------+---------+-----------+------+----+------+-----------+-----------+-----------+------------+-----+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# train_test_set_003 = spark.sql(\"\"\"\n",
    "#         SELECT * FROM \n",
    "#         train_test_set_002_sql t\n",
    "#         LEFT JOIN items_sql i ON t.item_nbr = i.item_nbr\n",
    "# \"\"\")\n",
    "train_test_set_003 = train_test_set_002.join(items, ['item_nbr'], 'outer')\n",
    "#train_test_set_003.createOrReplaceTempView(\"train_test_set_003_sql\")\n",
    "train_test_set_003.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "ename": "Py4JJavaError",
     "evalue": "An error occurred while calling o86.showString.\n: org.apache.spark.SparkException: Job aborted due to stage failure: Task 112 in stage 23.0 failed 1 times, most recent failure: Lost task 112.0 in stage 23.0 (TID 648, localhost, executor driver): java.io.FileNotFoundException: /private/var/folders/hh/jzkw5p4s4hn28ktwmn6c4vgc0000gn/T/blockmgr-266508db-3aa4-42aa-95e1-15ae8a15902e/33/shuffle_10_112_0.index.4255a36c-0190-4707-ae1f-a80e90812b44 (No space left on device)\n\tat java.io.FileOutputStream.open0(Native Method)\n\tat java.io.FileOutputStream.open(FileOutputStream.java:270)\n\tat java.io.FileOutputStream.<init>(FileOutputStream.java:213)\n\tat java.io.FileOutputStream.<init>(FileOutputStream.java:162)\n\tat org.apache.spark.shuffle.IndexShuffleBlockResolver.writeIndexFileAndCommit(IndexShuffleBlockResolver.scala:144)\n\tat org.apache.spark.shuffle.sort.BypassMergeSortShuffleWriter.write(BypassMergeSortShuffleWriter.java:164)\n\tat org.apache.spark.scheduler.ShuffleMapTask.runTask(ShuffleMapTask.scala:96)\n\tat org.apache.spark.scheduler.ShuffleMapTask.runTask(ShuffleMapTask.scala:53)\n\tat org.apache.spark.scheduler.Task.run(Task.scala:108)\n\tat org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:335)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\n\nDriver stacktrace:\n\tat org.apache.spark.scheduler.DAGScheduler.org$apache$spark$scheduler$DAGScheduler$$failJobAndIndependentStages(DAGScheduler.scala:1499)\n\tat org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1487)\n\tat org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1486)\n\tat scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)\n\tat scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48)\n\tat org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:1486)\n\tat org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:814)\n\tat org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:814)\n\tat scala.Option.foreach(Option.scala:257)\n\tat org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:814)\n\tat org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:1714)\n\tat org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1669)\n\tat org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1658)\n\tat org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:48)\n\tat org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:630)\n\tat org.apache.spark.SparkContext.runJob(SparkContext.scala:2022)\n\tat org.apache.spark.SparkContext.runJob(SparkContext.scala:2043)\n\tat org.apache.spark.SparkContext.runJob(SparkContext.scala:2062)\n\tat org.apache.spark.sql.execution.SparkPlan.executeTake(SparkPlan.scala:336)\n\tat org.apache.spark.sql.execution.CollectLimitExec.executeCollect(limit.scala:38)\n\tat org.apache.spark.sql.Dataset.org$apache$spark$sql$Dataset$$collectFromPlan(Dataset.scala:2853)\n\tat org.apache.spark.sql.Dataset$$anonfun$head$1.apply(Dataset.scala:2153)\n\tat org.apache.spark.sql.Dataset$$anonfun$head$1.apply(Dataset.scala:2153)\n\tat org.apache.spark.sql.Dataset$$anonfun$55.apply(Dataset.scala:2837)\n\tat org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:65)\n\tat org.apache.spark.sql.Dataset.withAction(Dataset.scala:2836)\n\tat org.apache.spark.sql.Dataset.head(Dataset.scala:2153)\n\tat org.apache.spark.sql.Dataset.take(Dataset.scala:2366)\n\tat org.apache.spark.sql.Dataset.showString(Dataset.scala:245)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.lang.reflect.Method.invoke(Method.java:498)\n\tat py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)\n\tat py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)\n\tat py4j.Gateway.invoke(Gateway.java:280)\n\tat py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)\n\tat py4j.commands.CallCommand.execute(CallCommand.java:79)\n\tat py4j.GatewayConnection.run(GatewayConnection.java:214)\n\tat java.lang.Thread.run(Thread.java:748)\nCaused by: java.io.FileNotFoundException: /private/var/folders/hh/jzkw5p4s4hn28ktwmn6c4vgc0000gn/T/blockmgr-266508db-3aa4-42aa-95e1-15ae8a15902e/33/shuffle_10_112_0.index.4255a36c-0190-4707-ae1f-a80e90812b44 (No space left on device)\n\tat java.io.FileOutputStream.open0(Native Method)\n\tat java.io.FileOutputStream.open(FileOutputStream.java:270)\n\tat java.io.FileOutputStream.<init>(FileOutputStream.java:213)\n\tat java.io.FileOutputStream.<init>(FileOutputStream.java:162)\n\tat org.apache.spark.shuffle.IndexShuffleBlockResolver.writeIndexFileAndCommit(IndexShuffleBlockResolver.scala:144)\n\tat org.apache.spark.shuffle.sort.BypassMergeSortShuffleWriter.write(BypassMergeSortShuffleWriter.java:164)\n\tat org.apache.spark.scheduler.ShuffleMapTask.runTask(ShuffleMapTask.scala:96)\n\tat org.apache.spark.scheduler.ShuffleMapTask.runTask(ShuffleMapTask.scala:53)\n\tat org.apache.spark.scheduler.Task.run(Task.scala:108)\n\tat org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:335)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\t... 1 more\n",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mPy4JJavaError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-17-ecb5789e81fc>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;31m# \"\"\")\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0mtrain_test_set_004\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtrain_test_set_003\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjoin\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstores\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m'store_nbr'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'outer'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m \u001b[0mtrain_test_set_004\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/usr/local/opt/apache-spark/libexec/python/pyspark/sql/dataframe.py\u001b[0m in \u001b[0;36mshow\u001b[0;34m(self, n, truncate)\u001b[0m\n\u001b[1;32m    334\u001b[0m         \"\"\"\n\u001b[1;32m    335\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtruncate\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbool\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mtruncate\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 336\u001b[0;31m             \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshowString\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m20\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    337\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    338\u001b[0m             \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshowString\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtruncate\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/opt/apache-spark/libexec/python/lib/py4j-0.10.4-src.zip/py4j/java_gateway.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, *args)\u001b[0m\n\u001b[1;32m   1131\u001b[0m         \u001b[0manswer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgateway_client\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1132\u001b[0m         return_value = get_return_value(\n\u001b[0;32m-> 1133\u001b[0;31m             answer, self.gateway_client, self.target_id, self.name)\n\u001b[0m\u001b[1;32m   1134\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1135\u001b[0m         \u001b[0;32mfor\u001b[0m \u001b[0mtemp_arg\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mtemp_args\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/opt/apache-spark/libexec/python/pyspark/sql/utils.py\u001b[0m in \u001b[0;36mdeco\u001b[0;34m(*a, **kw)\u001b[0m\n\u001b[1;32m     61\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdeco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     62\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 63\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     64\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mpy4j\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mprotocol\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mPy4JJavaError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     65\u001b[0m             \u001b[0ms\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjava_exception\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtoString\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/opt/apache-spark/libexec/python/lib/py4j-0.10.4-src.zip/py4j/protocol.py\u001b[0m in \u001b[0;36mget_return_value\u001b[0;34m(answer, gateway_client, target_id, name)\u001b[0m\n\u001b[1;32m    317\u001b[0m                 raise Py4JJavaError(\n\u001b[1;32m    318\u001b[0m                     \u001b[0;34m\"An error occurred while calling {0}{1}{2}.\\n\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 319\u001b[0;31m                     format(target_id, \".\", name), value)\n\u001b[0m\u001b[1;32m    320\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    321\u001b[0m                 raise Py4JError(\n",
      "\u001b[0;31mPy4JJavaError\u001b[0m: An error occurred while calling o86.showString.\n: org.apache.spark.SparkException: Job aborted due to stage failure: Task 112 in stage 23.0 failed 1 times, most recent failure: Lost task 112.0 in stage 23.0 (TID 648, localhost, executor driver): java.io.FileNotFoundException: /private/var/folders/hh/jzkw5p4s4hn28ktwmn6c4vgc0000gn/T/blockmgr-266508db-3aa4-42aa-95e1-15ae8a15902e/33/shuffle_10_112_0.index.4255a36c-0190-4707-ae1f-a80e90812b44 (No space left on device)\n\tat java.io.FileOutputStream.open0(Native Method)\n\tat java.io.FileOutputStream.open(FileOutputStream.java:270)\n\tat java.io.FileOutputStream.<init>(FileOutputStream.java:213)\n\tat java.io.FileOutputStream.<init>(FileOutputStream.java:162)\n\tat org.apache.spark.shuffle.IndexShuffleBlockResolver.writeIndexFileAndCommit(IndexShuffleBlockResolver.scala:144)\n\tat org.apache.spark.shuffle.sort.BypassMergeSortShuffleWriter.write(BypassMergeSortShuffleWriter.java:164)\n\tat org.apache.spark.scheduler.ShuffleMapTask.runTask(ShuffleMapTask.scala:96)\n\tat org.apache.spark.scheduler.ShuffleMapTask.runTask(ShuffleMapTask.scala:53)\n\tat org.apache.spark.scheduler.Task.run(Task.scala:108)\n\tat org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:335)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\n\nDriver stacktrace:\n\tat org.apache.spark.scheduler.DAGScheduler.org$apache$spark$scheduler$DAGScheduler$$failJobAndIndependentStages(DAGScheduler.scala:1499)\n\tat org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1487)\n\tat org.apache.spark.scheduler.DAGScheduler$$anonfun$abortStage$1.apply(DAGScheduler.scala:1486)\n\tat scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)\n\tat scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:48)\n\tat org.apache.spark.scheduler.DAGScheduler.abortStage(DAGScheduler.scala:1486)\n\tat org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:814)\n\tat org.apache.spark.scheduler.DAGScheduler$$anonfun$handleTaskSetFailed$1.apply(DAGScheduler.scala:814)\n\tat scala.Option.foreach(Option.scala:257)\n\tat org.apache.spark.scheduler.DAGScheduler.handleTaskSetFailed(DAGScheduler.scala:814)\n\tat org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.doOnReceive(DAGScheduler.scala:1714)\n\tat org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1669)\n\tat org.apache.spark.scheduler.DAGSchedulerEventProcessLoop.onReceive(DAGScheduler.scala:1658)\n\tat org.apache.spark.util.EventLoop$$anon$1.run(EventLoop.scala:48)\n\tat org.apache.spark.scheduler.DAGScheduler.runJob(DAGScheduler.scala:630)\n\tat org.apache.spark.SparkContext.runJob(SparkContext.scala:2022)\n\tat org.apache.spark.SparkContext.runJob(SparkContext.scala:2043)\n\tat org.apache.spark.SparkContext.runJob(SparkContext.scala:2062)\n\tat org.apache.spark.sql.execution.SparkPlan.executeTake(SparkPlan.scala:336)\n\tat org.apache.spark.sql.execution.CollectLimitExec.executeCollect(limit.scala:38)\n\tat org.apache.spark.sql.Dataset.org$apache$spark$sql$Dataset$$collectFromPlan(Dataset.scala:2853)\n\tat org.apache.spark.sql.Dataset$$anonfun$head$1.apply(Dataset.scala:2153)\n\tat org.apache.spark.sql.Dataset$$anonfun$head$1.apply(Dataset.scala:2153)\n\tat org.apache.spark.sql.Dataset$$anonfun$55.apply(Dataset.scala:2837)\n\tat org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:65)\n\tat org.apache.spark.sql.Dataset.withAction(Dataset.scala:2836)\n\tat org.apache.spark.sql.Dataset.head(Dataset.scala:2153)\n\tat org.apache.spark.sql.Dataset.take(Dataset.scala:2366)\n\tat org.apache.spark.sql.Dataset.showString(Dataset.scala:245)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.lang.reflect.Method.invoke(Method.java:498)\n\tat py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)\n\tat py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)\n\tat py4j.Gateway.invoke(Gateway.java:280)\n\tat py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)\n\tat py4j.commands.CallCommand.execute(CallCommand.java:79)\n\tat py4j.GatewayConnection.run(GatewayConnection.java:214)\n\tat java.lang.Thread.run(Thread.java:748)\nCaused by: java.io.FileNotFoundException: /private/var/folders/hh/jzkw5p4s4hn28ktwmn6c4vgc0000gn/T/blockmgr-266508db-3aa4-42aa-95e1-15ae8a15902e/33/shuffle_10_112_0.index.4255a36c-0190-4707-ae1f-a80e90812b44 (No space left on device)\n\tat java.io.FileOutputStream.open0(Native Method)\n\tat java.io.FileOutputStream.open(FileOutputStream.java:270)\n\tat java.io.FileOutputStream.<init>(FileOutputStream.java:213)\n\tat java.io.FileOutputStream.<init>(FileOutputStream.java:162)\n\tat org.apache.spark.shuffle.IndexShuffleBlockResolver.writeIndexFileAndCommit(IndexShuffleBlockResolver.scala:144)\n\tat org.apache.spark.shuffle.sort.BypassMergeSortShuffleWriter.write(BypassMergeSortShuffleWriter.java:164)\n\tat org.apache.spark.scheduler.ShuffleMapTask.runTask(ShuffleMapTask.scala:96)\n\tat org.apache.spark.scheduler.ShuffleMapTask.runTask(ShuffleMapTask.scala:53)\n\tat org.apache.spark.scheduler.Task.run(Task.scala:108)\n\tat org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:335)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\t... 1 more\n"
     ]
    }
   ],
   "source": [
    "# train_test_set_004 = spark.sql(\"\"\"\n",
    "#         SELECT * FROM\n",
    "#         train_test_set_003_sql t\n",
    "#         LEFT JOIN stores_sql s ON t.store_nbr = s.store_nbr\n",
    "# \"\"\")\n",
    "train_test_set_004 = train_test_set_003.join(stores, ['store_nbr'], 'outer')\n",
    "train_test_set_004.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#train_test_set_004.createOrReplaceTempView(\"train_test_set_004_sql\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deal with NaN values in oil table (pandas), then create a spark dataframe from pandas with it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date          0\n",
       "dcoilwtico    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oil_pandas = pd.read_csv(\"oil.csv\")\n",
    "oil_pandas['dcoilwtico'] = oil_pandas['dcoilwtico'].fillna(method=\"ffill\")\n",
    "oil_pandas['dcoilwtico'] = oil_pandas['dcoilwtico'].fillna(method=\"bfill\")\n",
    "oil_pandas.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#oil_pandas = pd.DataFrame(oil_pandas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1218 entries, 0 to 1217\n",
      "Data columns (total 2 columns):\n",
      "date          1218 non-null object\n",
      "dcoilwtico    1218 non-null float64\n",
      "dtypes: float64(1), object(1)\n",
      "memory usage: 19.1+ KB\n"
     ]
    }
   ],
   "source": [
    "oil_pandas.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sqlCtx = SQLContext(sc)\n",
    "oil = sqlCtx.createDataFrame(oil_pandas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "oil.createOrReplaceTempView(\"oil_sql\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# train_test_set_005 = spark.sql(\"\"\"\n",
    "#         SELECT * FROM\n",
    "#         train_test_set_004_sql t\n",
    "#         LEFT JOIN oil_sql o ON t.date = o.date\n",
    "# \"\"\")\n",
    "train_test_set_005 = train_test_set_004.join(oil, ['date'], 'outer')\n",
    "train_test_set_005.show()\n",
    "#train_test_set_005.createOrReplaceTempView(\"train_test_set_005_sql\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Join transaction table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-------+---------+--------+-----------+------+----+------+-----------+-----------+-----------+--------+------------+-----+----------+---------+-----+---------+----+-------+----------+----------+\n",
      "|      date|     id|store_nbr|item_nbr|onpromotion|source|type|locale|locale_name|description|transferred|item_nbr|      family|class|perishable|store_nbr| city|    state|type|cluster|      date|dcoilwtico|\n",
      "+----------+-------+---------+--------+-----------+------+----+------+-----------+-----------+-----------+--------+------------+-----+----------+---------+-----+---------+----+-------+----------+----------+\n",
      "|2013-03-14|2924449|        1|  103520|       null| train|null|  null|       null|       null|       null|  103520|   GROCERY I| 1028|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924450|        1|  103665|       null| train|null|  null|       null|       null|       null|  103665|BREAD/BAKERY| 2712|         1|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924451|        1|  105574|       null| train|null|  null|       null|       null|       null|  105574|   GROCERY I| 1045|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924452|        1|  105575|       null| train|null|  null|       null|       null|       null|  105575|   GROCERY I| 1045|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924453|        1|  105577|       null| train|null|  null|       null|       null|       null|  105577|   GROCERY I| 1045|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924454|        1|  105737|       null| train|null|  null|       null|       null|       null|  105737|   GROCERY I| 1044|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924455|        1|  105857|       null| train|null|  null|       null|       null|       null|  105857|   GROCERY I| 1092|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924456|        1|  106716|       null| train|null|  null|       null|       null|       null|  106716|   GROCERY I| 1032|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924457|        1|  108696|       null| train|null|  null|       null|       null|       null|  108696|        DELI| 2636|         1|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924458|        1|  108698|       null| train|null|  null|       null|       null|       null|  108698|        DELI| 2644|         1|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924459|        1|  108701|       null| train|null|  null|       null|       null|       null|  108701|        DELI| 2644|         1|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924460|        1|  108786|       null| train|null|  null|       null|       null|       null|  108786|    CLEANING| 3044|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924461|        1|  108831|       null| train|null|  null|       null|       null|       null|  108831|     POULTRY| 2416|         1|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924462|        1|  108952|       null| train|null|  null|       null|       null|       null|  108952|    CLEANING| 3024|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924463|        1|  111223|       null| train|null|  null|       null|       null|       null|  111223|   GROCERY I| 1034|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924464|        1|  112830|       null| train|null|  null|       null|       null|       null|  112830|   GROCERY I| 1044|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924465|        1|  114778|       null| train|null|  null|       null|       null|       null|  114778|   GROCERY I| 1016|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924466|        1|  114790|       null| train|null|  null|       null|       null|       null|  114790|   GROCERY I| 1004|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924467|        1|  115611|       null| train|null|  null|       null|       null|       null|  115611|   GROCERY I| 1014|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "|2013-03-14|2924468|        1|  115720|       null| train|null|  null|       null|       null|       null|  115720|   GROCERY I| 1084|         0|        1|Quito|Pichincha|   D|     13|2013-03-14|     93.03|\n",
      "+----------+-------+---------+--------+-----------+------+----+------+-----------+-----------+-----------+--------+------------+-----+----------+---------+-----+---------+----+-------+----------+----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "train_test_set_005.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR:root:An unexpected error occurred while tokenizing input\n",
      "The following traceback may be corrupted or invalid\n",
      "The error message is: ('EOF in multi-line string', (1, 0))\n",
      "\n"
     ]
    },
    {
     "ename": "AnalysisException",
     "evalue": "\"Reference 't.date' is ambiguous, could be: date#651, date#1059.; line 4 pos 40\"",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mPy4JJavaError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/opt/apache-spark/libexec/python/pyspark/sql/utils.py\u001b[0m in \u001b[0;36mdeco\u001b[0;34m(*a, **kw)\u001b[0m\n\u001b[1;32m     62\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 63\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     64\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mpy4j\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mprotocol\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mPy4JJavaError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/opt/apache-spark/libexec/python/lib/py4j-0.10.4-src.zip/py4j/protocol.py\u001b[0m in \u001b[0;36mget_return_value\u001b[0;34m(answer, gateway_client, target_id, name)\u001b[0m\n\u001b[1;32m    318\u001b[0m                     \u001b[0;34m\"An error occurred while calling {0}{1}{2}.\\n\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 319\u001b[0;31m                     format(target_id, \".\", name), value)\n\u001b[0m\u001b[1;32m    320\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mPy4JJavaError\u001b[0m: An error occurred while calling o56.sql.\n: org.apache.spark.sql.AnalysisException: Reference 't.date' is ambiguous, could be: date#651, date#1059.; line 4 pos 40\n\tat org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.resolve(LogicalPlan.scala:287)\n\tat org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.resolveChildren(LogicalPlan.scala:171)\n\tat org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveReferences$$anonfun$apply$9$$anonfun$applyOrElse$6$$anonfun$37.apply(Analyzer.scala:851)\n\tat org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveReferences$$anonfun$apply$9$$anonfun$applyOrElse$6$$anonfun$37.apply(Analyzer.scala:851)\n\tat org.apache.spark.sql.catalyst.analysis.package$.withPosition(package.scala:48)\n\tat org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveReferences$$anonfun$apply$9$$anonfun$applyOrElse$6.applyOrElse(Analyzer.scala:851)\n\tat org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveReferences$$anonfun$apply$9$$anonfun$applyOrElse$6.applyOrElse(Analyzer.scala:848)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$transformUp$1.apply(TreeNode.scala:289)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$transformUp$1.apply(TreeNode.scala:289)\n\tat org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:70)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode.transformUp(TreeNode.scala:288)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$3.apply(TreeNode.scala:286)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$3.apply(TreeNode.scala:286)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$4.apply(TreeNode.scala:306)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:187)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode.mapChildren(TreeNode.scala:304)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode.transformUp(TreeNode.scala:286)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$3.apply(TreeNode.scala:286)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$3.apply(TreeNode.scala:286)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$4.apply(TreeNode.scala:306)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:187)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode.mapChildren(TreeNode.scala:304)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode.transformUp(TreeNode.scala:286)\n\tat org.apache.spark.sql.catalyst.plans.QueryPlan$$anonfun$transformExpressionsUp$1.apply(QueryPlan.scala:268)\n\tat org.apache.spark.sql.catalyst.plans.QueryPlan$$anonfun$transformExpressionsUp$1.apply(QueryPlan.scala:268)\n\tat org.apache.spark.sql.catalyst.plans.QueryPlan.transformExpression$1(QueryPlan.scala:279)\n\tat org.apache.spark.sql.catalyst.plans.QueryPlan.org$apache$spark$sql$catalyst$plans$QueryPlan$$recursiveTransform$1(QueryPlan.scala:289)\n\tat org.apache.spark.sql.catalyst.plans.QueryPlan.org$apache$spark$sql$catalyst$plans$QueryPlan$$recursiveTransform$1(QueryPlan.scala:290)\n\tat org.apache.spark.sql.catalyst.plans.QueryPlan$$anonfun$6.apply(QueryPlan.scala:298)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:187)\n\tat org.apache.spark.sql.catalyst.plans.QueryPlan.mapExpressions(QueryPlan.scala:298)\n\tat org.apache.spark.sql.catalyst.plans.QueryPlan.transformExpressionsUp(QueryPlan.scala:268)\n\tat org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveReferences$$anonfun$apply$9.applyOrElse(Analyzer.scala:848)\n\tat org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveReferences$$anonfun$apply$9.applyOrElse(Analyzer.scala:790)\n\tat org.apache.spark.sql.catalyst.plans.logical.LogicalPlan$$anonfun$resolveOperators$1.apply(LogicalPlan.scala:62)\n\tat org.apache.spark.sql.catalyst.plans.logical.LogicalPlan$$anonfun$resolveOperators$1.apply(LogicalPlan.scala:62)\n\tat org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:70)\n\tat org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.resolveOperators(LogicalPlan.scala:61)\n\tat org.apache.spark.sql.catalyst.plans.logical.LogicalPlan$$anonfun$1.apply(LogicalPlan.scala:59)\n\tat org.apache.spark.sql.catalyst.plans.logical.LogicalPlan$$anonfun$1.apply(LogicalPlan.scala:59)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$4.apply(TreeNode.scala:306)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode.mapProductIterator(TreeNode.scala:187)\n\tat org.apache.spark.sql.catalyst.trees.TreeNode.mapChildren(TreeNode.scala:304)\n\tat org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.resolveOperators(LogicalPlan.scala:59)\n\tat org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveReferences$.apply(Analyzer.scala:790)\n\tat org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveReferences$.apply(Analyzer.scala:668)\n\tat org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1$$anonfun$apply$1.apply(RuleExecutor.scala:85)\n\tat org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1$$anonfun$apply$1.apply(RuleExecutor.scala:82)\n\tat scala.collection.LinearSeqOptimized$class.foldLeft(LinearSeqOptimized.scala:124)\n\tat scala.collection.immutable.List.foldLeft(List.scala:84)\n\tat org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1.apply(RuleExecutor.scala:82)\n\tat org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1.apply(RuleExecutor.scala:74)\n\tat scala.collection.immutable.List.foreach(List.scala:381)\n\tat org.apache.spark.sql.catalyst.rules.RuleExecutor.execute(RuleExecutor.scala:74)\n\tat org.apache.spark.sql.execution.QueryExecution.analyzed$lzycompute(QueryExecution.scala:69)\n\tat org.apache.spark.sql.execution.QueryExecution.analyzed(QueryExecution.scala:67)\n\tat org.apache.spark.sql.execution.QueryExecution.assertAnalyzed(QueryExecution.scala:50)\n\tat org.apache.spark.sql.Dataset$.ofRows(Dataset.scala:66)\n\tat org.apache.spark.sql.SparkSession.sql(SparkSession.scala:623)\n\tat sun.reflect.GeneratedMethodAccessor142.invoke(Unknown Source)\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.lang.reflect.Method.invoke(Method.java:498)\n\tat py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)\n\tat py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)\n\tat py4j.Gateway.invoke(Gateway.java:280)\n\tat py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)\n\tat py4j.commands.CallCommand.execute(CallCommand.java:79)\n\tat py4j.GatewayConnection.run(GatewayConnection.java:214)\n\tat java.lang.Thread.run(Thread.java:748)\n",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mAnalysisException\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-144-216ed6010b7f>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      3\u001b[0m         \u001b[0mtrain_test_set_005_sql\u001b[0m \u001b[0mt\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m         \u001b[0mLEFT\u001b[0m \u001b[0mJOIN\u001b[0m \u001b[0mtransactions_sql\u001b[0m \u001b[0mr\u001b[0m \u001b[0mON\u001b[0m \u001b[0mt\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdate\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdate\u001b[0m \u001b[0mAND\u001b[0m \u001b[0mt\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstore_nbr\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mr\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstore_nbr\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 5\u001b[0;31m \"\"\").show()\n\u001b[0m",
      "\u001b[0;32m/usr/local/opt/apache-spark/libexec/python/pyspark/sql/session.py\u001b[0m in \u001b[0;36msql\u001b[0;34m(self, sqlQuery)\u001b[0m\n\u001b[1;32m    554\u001b[0m         \u001b[0;34m[\u001b[0m\u001b[0mRow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf1\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mf2\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34mu'row1'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mRow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf1\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mf2\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34mu'row2'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mRow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf1\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m3\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mf2\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34mu'row3'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    555\u001b[0m         \"\"\"\n\u001b[0;32m--> 556\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mDataFrame\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jsparkSession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msqlQuery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_wrapped\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    557\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    558\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0msince\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m2.0\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/opt/apache-spark/libexec/python/lib/py4j-0.10.4-src.zip/py4j/java_gateway.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, *args)\u001b[0m\n\u001b[1;32m   1131\u001b[0m         \u001b[0manswer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgateway_client\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1132\u001b[0m         return_value = get_return_value(\n\u001b[0;32m-> 1133\u001b[0;31m             answer, self.gateway_client, self.target_id, self.name)\n\u001b[0m\u001b[1;32m   1134\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1135\u001b[0m         \u001b[0;32mfor\u001b[0m \u001b[0mtemp_arg\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mtemp_args\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/opt/apache-spark/libexec/python/pyspark/sql/utils.py\u001b[0m in \u001b[0;36mdeco\u001b[0;34m(*a, **kw)\u001b[0m\n\u001b[1;32m     67\u001b[0m                                              e.java_exception.getStackTrace()))\n\u001b[1;32m     68\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0ms\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstartswith\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'org.apache.spark.sql.AnalysisException: '\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 69\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0mAnalysisException\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msplit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m': '\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstackTrace\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     70\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0ms\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstartswith\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'org.apache.spark.sql.catalyst.analysis'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     71\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mAnalysisException\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ms\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msplit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m': '\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstackTrace\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAnalysisException\u001b[0m: \"Reference 't.date' is ambiguous, could be: date#651, date#1059.; line 4 pos 40\""
     ]
    }
   ],
   "source": [
    "spark.sql(\"\"\"\n",
    "        SELECT * FROM\n",
    "        train_test_set_005_sql t\n",
    "        LEFT JOIN transactions_sql r ON t.date = r.date AND t.store_nbr = r.store_nbr\n",
    "\"\"\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}