store-sales-forecasting-pysparkdatamodeling.ipynb

{"cells":[{"source":"<a href=\"https://www.kaggle.com/code/kamaljp/store-sales-forecasting-pysparkdatamodeling?scriptVersionId=115252548\" target=\"_blank\"><img align=\"left\" alt=\"Kaggle\" title=\"Open in Kaggle\" src=\"https://kaggle.com/static/images/open-in-kaggle.svg\"></a>","metadata":{},"cell_type":"markdown","outputs":[],"execution_count":0},{"cell_type":"markdown","id":"9a787546","metadata":{"papermill":{"duration":0.016157,"end_time":"2023-01-02T01:36:30.87698","exception":false,"start_time":"2023-01-02T01:36:30.860823","status":"completed"},"tags":[]},"source":["**Store Sales Forecasting** an ongoing Kaggle competition that I \n","have decided to use pyspark to load, data model, analyse and then \n","move it into data modeling."]},{"cell_type":"code","execution_count":1,"id":"81fc7ccd","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:36:30.908169Z","iopub.status.busy":"2023-01-02T01:36:30.907767Z","iopub.status.idle":"2023-01-02T01:37:16.323279Z","shell.execute_reply":"2023-01-02T01:37:16.322269Z"},"papermill":{"duration":45.433851,"end_time":"2023-01-02T01:37:16.325753","exception":false,"start_time":"2023-01-02T01:36:30.891902","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[31m4.6 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\r\n","\u001b[?25h  Preparing metadata (setup.py) ... \u001b[?25l-\b \b\\\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[31m16.9 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 \bdone\r\n","\u001b[?25h  Created wheel for pyspark: filename=pyspark-3.3.1-py2.py3-none-any.whl size=281845513 sha256=515809b127ae1d55b157babd761643bcedd8b09984e23547c3342b40600f6fc4\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":"d23b0902","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:16.390144Z","iopub.status.busy":"2023-01-02T01:37:16.389831Z","iopub.status.idle":"2023-01-02T01:37:16.436571Z","shell.execute_reply":"2023-01-02T01:37:16.435297Z"},"papermill":{"duration":0.082312,"end_time":"2023-01-02T01:37:16.438857","exception":false,"start_time":"2023-01-02T01:37:16.356545","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":"0cc1cbd3","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:16.500468Z","iopub.status.busy":"2023-01-02T01:37:16.500122Z","iopub.status.idle":"2023-01-02T01:37:16.540521Z","shell.execute_reply":"2023-01-02T01:37:16.539331Z"},"papermill":{"duration":0.075074,"end_time":"2023-01-02T01:37:16.543973","exception":false,"start_time":"2023-01-02T01:37:16.468899","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["holidays_events.csv\n","oil.csv\n","sample_submission.csv\n","stores.csv\n","test.csv\n","train.csv\n","transactions.csv\n"]}],"source":["%%sh\n","cd /kaggle/input/store-sales-time-series-forecasting/\n","ls"]},{"cell_type":"code","execution_count":4,"id":"bbf14888","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:16.605918Z","iopub.status.busy":"2023-01-02T01:37:16.605545Z","iopub.status.idle":"2023-01-02T01:37:16.61031Z","shell.execute_reply":"2023-01-02T01:37:16.609434Z"},"papermill":{"duration":0.037568,"end_time":"2023-01-02T01:37:16.611988","exception":false,"start_time":"2023-01-02T01:37:16.57442","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":5,"id":"2497a062","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:16.674556Z","iopub.status.busy":"2023-01-02T01:37:16.673931Z","iopub.status.idle":"2023-01-02T01:37:21.946959Z","shell.execute_reply":"2023-01-02T01:37:21.945978Z"},"papermill":{"duration":5.308395,"end_time":"2023-01-02T01:37:21.950067","exception":false,"start_time":"2023-01-02T01:37:16.641672","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 01:37:19 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":6,"id":"c8102456","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:22.02489Z","iopub.status.busy":"2023-01-02T01:37:22.024536Z","iopub.status.idle":"2023-01-02T01:37:24.403509Z","shell.execute_reply":"2023-01-02T01:37:24.402497Z"},"papermill":{"duration":2.413369,"end_time":"2023-01-02T01:37:24.405844","exception":false,"start_time":"2023-01-02T01:37:21.992475","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":7,"id":"c8ac5336","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:24.469489Z","iopub.status.busy":"2023-01-02T01:37:24.469157Z","iopub.status.idle":"2023-01-02T01:37:24.650091Z","shell.execute_reply":"2023-01-02T01:37:24.649227Z"},"papermill":{"duration":0.21585,"end_time":"2023-01-02T01:37:24.652521","exception":false,"start_time":"2023-01-02T01:37:24.436671","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["DataFrame[]"]},"execution_count":7,"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":8,"id":"947d7458","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:24.72541Z","iopub.status.busy":"2023-01-02T01:37:24.725061Z","iopub.status.idle":"2023-01-02T01:37:32.378738Z","shell.execute_reply":"2023-01-02T01:37:32.377635Z"},"papermill":{"duration":7.689485,"end_time":"2023-01-02T01:37:32.381504","exception":false,"start_time":"2023-01-02T01:37:24.692019","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":"markdown","id":"8c96f3c1","metadata":{"papermill":{"duration":0.033968,"end_time":"2023-01-02T01:37:32.459431","exception":false,"start_time":"2023-01-02T01:37:32.425463","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":"b498a66f","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:32.52341Z","iopub.status.busy":"2023-01-02T01:37:32.522884Z","iopub.status.idle":"2023-01-02T01:37:32.589438Z","shell.execute_reply":"2023-01-02T01:37:32.588728Z"},"papermill":{"duration":0.10059,"end_time":"2023-01-02T01:37:32.591579","exception":false,"start_time":"2023-01-02T01:37:32.490989","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":"markdown","id":"2fc4c334","metadata":{"papermill":{"duration":0.030113,"end_time":"2023-01-02T01:37:32.652033","exception":false,"start_time":"2023-01-02T01:37:32.62192","status":"completed"},"tags":[]},"source":["The temp tables are dropped like the usual sql tables. sparkql(\"DROP TABLE holidays_data\")"]},{"cell_type":"code","execution_count":10,"id":"f2e339c1","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:32.720494Z","iopub.status.busy":"2023-01-02T01:37:32.720035Z","iopub.status.idle":"2023-01-02T01:37:32.983041Z","shell.execute_reply":"2023-01-02T01:37:32.982247Z"},"papermill":{"duration":0.303198,"end_time":"2023-01-02T01:37:32.985605","exception":false,"start_time":"2023-01-02T01:37:32.682407","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+---------+--------------+-----------+\n","|namespace|     tableName|isTemporary|\n","+---------+--------------+-----------+\n","|         |holidays_table|       true|\n","|         |     oil_table|       true|\n","|         |  stores_table|       true|\n","|         |   train_table|       true|\n","|         |     txn_table|       true|\n","+---------+--------------+-----------+\n","\n"]}],"source":["#We have the data inside the spark data base to start manipulation\n","#using sql\n","sparkql(\"SHOW TABLES\").show()"]},{"cell_type":"markdown","id":"1afd60d4","metadata":{"papermill":{"duration":0.046577,"end_time":"2023-01-02T01:37:33.081357","exception":false,"start_time":"2023-01-02T01:37:33.03478","status":"completed"},"tags":[]},"source":["### Lets get to know the data... one table at a time"]},{"cell_type":"code","execution_count":11,"id":"866926cd","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:33.175473Z","iopub.status.busy":"2023-01-02T01:37:33.175086Z","iopub.status.idle":"2023-01-02T01:37:33.784176Z","shell.execute_reply":"2023-01-02T01:37:33.783401Z"},"papermill":{"duration":0.658022,"end_time":"2023-01-02T01:37:33.786674","exception":false,"start_time":"2023-01-02T01:37:33.128652","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+-------------------+-------+--------+-----------+--------------------+-----------+\n","|               date|   type|  locale|locale_name|         description|transferred|\n","+-------------------+-------+--------+-----------+--------------------+-----------+\n","|2012-03-02 00:00:00|Holiday|   Local|      Manta|  Fundacion de Manta|      false|\n","|2012-04-01 00:00:00|Holiday|Regional|   Cotopaxi|Provincializacion...|      false|\n","|2012-04-12 00:00:00|Holiday|   Local|     Cuenca| Fundacion de Cuenca|      false|\n","|2012-04-14 00:00:00|Holiday|   Local|   Libertad|Cantonizacion de ...|      false|\n","|2012-04-21 00:00:00|Holiday|   Local|   Riobamba|Cantonizacion de ...|      false|\n","+-------------------+-------+--------+-----------+--------------------+-----------+\n","\n"]}],"source":["sparkql(\"select * from holidays_table limit 5\").show()"]},{"cell_type":"code","execution_count":12,"id":"66bd1db1","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:33.878932Z","iopub.status.busy":"2023-01-02T01:37:33.878554Z","iopub.status.idle":"2023-01-02T01:37:34.118907Z","shell.execute_reply":"2023-01-02T01:37:34.117915Z"},"papermill":{"duration":0.288218,"end_time":"2023-01-02T01:37:34.120924","exception":false,"start_time":"2023-01-02T01:37:33.832706","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["350"]},"execution_count":12,"metadata":{},"output_type":"execute_result"}],"source":["#Observe there are multiple categories, type, locale, locale_name\n","holidays_data.count()"]},{"cell_type":"code","execution_count":13,"id":"854dfd09","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:34.210574Z","iopub.status.busy":"2023-01-02T01:37:34.209408Z","iopub.status.idle":"2023-01-02T01:37:34.806416Z","shell.execute_reply":"2023-01-02T01:37:34.805488Z"},"papermill":{"duration":0.645071,"end_time":"2023-01-02T01:37:34.809033","exception":false,"start_time":"2023-01-02T01:37:34.163962","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+------------+----------+--------+-----------+\n","|categ_counts|      type|  locale|locale_name|\n","+------------+----------+--------+-----------+\n","|          12|   Holiday|   Local|     Ambato|\n","|           6|   Holiday|   Local|    Cayambe|\n","|           6|   Holiday|Regional|   Cotopaxi|\n","|           6|   Holiday|   Local|     Cuenca|\n","|           1|  Transfer|   Local|     Cuenca|\n","|          40|Additional|National|    Ecuador|\n","|          56|     Event|National|    Ecuador|\n","|           8|  Transfer|National|    Ecuador|\n","|          60|   Holiday|National|    Ecuador|\n","|           5|  Work Day|National|    Ecuador|\n","|           5|    Bridge|National|    Ecuador|\n","|           6|   Holiday|   Local|  El Carmen|\n","|           6|   Holiday|   Local| Esmeraldas|\n","|          12|   Holiday|   Local|   Guaranda|\n","|           5|   Holiday|   Local|  Guayaquil|\n","|           5|Additional|   Local|  Guayaquil|\n","|           1|  Transfer|   Local|  Guayaquil|\n","|           1|  Transfer|   Local|     Ibarra|\n","|           6|   Holiday|   Local|     Ibarra|\n","|           6|   Holiday|Regional|   Imbabura|\n","+------------+----------+--------+-----------+\n","only showing top 20 rows\n","\n"]}],"source":["sparkql(\"\"\"select count(*) as categ_counts, hd.type, \\\n","            hd.locale,hd.locale_name \\\n","            from holidays_table hd \\\n","            group by hd.type,hd.locale,hd.locale_name \n","            order by hd.locale_name\n","            \"\"\").show()"]},{"cell_type":"code","execution_count":14,"id":"0345206e","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:34.895662Z","iopub.status.busy":"2023-01-02T01:37:34.895241Z","iopub.status.idle":"2023-01-02T01:37:35.010452Z","shell.execute_reply":"2023-01-02T01:37:35.009611Z"},"papermill":{"duration":0.160954,"end_time":"2023-01-02T01:37:35.012482","exception":false,"start_time":"2023-01-02T01:37:34.851528","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["1218"]},"execution_count":14,"metadata":{},"output_type":"execute_result"}],"source":["oil_data.count()"]},{"cell_type":"markdown","id":"9a85b424","metadata":{"papermill":{"duration":0.042494,"end_time":"2023-01-02T01:37:35.097384","exception":false,"start_time":"2023-01-02T01:37:35.05489","status":"completed"},"tags":[]},"source":["### The data is available till Aug'17 starting from Jan'13"]},{"cell_type":"code","execution_count":15,"id":"589f8c2e","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:35.183557Z","iopub.status.busy":"2023-01-02T01:37:35.182347Z","iopub.status.idle":"2023-01-02T01:37:35.368895Z","shell.execute_reply":"2023-01-02T01:37:35.367784Z"},"papermill":{"duration":0.230583,"end_time":"2023-01-02T01:37:35.371192","exception":false,"start_time":"2023-01-02T01:37:35.140609","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+-------------------+----------+\n","|               date|dcoilwtico|\n","+-------------------+----------+\n","|2013-01-01 00:00:00|      null|\n","|2013-01-02 00:00:00|     93.14|\n","|2013-01-03 00:00:00|     92.97|\n","|2013-01-04 00:00:00|     93.12|\n","|2013-01-07 00:00:00|      93.2|\n","+-------------------+----------+\n","\n"]}],"source":["sparkql(\"select * from oil_table limit 5\").show()"]},{"cell_type":"code","execution_count":16,"id":"d7347761","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:35.438177Z","iopub.status.busy":"2023-01-02T01:37:35.436758Z","iopub.status.idle":"2023-01-02T01:37:35.633411Z","shell.execute_reply":"2023-01-02T01:37:35.632611Z"},"papermill":{"duration":0.233128,"end_time":"2023-01-02T01:37:35.636335","exception":false,"start_time":"2023-01-02T01:37:35.403207","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+---------+-------------+--------------------+----+-------+\n","|store_nbr|         city|               state|type|cluster|\n","+---------+-------------+--------------------+----+-------+\n","|        1|        Quito|           Pichincha|   D|     13|\n","|        2|        Quito|           Pichincha|   D|     13|\n","|        3|        Quito|           Pichincha|   D|      8|\n","|        4|        Quito|           Pichincha|   D|      9|\n","|        5|Santo Domingo|Santo Domingo de ...|   D|      4|\n","+---------+-------------+--------------------+----+-------+\n","\n"]}],"source":["sparkql(\"select * from stores_table limit 5\").show()"]},{"cell_type":"code","execution_count":17,"id":"8ab45add","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:35.719603Z","iopub.status.busy":"2023-01-02T01:37:35.718314Z","iopub.status.idle":"2023-01-02T01:37:35.851293Z","shell.execute_reply":"2023-01-02T01:37:35.850533Z"},"papermill":{"duration":0.172619,"end_time":"2023-01-02T01:37:35.8534","exception":false,"start_time":"2023-01-02T01:37:35.680781","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["54"]},"execution_count":17,"metadata":{},"output_type":"execute_result"}],"source":["stores_data.count()"]},{"cell_type":"code","execution_count":18,"id":"38915c41","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:35.947188Z","iopub.status.busy":"2023-01-02T01:37:35.946839Z","iopub.status.idle":"2023-01-02T01:37:36.149438Z","shell.execute_reply":"2023-01-02T01:37:36.14862Z"},"papermill":{"duration":0.252205,"end_time":"2023-01-02T01:37:36.151935","exception":false,"start_time":"2023-01-02T01:37:35.89973","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+---+-------------------+---------+----------+-----+-----------+\n","| id|               date|store_nbr|    family|sales|onpromotion|\n","+---+-------------------+---------+----------+-----+-----------+\n","|  0|2013-01-01 00:00:00|        1|AUTOMOTIVE|  0.0|          0|\n","|  1|2013-01-01 00:00:00|        1| BABY CARE|  0.0|          0|\n","|  2|2013-01-01 00:00:00|        1|    BEAUTY|  0.0|          0|\n","|  3|2013-01-01 00:00:00|        1| BEVERAGES|  0.0|          0|\n","|  4|2013-01-01 00:00:00|        1|     BOOKS|  0.0|          0|\n","+---+-------------------+---------+----------+-----+-----------+\n","\n"]}],"source":["sparkql(\"select * from train_table limit 5\").show()"]},{"cell_type":"code","execution_count":19,"id":"ac9afa63","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:36.241751Z","iopub.status.busy":"2023-01-02T01:37:36.24136Z","iopub.status.idle":"2023-01-02T01:37:37.112805Z","shell.execute_reply":"2023-01-02T01:37:37.111953Z"},"papermill":{"duration":0.91803,"end_time":"2023-01-02T01:37:37.114948","exception":false,"start_time":"2023-01-02T01:37:36.196918","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["                                                                                \r"]},{"data":{"text/plain":["3000888"]},"execution_count":19,"metadata":{},"output_type":"execute_result"}],"source":["train_data.count()"]},{"cell_type":"code","execution_count":20,"id":"4f34a05d","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:37.205312Z","iopub.status.busy":"2023-01-02T01:37:37.204858Z","iopub.status.idle":"2023-01-02T01:37:37.362879Z","shell.execute_reply":"2023-01-02T01:37:37.362076Z"},"papermill":{"duration":0.208699,"end_time":"2023-01-02T01:37:37.365168","exception":false,"start_time":"2023-01-02T01:37:37.156469","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+-------------------+---------+------------+\n","|               date|store_nbr|transactions|\n","+-------------------+---------+------------+\n","|2013-01-01 00:00:00|       25|         770|\n","|2013-01-02 00:00:00|        1|        2111|\n","|2013-01-02 00:00:00|        2|        2358|\n","|2013-01-02 00:00:00|        3|        3487|\n","|2013-01-02 00:00:00|        4|        1922|\n","+-------------------+---------+------------+\n","\n"]}],"source":["sparkql(\"SELECT * FROM txn_table LIMIT 5\").show()"]},{"cell_type":"code","execution_count":21,"id":"e4cbc116","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:37.461637Z","iopub.status.busy":"2023-01-02T01:37:37.461195Z","iopub.status.idle":"2023-01-02T01:37:37.609406Z","shell.execute_reply":"2023-01-02T01:37:37.608599Z"},"papermill":{"duration":0.200807,"end_time":"2023-01-02T01:37:37.611663","exception":false,"start_time":"2023-01-02T01:37:37.410856","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["83488"]},"execution_count":21,"metadata":{},"output_type":"execute_result"}],"source":["txn_data.count()"]},{"cell_type":"code","execution_count":22,"id":"e91b8cfe","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:37.699171Z","iopub.status.busy":"2023-01-02T01:37:37.698859Z","iopub.status.idle":"2023-01-02T01:37:37.804734Z","shell.execute_reply":"2023-01-02T01:37:37.80385Z"},"papermill":{"duration":0.15215,"end_time":"2023-01-02T01:37:37.807027","exception":false,"start_time":"2023-01-02T01:37:37.654877","status":"completed"},"tags":[]},"outputs":[],"source":["data = [(123, 1, \"01/01/2021\",),\n","        (123, 0, \"01/02/2021\",),\n","        (123, 1, \"01/03/2021\",),\n","        (123, 0, \"01/06/2021\",),\n","        (123, 0, \"01/08/2021\",),\n","        (777, 0, \"01/01/2021\",),\n","        (777, 1, \"01/03/2021\",), ]\n","\n","df = spark.createDataFrame(data, (\"ID\", \"FLAG\", \"DATE\",)) \\\n","        .withColumn(\"DATE\", to_date(col(\"DATE\"), \"dd/MM/yyyy\"))"]},{"cell_type":"code","execution_count":23,"id":"d9049b10","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:37.896339Z","iopub.status.busy":"2023-01-02T01:37:37.895954Z","iopub.status.idle":"2023-01-02T01:37:38.718826Z","shell.execute_reply":"2023-01-02T01:37:38.717913Z"},"papermill":{"duration":0.869796,"end_time":"2023-01-02T01:37:38.721291","exception":false,"start_time":"2023-01-02T01:37:37.851495","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["                                                                                \r"]},{"name":"stdout","output_type":"stream","text":["+---+----+----------+\n","| ID|FLAG|      DATE|\n","+---+----+----------+\n","|123|   1|2021-01-01|\n","|123|   0|2021-02-01|\n","+---+----+----------+\n","only showing top 2 rows\n","\n"]}],"source":["df.show(2)"]},{"cell_type":"code","execution_count":24,"id":"ba7dfade","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:38.817284Z","iopub.status.busy":"2023-01-02T01:37:38.816843Z","iopub.status.idle":"2023-01-02T01:37:39.052492Z","shell.execute_reply":"2023-01-02T01:37:39.051515Z"},"papermill":{"duration":0.286255,"end_time":"2023-01-02T01:37:39.05463","exception":false,"start_time":"2023-01-02T01:37:38.768375","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+-------------------+-------------------+--------------------+\n","|           min_date|           max_date|            interval|\n","+-------------------+-------------------+--------------------+\n","|2013-01-01 00:00:00|2017-08-31 00:00:00|INTERVAL '-1703 0...|\n","+-------------------+-------------------+--------------------+\n","\n"]}],"source":["sparkql(\"\"\"SELECT MIN(date) as min_date,MAX(date) as max_date,\n","                MIN(date) - MAX(date) as interval\n","                from oil_table\"\"\").show()"]},{"cell_type":"code","execution_count":25,"id":"efff5f45","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:39.119044Z","iopub.status.busy":"2023-01-02T01:37:39.118751Z","iopub.status.idle":"2023-01-02T01:37:39.219167Z","shell.execute_reply":"2023-01-02T01:37:39.218474Z"},"papermill":{"duration":0.134995,"end_time":"2023-01-02T01:37:39.221516","exception":false,"start_time":"2023-01-02T01:37:39.086521","status":"completed"},"tags":[]},"outputs":[],"source":["all_dates_df = df.groupBy(\"id\").agg(\n","    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(\"id\",expr(\"sequence(min_date, max_date, interval 1 month)\").alias(\"date_seq\")). \\\n","        withColumn(\"date_new\",explode(\"date_seq\")). \\\n","        withColumn(\"date_form\",date_format(\"date_new\", \"dd/MM/yyyy\"))"]},{"cell_type":"code","execution_count":26,"id":"7dfe7f4a","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:39.286784Z","iopub.status.busy":"2023-01-02T01:37:39.28642Z","iopub.status.idle":"2023-01-02T01:37:39.489962Z","shell.execute_reply":"2023-01-02T01:37:39.489128Z"},"papermill":{"duration":0.238761,"end_time":"2023-01-02T01:37:39.492482","exception":false,"start_time":"2023-01-02T01:37:39.253721","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+-------------------+-------------------+--------------------+\n","|           max_date|           min_date|           diff_date|\n","+-------------------+-------------------+--------------------+\n","|2017-08-01 00:00:00|2013-01-01 00:00:00|INTERVAL '1673 00...|\n","+-------------------+-------------------+--------------------+\n","\n"]}],"source":["oil_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","            (date_trunc(\"mm\", max(to_date(\"date\", \"dd/MM/yyyy\"))) - \\\n","            date_trunc(\"mm\", min(to_date(\"date\", \"dd/MM/yyyy\")))).alias('diff_date')).show()"]},{"cell_type":"markdown","id":"ebc21ca5","metadata":{"papermill":{"duration":0.04241,"end_time":"2023-01-02T01:37:39.578551","exception":false,"start_time":"2023-01-02T01:37:39.536141","status":"completed"},"tags":[]},"source":["### Creating the date sequence that we want"]},{"cell_type":"code","execution_count":27,"id":"9b44d993","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:39.65362Z","iopub.status.busy":"2023-01-02T01:37:39.653209Z","iopub.status.idle":"2023-01-02T01:37:39.69805Z","shell.execute_reply":"2023-01-02T01:37:39.697162Z"},"papermill":{"duration":0.080533,"end_time":"2023-01-02T01:37:39.700677","exception":false,"start_time":"2023-01-02T01:37:39.620144","status":"completed"},"tags":[]},"outputs":[],"source":["data_date_series = oil_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":28,"id":"a5d4f0c0","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:39.778007Z","iopub.status.busy":"2023-01-02T01:37:39.777656Z","iopub.status.idle":"2023-01-02T01:37:39.788635Z","shell.execute_reply":"2023-01-02T01:37:39.787718Z"},"papermill":{"duration":0.046679,"end_time":"2023-01-02T01:37:39.79074","exception":false,"start_time":"2023-01-02T01:37:39.744061","status":"completed"},"tags":[]},"outputs":[],"source":["date_series=data_date_series.drop(\"date_seq\",\"date_new\")"]},{"cell_type":"code","execution_count":29,"id":"8f440967","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:39.860207Z","iopub.status.busy":"2023-01-02T01:37:39.859691Z","iopub.status.idle":"2023-01-02T01:37:40.071202Z","shell.execute_reply":"2023-01-02T01:37:40.070433Z"},"papermill":{"duration":0.248539,"end_time":"2023-01-02T01:37:40.07328","exception":false,"start_time":"2023-01-02T01:37:39.824741","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["1674"]},"execution_count":29,"metadata":{},"output_type":"execute_result"}],"source":["date_series.count()"]},{"cell_type":"code","execution_count":30,"id":"bafbc829","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:40.163245Z","iopub.status.busy":"2023-01-02T01:37:40.162854Z","iopub.status.idle":"2023-01-02T01:37:40.183332Z","shell.execute_reply":"2023-01-02T01:37:40.182428Z"},"papermill":{"duration":0.069233,"end_time":"2023-01-02T01:37:40.186164","exception":false,"start_time":"2023-01-02T01:37:40.116931","status":"completed"},"tags":[]},"outputs":[],"source":["date_series.createOrReplaceTempView('date_table')"]},{"cell_type":"code","execution_count":31,"id":"785b9b6a","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:40.269903Z","iopub.status.busy":"2023-01-02T01:37:40.269518Z","iopub.status.idle":"2023-01-02T01:37:40.441892Z","shell.execute_reply":"2023-01-02T01:37:40.441053Z"},"papermill":{"duration":0.214357,"end_time":"2023-01-02T01:37:40.446355","exception":false,"start_time":"2023-01-02T01:37:40.231998","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+----------+\n","| date_form|\n","+----------+\n","|2013-01-01|\n","|2013-01-02|\n","+----------+\n","only showing top 2 rows\n","\n"]}],"source":["sparkql(\"\"\"SELECT date_form \n","            from date_table\"\"\").show(2)"]},{"cell_type":"markdown","id":"901e1e2c","metadata":{"papermill":{"duration":0.044527,"end_time":"2023-01-02T01:37:40.536697","exception":false,"start_time":"2023-01-02T01:37:40.49217","status":"completed"},"tags":[]},"source":["### Build the tables SQL style:Not so fast. \n","\n","In Spark SQL implementation the constraints like\n","Primary, Secondary is not established. The PR has been already raised in tho ASF though.\n","\n","sparkql(\"\"\" CREATE TABLE full_oil_table AS\n","        \n","        SELECT date_form, COALESCE(dcoilwtico,0) as dcoilwtico\n","        \n","        FROM date_table dt LEFT JOIN oil_table ot\n","        \n","        ON dt.date_form = ot.date\"\"\")\n","        \n","        \n","The above command requires hive support, and errors out. We cannot create fully constrained tables in spark context. We have to do it in RDBMS environment if required"]},{"cell_type":"code","execution_count":32,"id":"9502081d","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:40.633809Z","iopub.status.busy":"2023-01-02T01:37:40.633329Z","iopub.status.idle":"2023-01-02T01:37:40.693707Z","shell.execute_reply":"2023-01-02T01:37:40.69283Z"},"papermill":{"duration":0.114252,"end_time":"2023-01-02T01:37:40.696161","exception":false,"start_time":"2023-01-02T01:37:40.581909","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":33,"id":"7078ed5d","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:40.774627Z","iopub.status.busy":"2023-01-02T01:37:40.774225Z","iopub.status.idle":"2023-01-02T01:37:41.125591Z","shell.execute_reply":"2023-01-02T01:37:41.124938Z"},"papermill":{"duration":0.386593,"end_time":"2023-01-02T01:37:41.12749","exception":false,"start_time":"2023-01-02T01:37:40.740897","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+----------+----------+\n","| date_form|dcoilwtico|\n","+----------+----------+\n","|2013-01-01|       0.0|\n","|2013-01-02|     93.14|\n","+----------+----------+\n","only showing top 2 rows\n","\n"]}],"source":["# Creating table the sql style\n","sparkql(\"\"\"SELECT * \n","            FROM full_oil_table\"\"\").show(2)"]},{"cell_type":"markdown","id":"a3fb4152","metadata":{"papermill":{"duration":0.031261,"end_time":"2023-01-02T01:37:41.190775","exception":false,"start_time":"2023-01-02T01:37:41.159514","status":"completed"},"tags":[]},"source":["#### Attempting to join the oil_data with holiday_table \n","\n","-- Doing some recon on the table columns, the ranges and data types\n","\n","-- Thinking of the format to be used for columns used for joining "]},{"cell_type":"code","execution_count":34,"id":"4bd74e88","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:41.25669Z","iopub.status.busy":"2023-01-02T01:37:41.256332Z","iopub.status.idle":"2023-01-02T01:37:41.455382Z","shell.execute_reply":"2023-01-02T01:37:41.453888Z"},"papermill":{"duration":0.235339,"end_time":"2023-01-02T01:37:41.457497","exception":false,"start_time":"2023-01-02T01:37:41.222158","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+-------------------+-------------------+--------------------+\n","|           max_date|           min_date|           avbl_span|\n","+-------------------+-------------------+--------------------+\n","|2017-12-26 00:00:00|2012-03-02 00:00:00|INTERVAL '2125 00...|\n","+-------------------+-------------------+--------------------+\n","\n"]}],"source":["sparkql(\"\"\"SELECT MAX(date) as max_date,\n","            MIN(date) as min_date,\n","            MAX(date) - MIN(date) as avbl_span\n","            FROM holidays_table\"\"\").show(2)"]},{"cell_type":"code","execution_count":35,"id":"554cccc7","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:41.535306Z","iopub.status.busy":"2023-01-02T01:37:41.534461Z","iopub.status.idle":"2023-01-02T01:37:41.645214Z","shell.execute_reply":"2023-01-02T01:37:41.644334Z"},"papermill":{"duration":0.148339,"end_time":"2023-01-02T01:37:41.648591","exception":false,"start_time":"2023-01-02T01:37:41.500252","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+-------------------+-------+--------+-----------+--------------------+-----------+\n","|               date|   type|  locale|locale_name|         description|transferred|\n","+-------------------+-------+--------+-----------+--------------------+-----------+\n","|2012-03-02 00:00:00|Holiday|   Local|      Manta|  Fundacion de Manta|      false|\n","|2012-04-01 00:00:00|Holiday|Regional|   Cotopaxi|Provincializacion...|      false|\n","+-------------------+-------+--------+-----------+--------------------+-----------+\n","only showing top 2 rows\n","\n"]}],"source":["sparkql(\"\"\"SELECT *\n","            FROM holidays_table\"\"\").show(2)"]},{"cell_type":"code","execution_count":36,"id":"4598d96f","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:41.720403Z","iopub.status.busy":"2023-01-02T01:37:41.720054Z","iopub.status.idle":"2023-01-02T01:37:41.815981Z","shell.execute_reply":"2023-01-02T01:37:41.81492Z"},"papermill":{"duration":0.13319,"end_time":"2023-01-02T01:37:41.819381","exception":false,"start_time":"2023-01-02T01:37:41.686191","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+-----------------------------+\n","|date_format(date, yyyy-MM-dd)|\n","+-----------------------------+\n","|                   2012-03-02|\n","|                   2012-04-01|\n","+-----------------------------+\n","only showing top 2 rows\n","\n"]}],"source":["# Date_trunc is avble but not useful\n","# To_char is not avble\n","# Extract is avble, did not try\n","# date_format, found today only, my new_year gift ;)\n","\n","sparkql(\"\"\"SELECT date_format(date, 'yyyy-MM-dd') FROM holidays_table\"\"\").show(2)"]},{"cell_type":"code","execution_count":37,"id":"bff954ee","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:41.917537Z","iopub.status.busy":"2023-01-02T01:37:41.917111Z","iopub.status.idle":"2023-01-02T01:37:42.280097Z","shell.execute_reply":"2023-01-02T01:37:42.27913Z"},"papermill":{"duration":0.41557,"end_time":"2023-01-02T01:37:42.282448","exception":false,"start_time":"2023-01-02T01:37:41.866878","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+----------+-------------------+--------+--------+-----------+----------+\n","|date_form |date               |type    |locale  |locale_name|dcoilwtico|\n","+----------+-------------------+--------+--------+-----------+----------+\n","|2013-01-01|2013-01-01 00:00:00|Holiday |National|Ecuador    |0.0       |\n","|2013-01-05|2013-01-05 00:00:00|Work Day|National|Ecuador    |0.0       |\n","+----------+-------------------+--------+--------+-----------+----------+\n","only showing top 2 rows\n","\n"]}],"source":["sparkql(\"\"\" SELECT ot.date_form, ht.date, ht.type, ht.locale,\n","        ht.locale_name,ot.dcoilwtico\n","        FROM holidays_table ht JOIN full_oil_table ot\n","        ON date_format(ht.date,'yyyy-MM-dd') = ot.date_form\n","\"\"\").show(2, truncate=False)\n","## The tables are joining"]},{"cell_type":"code","execution_count":38,"id":"da539925","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:42.350595Z","iopub.status.busy":"2023-01-02T01:37:42.350187Z","iopub.status.idle":"2023-01-02T01:37:42.38584Z","shell.execute_reply":"2023-01-02T01:37:42.384923Z"},"papermill":{"duration":0.072938,"end_time":"2023-01-02T01:37:42.388701","exception":false,"start_time":"2023-01-02T01:37:42.315763","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","        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":39,"id":"93860687","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:42.471194Z","iopub.status.busy":"2023-01-02T01:37:42.470812Z","iopub.status.idle":"2023-01-02T01:37:42.75818Z","shell.execute_reply":"2023-01-02T01:37:42.757232Z"},"papermill":{"duration":0.325492,"end_time":"2023-01-02T01:37:42.761011","exception":false,"start_time":"2023-01-02T01:37:42.435519","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+----------+-------+--------+-----------+----------+\n","| date_form|   type|  locale|locale_name|dcoilwtico|\n","+----------+-------+--------+-----------+----------+\n","|2013-01-01|Holiday|National|    Ecuador|       0.0|\n","|2013-01-02|Working|National|   National|     93.14|\n","+----------+-------+--------+-----------+----------+\n","only showing top 2 rows\n","\n"]}],"source":["sparkql(\"\"\"SELECT * \n","            FROM full_oil_with_holidays\"\"\").show(2)"]},{"cell_type":"markdown","id":"e4859723","metadata":{"papermill":{"duration":0.039557,"end_time":"2023-01-02T01:37:42.847896","exception":false,"start_time":"2023-01-02T01:37:42.808339","status":"completed"},"tags":[]},"source":["Validating the table join\n","\n","- Check if there is extra rows\n","\n","- Find the extra rows \n","\n","- Ensure there is no duplication"]},{"cell_type":"code","execution_count":40,"id":"b55801c8","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:42.913073Z","iopub.status.busy":"2023-01-02T01:37:42.912758Z","iopub.status.idle":"2023-01-02T01:37:43.207824Z","shell.execute_reply":"2023-01-02T01:37:43.207081Z"},"papermill":{"duration":0.329742,"end_time":"2023-01-02T01:37:43.209754","exception":false,"start_time":"2023-01-02T01:37:42.880012","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["1704"]},"execution_count":40,"metadata":{},"output_type":"execute_result"}],"source":["sparkql(\"\"\" SELECT *\n","        FROM full_oil_with_holidays\n","\"\"\").count()"]},{"cell_type":"code","execution_count":41,"id":"aa19a898","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:43.2775Z","iopub.status.busy":"2023-01-02T01:37:43.277143Z","iopub.status.idle":"2023-01-02T01:37:43.486148Z","shell.execute_reply":"2023-01-02T01:37:43.485319Z"},"papermill":{"duration":0.245636,"end_time":"2023-01-02T01:37:43.487967","exception":false,"start_time":"2023-01-02T01:37:43.242331","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["1674"]},"execution_count":41,"metadata":{},"output_type":"execute_result"}],"source":["sparkql(\"\"\" SELECT distinct date_form\n","        FROM full_oil_with_holidays\n","\"\"\").count()"]},{"cell_type":"markdown","id":"f831254e","metadata":{"papermill":{"duration":0.032059,"end_time":"2023-01-02T01:37:43.552282","exception":false,"start_time":"2023-01-02T01:37:43.520223","status":"completed"},"tags":[]},"source":["We can observe the date has been duplicated. The reason must be linked with the locales and types. Running a group by with those \n","columns must assure there is no data duplication"]},{"cell_type":"code","execution_count":42,"id":"6c318d03","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:43.617846Z","iopub.status.busy":"2023-01-02T01:37:43.61747Z","iopub.status.idle":"2023-01-02T01:37:44.003031Z","shell.execute_reply":"2023-01-02T01:37:44.001927Z"},"papermill":{"duration":0.420912,"end_time":"2023-01-02T01:37:44.005276","exception":false,"start_time":"2023-01-02T01:37:43.584364","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+----------+----------+-------+\n","|typ_counts| date_form|   type|\n","+----------+----------+-------+\n","|         3|2013-06-25|Holiday|\n","|         2|2013-07-03|Holiday|\n","|         3|2014-06-25|Holiday|\n","|         2|2014-07-03|Holiday|\n","|         3|2015-06-25|Holiday|\n","|         2|2015-07-03|Holiday|\n","|         2|2016-05-08|  Event|\n","|         3|2016-06-25|Holiday|\n","|         2|2016-07-03|Holiday|\n","|         2|2017-04-14|Holiday|\n","|         3|2017-06-25|Holiday|\n","|         2|2017-07-03|Holiday|\n","+----------+----------+-------+\n","\n"]}],"source":["sparkql(\"\"\" SELECT COUNT(1) as typ_counts, date_form, type\n","        FROM full_oil_with_holidays\n","        GROUP BY date_form, type\n","        HAVING COUNT(1) > 1\n","\"\"\").show()"]},{"cell_type":"code","execution_count":43,"id":"65fe257c","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:44.102092Z","iopub.status.busy":"2023-01-02T01:37:44.101712Z","iopub.status.idle":"2023-01-02T01:37:44.706799Z","shell.execute_reply":"2023-01-02T01:37:44.70552Z"},"papermill":{"duration":0.653442,"end_time":"2023-01-02T01:37:44.709043","exception":false,"start_time":"2023-01-02T01:37:44.055601","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+---------+----------+\n","|date_form|dcoilwtico|\n","+---------+----------+\n","+---------+----------+\n","\n"]}],"source":["sparkql(\"\"\" SELECT ft.date_form, ft.dcoilwtico\n","        FROM full_oil_with_holidays ft\n","        EXCEPT\n","        SELECT ot.date_form, ot.dcoilwtico \n","        FROM full_oil_table ot\n","\"\"\").show()"]},{"cell_type":"markdown","id":"1f8334b2","metadata":{"papermill":{"duration":0.039041,"end_time":"2023-01-02T01:37:44.793406","exception":false,"start_time":"2023-01-02T01:37:44.754365","status":"completed"},"tags":[]},"source":["Based on above checks the table join and new view creation is successful. Proceeding to the next join\n","\n","Stores table shown below looks like a dimension table. The store_nbr can be the unique id. Lets check that.\n","\n","The store-nbr is arbitrary, to identify a particular store. There are multiple store in same city, state, type and cluster. It is a valid joiner."]},{"cell_type":"code","execution_count":44,"id":"52fe778e","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:44.864039Z","iopub.status.busy":"2023-01-02T01:37:44.863696Z","iopub.status.idle":"2023-01-02T01:37:44.953979Z","shell.execute_reply":"2023-01-02T01:37:44.953109Z"},"papermill":{"duration":0.128921,"end_time":"2023-01-02T01:37:44.95697","exception":false,"start_time":"2023-01-02T01:37:44.828049","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+---------+-------------+--------------------+----+-------+\n","|store_nbr|         city|               state|type|cluster|\n","+---------+-------------+--------------------+----+-------+\n","|        1|        Quito|           Pichincha|   D|     13|\n","|        2|        Quito|           Pichincha|   D|     13|\n","|        3|        Quito|           Pichincha|   D|      8|\n","|        4|        Quito|           Pichincha|   D|      9|\n","|        5|Santo Domingo|Santo Domingo de ...|   D|      4|\n","+---------+-------------+--------------------+----+-------+\n","only showing top 5 rows\n","\n"]}],"source":["sparkql(\"\"\" SELECT st.*\n","        FROM stores_table st\n","\"\"\").show(5)"]},{"cell_type":"code","execution_count":45,"id":"7e3c7c3b","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:45.055301Z","iopub.status.busy":"2023-01-02T01:37:45.054999Z","iopub.status.idle":"2023-01-02T01:37:45.074034Z","shell.execute_reply":"2023-01-02T01:37:45.073003Z"},"papermill":{"duration":0.065334,"end_time":"2023-01-02T01:37:45.077001","exception":false,"start_time":"2023-01-02T01:37:45.011667","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":46,"id":"61440117","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:45.154659Z","iopub.status.busy":"2023-01-02T01:37:45.154315Z","iopub.status.idle":"2023-01-02T01:37:48.163051Z","shell.execute_reply":"2023-01-02T01:37:48.162103Z"},"papermill":{"duration":3.04699,"end_time":"2023-01-02T01:37:48.166082","exception":false,"start_time":"2023-01-02T01:37:45.119092","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["[Stage 97:=============================>                            (2 + 2) / 4]\r"]},{"name":"stdout","output_type":"stream","text":["+--------+----------+\n","|day_data|      date|\n","+--------+----------+\n","|    1782|2013-01-01|\n","|    1782|2013-01-02|\n","+--------+----------+\n","only showing top 2 rows\n","\n"]},{"name":"stderr","output_type":"stream","text":["                                                                                \r"]}],"source":["sparkql(\"\"\"select COUNT(1) as day_data,tt.date\n","            FROM full_train_table tt\n","            GROUP BY tt.date\n","            ORDER BY tt.date\"\"\").show(2)"]},{"cell_type":"code","execution_count":47,"id":"25319da8","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:48.235819Z","iopub.status.busy":"2023-01-02T01:37:48.235492Z","iopub.status.idle":"2023-01-02T01:37:49.878443Z","shell.execute_reply":"2023-01-02T01:37:49.877011Z"},"papermill":{"duration":1.680973,"end_time":"2023-01-02T01:37:49.880927","exception":false,"start_time":"2023-01-02T01:37:48.199954","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["[Stage 100:>                                                        (0 + 4) / 4]\r"]},{"name":"stdout","output_type":"stream","text":["+-------------------+-------------------+--------------------+\n","|           max_date|           min_date|           avbl_span|\n","+-------------------+-------------------+--------------------+\n","|2017-08-15 00:00:00|2013-01-01 00:00:00|INTERVAL '1687 00...|\n","+-------------------+-------------------+--------------------+\n","\n"]},{"name":"stderr","output_type":"stream","text":["                                                                                \r"]}],"source":["sparkql(\"\"\"SELECT MAX(date) as max_date,\n","            MIN(date) as min_date,\n","            MAX(date) - MIN(date) as avbl_span\n","            FROM train_table\"\"\").show(2)\n","\n","#Number of days is 1687 which is 14 days more than data\n","# available in oil_data. "]},{"cell_type":"markdown","id":"9cf63cc4","metadata":{"papermill":{"duration":0.03353,"end_time":"2023-01-02T01:37:49.962421","exception":false,"start_time":"2023-01-02T01:37:49.928891","status":"completed"},"tags":[]},"source":["Remember the SQL follows the \n","                        \n","                        From\n","                                Join\n","                                \n","                             Where\n","                             \n","                         Groupby\n","                         \n","                                 Select\n","                                 \n","                                        Order by for execution.\n","                                        \n","\n","Based on above execution, order by can see variables present in Select. But G / W cannot see them\n","\n","Lets get joining the train table with the stores and full_oil_table."]},{"cell_type":"code","execution_count":48,"id":"7e9ed8db","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:50.0303Z","iopub.status.busy":"2023-01-02T01:37:50.029432Z","iopub.status.idle":"2023-01-02T01:37:50.411497Z","shell.execute_reply":"2023-01-02T01:37:50.410598Z"},"papermill":{"duration":0.418975,"end_time":"2023-01-02T01:37:50.414243","exception":false,"start_time":"2023-01-02T01:37:49.995268","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+---+----------+---------+----------+-----+-----------+---------+-----+---------+----+-------+----------+----------+\n","| id|      date|store_nbr|    family|sales|onpromotion|store_nbr| city|    state|type|cluster| date_form|dcoilwtico|\n","+---+----------+---------+----------+-----+-----------+---------+-----+---------+----+-------+----------+----------+\n","|  0|2013-01-01|        1|AUTOMOTIVE|  0.0|          0|        1|Quito|Pichincha|   D|     13|2013-01-01|       0.0|\n","|  1|2013-01-01|        1| BABY CARE|  0.0|          0|        1|Quito|Pichincha|   D|     13|2013-01-01|       0.0|\n","+---+----------+---------+----------+-----+-----------+---------+-----+---------+----+-------+----------+----------+\n","only showing top 2 rows\n","\n"]}],"source":["sparkql(\"\"\" SELECT ftt.*, st.*,fot.*\n","        FROM full_train_table ftt JOIN stores_table st\n","        on ftt.store_nbr = st.store_nbr\n","        join full_oil_table fot\n","        on fot.date_form = ftt.date\n","\"\"\").show(2)"]},{"cell_type":"code","execution_count":49,"id":"7b052ad3","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:50.498044Z","iopub.status.busy":"2023-01-02T01:37:50.497743Z","iopub.status.idle":"2023-01-02T01:37:55.634174Z","shell.execute_reply":"2023-01-02T01:37:55.632397Z"},"papermill":{"duration":5.172687,"end_time":"2023-01-02T01:37:55.63633","exception":false,"start_time":"2023-01-02T01:37:50.463643","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["[Stage 112:==========================================>              (3 + 1) / 4]\r"]},{"name":"stdout","output_type":"stream","text":["+----+----------+---------+----------+-----+-----------+---------+-----+---------+----+-------+----------+----------+\n","|  id|      date|store_nbr|    family|sales|onpromotion|store_nbr| city|    state|type|cluster| date_form|dcoilwtico|\n","+----+----------+---------+----------+-----+-----------+---------+-----+---------+----+-------+----------+----------+\n","|3564|2013-01-03|        1|AUTOMOTIVE|  3.0|          0|        1|Quito|Pichincha|   D|     13|2013-01-03|     92.97|\n","|3565|2013-01-03|        1| BABY CARE|  0.0|          0|        1|Quito|Pichincha|   D|     13|2013-01-03|     92.97|\n","+----+----------+---------+----------+-----+-----------+---------+-----+---------+----+-------+----------+----------+\n","only showing top 2 rows\n","\n"]},{"name":"stderr","output_type":"stream","text":["                                                                                \r"]}],"source":["sparkql(\"\"\" SELECT ftt.*, st.*,fot.*\n","        FROM full_train_table ftt JOIN stores_table st\n","        on ftt.store_nbr = st.store_nbr\n","        RIGHT JOIN full_oil_table fot\n","        on fot.date_form = ftt.date\n","\"\"\").show(2)"]},{"cell_type":"markdown","id":"d39d11fc","metadata":{"papermill":{"duration":0.033882,"end_time":"2023-01-02T01:37:55.70431","exception":false,"start_time":"2023-01-02T01:37:55.670428","status":"completed"},"tags":[]},"source":["Lets try validating the join by the usual process of checking the data\n","\n","-- Row Counts of store Number of individual tables and final \n","joined tables"]},{"cell_type":"code","execution_count":50,"id":"f6f53a95","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:55.774299Z","iopub.status.busy":"2023-01-02T01:37:55.77397Z","iopub.status.idle":"2023-01-02T01:37:56.502152Z","shell.execute_reply":"2023-01-02T01:37:56.50129Z"},"papermill":{"duration":0.76631,"end_time":"2023-01-02T01:37:56.504151","exception":false,"start_time":"2023-01-02T01:37:55.737841","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["                                                                                \r"]},{"data":{"text/plain":["3000888"]},"execution_count":50,"metadata":{},"output_type":"execute_result"}],"source":["sparkql(\"\"\"SELECT * FROM train_table\"\"\").count()"]},{"cell_type":"code","execution_count":51,"id":"c868cad9","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:37:56.573398Z","iopub.status.busy":"2023-01-02T01:37:56.573017Z","iopub.status.idle":"2023-01-02T01:38:00.374181Z","shell.execute_reply":"2023-01-02T01:38:00.373202Z"},"papermill":{"duration":3.838932,"end_time":"2023-01-02T01:38:00.376673","exception":false,"start_time":"2023-01-02T01:37:56.537741","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["                                                                                \r"]},{"data":{"text/plain":["2975940"]},"execution_count":51,"metadata":{},"output_type":"execute_result"}],"source":["sparkql(\"\"\" SELECT ftt.*, st.*,fot.*\n","        FROM full_train_table ftt JOIN stores_table st\n","        on ftt.store_nbr = st.store_nbr\n","        join full_oil_table fot\n","        on fot.date_form = ftt.date\n","\"\"\").count()"]},{"cell_type":"markdown","id":"a8676c2e","metadata":{"papermill":{"duration":0.033568,"end_time":"2023-01-02T01:38:00.449539","exception":false,"start_time":"2023-01-02T01:38:00.415971","status":"completed"},"tags":[]},"source":["Hmm the rows has been lost... I guess some of the full oil table \n","has lesser date rows... "]},{"cell_type":"code","execution_count":52,"id":"d26fb211","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:00.51894Z","iopub.status.busy":"2023-01-02T01:38:00.518634Z","iopub.status.idle":"2023-01-02T01:38:02.798674Z","shell.execute_reply":"2023-01-02T01:38:02.79733Z"},"papermill":{"duration":2.317568,"end_time":"2023-01-02T01:38:02.801181","exception":false,"start_time":"2023-01-02T01:38:00.483613","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["                                                                                \r"]},{"data":{"text/plain":["1684"]},"execution_count":52,"metadata":{},"output_type":"execute_result"}],"source":["sparkql(\"\"\"select COUNT(1) as day_data,tt.date\n","            FROM full_train_table tt\n","            GROUP BY tt.date\n","            ORDER BY tt.date\"\"\").count()"]},{"cell_type":"code","execution_count":53,"id":"5aacd2b5","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:02.879326Z","iopub.status.busy":"2023-01-02T01:38:02.878987Z","iopub.status.idle":"2023-01-02T01:38:03.023409Z","shell.execute_reply":"2023-01-02T01:38:03.022537Z"},"papermill":{"duration":0.185182,"end_time":"2023-01-02T01:38:03.026853","exception":false,"start_time":"2023-01-02T01:38:02.841671","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["1674"]},"execution_count":53,"metadata":{},"output_type":"execute_result"}],"source":["sparkql(\"\"\"select COUNT(1) as day_data,tt.date_form\n","            FROM full_oil_table tt\n","            GROUP BY tt.date_form\n","            ORDER BY tt.date_form\"\"\").count()"]},{"cell_type":"code","execution_count":54,"id":"73bdbfe4","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:03.115023Z","iopub.status.busy":"2023-01-02T01:38:03.114704Z","iopub.status.idle":"2023-01-02T01:38:03.120045Z","shell.execute_reply":"2023-01-02T01:38:03.119161Z"},"papermill":{"duration":0.043604,"end_time":"2023-01-02T01:38:03.121839","exception":false,"start_time":"2023-01-02T01:38:03.078235","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["2983068"]},"execution_count":54,"metadata":{},"output_type":"execute_result"}],"source":["# That provides part of the answer.\n","3000888 - 1782 * 10 "]},{"cell_type":"code","execution_count":55,"id":"bb7f3241","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:03.196886Z","iopub.status.busy":"2023-01-02T01:38:03.196441Z","iopub.status.idle":"2023-01-02T01:38:04.695344Z","shell.execute_reply":"2023-01-02T01:38:04.694446Z"},"papermill":{"duration":1.540906,"end_time":"2023-01-02T01:38:04.698457","exception":false,"start_time":"2023-01-02T01:38:03.157551","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["                                                                                \r"]},{"data":{"text/plain":["54"]},"execution_count":55,"metadata":{},"output_type":"execute_result"}],"source":["# Lets check the store numbers. That tallys up with the \n","# 54 store numbers\n","sparkql(\"\"\"select COUNT(1) as day_data,tt.store_nbr\n","            FROM full_train_table tt\n","            GROUP BY tt.store_nbr\n","            ORDER BY tt.store_nbr\"\"\").count()"]},{"cell_type":"markdown","id":"fd79c124","metadata":{"papermill":{"duration":0.034932,"end_time":"2023-01-02T01:38:04.769366","exception":false,"start_time":"2023-01-02T01:38:04.734434","status":"completed"},"tags":[]},"source":["So there we found the culprit. We had to do left outer join.\n","There might be days which is present in train_table and not \n","in oil_data. We need to work on that next"]},{"cell_type":"code","execution_count":56,"id":"c85001ed","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:04.842631Z","iopub.status.busy":"2023-01-02T01:38:04.842262Z","iopub.status.idle":"2023-01-02T01:38:07.406176Z","shell.execute_reply":"2023-01-02T01:38:07.405349Z"},"papermill":{"duration":2.603463,"end_time":"2023-01-02T01:38:07.408837","exception":false,"start_time":"2023-01-02T01:38:04.805374","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["                                                                                \r"]},{"data":{"text/plain":["3000888"]},"execution_count":56,"metadata":{},"output_type":"execute_result"}],"source":["sparkql(\"\"\" SELECT ftt.*, st.*,fot.*\n","        FROM full_train_table ftt JOIN stores_table st\n","        on ftt.store_nbr = st.store_nbr\n","        LEFT JOIN full_oil_table fot\n","        on fot.date_form = ftt.date\"\"\").count()"]},{"cell_type":"code","execution_count":57,"id":"235e26ce","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:07.479816Z","iopub.status.busy":"2023-01-02T01:38:07.479454Z","iopub.status.idle":"2023-01-02T01:38:07.505318Z","shell.execute_reply":"2023-01-02T01:38:07.50448Z"},"papermill":{"duration":0.064375,"end_time":"2023-01-02T01:38:07.508094","exception":false,"start_time":"2023-01-02T01:38:07.443719","status":"completed"},"tags":[]},"outputs":[],"source":["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\n","        FROM full_train_table ftt JOIN stores_table st\n","        on ftt.store_nbr = st.store_nbr\n","        LEFT JOIN full_oil_table fot\n","        on fot.date_form = ftt.date\"\"\"). \\\n","        createOrReplaceTempView(\"train_store_oil_table\")"]},{"cell_type":"code","execution_count":58,"id":"2794b9a0","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:07.591932Z","iopub.status.busy":"2023-01-02T01:38:07.591507Z","iopub.status.idle":"2023-01-02T01:38:11.143452Z","shell.execute_reply":"2023-01-02T01:38:11.142233Z"},"papermill":{"duration":3.590436,"end_time":"2023-01-02T01:38:11.145674","exception":false,"start_time":"2023-01-02T01:38:07.555238","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["[Stage 159:>                                                        (0 + 3) / 3]\r"]},{"name":"stdout","output_type":"stream","text":["+-------+----------+---------+----------+-----+-----------+-----+---------+----+-------+----------+\n","|     id|      date|store_nbr|    family|sales|onpromotion| city|    state|type|cluster|dcoilwtico|\n","+-------+----------+---------+----------+-----+-----------+-----+---------+----+-------+----------+\n","|2975940|2017-08-02|        1|AUTOMOTIVE|  4.0|          0|Quito|Pichincha|   D|     13|      null|\n","|2975941|2017-08-02|        1| BABY CARE|  0.0|          0|Quito|Pichincha|   D|     13|      null|\n","+-------+----------+---------+----------+-----+-----------+-----+---------+----+-------+----------+\n","only showing top 2 rows\n","\n"]},{"name":"stderr","output_type":"stream","text":["                                                                                \r"]}],"source":["sparkql(\"\"\"SELECT tsot.*\n","            FROM train_store_oil_table tsot\n","            WHERE tsot.date = '2017-08-02'\"\"\").show(2)"]},{"cell_type":"code","execution_count":59,"id":"0ecec420","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:11.217325Z","iopub.status.busy":"2023-01-02T01:38:11.216998Z","iopub.status.idle":"2023-01-02T01:38:11.629467Z","shell.execute_reply":"2023-01-02T01:38:11.628609Z"},"papermill":{"duration":0.451663,"end_time":"2023-01-02T01:38:11.63272","exception":false,"start_time":"2023-01-02T01:38:11.181057","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+----------+---------+\n","|      date|total_txn|\n","+----------+---------+\n","|2013-01-01|      770|\n","|2013-01-02|    93215|\n","|2013-01-03|    78504|\n","|2013-01-04|    78494|\n","|2013-01-05|    93573|\n","+----------+---------+\n","only showing top 5 rows\n","\n"]}],"source":["sparkql(\"\"\"SELECT date_format(txt.date,'yyyy-MM-dd') as date,\n","                sum(txt.transactions) as total_txn\n","                FROM txn_table txt\n","            GROUP BY date_format(txt.date,'yyyy-MM-dd')\n","            ORDER BY date\"\"\").show(5)"]},{"cell_type":"code","execution_count":60,"id":"7b0443ba","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:11.714661Z","iopub.status.busy":"2023-01-02T01:38:11.714268Z","iopub.status.idle":"2023-01-02T01:38:11.89646Z","shell.execute_reply":"2023-01-02T01:38:11.895021Z"},"papermill":{"duration":0.223204,"end_time":"2023-01-02T01:38:11.899113","exception":false,"start_time":"2023-01-02T01:38:11.675909","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+-------------------+-------------------+--------------------+\n","|           max_date|           min_date|           avbl_span|\n","+-------------------+-------------------+--------------------+\n","|2017-08-15 00:00:00|2013-01-01 00:00:00|INTERVAL '1687 00...|\n","+-------------------+-------------------+--------------------+\n","\n"]}],"source":["sparkql(\"\"\"SELECT MAX(date) as max_date,\n","            MIN(date) as min_date,\n","            MAX(date) - MIN(date) as avbl_span\n","            FROM txn_table\"\"\").show(2)"]},{"cell_type":"code","execution_count":61,"id":"220ec0c7","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:11.972499Z","iopub.status.busy":"2023-01-02T01:38:11.971912Z","iopub.status.idle":"2023-01-02T01:38:12.477904Z","shell.execute_reply":"2023-01-02T01:38:12.477076Z"},"papermill":{"duration":0.545107,"end_time":"2023-01-02T01:38:12.479997","exception":false,"start_time":"2023-01-02T01:38:11.93489","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["[Row(date='2017-08-11', total_txn=89551),\n"," Row(date='2017-08-12', total_txn=89927),\n"," Row(date='2017-08-13', total_txn=85993),\n"," Row(date='2017-08-14', total_txn=85448),\n"," Row(date='2017-08-15', total_txn=86561)]"]},"execution_count":61,"metadata":{},"output_type":"execute_result"}],"source":["sparkql(\"\"\"SELECT date_format(txt.date,'yyyy-MM-dd') as date,\n","                sum(txt.transactions) as total_txn\n","                FROM txn_table txt\n","            GROUP BY date_format(txt.date,'yyyy-MM-dd')\n","            ORDER BY date\"\"\").tail(5)"]},{"cell_type":"markdown","id":"0067dd9a","metadata":{"papermill":{"duration":0.047152,"end_time":"2023-01-02T01:38:12.575096","exception":false,"start_time":"2023-01-02T01:38:12.527944","status":"completed"},"tags":[]},"source":["There are missing txn data in the middle of the span. Which the above way of checking will not show. Lets proceed with the joining"]},{"cell_type":"code","execution_count":62,"id":"9ce2aaeb","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:12.651888Z","iopub.status.busy":"2023-01-02T01:38:12.651539Z","iopub.status.idle":"2023-01-02T01:38:13.238659Z","shell.execute_reply":"2023-01-02T01:38:13.237771Z"},"papermill":{"duration":0.625823,"end_time":"2023-01-02T01:38:13.24158","exception":false,"start_time":"2023-01-02T01:38:12.615757","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+---+----------+---------+----------+-----+-----------+-----+---------+----+-------+----------+----------+------------+---------+\n","| id|      date|store_nbr|    family|sales|onpromotion| city|    state|type|cluster|dcoilwtico|  txn_date|transactions|store_nbr|\n","+---+----------+---------+----------+-----+-----------+-----+---------+----+-------+----------+----------+------------+---------+\n","|  0|2013-01-01|        1|AUTOMOTIVE|  0.0|          0|Quito|Pichincha|   D|     13|       0.0|2013-01-01|         770|       25|\n","|  1|2013-01-01|        1| BABY CARE|  0.0|          0|Quito|Pichincha|   D|     13|       0.0|2013-01-01|         770|       25|\n","+---+----------+---------+----------+-----+-----------+-----+---------+----+-------+----------+----------+------------+---------+\n","only showing top 2 rows\n","\n"]}],"source":["sparkql(\"\"\"SELECT tsot.*, date_format(txt.date,'yyyy-MM-dd') as txn_date,\n","            txt.transactions, txt.store_nbr\n","            FROM train_store_oil_table tsot LEFT JOIN txn_table txt\n","            on tsot.date = date_format(txt.date,'yyyy-MM-dd')\n","        \"\"\").show(2)"]},{"cell_type":"code","execution_count":63,"id":"f382c9a7","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:13.314599Z","iopub.status.busy":"2023-01-02T01:38:13.314278Z","iopub.status.idle":"2023-01-02T01:38:16.677292Z","shell.execute_reply":"2023-01-02T01:38:16.676369Z"},"papermill":{"duration":3.401118,"end_time":"2023-01-02T01:38:16.679374","exception":false,"start_time":"2023-01-02T01:38:13.278256","status":"completed"},"tags":[]},"outputs":[{"name":"stderr","output_type":"stream","text":["                                                                                \r"]},{"data":{"text/plain":["3000888"]},"execution_count":63,"metadata":{},"output_type":"execute_result"}],"source":["sparkql(\"\"\"SELECT tsot.*, date_format(txt.date,'yyyy-MM-dd') as txn_date,\n","            txt.transactions, txt.store_nbr\n","            FROM train_store_oil_table tsot LEFT JOIN txn_table txt\n","            on tsot.date = date_format(txt.date,'yyyy-MM-dd') and\n","                tsot.store_nbr = txt.store_nbr\n","        \"\"\").count()"]},{"cell_type":"code","execution_count":64,"id":"88b4f992","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:16.762151Z","iopub.status.busy":"2023-01-02T01:38:16.761805Z","iopub.status.idle":"2023-01-02T01:38:16.855873Z","shell.execute_reply":"2023-01-02T01:38:16.855015Z"},"papermill":{"duration":0.135382,"end_time":"2023-01-02T01:38:16.857861","exception":false,"start_time":"2023-01-02T01:38:16.722479","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["83488"]},"execution_count":64,"metadata":{},"output_type":"execute_result"}],"source":["sparkql(\"\"\"SELECT date_format(txt.date,'yyyy-MM-dd') as txn_date,\n","            txt.transactions, txt.store_nbr\n","            FROM txn_table txt\"\"\").count()"]},{"cell_type":"code","execution_count":65,"id":"b106bdc5","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:16.931979Z","iopub.status.busy":"2023-01-02T01:38:16.931658Z","iopub.status.idle":"2023-01-02T01:38:16.9371Z","shell.execute_reply":"2023-01-02T01:38:16.936264Z"},"papermill":{"duration":0.044299,"end_time":"2023-01-02T01:38:16.938882","exception":false,"start_time":"2023-01-02T01:38:16.894583","status":"completed"},"tags":[]},"outputs":[{"data":{"text/plain":["250538137344"]},"execution_count":65,"metadata":{},"output_type":"execute_result"}],"source":["83488 * 3000888"]},{"cell_type":"code","execution_count":66,"id":"49ac7f4e","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:17.013036Z","iopub.status.busy":"2023-01-02T01:38:17.012401Z","iopub.status.idle":"2023-01-02T01:38:17.044764Z","shell.execute_reply":"2023-01-02T01:38:17.043546Z"},"papermill":{"duration":0.072275,"end_time":"2023-01-02T01:38:17.047327","exception":false,"start_time":"2023-01-02T01:38:16.975052","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","            COALESCE(txt.store_nbr, tsot.store_nbr) as store_nbr\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_data\")"]},{"cell_type":"code","execution_count":67,"id":"deae6547","metadata":{"execution":{"iopub.execute_input":"2023-01-02T01:38:17.119976Z","iopub.status.busy":"2023-01-02T01:38:17.119678Z","iopub.status.idle":"2023-01-02T01:38:17.653901Z","shell.execute_reply":"2023-01-02T01:38:17.652822Z"},"papermill":{"duration":0.573085,"end_time":"2023-01-02T01:38:17.656093","exception":false,"start_time":"2023-01-02T01:38:17.083008","status":"completed"},"tags":[]},"outputs":[{"name":"stdout","output_type":"stream","text":["+---+----------+---------+-------------------+---------+-----------+-------+-----------+----+-------+----------+----------+----------+---------+\n","| id|      date|store_nbr|             family|    sales|onpromotion|   city|      state|type|cluster|dcoilwtico|  txn_date|store_txns|store_nbr|\n","+---+----------+---------+-------------------+---------+-----------+-------+-----------+----+-------+----------+----------+----------+---------+\n","|561|2013-01-01|       25|         AUTOMOTIVE|      0.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|562|2013-01-01|       25|          BABY CARE|      0.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|563|2013-01-01|       25|             BEAUTY|      2.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|564|2013-01-01|       25|          BEVERAGES|    810.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|565|2013-01-01|       25|              BOOKS|      0.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|566|2013-01-01|       25|       BREAD/BAKERY|  180.589|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|567|2013-01-01|       25|        CELEBRATION|      0.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|568|2013-01-01|       25|           CLEANING|    186.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|569|2013-01-01|       25|              DAIRY|    143.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|570|2013-01-01|       25|               DELI|    71.09|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|571|2013-01-01|       25|               EGGS|     46.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|572|2013-01-01|       25|       FROZEN FOODS|29.654999|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|573|2013-01-01|       25|          GROCERY I|    700.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|574|2013-01-01|       25|         GROCERY II|     15.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|575|2013-01-01|       25|           HARDWARE|      0.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|576|2013-01-01|       25| HOME AND KITCHEN I|      0.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|577|2013-01-01|       25|HOME AND KITCHEN II|      0.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|578|2013-01-01|       25|    HOME APPLIANCES|      0.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|579|2013-01-01|       25|          HOME CARE|      0.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","|580|2013-01-01|       25|         LADIESWEAR|      0.0|          0|Salinas|Santa Elena|   D|      1|       0.0|2013-01-01|       770|       25|\n","+---+----------+---------+-------------------+---------+-----------+-------+-----------+----+-------+----------+----------+----------+---------+\n","only showing top 20 rows\n","\n"]}],"source":["sparkql(\"\"\"SELECT * FROM all_data_joined_data adj\n","            WHERE adj.date = '2013-01-01'\n","            and adj.store_txns != 0\"\"\").show()"]}],"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":116.518153,"end_time":"2023-01-02T01:38:20.313374","environment_variables":{},"exception":null,"input_path":"__notebook__.ipynb","output_path":"__notebook__.ipynb","parameters":{},"start_time":"2023-01-02T01:36:23.795221","version":"2.3.4"}},"nbformat":4,"nbformat_minor":5}