test.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql postgresql://admin:admin@127.0.0.1/sparkifydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM songplays LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT count(*) FROM songplays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM users LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT count(*) FROM users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM songs LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT count(*) FROM songs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM artists LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT count(*) FROM artists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM time LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT count(*) FROM time"
   ]
  },
  {
   "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": "markdown",
   "metadata": {},
   "source": [
    "# Sanity Tests \n",
    "\n",
    "Execute the cells below once you are ready to submit the project. Some basic sanity testing will be performed to esnure that your work does NOT contain any commonly found issues. \n",
    "\n",
    "Run each cell and if a cell produces an warning message is orange, you should make appropriate changes to your code before submitting. If all test in a cell pass,no warnings will be printed. \n",
    "\n",
    "The test cases assume that you are using certain column names in your tables. If you get a `IndexError: single positional indexer is out-of-bounds` you may need to change the column names being used by the test cases. Instructions for doing this appear right boefore cell that may require these changes.\n",
    "\n",
    "The tests below are only meant to help you make your work foolproof. The submission will still be graded by a human grader against the project rubric.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grab Table Names for Testing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sql_queries as sqlq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql _tablenames <<\n",
    "SELECT tablename\n",
    "FROM pg_catalog.pg_tables\n",
    "WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema' AND tableowner = 'admin';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tablenames = _tablenames.DataFrame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_table = [name for name in list(tablenames.tablename) if name in sqlq.user_table_create][0]\n",
    "artists_table = [name for name in list(tablenames.tablename) if name in sqlq.artist_table_create][0]\n",
    "song_table = [name for name in list(tablenames.tablename) if name in sqlq.song_table_create][0]\n",
    "songplay_table = [name for name in list(tablenames.tablename) if name in sqlq.songplay_table_create][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Primary Key Tests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql _output << SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull, i.indisprimary \\\n",
    "FROM   pg_index i \\\n",
    "JOIN   pg_attribute a ON a.attrelid = i.indrelid \\\n",
    "                     AND a.attnum = ANY(i.indkey) \\\n",
    "WHERE  i.indrelid = '{user_table}'::regclass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not _output:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"The {user_table} table does not have a primary key!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql _output << SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull, i.indisprimary \\\n",
    "FROM   pg_index i \\\n",
    "JOIN   pg_attribute a ON a.attrelid = i.indrelid \\\n",
    "                     AND a.attnum = ANY(i.indkey) \\\n",
    "WHERE  i.indrelid = '{artists_table}'::regclass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not _output: \n",
    "    print('\\033[93m'+'[WARNING] '+ f\"The {artists_table} table does not have a primary key!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql _output << SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull, i.indisprimary \\\n",
    "FROM   pg_index i \\\n",
    "JOIN   pg_attribute a ON a.attrelid = i.indrelid \\\n",
    "                     AND a.attnum = ANY(i.indkey) \\\n",
    "WHERE  i.indrelid = '{songplay_table}'::regclass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not _output:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"The {songplay_table} table does not have a primary key!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql _output << SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull, i.indisprimary \\\n",
    "FROM   pg_index i \\\n",
    "JOIN   pg_attribute a ON a.attrelid = i.indrelid \\\n",
    "                     AND a.attnum = ANY(i.indkey) \\\n",
    "WHERE  i.indrelid = '{song_table}'::regclass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not _output:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"The {song_table} table does not have a primary key!\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Data Type and Constraints Check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql _output << SELECT * FROM information_schema.columns where table_name='{user_table}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Check the column `user_id` for correct data type.**\n",
    "If you get a `IndexError: single positional indexer is out-of-bounds` error, you may be using a different column name. Change the column name below and run the cell again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "output = _output.DataFrame()\n",
    "_dtype = output[output.column_name == 'user_id'].data_type.iloc[0]\n",
    "if _dtype not in ['integer', 'bigint']:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type {_dtype} may not be an appropriate data type for column 'user_id' in the '{user_table}' table.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql _output << SELECT * FROM information_schema.columns where table_name='{song_table}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Check the column `year` for correct data type.\n",
    "Check columns `title` and `duration` for not-NULL constraints.**\n",
    "\n",
    "If you get a `IndexError: single positional indexer is out-of-bounds` error, you may be using different column names. Change the column name(s) below and run the cell again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "output = _output.DataFrame()\n",
    "\n",
    "_dtype = output[output.column_name == 'year'].data_type.iloc[0]\n",
    "if _dtype not in ['integer']:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type '{_dtype}' may not be an appropriate data type for column 'year' in the '{song_table}' table.\")\n",
    "\n",
    "_nullable_title = output[output.column_name == 'title'].is_nullable.iloc[0]\n",
    "_nullable_duration = output[output.column_name == 'duration'].is_nullable.iloc[0]\n",
    "if (_nullable_duration != 'NO') or (_nullable_title != 'NO'): \n",
    "    print('\\033[93m'+'[WARNING] '+ f\"You may want to add appropriate NOT NULL constraints to the '{song_table}' table.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql _output << SELECT * FROM information_schema.columns where table_name='{artists_table}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Check the columns `latitude` and `longitude` for correct data type.\n",
    "Check column `name` for not-NULL constraint.**\n",
    "\n",
    "If you get a `IndexError: single positional indexer is out-of-bounds` error, you may be using different column names. Change the column name(s) below and run the cell again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "output = _output.DataFrame()\n",
    "\n",
    "_dtype_latitude = output[output.column_name == 'latitude'].data_type.iloc[0]\n",
    "if _dtype_latitude not in ['double precision']:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type '{_dtype_latitude}' may not be an appropriate data type for column 'latitude' in the '{artists_table}' table\")\n",
    "\n",
    "_dtype_longitude = output[output.column_name == 'longitude'].data_type.iloc[0]\n",
    "if _dtype_longitude not in ['double precision']:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type '{_dtype_longitude}' may not be an appropriate data type for column 'longitude' in the '{artists_table}' table\")\n",
    "\n",
    "\n",
    "_nullable_name = output[output.column_name == 'name'].is_nullable.iloc[0]\n",
    "if _nullable_name != 'NO':\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"You may want to add appropriate NOT NULL constraints to the '{artists_table}' table.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql _output << SELECT * FROM information_schema.columns where table_name='{songplay_table}'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Check the columns `start_time` and `user_id` for correct data type.\n",
    "Check columns `start_time` and `user_id` for not-NULL constraint.**\n",
    "\n",
    "If you get a `IndexError: single positional indexer is out-of-bounds` error, you may be using different column names. Change the column name(s) below and run the cell again. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "output = _output.DataFrame()\n",
    "\n",
    "_dtype_start_time = output[output.column_name == 'start_time'].data_type.iloc[0]\n",
    "if 'timestamp' not in _dtype_start_time: \n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type '{_dtype_start_time}' may not be an appropriate data type for column 'start_time' in the '{songplay_table}' table.\")\n",
    "\n",
    "_dtype_user_id = output[output.column_name == 'user_id'].data_type.iloc[0]\n",
    "if _dtype_user_id not in ['integer', 'bigint']:\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"Type '{_dtype_user_id}' may not be an appropriate data type for column 'user_id' in the '{songplay_table}' table.\")\n",
    "\n",
    "\n",
    "_nullable_time = output[output.column_name == 'start_time'].is_nullable.iloc[0]\n",
    "_nullable_uid = output[output.column_name == 'user_id'].is_nullable.iloc[0]\n",
    "\n",
    "if (_nullable_time != 'NO') or (_nullable_uid != 'NO'):\n",
    "    print('\\033[93m'+'[WARNING] '+ f\"You may want to add appropriate NOT NULL constraints to the '{songplay_table}' table.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run Tests for Upsertion Check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "if not re.search('ON\\s+CONFLICT',sqlq.songplay_table_insert,re.IGNORECASE) or \\\n",
    "   not re.search('ON\\s+CONFLICT',sqlq.user_table_insert,re.IGNORECASE) or \\\n",
    "   not re.search('ON\\s+CONFLICT',sqlq.song_table_insert,re.IGNORECASE) or \\\n",
    "   not re.search('ON\\s+CONFLICT',sqlq.artist_table_insert,re.IGNORECASE):\n",
    "    print('\\033[93m'+'[WARNING]Some of your insert queries may need an \"ON CONFLICT\" clause.')\n",
    "    print('\\033[93m'+'         You can either skip conflicting insertions with with \"ON CONFLICT DO NOTHING\"')\n",
    "    print('\\033[93m'+'         OR use \"ON CONFLICT DO UPDATE SET\"')\n",
    "    print('\\033[93m'+'         Check this link for more details: https://www.postgresqltutorial.com/postgresql-upsert/')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:root] *",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}