AWS Glue or Not.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "17cab254",
   "metadata": {},
   "source": [
    "### AWS Glue : Learners Log - 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "010357e8",
   "metadata": {},
   "source": [
    "Many of us don't realize the effort requied to move the files inside a database. Thanks to pandas dataframe, super-fast RAM and low latency SSD.I realized it when I challenged myself to load the AirBnB Amsterdam dataset (Sourced from Kaggle) into the Postgres database that runs in docker on my laptop. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44e97217",
   "metadata": {},
   "source": [
    "AWS Glue Automates this Loading of the Data.\n",
    "Steps I am showing below is done by the Crawlers in AWS Glue service, and creates the database instantly. I have tried loading huge files into database in AWS, it took me hardly 10 minutes to upload the CSV and let the crawlers do the work "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eff9226c",
   "metadata": {},
   "source": [
    "Dataset consist of 5 different data files in CSV format, listed below\n",
    "\n",
    "- calendar.csv\n",
    "\n",
    "- listings.csv\n",
    "\n",
    "- listings_details.csv\n",
    "\n",
    "- neighbourhoods.csv\n",
    "\n",
    "- reviews.csv\n",
    "\n",
    "- reviews_details.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c0d3ecb",
   "metadata": {},
   "source": [
    "Steps for uploading one csv into a table inside a database\n",
    "\n",
    "1) Create the Database if not created\n",
    "\n",
    "2) Connect to the Database\n",
    "\n",
    "3) Create the Table using the below sql command inside the database server\n",
    "    \n",
    "        CREATE TABLE tableName (var1 varType, var2, varType...);\n",
    "\n",
    "4) Insert the data into the Table row by row using the following SQL command\n",
    "\n",
    "        INSERT INTO tableName (var1, var2 ...) values (val1, val2...);\n",
    "    \n",
    "Repeat the above steps for all the 6 files that I have. Below are the row and column counts of two major CSV file\n",
    "\n",
    "- listings_details.csv ==> 20,000 ROWS and 90 Column\n",
    "\n",
    "- reviews_details.csv ==> 413,000 ROWS and 8 Columns\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "950724f4",
   "metadata": {},
   "source": [
    "Humungous would be an understatement for the number of data points these two files have. The other 4 files are 10% compared to these two files. \n",
    "\n",
    "How long will it take to complete Inserting these ROWS into the Reviews_Details Table and Listings_Details Table? \n",
    "\n",
    "- Scripting the SQL Insert Commands using programming language and then loading : 10 Hours (Including the errors and corrections time)\n",
    "\n",
    "- Using the psycopg2 library to connect with DB server, and \n",
    "    Use the COPY tableName FROM '/file/location/file.csv' : 3 hours \n",
    "\n",
    "- AWS Glue : 10 minutes to upload the files and 1 or less to build the database tables and ready for querying\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3233ae9",
   "metadata": {},
   "source": [
    "Psycopg2 Process:\n",
    "\n",
    "The detailed process can be found in the Jupyter notebook on github, which can be downloaded and understood. I am listing the high level processes followed here... \n",
    "\n",
    "1) Read the CSV file into Pandas environment using\n",
    "    \n",
    "    pd.read_csv(CSVfile.csv)\n",
    "    \n",
    "2) Use the Pandas IO module which has SQL Schema inference method to get the      "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}