SparkETLCode.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a322c8ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0eca9240900f410aa45ab8e6bd6f0fc8",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Starting Spark application\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<tr><th>ID</th><th>YARN Application ID</th><th>Kind</th><th>State</th><th>Spark UI</th><th>Driver log</th><th>Current session?</th></tr><tr><td>1</td><td>application_1643708810590_0003</td><td>pyspark</td><td>idle</td><td><a target=\"_blank\" href=\"http://ip-172-31-95-200.ec2.internal:20888/proxy/application_1643708810590_0003/\">Link</a></td><td><a target=\"_blank\" href=\"http://ip-172-31-94-55.ec2.internal:8042/node/containerlogs/container_1643708810590_0003_01_000001/livy\">Link</a></td><td>✔</td></tr></table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SparkSession available as 'spark'.\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<pyspark.sql.session.SparkSession object at 0x7fc18ff56b90>"
     ]
    }
   ],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "94974d3e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "9e7f08f8b5bc4ffbbf39b84650cbc6f3",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# importing required libraries\n",
    "from pyspark.sql.types import StructType, StructField, IntegerType, StringType, BooleanType, DoubleType, LongType\n",
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.functions import concat, lit, col\n",
    "from pyspark.sql.types import *\n",
    "from pyspark.sql.window import Window\n",
    "import pyspark.sql.functions as F\n",
    "from pyspark.sql.functions import row_number\n",
    "from pyspark.sql.functions import monotonically_increasing_id\n",
    "from pyspark.sql import functions as sf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0fc939b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "93ee27b4e03f434dab6745e865874cff",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Using \"StructType\" to manually create datatype for specified columns\n",
    "fileSchema = StructType([StructField('year', IntegerType(),True),\n",
    "                        StructField('month', StringType(),True),\n",
    "                        StructField('day', IntegerType(),True),\n",
    "                        StructField('weekday', StringType(),True),\n",
    "                        StructField('hour', IntegerType(),True),\n",
    "                        StructField('atm_status', StringType(),True),\n",
    "                        StructField('atm_id', StringType(),True),\n",
    "                        StructField('atm_manufacturer', StringType(),True),\n",
    "                        StructField('atm_location', StringType(),True),\n",
    "                        StructField('atm_streetname', StringType(),True),\n",
    "                        StructField('atm_street_number', IntegerType(),True),\n",
    "                        StructField('atm_zipcode', IntegerType(),True),\n",
    "                        StructField('atm_lat', DoubleType(),True),\n",
    "                        StructField('atm_lon', DoubleType(),True),\n",
    "                        StructField('currency', StringType(),True),\n",
    "                        StructField('card_type', StringType(),True),\n",
    "                        StructField('transaction_amount', IntegerType(),True), \n",
    "                        StructField('service', StringType(),True),\n",
    "                        StructField('message_code', StringType(),True),\n",
    "                        StructField('message_text', StringType(),True),\n",
    "                        StructField('weather_lat', DoubleType(),True),\n",
    "                        StructField('weather_lon', DoubleType(),True),\n",
    "                        StructField('weather_city_id', IntegerType(),True),\n",
    "                        StructField('weather_city_name', StringType(),True), \n",
    "                        StructField('temp', DoubleType(),True),\n",
    "                        StructField('pressure', IntegerType(),True),\n",
    "                        StructField('humidity', IntegerType(),True),\n",
    "                        StructField('wind_speed', IntegerType(),True),\n",
    "                        StructField('wind_deg', IntegerType(),True),\n",
    "                        StructField('rain_3h', DoubleType(),True),\n",
    "                        StructField('clouds_all', IntegerType(),True),\n",
    "                        StructField('weather_id', IntegerType(),True),\n",
    "                        StructField('weather_main', StringType(),True),\n",
    "                        StructField('weather_description', StringType(),True),\n",
    "                        ])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e2584498",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f9c4d0571baa44a3b437d81e574846b6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Reading data\n",
    "ATM_data = spark.read.csv(\"/user/root/SRC_ATM_TRANS/\",header=False,schema = fileSchema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2fef7e5d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e6c2e879fe5941ddba0113453c48b8cf",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2468572"
     ]
    }
   ],
   "source": [
    "#Getting count of mainfile\n",
    "ATM_data.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8ade8c61",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "41b8dc098c2f4bfaa611d0ffb68fcdae",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2468300"
     ]
    }
   ],
   "source": [
    "ATM_data.distinct().count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8e02d9b",
   "metadata": {},
   "source": [
    "### Creating DIM_LOC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a25ee5b5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ac67ffe44a7c4b78a192da4f95652bb4",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_LOC_TEMP  = ATM_data.select(\"atm_location\", \"atm_streetname\",\"atm_street_number\",\"atm_zipcode\", \"atm_lat\", \"atm_lon\").distinct()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "18961780",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "fcdba9741608404b895dc2922075aa07",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_LOC_temp = DIM_LOC_TEMP.dropDuplicates(([\"atm_location\", \"atm_streetname\",\"atm_street_number\",\"atm_zipcode\", \"atm_lat\", \"atm_lon\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "0e8704d4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "9e3327628c824b418ccc552069cba324",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "109"
     ]
    }
   ],
   "source": [
    "DIM_LOC_temp.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a6481341",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "47e0697e89a440cfa3709286fa29c04a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_LOC_temp = DIM_LOC_temp.withColumn(\"location_id\",row_number().over(Window.orderBy(monotonically_increasing_id())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c85ca1c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "db5542493273489a82e951076e7b87e7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame[atm_location: string, atm_streetname: string, atm_street_number: int, atm_zipcode: int, atm_lat: double, atm_lon: double, location_id: int]"
     ]
    }
   ],
   "source": [
    "DIM_LOC_temp.orderBy('location_id', ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e0f636ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "8852c49009984063b0fe6db30b9a4229",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+----------------+-------------------+-------------+-------+------+------+\n",
      "|location_id|        location|         streetname|street_number|zipcode|   lat|   lon|\n",
      "+-----------+----------------+-------------------+-------------+-------+------+------+\n",
      "|          1|         Kolding|           Vejlevej|          135|   6000|55.505| 9.457|\n",
      "|          2|  Skelagervej 15|        Skelagervej|           15|   9000|57.023| 9.891|\n",
      "|          3|Intern HolbÃƒÂ¦k|        Slotsvolden|            7|   4300|55.718|11.704|\n",
      "|          4|          Odense|       FÃƒÂ¦lledvej|            3|   5000|55.394| 10.37|\n",
      "|          5|           Ikast|RÃƒÂ¥dhusstrÃƒÂ¦det|           12|   7430|56.139| 9.154|\n",
      "|          6|         Randers|       ÃƒËœstervold|           16|   8900|56.462|10.038|\n",
      "|          7|         Esbjerg|       Strandbygade|           20|   6700|55.468|  8.44|\n",
      "|          8|          Lyngby|        Jernbanevej|            6|   2800|55.772|  12.5|\n",
      "|          9|           Skive|           Adelgade|            8|   7800|56.567| 9.027|\n",
      "|         10|           Durup|             Torvet|            4|   7870|56.745| 8.949|\n",
      "+-----------+----------------+-------------------+-------------+-------+------+------+\n",
      "only showing top 10 rows"
     ]
    }
   ],
   "source": [
    "DIM_LOC = DIM_LOC_temp.select(col('location_id'),col('atm_location').alias(\"location\"),col('atm_streetname').alias('streetname'),col('atm_street_number').alias('street_number'),col('atm_zipcode').alias('zipcode'),col('atm_lat').alias('lat'),col('atm_lon').alias('lon'))\n",
    "DIM_LOC.show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "deb7530d",
   "metadata": {},
   "source": [
    "### Creating DIM_ATM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7f650230",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "86a89a33dd4649ecbed3e98df6d3b95d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_ATM_TEMP = ATM_data.select(\"atm_id\", \"atm_lat\",\"atm_lon\",\"atm_manufacturer\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "332e8c54",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "fe80ac8d09044f8bbc7926899f670f7e",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_ATM_TEMP.registerTempTable(\"atm\")\n",
    "DIM_LOC_temp.registerTempTable(\"loc\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c74234c0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "27e54da3210c4d9ebb6390e3409d4199",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_ATM_1= spark.sql(\"select atm.atm_id,atm.atm_manufacturer,loc.*from atm left join loc on atm.atm_lat==loc.atm_lat and atm.atm_lon==loc.atm_lon\").distinct()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c391c3ca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "35b789c1798c4287954f9c478a62d997",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_ATM_2 =  DIM_ATM_1.withColumn(\"atm_number\",row_number().over(Window.orderBy(monotonically_increasing_id())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "593e3aa4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "77ef84c5eeac49deaf2cbafffa0b331c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DataFrame[atm_id: string, atm_manufacturer: string, atm_location: string, atm_streetname: string, atm_street_number: int, atm_zipcode: int, atm_lat: double, atm_lon: double, location_id: int, atm_number: int]"
     ]
    }
   ],
   "source": [
    "DIM_ATM_2.orderBy('atm_id', ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8c497866",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "aca9ffef815b439d9040f4e7d0c00248",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+----------+----------------+-----------+\n",
      "|atm_id|atm_number|atm_manufacturer|location_id|\n",
      "+------+----------+----------------+-----------+\n",
      "|   102|         1|             NCR|         50|\n",
      "|    10|         2|             NCR|         96|\n",
      "|    50|         3|             NCR|         32|\n",
      "|    89|         4|             NCR|         59|\n",
      "|    73|         5|             NCR|         85|\n",
      "|   101|         6|             NCR|         72|\n",
      "|    92|         7|             NCR|         51|\n",
      "|    20|         8|             NCR|         37|\n",
      "|    30|         9|             NCR|         58|\n",
      "|    24|        10|             NCR|         13|\n",
      "|    46|        11| Diebold Nixdorf|         20|\n",
      "|    63|        12|             NCR|         84|\n",
      "|    66|        13|             NCR|         21|\n",
      "|    12|        14|             NCR|         70|\n",
      "|    86|        15|             NCR|        105|\n",
      "|    16|        16|             NCR|         67|\n",
      "|    82|        17|             NCR|         54|\n",
      "|    41|        18| Diebold Nixdorf|         74|\n",
      "|    77|        19|             NCR|         65|\n",
      "|    88|        20|             NCR|         35|\n",
      "+------+----------+----------------+-----------+\n",
      "only showing top 20 rows"
     ]
    }
   ],
   "source": [
    "DIM_ATM = DIM_ATM_2.select(\"atm.atm_id\", \"atm_number\",\"atm_manufacturer\",\"location_id\")\n",
    "DIM_ATM.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "29e16354",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "16785c0fe0414a058d77a801054c55d5",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_ATM = DIM_ATM.dropDuplicates(([\"atm_id\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "36d6d586",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "af997351c0164b7f964f3d32fb803c0a",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "113"
     ]
    }
   ],
   "source": [
    "DIM_ATM.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df680a61",
   "metadata": {},
   "source": [
    "### creating DIM_DATE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "0a0e5635",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "82dde607c89a40759028721619201567",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "dim_date = ATM_data.select('year','month','day','hour','weekday').distinct()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "6ba1a96d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f6016c967c0144c2a576bdb8507f05c7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------+---+----+--------+\n",
      "|year| month|day|hour| weekday|\n",
      "+----+------+---+----+--------+\n",
      "|2017|August|  1|  11| Tuesday|\n",
      "|2017|August|  3|  21|Thursday|\n",
      "|2017|  July| 29|  17|Saturday|\n",
      "+----+------+---+----+--------+\n",
      "only showing top 3 rows"
     ]
    }
   ],
   "source": [
    "dim_date.show(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ff1869d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5aa2e7a7f1044a9b94614b4aa9a08aab",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_DATE = dim_date.withColumn(\"full_date_time\",from_unixtime(unix_timestamp(concat(dim_date.year.cast(StringType()),dim_date.month.cast(StringType()),lpad(dim_date.day.cast(StringType()),2,'0'),lpad(dim_date.hour.cast(StringType()),2,'0')),'yyyyMMMMddHH'),'YYYY-MM-dd HH:mm:SS'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "1edd99c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6a485c79aa494583bd82f3bdbcbf53eb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+--------+---+----+---------+-------------------+-------+\n",
      "|year|   month|day|hour|  weekday|     full_date_time|date_id|\n",
      "+----+--------+---+----+---------+-------------------+-------+\n",
      "|2017| January|  5|  21| Thursday|2017-01-05 21:00:00|      1|\n",
      "|2017| January| 22|  15|   Sunday|2017-01-22 15:00:00|      2|\n",
      "|2017| January| 23|  18|   Monday|2017-01-23 18:00:00|      3|\n",
      "|2017| January|  4|   4|Wednesday|2017-01-04 04:00:00|      4|\n",
      "|2017|February|  8|  13|Wednesday|2017-02-08 13:00:00|      5|\n",
      "|2017|February| 18|  21| Saturday|2017-02-18 21:00:00|      6|\n",
      "|2017| January| 24|  14|  Tuesday|2017-01-24 14:00:00|      7|\n",
      "|2017|February|  6|  15|   Monday|2017-02-06 15:00:00|      8|\n",
      "|2017| January| 27|   2|   Friday|2017-01-27 02:00:00|      9|\n",
      "|2017| January| 14|   5| Saturday|2017-01-14 05:00:00|     10|\n",
      "|2017| January| 16|  19|   Monday|2017-01-16 19:00:00|     11|\n",
      "|2017| January| 17|  19|  Tuesday|2017-01-17 19:00:00|     12|\n",
      "|2017|February| 13|  14|   Monday|2017-02-13 14:00:00|     13|\n",
      "|2017| January| 18|   3|Wednesday|2017-01-18 03:00:00|     14|\n",
      "|2017| January|  3|  12|  Tuesday|2017-01-03 12:00:00|     15|\n",
      "|2017| January|  7|  15| Saturday|2017-01-07 15:00:00|     16|\n",
      "|2017|February| 14|  12|  Tuesday|2017-02-14 12:00:00|     17|\n",
      "|2017|February|  7|  21|  Tuesday|2017-02-07 21:00:00|     18|\n",
      "|2017| January|  2|   8|   Monday|2017-01-02 08:00:00|     19|\n",
      "|2017|February|  3|   9|   Friday|2017-02-03 09:00:00|     20|\n",
      "|2017|February|  3|   7|   Friday|2017-02-03 07:00:00|     21|\n",
      "|2017| January| 11|   1|Wednesday|2017-01-11 01:00:00|     22|\n",
      "|2017| January| 24|   2|  Tuesday|2017-01-24 02:00:00|     23|\n",
      "|2017| January|  5|  12| Thursday|2017-01-05 12:00:00|     24|\n",
      "|2017| January| 31|   3|  Tuesday|2017-01-31 03:00:00|     25|\n",
      "|2017|February| 10|   7|   Friday|2017-02-10 07:00:00|     26|\n",
      "|2017| January| 18|   7|Wednesday|2017-01-18 07:00:00|     27|\n",
      "|2017| January| 21|   6| Saturday|2017-01-21 06:00:00|     28|\n",
      "|2017| January| 27|  21|   Friday|2017-01-27 21:00:00|     29|\n",
      "|2017|February| 13|  16|   Monday|2017-02-13 16:00:00|     30|\n",
      "|2017|February|  4|  13| Saturday|2017-02-04 13:00:00|     31|\n",
      "|2017| January| 22|  16|   Sunday|2017-01-22 16:00:00|     32|\n",
      "|2017|February| 11|   9| Saturday|2017-02-11 09:00:00|     33|\n",
      "|2017| January|  9|  15|   Monday|2017-01-09 15:00:00|     34|\n",
      "|2017| January| 14|  23| Saturday|2017-01-14 23:00:00|     35|\n",
      "|2017|February|  4|  23| Saturday|2017-02-04 23:00:00|     36|\n",
      "|2017| January| 12|  10| Thursday|2017-01-12 10:00:00|     37|\n",
      "|2017|February|  1|   7|Wednesday|2017-02-01 07:00:00|     38|\n",
      "|2017|   April|  7|   9|   Friday|2017-04-07 09:00:00|     39|\n",
      "|2017|   March| 17|   1|   Friday|2017-03-17 01:00:00|     40|\n",
      "+----+--------+---+----+---------+-------------------+-------+\n",
      "only showing top 40 rows"
     ]
    }
   ],
   "source": [
    "DIM_DATE = DIM_DATE.withColumn(\"date_id\",F.row_number().over(Window.partitionBy().orderBy(DIM_DATE[\"year\"])))\n",
    "DIM_DATE.show(40)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "aae1baef",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3d64be82f7014170abfdbfefa6b34262",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+-------------------+----+------+---+----+---------+\n",
      "|date_id|     full_date_time|year| month|day|hour|  weekday|\n",
      "+-------+-------------------+----+------+---+----+---------+\n",
      "|      1|2017-08-01 11:00:00|2017|August|  1|  11|  Tuesday|\n",
      "|      2|2017-08-03 21:00:00|2017|August|  3|  21| Thursday|\n",
      "|      3|2017-07-29 17:00:00|2017|  July| 29|  17| Saturday|\n",
      "|      4|2017-08-03 15:00:00|2017|August|  3|  15| Thursday|\n",
      "|      5|2017-08-13 02:00:00|2017|August| 13|   2|   Sunday|\n",
      "|      6|2017-07-06 14:00:00|2017|  July|  6|  14| Thursday|\n",
      "|      7|2017-07-13 16:00:00|2017|  July| 13|  16| Thursday|\n",
      "|      8|2017-07-30 13:00:00|2017|  July| 30|  13|   Sunday|\n",
      "|      9|2017-08-03 05:00:00|2017|August|  3|   5| Thursday|\n",
      "|     10|2017-08-12 22:00:00|2017|August| 12|  22| Saturday|\n",
      "|     11|2017-07-14 02:00:00|2017|  July| 14|   2|   Friday|\n",
      "|     12|2017-08-02 22:00:00|2017|August|  2|  22|Wednesday|\n",
      "|     13|2017-08-10 18:00:00|2017|August| 10|  18| Thursday|\n",
      "|     14|2017-07-03 10:00:00|2017|  July|  3|  10|   Monday|\n",
      "|     15|2017-07-27 18:00:00|2017|  July| 27|  18| Thursday|\n",
      "|     16|2017-07-11 18:00:00|2017|  July| 11|  18|  Tuesday|\n",
      "|     17|2017-07-25 11:00:00|2017|  July| 25|  11|  Tuesday|\n",
      "|     18|2017-07-11 02:00:00|2017|  July| 11|   2|  Tuesday|\n",
      "|     19|2017-07-27 15:00:00|2017|  July| 27|  15| Thursday|\n",
      "|     20|2017-07-23 17:00:00|2017|  July| 23|  17|   Sunday|\n",
      "+-------+-------------------+----+------+---+----+---------+\n",
      "only showing top 20 rows"
     ]
    }
   ],
   "source": [
    "DIM_DATE=DIM_DATE.select(\"date_id\",\"full_date_time\",\"year\",\"month\",\"day\",\"hour\",\"weekday\")\n",
    "DIM_DATE.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e767800",
   "metadata": {},
   "source": [
    "### Creating DIM_CARD_TYPE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "0a44b058",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "642470c369f0466aa3bb20e80b15982c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Identifying CARD TYPE DIM From Source File.\n",
    "TEMP_DIM_CARD= ATM_data.select('card_type')\n",
    "DIM_CARD_TYPE = TEMP_DIM_CARD.dropDuplicates((['card_type']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "76355a27",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ea2b2f536555436189fb5f72522b17b7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_CARD_TYPE = DIM_CARD_TYPE.withColumn(\"card_type_id\",row_number().over(Window.orderBy(monotonically_increasing_id())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "3f1591ee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ab18d1cd7aa64e64b8892bdf6dba9b26",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+--------------------+\n",
      "|card_type_id|           card_type|\n",
      "+------------+--------------------+\n",
      "|           1|Visa Dankort - on-us|\n",
      "|           2|  Mastercard - on-us|\n",
      "|           3|         HÃƒÂ¦vekort|\n",
      "|           4|            VisaPlus|\n",
      "|           5|     Dankort - on-us|\n",
      "|           6|        Visa Dankort|\n",
      "|           7| HÃƒÂ¦vekort - on-us|\n",
      "|           8|              CIRRUS|\n",
      "|           9|                VISA|\n",
      "|          10|             Maestro|\n",
      "|          11|          MasterCard|\n",
      "|          12|             Dankort|\n",
      "+------------+--------------------+"
     ]
    }
   ],
   "source": [
    "DIM_CARD_TYPE =DIM_CARD_TYPE.select(\"card_type_id\",\"card_type\")\n",
    "DIM_CARD_TYPE.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "25c76f0e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c3e5081277984af2b3055e0e221a71fb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "file1 = ATM_data.join(DIM_ATM_1,on=['atm_id','atm_manufacturer','atm_location','atm_streetname','atm_street_number', 'atm_zipcode', 'atm_lat', 'atm_lon'],how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c5f21200",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "646bbd0fb30c48f4a61323095a09a69f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2468572"
     ]
    }
   ],
   "source": [
    "file1.filter(file1.location_id.isNotNull()).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "5e594b01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f878862247ec48739da47f111ccd5fe6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "file1 = file1.withColumnRenamed('location_id','tlocation_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "189227d0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0bbe7cd7d292453ba7edd8fbf114dab0",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "file2 = file1.join(DIM_ATM_2,on=['atm_id','atm_manufacturer','atm_location','atm_streetname','atm_street_number', 'atm_zipcode', 'atm_lat', 'atm_lon'],how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "c4b938d0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c514bab305ab4ab6942ba8e7e6347d25",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2468572"
     ]
    }
   ],
   "source": [
    "file2.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "737db381",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ef0eca3d8e894f1b9ccee3d2ea9ff4b5",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2468572"
     ]
    }
   ],
   "source": [
    "file2.filter(file2.atm_number.isNotNull()).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "fe90a1b5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "dd4ede8a78de40d3852c4074135940f6",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "file2 = file2.drop('tlocation_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "8e0cca4a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3bebe9d317c44e6db3f88ad4d92b7e08",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "file3 = file2.withColumnRenamed('card_type','card_types')\n",
    "ATM_data_1 = file3.join(DIM_CARD_TYPE, file3.card_types == DIM_CARD_TYPE.card_type,how='LEFT')  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "0bd83ca5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e928c26b1b994c2fab490ed4dcdfe7e1",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2468572"
     ]
    }
   ],
   "source": [
    "ATM_data_1 .filter(ATM_data_1.card_type_id.isNotNull()).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "bcfe7846",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4b84085aafa24d1eaa097671eb7506df",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ATM_data_1 = ATM_data_1.withColumn(\"full_date_time\",from_unixtime(unix_timestamp(concat(dim_date.year.cast(StringType()),dim_date.month.cast(StringType()),lpad(dim_date.day.cast(StringType()),2,'0'),lpad(dim_date.hour.cast(StringType()),2,'0')),'yyyyMMMMddHH'),'YYYY-MM-dd HH:mm:SS'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "3f794e93",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6588b4888456419dab86b0f64fee845b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ATM_data_1 = ATM_data_1.withColumnRenamed('year','ryear')\n",
    "ATM_data_1 = ATM_data_1.withColumnRenamed('month','rmonth')\n",
    "ATM_data_1 = ATM_data_1.withColumnRenamed('day','rday')\n",
    "ATM_data_1 = ATM_data_1.withColumnRenamed('weekday','rweekday')\n",
    "ATM_data_1 = ATM_data_1.withColumnRenamed('hour','rhour')\n",
    "ATM_data_1 = ATM_data_1.withColumnRenamed('full_date_time','rfull_date_time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "8d950f5a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "06703d7a64494be6ae7fb65374342fd9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ATM_data_1 = ATM_data_1.join(DIM_DATE, ATM_data_1.rfull_date_time == DIM_DATE.full_date_time,how='LEFT') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "6e32ab49",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0f792bbd83394113ad49a67754037314",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ATM_data_1 = ATM_data_1.drop('year','month','day','weekday','hour','full_date_time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "027e458f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a2b0db0ddfe041b1a7ee3cde67804ef5",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ATM_data_1 = ATM_data_1.withColumnRenamed('ryear','year')\n",
    "ATM_data_1 = ATM_data_1.withColumnRenamed('rmonth','month')\n",
    "ATM_data_1 = ATM_data_1.withColumnRenamed('rday','day')\n",
    "ATM_data_1 = ATM_data_1.withColumnRenamed('rweekday','weekday')\n",
    "ATM_data_1 = ATM_data_1.withColumnRenamed('rhour','hour')\n",
    "ATM_data_1 = ATM_data_1.withColumnRenamed('rfull_date_time','full_date_time')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "f97e58db",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "90400f7c227043bfb0f6c417e1b0f25d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ATM_data_1 = ATM_data_1.withColumn(\"trans_id\",row_number().over(Window.orderBy(monotonically_increasing_id())))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23d97040",
   "metadata": {},
   "source": [
    "### Creating FACT_ATM_TRANS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "00245f6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "2e4fab301ced4e1393bca08e77454f31",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "FACT_ATM_TRANS = ATM_data_1.select(\"trans_id\",\"atm_id\", \"location_id\",\"date_id\",\"card_type_id\",\"atm_status\",\"currency\",\"service\",\"transaction_amount\",\"message_code\",\"message_text\",\"rain_3h\",\"clouds_all\",\"weather_id\",\"weather_main\",\"weather_description\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "ff62b066",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6985d8cb31114262b15046773a77f1d9",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2468572"
     ]
    }
   ],
   "source": [
    "FACT_ATM_TRANS.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "1d4e5460",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a8a633f3fcb844d3850b396139e6e709",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "113"
     ]
    }
   ],
   "source": [
    "FACT_ATM_TRANS.select(\"atm_id\").distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "98a5d942",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "64fa9d65fc574496b11b034409ef0b10",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "109"
     ]
    }
   ],
   "source": [
    "FACT_ATM_TRANS.select(\"location_id\").distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "7c4e260b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "04d3e5912ee74abebdd6344e1ea60a4b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8685"
     ]
    }
   ],
   "source": [
    "FACT_ATM_TRANS.select(\"date_id\").distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "d46c8a65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6557f0a72cbd442eb85d8afec286794b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12"
     ]
    }
   ],
   "source": [
    "FACT_ATM_TRANS.select(\"card_type_id\").distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "c80d2d88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cb14b79b2926411a8b655729a6c0284c",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "FACT_ATM_TRANS =FACT_ATM_TRANS.withColumnRenamed('location_id','weather_loc_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "12eaae15",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e10b45d3c80846e79e56c8c55fb20779",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+------+--------------+-------+------------+----------+--------+----------+------------------+------------+------------+-------+----------+----------+------------+-------------------+\n",
      "|trans_id|atm_id|weather_loc_id|date_id|card_type_id|atm_status|currency|   service|transaction_amount|message_code|message_text|rain_3h|clouds_all|weather_id|weather_main|weather_description|\n",
      "+--------+------+--------------+-------+------------+----------+--------+----------+------------------+------------+------------+-------+----------+----------+------------+-------------------+\n",
      "|       1|    51|            30|   8576|           1|    Active|     DKK|Withdrawal|              3562|        null|        null|  1.105|        92|       500|        Rain|         light rain|\n",
      "|       2|    51|            30|   1195|           2|    Active|     DKK|Withdrawal|              8441|        null|        null|    0.0|        92|       804|      Clouds|    overcast clouds|\n",
      "|       3|    51|            30|   5851|           2|    Active|     DKK|Withdrawal|              7798|        null|        null|    0.0|        92|       804|      Clouds|    overcast clouds|\n",
      "|       4|    51|            30|   5851|           1|    Active|     DKK|Withdrawal|                 5|        null|        null|    0.0|        92|       804|      Clouds|    overcast clouds|\n",
      "|       5|    51|            30|   5498|           2|    Active|     DKK|Withdrawal|              9545|        null|        null|    0.0|        68|       803|      Clouds|      broken clouds|\n",
      "|       6|    51|            30|   5508|           6|    Active|     DKK|Withdrawal|              2135|        null|        null|    0.0|        68|       803|      Clouds|      broken clouds|\n",
      "|       7|    51|            30|   5508|          11|    Active|     DKK|Withdrawal|              9789|        null|        null|    0.0|        68|       803|      Clouds|      broken clouds|\n",
      "|       8|    51|            30|   5508|          11|    Active|     DKK|Withdrawal|              4670|        null|        null|    0.0|        68|       803|      Clouds|      broken clouds|\n",
      "|       9|    51|            30|   4821|           1|    Active|     DKK|Withdrawal|               999|        null|        null|    0.0|        68|       803|      Clouds|      broken clouds|\n",
      "|      10|    51|            30|   4821|           2|    Active|     DKK|Withdrawal|              6034|        null|        null|    0.0|        68|       803|      Clouds|      broken clouds|\n",
      "|      11|    51|            30|   4821|           1|    Active|     DKK|Withdrawal|              8166|        null|        null|    0.0|        68|       803|      Clouds|      broken clouds|\n",
      "|      12|    51|            30|   4821|           1|    Active|     DKK|Withdrawal|              9639|        null|        null|    0.0|        68|       803|      Clouds|      broken clouds|\n",
      "|      13|    51|            30|   2597|           1|    Active|     DKK|Withdrawal|              2138|        null|        null|    0.0|        32|       802|      Clouds|   scattered clouds|\n",
      "|      14|    51|            30|   2597|           7|    Active|     DKK|Withdrawal|              7491|        null|        null|    0.0|        32|       802|      Clouds|   scattered clouds|\n",
      "|      15|    51|            30|   2597|          11|    Active|     DKK|Withdrawal|              1018|        null|        null|    0.0|        32|       802|      Clouds|   scattered clouds|\n",
      "|      16|    51|            30|   2597|           6|    Active|     DKK|Withdrawal|               753|        null|        null|    0.0|        32|       802|      Clouds|   scattered clouds|\n",
      "|      17|    51|            30|   2597|           1|    Active|     DKK|Withdrawal|              9955|        null|        null|    0.0|        32|       802|      Clouds|   scattered clouds|\n",
      "|      18|    51|            30|    216|          11|    Active|     DKK|Withdrawal|              3673|        null|        null|    0.0|        56|       803|      Clouds|      broken clouds|\n",
      "|      19|    51|            30|    216|          11|    Active|     DKK|Withdrawal|              2748|        null|        null|    0.0|        56|       803|      Clouds|      broken clouds|\n",
      "|      20|    51|            30|    216|           2|    Active|     DKK|Withdrawal|              1183|        null|        null|    0.0|        56|       803|      Clouds|      broken clouds|\n",
      "+--------+------+--------------+-------+------------+----------+--------+----------+------------------+------------+------------+-------+----------+----------+------------+-------------------+\n",
      "only showing top 20 rows"
     ]
    }
   ],
   "source": [
    "FACT_ATM_TRANS = FACT_ATM_TRANS.select(\"trans_id\",\"atm_id\",\"weather_loc_id\",\"date_id\",\"card_type_id\",\"atm_status\",\"currency\",\"service\",\"transaction_amount\",\"message_code\",\"message_text\",\"rain_3h\",\"clouds_all\",\"weather_id\",\"weather_main\",\"weather_description\")\n",
    "FACT_ATM_TRANS.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca716c20",
   "metadata": {},
   "source": [
    "### Copying into s3 bucket"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "c7d3fa36",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f0f945ff7cf8460f9a5b26f5dfa68409",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_ATM.write.csv(\"s3://nikhil-etl/DIM_ATM.csv\",mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "02d9d65d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c4e9eb19292847f1823bc5570efb1856",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_LOC.write.csv(\"s3://nikhil-etl/DIM_LOCATION.csv\",mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "cc094af6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e4700f7baa754b41be784d0193b66e40",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_DATE.write.csv(\"s3://nikhil-etl/DIM_DATE.csv\",mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "927fb092",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0f493e05a7c741abb9814e538c6f084d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "DIM_CARD_TYPE.write.csv(\"s3://nikhil-etl/DIM_CARD_TYPE.csv\",mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "c902169f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b934013b3961496d8c265d9ea7ef3567",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox()"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "FloatProgress(value=0.0, bar_style='info', description='Progress:', layout=Layout(height='25px', width='50%'),…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "FACT_ATM_TRANS.write.csv(\"s3://nikhil-etl/FACT_ATM_TRANS.csv\",mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec3da868",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PySpark",
   "language": "",
   "name": "pysparkkernel"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "python",
    "version": 2
   },
   "mimetype": "text/x-python",
   "name": "pyspark",
   "pygments_lexer": "python2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}