Manual_ETL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Test manual ETL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import glob\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "from sql_queries import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "host = os.environ['PGHOSTADDR'] \n",
    "password = os.environ['PGPASSWORD'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(host=host, port=\"5432\", dbname=\"sparkifydb\",  user=\"postgres\",  password=password)\n",
    "cur = conn.cursor()\n",
    "conn.autocommit = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_files(filepath):\n",
    "    all_files = []\n",
    "    for root, dirs, files in os.walk(filepath):\n",
    "        files = glob.glob(os.path.join(root,'*.json'))\n",
    "        for f in files :\n",
    "            all_files.append(os.path.abspath(f))\n",
    "    \n",
    "    return all_files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Log Staging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_files = get_files(\"data/log_data\")\n",
    "\n",
    "dfs = []\n",
    "for i, datafile in enumerate(all_files, 1):\n",
    "    df = pd.read_json(datafile, lines=True)\n",
    "    dfs.append(df)\n",
    "    \n",
    "log_data = pd.concat(dfs, ignore_index=True) \n",
    "\n",
    "log_data = log_data[(log_data['page']=='NextSong')].astype({'ts': 'datetime64[ms]'})\n",
    "\n",
    "log_data.to_csv(\"log_file.csv\", sep = '|',index=False, header=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('log_file.csv',encoding='utf-8') as f:\n",
    "    cur.copy_from(f, 'log_staging', sep = '|', null='')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Time dim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "t = pd.to_datetime(log_data['ts'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "time_data = list((t, t.dt.hour, t.dt.day, t.dt.isocalendar().week, t.dt.month, t.dt.year, t.dt.weekday))\n",
    "column_labels = list(('start_time', 'hour', 'day', 'week', 'month', 'year', 'weekday'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "time_df =  pd.DataFrame.from_dict(dict(zip(column_labels, time_data)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, row in time_df.iterrows():\n",
    "    cur.execute(time_table_insert, list(row))\n",
    "    #conn.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load User dim\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#print(user_table_insert)\n",
    "cur.execute(user_table_insert)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Song Staging"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_files = get_files(\"data/song_data\")\n",
    "\n",
    "dfs = []\n",
    "for i, datafile in enumerate(all_files, 1):\n",
    "    df = pd.read_json(datafile, lines=True)\n",
    "    dfs.append(df)\n",
    "\n",
    "song_data = pd.concat(dfs, ignore_index=True)\n",
    "\n",
    "song_data.to_csv(\"song_file.csv\", sep = '|',index=False, header=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('song_file.csv',encoding='utf-8') as f:\n",
    "    cur.copy_from(f, 'song_staging', sep = '|', null='')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Song dim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(song_table_insert)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Artist Dim"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(artist_table_insert)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load fact table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute(songplay_table_insert)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Close Connection to Sparkify Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "myenv",
   "language": "python",
   "name": "myenv"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}