optimized_data_insertion.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "c8a10a54",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: pandas in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (1.5.2)\n",
      "Requirement already satisfied: pytz>=2020.1 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from pandas) (2022.6)\n",
      "Requirement already satisfied: python-dateutil>=2.8.1 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from pandas) (2.8.2)\n",
      "Requirement already satisfied: numpy>=1.20.3 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from pandas) (1.23.5)\n",
      "Requirement already satisfied: six>=1.5 in /Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/site-packages (from python-dateutil>=2.8.1->pandas) (1.15.0)\n",
      "\u001b[33mWARNING: You are using pip version 21.2.4; however, version 22.3.1 is available.\n",
      "You should consider upgrading via the '/Library/Developer/CommandLineTools/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n",
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: SQLAlchemy in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (1.4.44)\n",
      "\u001b[33mWARNING: You are using pip version 21.2.4; however, version 22.3.1 is available.\n",
      "You should consider upgrading via the '/Library/Developer/CommandLineTools/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n",
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: PyMySQL in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (1.0.2)\n",
      "\u001b[33mWARNING: You are using pip version 21.2.4; however, version 22.3.1 is available.\n",
      "You should consider upgrading via the '/Library/Developer/CommandLineTools/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n",
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: pyodbc in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (4.0.35)\n",
      "\u001b[33mWARNING: You are using pip version 21.2.4; however, version 22.3.1 is available.\n",
      "You should consider upgrading via the '/Library/Developer/CommandLineTools/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n",
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Collecting seaborn\n",
      "  Downloading seaborn-0.12.1-py3-none-any.whl (288 kB)\n",
      "\u001b[K     |████████████████████████████████| 288 kB 554 kB/s eta 0:00:01\n",
      "\u001b[?25hCollecting matplotlib!=3.6.1,>=3.1\n",
      "  Downloading matplotlib-3.6.2-cp39-cp39-macosx_11_0_arm64.whl (7.2 MB)\n",
      "\u001b[K     |████████████████████████████████| 7.2 MB 868 kB/s eta 0:00:01\n",
      "\u001b[?25hRequirement already satisfied: numpy>=1.17 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from seaborn) (1.23.5)\n",
      "Requirement already satisfied: pandas>=0.25 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from seaborn) (1.5.2)\n",
      "Requirement already satisfied: pyparsing>=2.2.1 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from matplotlib!=3.6.1,>=3.1->seaborn) (3.0.9)\n",
      "Collecting cycler>=0.10\n",
      "  Downloading cycler-0.11.0-py3-none-any.whl (6.4 kB)\n",
      "Collecting contourpy>=1.0.1\n",
      "  Downloading contourpy-1.0.6-cp39-cp39-macosx_11_0_arm64.whl (226 kB)\n",
      "\u001b[K     |████████████████████████████████| 226 kB 2.3 MB/s eta 0:00:01\n",
      "\u001b[?25hCollecting kiwisolver>=1.0.1\n",
      "  Downloading kiwisolver-1.4.4-cp39-cp39-macosx_11_0_arm64.whl (63 kB)\n",
      "\u001b[K     |████████████████████████████████| 63 kB 6.1 MB/s eta 0:00:011\n",
      "\u001b[?25hCollecting fonttools>=4.22.0\n",
      "  Downloading fonttools-4.38.0-py3-none-any.whl (965 kB)\n",
      "\u001b[K     |████████████████████████████████| 965 kB 5.2 MB/s eta 0:00:01\n",
      "\u001b[?25hCollecting pillow>=6.2.0\n",
      "  Downloading Pillow-9.3.0-cp39-cp39-macosx_11_0_arm64.whl (2.9 MB)\n",
      "\u001b[K     |████████████████████████████████| 2.9 MB 5.1 MB/s eta 0:00:01\n",
      "\u001b[?25hRequirement already satisfied: packaging>=20.0 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from matplotlib!=3.6.1,>=3.1->seaborn) (21.3)\n",
      "Requirement already satisfied: python-dateutil>=2.7 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from matplotlib!=3.6.1,>=3.1->seaborn) (2.8.2)\n",
      "Requirement already satisfied: pytz>=2020.1 in /Users/parvashah/Library/Python/3.9/lib/python/site-packages (from pandas>=0.25->seaborn) (2022.6)\n",
      "Requirement already satisfied: six>=1.5 in /Library/Developer/CommandLineTools/Library/Frameworks/Python3.framework/Versions/3.9/lib/python3.9/site-packages (from python-dateutil>=2.7->matplotlib!=3.6.1,>=3.1->seaborn) (1.15.0)\n",
      "Installing collected packages: pillow, kiwisolver, fonttools, cycler, contourpy, matplotlib, seaborn\n",
      "\u001b[33m  WARNING: The scripts fonttools, pyftmerge, pyftsubset and ttx are installed in '/Users/parvashah/Library/Python/3.9/bin' which is not on PATH.\n",
      "  Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.\u001b[0m\n",
      "Successfully installed contourpy-1.0.6 cycler-0.11.0 fonttools-4.38.0 kiwisolver-1.4.4 matplotlib-3.6.2 pillow-9.3.0 seaborn-0.12.1\n",
      "\u001b[33mWARNING: You are using pip version 21.2.4; however, version 22.3.1 is available.\n",
      "You should consider upgrading via the '/Library/Developer/CommandLineTools/usr/bin/python3 -m pip install --upgrade pip' command.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!pip3 install pandas\n",
    "!pip3 install SQLAlchemy\n",
    "!pip3 install PyMySQL\n",
    "!pip3 install pyodbc\n",
    "!pip3 install seaborn\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import pymysql\n",
    "from urllib.parse import quote \n",
    "import mysql.connector\n",
    "import sqlalchemy\n",
    "from sqlalchemy.engine import create_engine\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "15a6657e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<mysql.connector.connection_cext.CMySQLConnection object at 0x5475bbee0>\n"
     ]
    }
   ],
   "source": [
    "#connect to mysql servel\n",
    "mydb = mysql.connector.connect(\n",
    "  host=\"localhost\",\n",
    "  user=\"root\",\n",
    "  password=\"Parvashah@123\",\n",
    "  database=\"yelpdb\"\n",
    ")\n",
    "\n",
    "print(mydb)\n",
    "\n",
    "mycursor = mydb.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "a0c0db40",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a an engine for more optimized insertion of data through SQLalchemy\n",
    "constring = 'mysql+mysqlconnector://root:%s@localhost:3306/yelpdb' % quote('Parvashah@123')\n",
    "dbEngine = sqlalchemy.create_engine(constring, connect_args={'connect_timeout': 10}, echo=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "690c8693",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "engine is valid\n"
     ]
    }
   ],
   "source": [
    "# Testing the connection of the engine\n",
    "try:\n",
    "    with dbEngine.connect() as con:\n",
    "        con.execute(\"SELECT 1\")\n",
    "    print('engine is valid')\n",
    "except Exception as e:\n",
    "    print(f'Engine invalid: {str(e)}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "0e7823c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Table Creeation\n",
    "# create business table\n",
    "mycursor.execute(\"DROP TABLE IF EXISTS business\")\n",
    "mycursor.execute(\"CREATE TABLE business (business_id VARCHAR(255) PRIMARY KEY, name VARCHAR(255), address VARCHAR(255), city VARCHAR(255), state TEXT(2), postal_code INT(5), stars DECIMAL(10,1), review_count INT, is_open BOOL)\")\n",
    "# create category table\n",
    "mycursor.execute(\"DROP TABLE IF EXISTS category\")\n",
    "mycursor.execute(\"CREATE TABLE category (business_id VARCHAR(255),categories VARCHAR(255), PRIMARY KEY (business_id, categories),CONSTRAINT cat_b_id_fk FOREIGN KEY (business_id) REFERENCES business (business_id) ON UPDATE CASCADE ON DELETE CASCADE)\")\n",
    "# create user table\n",
    "mycursor.execute(\"DROP TABLE IF EXISTS user\")\n",
    "mycursor.execute(\"CREATE TABLE user (user_id VARCHAR(255) PRIMARY KEY, name VARCHAR(255), review_count INT, yelping_since DATETIME, average_stars FLOAT)\")\n",
    "# create review table\n",
    "mycursor.execute(\"DROP TABLE IF EXISTS review\")\n",
    "mycursor.execute(\"CREATE TABLE review (review_id VARCHAR(255) PRIMARY KEY, user_id VARCHAR(255), business_id VARCHAR(255), stars INT(1), text TEXT(65535), time DATETIME, CONSTRAINT user_id_fk FOREIGN KEY (user_id) REFERENCES user (user_id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT business_id_fk FOREIGN KEY (business_id) REFERENCES business (business_id) ON UPDATE CASCADE ON DELETE CASCADE)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "482669b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Business dataframe creation\n",
    "df_b = pd.read_json('yelp_dataset/yelp_academic_dataset_business.json', lines=True)\n",
    "# delete unwanted columns\n",
    "del df_b['attributes'], df_b['hours'], df_b['categories'], df_b['latitude'], df_b['longitude']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "d0596f55",
   "metadata": {},
   "outputs": [],
   "source": [
    "# category dataframe creation\n",
    "df_p = pd.read_json('yelp_dataset/yelp_academic_dataset_business.json', lines=True)\n",
    "cat_info = []\n",
    "for index, row in df_p.iterrows():\n",
    "    try:\n",
    "        #split the category values by \",\" and append in list\n",
    "        for cat in row[\"categories\"].split(\",\"):\n",
    "            cat_info.append((row[\"business_id\"],cat))\n",
    "    except:\n",
    "        continue\n",
    "df_cat = pd.DataFrame(cat_info,columns=['business_id','categories'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "ab3117b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert data into business table from json file\n",
    "df_b.to_sql(con=dbEngine, schema=\"yelpdb\", name=\"temporary_table\", if_exists=\"replace\", index=False, chunksize=1000)\n",
    "with dbEngine.begin() as cnx:\n",
    "    insert_sql = 'INSERT IGNORE INTO business (SELECT * FROM temporary_table)'\n",
    "    cnx.execute(insert_sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "b37c7a4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create user dataframe\n",
    "chunks = pd.read_json('yelp_dataset/yelp_academic_dataset_user.json', lines=True, chunksize = 5000)\n",
    "users = pd.DataFrame()\n",
    "for chunk in chunks:\n",
    "    users = pd.concat([users, chunk])\n",
    "    del users['useful'], users['funny'], users['cool'], users['elite'], users['friends'], users['fans'], users['compliment_hot'], users['compliment_more'], users['compliment_profile'], users['compliment_cute'], users['compliment_list'], users['compliment_note'], users['compliment_plain'], users['compliment_cool'], users['compliment_funny'], users['compliment_writer'], users['compliment_photos']\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "a673beb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert data into user table from user dataframe\n",
    "users.to_sql(con=dbEngine, schema=\"yelpdb\", name=\"temporary_table\", if_exists=\"replace\", index=False, chunksize=5000)\n",
    "with dbEngine.begin() as cnx:\n",
    "    insert_sql = 'INSERT IGNORE INTO user (SELECT * FROM temporary_table)'\n",
    "    cnx.execute(insert_sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "56c4a54e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Review json has more than 6 M lines of data to handle that we need to take additional measures and create review dataframe\n",
    "# dataframe creation is done in chunks since it is a large file which cannot be read at once. \n",
    "# In addition we specify the datatype so it takes considerably less time\n",
    "b_pandas = []\n",
    "r_dtypes = {\"review_id\": np.string_,\n",
    "            \"user_id\": np.string_,\n",
    "            \"business_id\": np.string_,\n",
    "            \"stars\": np.int32, \n",
    "            \"text\": np.string_, \n",
    "            \"time\": np.datetime64\n",
    "           }\n",
    "with open(\"yelp_dataset/yelp_academic_dataset_review.json\", \"r\") as f:\n",
    "    reader = pd.read_json(f, orient=\"records\", lines=True, \n",
    "                          dtype=r_dtypes, chunksize=5000)\n",
    "        \n",
    "    for chunk in reader:\n",
    "        reduced_chunk = chunk.drop(columns=['useful', 'funny','cool'])\n",
    "        b_pandas.append(reduced_chunk)\n",
    "    \n",
    "df_review = pd.concat(b_pandas, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "1a45ffa4",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df_review' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[1], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m review_sample \u001b[39m=\u001b[39m df_review\u001b[39m.\u001b[39msample(\u001b[39m10000\u001b[39m)\n",
      "\u001b[0;31mNameError\u001b[0m: name 'df_review' is not defined"
     ]
    }
   ],
   "source": [
    "review_sample = df_review.sample(10000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "30e32544",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Insert data into review table from the dataframe\n",
    "review_sample.to_sql(con=dbEngine, schema=\"yelpdb\", name=\"temporary_table\", if_exists=\"replace\", index=False, chunksize=10000)\n",
    "with dbEngine.begin() as cnx:\n",
    "    insert_sql = 'INSERT IGNORE INTO review (SELECT * FROM temporary_table)'\n",
    "    cnx.execute(insert_sql)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "22a6eb5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Insert data into category table from the dataframe\n",
    "df_cat.to_sql(con=dbEngine, schema=\"yelpdb\", name=\"temporary_table\", if_exists=\"replace\", index=False, chunksize=10000)\n",
    "with dbEngine.begin() as cnx:\n",
    "    insert_sql = 'INSERT IGNORE INTO category (SELECT * FROM temporary_table)'\n",
    "    cnx.execute(insert_sql)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.6 64-bit",
   "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.9.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "31f2aee4e71d21fbe5cf8b01ff0e069b9275f58929596ceb00d14d90e3e16cd6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}