etl-test.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook is to test the etl in the `etl-proto.ipynb`, it can be used later to test results for etl process by `etl.py` (here the outputs corresponds to test results for etl-proto.ipynb)\n",
    "### REMEMBER: Restart this notebook to close connection to `sparkifydb`\n",
    "Each time you run the cells below, remember to restart this notebook to close the connection to your database. Otherwise, you won't be able to run your code in `create_tables.py`, `etl.py`, or `etl.ipynb` files since you can't make multiple connections to the same database (in this case, sparkifydb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connection info needed in SQLAlchemy format, example:\n",
    "               postgresql://username:password@hostname/dbname"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: postgres@sparkifydb'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql postgresql://postgres:admin@127.0.0.1/sparkifydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@127.0.0.1/sparkifydb\n",
      "0 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>songplay_id</th>\n",
       "            <th>start_time</th>\n",
       "            <th>userid</th>\n",
       "            <th>song_id</th>\n",
       "            <th>artist_id</th>\n",
       "            <th>level</th>\n",
       "            <th>session_id</th>\n",
       "            <th>location</th>\n",
       "            <th>user_agent</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songplays WHERE song_id is not null and artist_id is not null ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The output should be None as so far you just processed 1 log file + 1 song file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>userid</th>\n",
       "            <th>firstname</th>\n",
       "            <th>lastname</th>\n",
       "            <th>gender</th>\n",
       "            <th>level</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>69</td>\n",
       "            <td>Anabelle</td>\n",
       "            <td>Simpson</td>\n",
       "            <td>F</td>\n",
       "            <td>free</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>32</td>\n",
       "            <td>Lily</td>\n",
       "            <td>Burns</td>\n",
       "            <td>F</td>\n",
       "            <td>free</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>75</td>\n",
       "            <td>Joseph</td>\n",
       "            <td>Gutierrez</td>\n",
       "            <td>M</td>\n",
       "            <td>free</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>92</td>\n",
       "            <td>Ryann</td>\n",
       "            <td>Smith</td>\n",
       "            <td>F</td>\n",
       "            <td>free</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>49</td>\n",
       "            <td>Chloe</td>\n",
       "            <td>Cuevas</td>\n",
       "            <td>F</td>\n",
       "            <td>free</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(69, 'Anabelle', 'Simpson', 'F', 'free'),\n",
       " (32, 'Lily', 'Burns', 'F', 'free'),\n",
       " (75, 'Joseph', 'Gutierrez', 'M', 'free'),\n",
       " (92, 'Ryann', 'Smith', 'F', 'free'),\n",
       " (49, 'Chloe', 'Cuevas', 'F', 'free')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM users LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>song_id</th>\n",
       "            <th>title</th>\n",
       "            <th>artist_id</th>\n",
       "            <th>year</th>\n",
       "            <th>duration</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>SONHOTT12A8C13493C</td>\n",
       "            <td>Something Girls</td>\n",
       "            <td>AR7G5I41187FB4CE6C</td>\n",
       "            <td>1982</td>\n",
       "            <td>233.40363</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('SONHOTT12A8C13493C', 'Something Girls', 'AR7G5I41187FB4CE6C', 1982, 233.40363)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM songs;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember we just processed one song file, so it is so expected to get just one row as output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@127.0.0.1/sparkifydb\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>artist_id</th>\n",
       "            <th>name</th>\n",
       "            <th>location</th>\n",
       "            <th>latitude</th>\n",
       "            <th>longitude</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>AR7G5I41187FB4CE6C</td>\n",
       "            <td>Adam Ant</td>\n",
       "            <td>London, England</td>\n",
       "            <td>nan</td>\n",
       "            <td>nan</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('AR7G5I41187FB4CE6C', 'Adam Ant', 'London, England', nan, nan)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM artists ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember we just processed one song file, so it is so expected to get just one row as output which is the artist info of that song"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@127.0.0.1/sparkifydb\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>start_time</th>\n",
       "            <th>hour</th>\n",
       "            <th>day</th>\n",
       "            <th>week</th>\n",
       "            <th>month</th>\n",
       "            <th>year</th>\n",
       "            <th>dayofweek</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>2018-11-11 02:33:56.796000</td>\n",
       "            <td>2</td>\n",
       "            <td>11</td>\n",
       "            <td>45</td>\n",
       "            <td>11</td>\n",
       "            <td>2018</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2018-11-11 02:36:10.796000</td>\n",
       "            <td>2</td>\n",
       "            <td>11</td>\n",
       "            <td>45</td>\n",
       "            <td>11</td>\n",
       "            <td>2018</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2018-11-11 02:40:34.796000</td>\n",
       "            <td>2</td>\n",
       "            <td>11</td>\n",
       "            <td>45</td>\n",
       "            <td>11</td>\n",
       "            <td>2018</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2018-11-11 04:36:13.796000</td>\n",
       "            <td>4</td>\n",
       "            <td>11</td>\n",
       "            <td>45</td>\n",
       "            <td>11</td>\n",
       "            <td>2018</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2018-11-11 04:36:46.796000</td>\n",
       "            <td>4</td>\n",
       "            <td>11</td>\n",
       "            <td>45</td>\n",
       "            <td>11</td>\n",
       "            <td>2018</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(datetime.datetime(2018, 11, 11, 2, 33, 56, 796000), 2, 11, 45, 11, 2018, 6),\n",
       " (datetime.datetime(2018, 11, 11, 2, 36, 10, 796000), 2, 11, 45, 11, 2018, 6),\n",
       " (datetime.datetime(2018, 11, 11, 2, 40, 34, 796000), 2, 11, 45, 11, 2018, 6),\n",
       " (datetime.datetime(2018, 11, 11, 4, 36, 13, 796000), 4, 11, 45, 11, 2018, 6),\n",
       " (datetime.datetime(2018, 11, 11, 4, 36, 46, 796000), 4, 11, 45, 11, 2018, 6)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM time LIMIT 5;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## REMEMBER: Restart this notebook to close connection to `sparkifydb`\n",
    "Each time you run the cells above, remember to restart this notebook to close the connection to your database. Otherwise, you won't be able to run your code in `create_tables.py`, `etl.py`, or `etl.ipynb` files since you can't make multiple connections to the same database (in this case, sparkifydb)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}