dakelake.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "4b40aa08",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0f31df9eb2564a20b8b89ab695da2c65",
       "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": [
    "from datetime import datetime\n",
    "import os\n",
    "from pyspark.sql.functions import udf, col\n",
    "from pyspark.sql.functions import year, month, dayofmonth, hour, weekofyear, date_format, dayofweek\n",
    "import pyspark.sql.types as T    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "4528870a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "af1ce3fd7e6446bc97ee6bd9c8ef1243",
       "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": [
    "input_data = \"s3a://udacity-dend/\"\n",
    "output_data = \"s3://sparkifydatasource/sparkify_test/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "59985d4b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4994d3d6ad29425e97f7ebbbd56894ce",
       "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": [
    "song_schema = T.StructType([T.StructField('artist_id'       , T.StringType())\n",
    "                             , T.StructField('artist_latitude'  , T.StringType())\n",
    "                             , T.StructField('artist_location'  , T.StringType())\n",
    "                             , T.StructField('artist_longitude' , T.StringType())\n",
    "                             , T.StructField('artist_name'      , T.StringType())\n",
    "                             , T.StructField('duration'         , T.DoubleType())\n",
    "                             , T.StructField('num_songs'        , T.IntegerType())\n",
    "                             , T.StructField('song_id'          , T.StringType())\n",
    "                             , T.StructField('title'            , T.StringType())\n",
    "                             , T.StructField('year'             , T.IntegerType())\n",
    "                              ])\n",
    "# Log table schema for JSON read\n",
    "log_schema = T.StructType([ T.StructField('artist'          , T.StringType())\n",
    "                              , T.StructField('auth'            , T.StringType())\n",
    "                              , T.StructField('gender'          , T.StringType())\n",
    "                              , T.StructField('lastName'        , T.StringType())\n",
    "                              , T.StructField('length'          , T.DoubleType())\n",
    "                              , T.StructField('level'           , T.StringType())\n",
    "                              , T.StructField('firstName'       , T.StringType())\n",
    "                              , T.StructField('itemInSession'   , T.IntegerType())\n",
    "                              , T.StructField('location'        , T.StringType())\n",
    "                              , T.StructField('method'          , T.StringType())\n",
    "                              , T.StructField('page'            , T.StringType())\n",
    "                              , T.StructField('registration'    , T.DoubleType())\n",
    "                              , T.StructField('sessionId'       , T.IntegerType())\n",
    "                              , T.StructField('song'            , T.StringType())\n",
    "                              , T.StructField('status'          , T.IntegerType())\n",
    "                              , T.StructField('ts'              , T.LongType())\n",
    "                              , T.StructField('userAgent'       , T.StringType())\n",
    "                              , T.StructField('userId'          , T.StringType())\n",
    "                              ])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6b0b7815",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "67ee0f8451484b1f98df05801f38bc3d",
       "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": [
    "song_data = os.path.join(input_data, 'song_data/*/*/*/*.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "980f730c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3d0fa7c1178141c8ad6c8f31d137f970",
       "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": [
    "df = spark.read.json(song_data, schema=song_schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "773003af",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1edba9444acb4c619bccec459b402b12",
       "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": [
      "root\n",
      " |-- artist_id: string (nullable = true)\n",
      " |-- artist_latitude: string (nullable = true)\n",
      " |-- artist_location: string (nullable = true)\n",
      " |-- artist_longitude: string (nullable = true)\n",
      " |-- artist_name: string (nullable = true)\n",
      " |-- duration: double (nullable = true)\n",
      " |-- num_songs: integer (nullable = true)\n",
      " |-- song_id: string (nullable = true)\n",
      " |-- title: string (nullable = true)\n",
      " |-- year: integer (nullable = true)"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "359f5c66",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "7b9c761203fb447fb1ceae0d0cafb627",
       "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": [
    "# extract columns to create songs table\n",
    "songs_table = df.select('song_id', 'title', 'artist_id', 'year', 'duration')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "30ecf158",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f06bbd58e9c740baab15084e98d4c477",
       "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": [
      "root\n",
      " |-- song_id: string (nullable = true)\n",
      " |-- title: string (nullable = true)\n",
      " |-- artist_id: string (nullable = true)\n",
      " |-- year: integer (nullable = true)\n",
      " |-- duration: double (nullable = true)\n",
      "\n",
      "+------------------+--------------------+------------------+----+----------+\n",
      "|           song_id|               title|         artist_id|year|  duration|\n",
      "+------------------+--------------------+------------------+----+----------+\n",
      "|SOVIYJY12AF72A4B00|The Dead Next Doo...|AR4T2IF1187B9ADBB7|1983| 233.22077|\n",
      "|SOVYXYL12AF72A3373|Rebel Yell (1999 ...|AR4T2IF1187B9ADBB7|1983| 287.92118|\n",
      "|SOEPTVC12A67ADD0DA|To Zucchabar [\"Gl...|ARQ846I1187B9A7083|   0| 196.04853|\n",
      "|SOLQYSZ12AB0181F97|    Mony Mony (Live)|AR4T2IF1187B9ADBB7|1987| 247.53587|\n",
      "|SOVPFJK12A6701CB16|Barcelona - (Frie...|AR3TZ691187FB3DBB1|2000| 273.44934|\n",
      "|SOETDBF12A81C20BC0|       Body Snatcher|AR4T2IF1187B9ADBB7|2005| 237.06077|\n",
      "|SOIEXLS12A6D4F792F|           Shangrila|AR4T2IF1187B9ADBB7|1993| 443.14077|\n",
      "|SOQEBML12A8C136AA4|Werther (1997 Dig...|AR5R7791187FB3A8C3|   0|  55.40526|\n",
      "|SOTCIHX12A8C13DDD2|Finally_ as that ...|ARYOIZG1187FB41E30|2006| 483.34322|\n",
      "|SODUMDU12AC468A22B|We're Skrewed (Ot...|ARHOSMU1242078130D|   0|  249.5473|\n",
      "|SOPSXLI12A6D4FA418|Practical Cats - ...|ARR79V31187FB5B96E|   0| 251.03628|\n",
      "|SOLLALT12A8C1399F3|Piano Concerto No...|ARWMEJW11F4C83C123|   0| 319.37261|\n",
      "|SOKUATC12AB01853F3|Turandot: Nessun ...|ARV481W1187FB38CD9|1997| 191.13751|\n",
      "|SOIGELY12A6D4F65C6|Aida (1986 Digita...|ARXLQYU11F4C83E050|   0| 345.25995|\n",
      "|SOBTCUI12A8AE48B70|Faust: Ballet Mus...|ARSUVLW12454A4C8B8|   0|  94.56281|\n",
      "|SOHPSTY12A6D4F719C|Ms. New Booty (Ed...|ARJYD111187FB3AC15|   0| 252.52526|\n",
      "|SONVWOX12A8C137FF5|Le Roi de Lahore_...|ARIDEBJ1187B9A3754|   0| 250.48771|\n",
      "|SOQYHYG12AB017CF0B|The Horrors Of Is...|ARNVCB81187B9ACBDF|2004| 279.95383|\n",
      "|SOMDBLQ12A6D4F8B72|Quartet for Piano...|AREVPHS1187FB3B6EB|   0| 496.53506|\n",
      "|SOAZUNV12A8C13923B|A Faust Symphony ...|ARSUVLW12454A4C8B8|   0|1237.86404|\n",
      "+------------------+--------------------+------------------+----+----------+\n",
      "only showing top 20 rows"
     ]
    }
   ],
   "source": [
    "type(songs_table)\n",
    "songs_table.printSchema()\n",
    "songs_table.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "afe4ef10",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "0257737365fa41faa4ce049a29a3b5b5",
       "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": [
      "14896"
     ]
    }
   ],
   "source": [
    "songs_table.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "01acea7b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "bb34433eefcd4a92ae005911626958b9",
       "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": [
    "song_out_path = os.path.join(output_data, 'sparkify_songs_table/')\n",
    "songs_table.write.parquet(song_out_path, mode='overwrite', partitionBy=('year','artist_id'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "0268e233",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "10c962b8c01c4cf381d5c6ae1e63c94d",
       "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": [
    "# extract columns to create artists table\n",
    "artists_table = df.select('artist_id', \\\n",
    "                              col('artist_name').alias('name'), \\\n",
    "                              col('artist_location').alias('location'),\\\n",
    "                              col('artist_latitude').alias('latitude'),\\\n",
    "                              col('artist_longitude').alias('longitude'))\\\n",
    "                              .dropDuplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "334f411b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1a07d76eee214a1297812849f42c8804",
       "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": [
      "root\n",
      " |-- artist_id: string (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- latitude: string (nullable = true)\n",
      " |-- longitude: string (nullable = true)"
     ]
    }
   ],
   "source": [
    "artists_table.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "a326ccff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "aa324d932c454cfe99f220bbff561c34",
       "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": [
    " # write artists table to parquet files\n",
    "artist_out_path = os.path.join(output_data, 'sparkify_artist_table/')\n",
    "artists_table.write.parquet(artist_out_path, mode='overwrite')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "41b134ed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f37a30aefab9449e8cb46d7445c53f50",
       "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": [
    "# get filepath to log data file\n",
    "log_data = os.path.join(input_data, 'log_data/*/*/*.json')\n",
    "df = spark.read.json(log_data, schema=log_schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "a7b99008",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "db961482f90649e298bc137124cbe0bd",
       "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": [
    "# filter by actions for song plays\n",
    "df = df.filter(df.page == 'NextSong')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "cab843e5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "3543a4c7f5ff44a98bac1ca7e053a30a",
       "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",
      "|              artist|     auth|gender|lastName|   length|level|firstName|itemInSession|            location|method|    page|     registration|sessionId|                song|status|           ts|           userAgent|userId|\n",
      "+--------------------+---------+------+--------+---------+-----+---------+-------------+--------------------+------+--------+-----------------+---------+--------------------+------+-------------+--------------------+------+\n",
      "|            Harmonia|Logged In|     M|   Smith|655.77751| free|     Ryan|            0|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|       Sehr kosmisch|   200|1542241826796|\"Mozilla/5.0 (X11...|    26|\n",
      "|         The Prodigy|Logged In|     M|   Smith|260.07465| free|     Ryan|            1|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|     The Big Gundown|   200|1542242481796|\"Mozilla/5.0 (X11...|    26|\n",
      "|               Train|Logged In|     M|   Smith|205.45261| free|     Ryan|            2|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      583|            Marry Me|   200|1542242741796|\"Mozilla/5.0 (X11...|    26|\n",
      "|         Sony Wonder|Logged In|     M|Gonzalez|218.06975| free|   Samuel|            0|Houston-The Woodl...|   PUT|NextSong|1.540492941796E12|      597|           Blackbird|   200|1542253449796|\"Mozilla/5.0 (Mac...|    61|\n",
      "|           Van Halen|Logged In|     F|  Levine|289.38404| paid|    Tegan|            2|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|Best Of Both Worl...|   200|1542260935796|\"Mozilla/5.0 (Mac...|    80|\n",
      "|           Magic Sam|Logged In|     F|  Levine|132.04853| paid|    Tegan|            3|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|Call Me If You Ne...|   200|1542261224796|\"Mozilla/5.0 (Mac...|    80|\n",
      "|Edward Sharpe & T...|Logged In|     F|  Levine|306.31138| paid|    Tegan|            4|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|                Home|   200|1542261356796|\"Mozilla/5.0 (Mac...|    80|\n",
      "|Usher featuring w...|Logged In|     F|  Levine|395.72853| paid|    Tegan|            5|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|                 OMG|   200|1542261662796|\"Mozilla/5.0 (Mac...|    80|\n",
      "|         Helen Reddy|Logged In|     F|  Levine|176.50893| paid|    Tegan|            7|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602| Candle On The Water|   200|1542262057796|\"Mozilla/5.0 (Mac...|    80|\n",
      "|        Taylor Swift|Logged In|     F|  Levine|201.06404| paid|    Tegan|            8|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|            Our Song|   200|1542262233796|\"Mozilla/5.0 (Mac...|    80|\n",
      "|           Sean Paul|Logged In|     F|  Levine|245.34159| paid|    Tegan|            9|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|Baby Boy [feat. B...|   200|1542262434796|\"Mozilla/5.0 (Mac...|    80|\n",
      "|         Soundgarden|Logged In|     F|    Koch|272.19546| paid|     Lily|            0|Chicago-Napervill...|   PUT|NextSong|1.541048010796E12|      582|      Black Hole Sun|   200|1542262456796|\"Mozilla/5.0 (X11...|    15|\n",
      "|         The Killers|Logged In|     F|  Levine|360.75057| paid|    Tegan|           10|Portland-South Po...|   PUT|NextSong|1.540794356796E12|      602|               Human|   200|1542262679796|\"Mozilla/5.0 (Mac...|    80|\n",
      "|       Amy Winehouse|Logged In|     F|    Koch|165.11955| paid|     Lily|            1|Chicago-Napervill...|   PUT|NextSong|1.541048010796E12|      582|            Addicted|   200|1542262728796|\"Mozilla/5.0 (X11...|    15|\n",
      "|      Steve Anderson|Logged In|     F|    Koch|265.06404| paid|     Lily|            2|Chicago-Napervill...|   PUT|NextSong|1.541048010796E12|      582|                 Air|   200|1542262893796|\"Mozilla/5.0 (X11...|    15|\n",
      "|          Rob Zombie|Logged In|     F|    Koch|220.13342| paid|     Lily|            3|Chicago-Napervill...|   PUT|NextSong|1.541048010796E12|      582|          Superbeast|   200|1542263158796|\"Mozilla/5.0 (X11...|    15|\n",
      "|  Deadmau5 & Kaskade|Logged In|     F|    Koch|595.56526| paid|     Lily|            4|Chicago-Napervill...|   PUT|NextSong|1.541048010796E12|      582|          I Remember|   200|1542263378796|\"Mozilla/5.0 (X11...|    15|\n",
      "|        Shania Twain|Logged In|     M|   Smith| 213.7073| free|     Ryan|            0|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      607|Don't Be Stupid (...|   200|1542265716796|\"Mozilla/5.0 (X11...|    26|\n",
      "|      Los Campesinos|Logged In|     M|   Smith|138.10893| free|     Ryan|            1|San Jose-Sunnyval...|   PUT|NextSong|1.541016707796E12|      607|We throw parties_...|   200|1542265929796|\"Mozilla/5.0 (X11...|    26|\n",
      "|            Ill Nino|Logged In|     F|  Cuevas| 188.9171| paid|    Chloe|            1|San Francisco-Oak...|   PUT|NextSong|1.540940782796E12|      606|How Can I Live (S...|   200|1542266927796|Mozilla/5.0 (Wind...|    49|\n",
      "+--------------------+---------+------+--------+---------+-----+---------+-------------+--------------------+------+--------+-----------------+---------+--------------------+------+-------------+--------------------+------+\n",
      "only showing top 20 rows"
     ]
    }
   ],
   "source": [
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "6db663f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "85cef7fd80c24efe9e081bce1a430f82",
       "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": [
      "6820"
     ]
    }
   ],
   "source": [
    "df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "273afdd1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "bf3b7bb8e3b94e70a770042431c672b3",
       "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": [
      "root\n",
      " |-- artist: string (nullable = true)\n",
      " |-- auth: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- lastName: string (nullable = true)\n",
      " |-- length: double (nullable = true)\n",
      " |-- level: string (nullable = true)\n",
      " |-- firstName: string (nullable = true)\n",
      " |-- itemInSession: integer (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- method: string (nullable = true)\n",
      " |-- page: string (nullable = true)\n",
      " |-- registration: double (nullable = true)\n",
      " |-- sessionId: integer (nullable = true)\n",
      " |-- song: string (nullable = true)\n",
      " |-- status: integer (nullable = true)\n",
      " |-- ts: long (nullable = true)\n",
      " |-- userAgent: string (nullable = true)\n",
      " |-- userId: string (nullable = true)"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "b6a95731",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "17f30c071fc747bf9b1b5fe33e69cca2",
       "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": [
    "# extract columns for users table\n",
    "users_table = df.repartition(df.userId) \\\n",
    "                    .select(col('userId').alias('user_id'), \\\n",
    "                            col('firstName').alias('first_name'),\\\n",
    "                            col('lastName').alias('last_name'), \\\n",
    "                            'gender',\n",
    "                            'level') \\\n",
    "                    .where(col(\"user_id\").isNotNull()) \\\n",
    "                    .dropDuplicates()\n",
    "\n",
    "# write users table to parquet files\n",
    "user_out_path = os.path.join(output_data, 'sparkify_user_table/')\n",
    "users_table.write.parquet(user_out_path, mode=\"overwrite\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "109a0b09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "59f8ddc0ab694a0b8e02f34e949c4f32",
       "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": [
      "root\n",
      " |-- user_id: string (nullable = true)\n",
      " |-- first_name: string (nullable = true)\n",
      " |-- last_name: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- level: string (nullable = true)"
     ]
    }
   ],
   "source": [
    "users_table.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "cd3c4716",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e5b99eb6527e4da79b27b1e4cce4c2f6",
       "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": [
    "# Defining User Defined Function (UDF) to convert log timestamp (seconds since epoch) to \\\n",
    "    \n",
    "get_timestamp = udf(lambda x : datetime.utcfromtimestamp(int(x)/1000), T.TimestampType())\n",
    "df = df.withColumn('timestamp', get_timestamp(df.ts))\n",
    "\n",
    "get_datetime = udf(lambda x : datetime.utcfromtimestamp(int(x)/1000), T.DateType())\n",
    "df = df.withColumn(\"datetime\", get_timestamp(\"ts\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "1e79133c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b9d1d18b28684952907b526c5264d7fc",
       "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": [
    "# extract columns to create time table\n",
    "time_table = df.select(col('timestamp').alias('start_time') \\\n",
    "                  , hour('timestamp').alias('hour') \\\n",
    "                  , dayofmonth('timestamp').alias('day') \\\n",
    "                  , weekofyear('timestamp').alias('week') \\\n",
    "                  , month('timestamp').alias('month') \\\n",
    "                  , year('timestamp').alias('year') \\\n",
    "                  , dayofweek('timestamp').alias('weekday'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "cdfb911e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4cd63913df0e40ed84f6d8456acfb3b7",
       "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": [
      "root\n",
      " |-- artist: string (nullable = true)\n",
      " |-- auth: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- lastName: string (nullable = true)\n",
      " |-- length: double (nullable = true)\n",
      " |-- level: string (nullable = true)\n",
      " |-- firstName: string (nullable = true)\n",
      " |-- itemInSession: integer (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- method: string (nullable = true)\n",
      " |-- page: string (nullable = true)\n",
      " |-- registration: double (nullable = true)\n",
      " |-- sessionId: integer (nullable = true)\n",
      " |-- song: string (nullable = true)\n",
      " |-- status: integer (nullable = true)\n",
      " |-- ts: long (nullable = true)\n",
      " |-- userAgent: string (nullable = true)\n",
      " |-- userId: string (nullable = true)\n",
      " |-- timestamp: timestamp (nullable = true)\n",
      " |-- datetime: timestamp (nullable = true)"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "5439373d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c108a35ee8584ac19ea16c7f95442f5c",
       "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": []
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "51f0eeff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ea42636571c44287a0fee02a71b94d79",
       "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": [
      "root\n",
      " |-- start_time: timestamp (nullable = true)\n",
      " |-- hour: integer (nullable = true)\n",
      " |-- day: integer (nullable = true)\n",
      " |-- week: integer (nullable = true)\n",
      " |-- month: integer (nullable = true)\n",
      " |-- year: integer (nullable = true)\n",
      " |-- weekday: integer (nullable = true)"
     ]
    }
   ],
   "source": [
    "time_table.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "97180f42",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5ac8289d55ac423698f1d3c61e882440",
       "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": [
    "# write time table to parquet files partitioned by year and month\n",
    "time_out_path = os.path.join(output_data, 'sparkify_time_table/')\n",
    "time_table.write.parquet(time_out_path, mode='overwrite', partitionBy=('year', 'month'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "543d3c91",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6d0d11982dc14ed6b473be822617a0c1",
       "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": [
    "# read in song data to use for songplays table\n",
    "song_data = os.path.join(input_data, 'song_data/*/*/*/*.json')\n",
    "song_df = spark.read.json(song_data, schema=song_schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "82bfa1a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "ec31ab3711fd4068bffb858982a3060e",
       "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": [
    "# extract columns from joined song and log datasets to create songplays table\n",
    "df_song_log = df.join(song_df, [df.song == song_df.title,\\\n",
    "                                        df.artist == song_df.artist_name])\n",
    "songplays_table = df_song_log.select(col('timestamp').alias('start_time'),\\\n",
    "                                        col('userId').alias('user_id'),\\\n",
    "                                        'level',\\\n",
    "                                        'song_id',\\\n",
    "                                        'artist_id',\\\n",
    "                                        col('sessionId').alias('session_id'),\\\n",
    "                                        'location',\\\n",
    "                                        col('userAgent').alias('user_agent'), \\\n",
    "                                        year(df.timestamp).alias('year'), \\\n",
    "                                        month(df.timestamp).alias('month'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "6bf9b0ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cf319e80d2b94f51bd1bec937ac833a7",
       "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": [
      "root\n",
      " |-- start_time: timestamp (nullable = true)\n",
      " |-- user_id: string (nullable = true)\n",
      " |-- level: string (nullable = true)\n",
      " |-- song_id: string (nullable = true)\n",
      " |-- artist_id: string (nullable = true)\n",
      " |-- session_id: integer (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- user_agent: string (nullable = true)\n",
      " |-- year: integer (nullable = true)\n",
      " |-- month: integer (nullable = true)"
     ]
    }
   ],
   "source": [
    "songplays_table.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "77fe8599",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "cefd3c39847a4eaa8e6ace1d24cdcbc1",
       "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",
      "|          start_time|user_id|level|           song_id|         artist_id|session_id|            location|          user_agent|year|month|\n",
      "+--------------------+-------+-----+------------------+------------------+----------+--------------------+--------------------+----+-----+\n",
      "|2018-11-21 08:25:...|     88| paid|SOCHPTV12A6BD53113|ARN8NCB1187FB49652|       744|Sacramento--Rosev...|\"Mozilla/5.0 (Mac...|2018|   11|\n",
      "|2018-11-29 16:58:...|     49| paid|SOGXSWA12A6D4FBC99|ARPFHN61187FB575F6|      1041|San Francisco-Oak...|Mozilla/5.0 (Wind...|2018|   11|\n",
      "|2018-11-28 08:18:...|     58| paid|SOJWCWM12A8C13B664|ARM6T8I1187FB36CC8|       887|Augusta-Richmond ...|\"Mozilla/5.0 (Win...|2018|   11|\n",
      "|2018-11-28 23:34:...|     24| paid|SOHRHCN12AB018B0F4|ARHQBRZ1187FB3BDA2|       984|Lake Havasu City-...|\"Mozilla/5.0 (Win...|2018|   11|\n",
      "|2018-11-26 15:37:...|     88| paid|SOARTQC12A58A77F0C|ARCE0IX1187FB528B4|       900|Sacramento--Rosev...|\"Mozilla/5.0 (Mac...|2018|   11|\n",
      "|2018-11-16 17:18:...|     36| paid|SOEMXXF12A6D4F932C|ARI4S0E1187B9B06C0|       461|Janesville-Beloit...|\"Mozilla/5.0 (Win...|2018|   11|\n",
      "|2018-11-30 04:57:...|     49| paid|SOEMXXF12A6D4F932C|ARI4S0E1187B9B06C0|      1079|San Francisco-Oak...|Mozilla/5.0 (Wind...|2018|   11|\n",
      "|2018-11-28 21:05:...|     73| paid|SOAHVKA12A8C146C5F|ARPBMSQ1187B98AE69|       954|Tampa-St. Petersb...|\"Mozilla/5.0 (Mac...|2018|   11|\n",
      "|2018-11-27 18:22:...|     36| paid|SODFRAX12A8C13274B|ARP29T31187B98DD5F|       957|Janesville-Beloit...|\"Mozilla/5.0 (Win...|2018|   11|\n",
      "|2018-11-03 17:59:...|     15| paid|SOHTKMO12AB01843B0|AR5EYTL1187B98EDA0|       199|Chicago-Napervill...|\"Mozilla/5.0 (X11...|2018|   11|\n",
      "|2018-11-17 17:42:...|     83| free|SOHTKMO12AB01843B0|AR5EYTL1187B98EDA0|       649|         Lubbock, TX|\"Mozilla/5.0 (Mac...|2018|   11|\n",
      "|2018-11-02 17:31:...|     10| free|SOHTKMO12AB01843B0|AR5EYTL1187B98EDA0|       182|Washington-Arling...|\"Mozilla/5.0 (Mac...|2018|   11|\n",
      "|2018-11-07 00:43:...|     97| paid|SOHTKMO12AB01843B0|AR5EYTL1187B98EDA0|       293|Lansing-East Lans...|\"Mozilla/5.0 (X11...|2018|   11|\n",
      "|2018-11-09 11:44:...|     95| paid|SOHTKMO12AB01843B0|AR5EYTL1187B98EDA0|       276|   Winston-Salem, NC|\"Mozilla/5.0 (iPh...|2018|   11|\n",
      "|2018-11-23 16:32:...|     88| paid|SOHTKMO12AB01843B0|AR5EYTL1187B98EDA0|       812|Sacramento--Rosev...|\"Mozilla/5.0 (Mac...|2018|   11|\n",
      "|2018-11-20 15:50:...|    100| free|SOHTKMO12AB01843B0|AR5EYTL1187B98EDA0|       595|New York-Newark-J...|\"Mozilla/5.0 (Mac...|2018|   11|\n",
      "|2018-11-30 17:25:...|     49| paid|SOHTKMO12AB01843B0|AR5EYTL1187B98EDA0|      1114|San Francisco-Oak...|Mozilla/5.0 (Wind...|2018|   11|\n",
      "|2018-11-14 12:56:...|     62| free|SOHTKMO12AB01843B0|AR5EYTL1187B98EDA0|       476|Houston-The Woodl...|\"Mozilla/5.0 (Mac...|2018|   11|\n",
      "|2018-11-21 20:04:...|     97| paid|SOXTMWU12AB0185E3B|ARB3YNF1187B992AF0|       817|Lansing-East Lans...|\"Mozilla/5.0 (X11...|2018|   11|\n",
      "|2018-11-30 07:47:...|     49| paid|SOKMDJJ12AB0181C64|AR6M65W1187FB3611E|      1079|San Francisco-Oak...|Mozilla/5.0 (Wind...|2018|   11|\n",
      "+--------------------+-------+-----+------------------+------------------+----------+--------------------+--------------------+----+-----+\n",
      "only showing top 20 rows"
     ]
    }
   ],
   "source": [
    "songplays_table.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "95709576",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "03a5154d9f5b462bbfde583a550b771c",
       "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": [
    "# write songplays table to parquet files partitioned by year and month\n",
    "songplays_out_path = os.path.join(output_data, 'sparkify_songplays_table/')\n",
    "songplays_table.write.parquet(songplays_out_path, mode='overwrite', partitionBy=('year', 'month'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00c6c112",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "PySpark",
   "language": "",
   "name": "pysparkkernel"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "python",
    "version": 3
   },
   "mimetype": "text/x-python",
   "name": "pyspark",
   "pygments_lexer": "python3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}