01-docker-terraform/2_docker_sql/data-loading-parquet.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "52bad16a",
   "metadata": {},
   "source": [
    "# Data loading \n",
    "\n",
    "Here we will be using the ```.paraquet``` file we downloaded and do the following:\n",
    " - Check metadata and table datatypes of the paraquet file/table\n",
    " - Convert the paraquet file to pandas dataframe and check the datatypes. Additionally check the data dictionary to make sure you have the right datatypes in pandas, as pandas will automatically create the table in our database.\n",
    " - Generate the DDL CREATE statement from pandas for a sanity check.\n",
    " - Create a connection to our database using SQLAlchemy\n",
    " - Convert our huge paraquet file into a iterable that has batches of 100,000 rows and load it into our database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afef2456",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-03T23:55:14.141738Z",
     "start_time": "2023-12-03T23:55:14.124217Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd \n",
    "import pyarrow.parquet as pq\n",
    "from time import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c750d1d4",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-03T02:54:01.925350Z",
     "start_time": "2023-12-03T02:54:01.661119Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyarrow._parquet.FileMetaData object at 0x7fed89ffa540>\n",
       "  created_by: parquet-cpp-arrow version 13.0.0\n",
       "  num_columns: 19\n",
       "  num_rows: 2846722\n",
       "  num_row_groups: 3\n",
       "  format_version: 2.6\n",
       "  serialized_size: 6357"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read metadata \n",
    "pq.read_metadata('yellow_tripdata_2023-09.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a970fcf0",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-03T23:28:08.411945Z",
     "start_time": "2023-12-03T23:28:08.177693Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VendorID: int32\n",
       "tpep_pickup_datetime: timestamp[us]\n",
       "tpep_dropoff_datetime: timestamp[us]\n",
       "passenger_count: int64\n",
       "trip_distance: double\n",
       "RatecodeID: int64\n",
       "store_and_fwd_flag: large_string\n",
       "PULocationID: int32\n",
       "DOLocationID: int32\n",
       "payment_type: int64\n",
       "fare_amount: double\n",
       "extra: double\n",
       "mta_tax: double\n",
       "tip_amount: double\n",
       "tolls_amount: double\n",
       "improvement_surcharge: double\n",
       "total_amount: double\n",
       "congestion_surcharge: double\n",
       "Airport_fee: double"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read file, read the table from file and check schema\n",
    "file = pq.ParquetFile('yellow_tripdata_2023-09.parquet')\n",
    "table = file.read()\n",
    "table.schema"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43f6ea7e",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-03T23:28:22.870376Z",
     "start_time": "2023-12-03T23:28:22.563414Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2846722 entries, 0 to 2846721\n",
      "Data columns (total 19 columns):\n",
      " #   Column                 Dtype         \n",
      "---  ------                 -----         \n",
      " 0   VendorID               int32         \n",
      " 1   tpep_pickup_datetime   datetime64[ns]\n",
      " 2   tpep_dropoff_datetime  datetime64[ns]\n",
      " 3   passenger_count        float64       \n",
      " 4   trip_distance          float64       \n",
      " 5   RatecodeID             float64       \n",
      " 6   store_and_fwd_flag     object        \n",
      " 7   PULocationID           int32         \n",
      " 8   DOLocationID           int32         \n",
      " 9   payment_type           int64         \n",
      " 10  fare_amount            float64       \n",
      " 11  extra                  float64       \n",
      " 12  mta_tax                float64       \n",
      " 13  tip_amount             float64       \n",
      " 14  tolls_amount           float64       \n",
      " 15  improvement_surcharge  float64       \n",
      " 16  total_amount           float64       \n",
      " 17  congestion_surcharge   float64       \n",
      " 18  Airport_fee            float64       \n",
      "dtypes: datetime64[ns](2), float64(12), int32(3), int64(1), object(1)\n",
      "memory usage: 380.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# Convert to pandas and check data \n",
    "df = table.to_pandas()\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccf039a0",
   "metadata": {},
   "source": [
    "We need to first create the connection to our postgres database. We can feed the connection information to generate the CREATE SQL query for the specific server. SQLAlchemy supports a variety of servers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44e701ae",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-03T22:50:25.811951Z",
     "start_time": "2023-12-03T22:50:25.393987Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.base.Connection at 0x7fed98ea3190>"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create an open SQL database connection object or a SQLAlchemy connectable\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('postgresql://root:root@localhost:5432/ny_taxi')\n",
    "engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c96a1075",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-03T22:50:43.628727Z",
     "start_time": "2023-12-03T22:50:43.442337Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TABLE yellow_taxi_data (\n",
      "\t\"VendorID\" INTEGER, \n",
      "\ttpep_pickup_datetime TIMESTAMP WITHOUT TIME ZONE, \n",
      "\ttpep_dropoff_datetime TIMESTAMP WITHOUT TIME ZONE, \n",
      "\tpassenger_count FLOAT(53), \n",
      "\ttrip_distance FLOAT(53), \n",
      "\t\"RatecodeID\" FLOAT(53), \n",
      "\tstore_and_fwd_flag TEXT, \n",
      "\t\"PULocationID\" INTEGER, \n",
      "\t\"DOLocationID\" INTEGER, \n",
      "\tpayment_type BIGINT, \n",
      "\tfare_amount FLOAT(53), \n",
      "\textra FLOAT(53), \n",
      "\tmta_tax FLOAT(53), \n",
      "\ttip_amount FLOAT(53), \n",
      "\ttolls_amount FLOAT(53), \n",
      "\timprovement_surcharge FLOAT(53), \n",
      "\ttotal_amount FLOAT(53), \n",
      "\tcongestion_surcharge FLOAT(53), \n",
      "\t\"Airport_fee\" FLOAT(53)\n",
      ")\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Generate CREATE SQL statement from schema for validation\n",
    "print(pd.io.sql.get_schema(df, name='yellow_taxi_data', con=engine))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eca7f32d",
   "metadata": {},
   "source": [
    "Datatypes for the table looks good! Since we used paraquet file the datasets seem to have been preserved. You may have to convert some datatypes so it is always good to do this check."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51a751ed",
   "metadata": {},
   "source": [
    "## Finally inserting data\n",
    "\n",
    "There are 2,846,722 rows in our dataset. We are going to use the ```parquet_file.iter_batches()``` function to create batches of 100,000, convert them into pandas and then load it into the postgres database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e20cec73",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-03T23:49:28.768786Z",
     "start_time": "2023-12-03T23:49:28.689732Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>VendorID</th>\n",
       "      <th>tpep_pickup_datetime</th>\n",
       "      <th>tpep_dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_distance</th>\n",
       "      <th>RatecodeID</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>PULocationID</th>\n",
       "      <th>DOLocationID</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tip_amount</th>\n",
       "      <th>tolls_amount</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>Airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2023-09-01 00:15:37</td>\n",
       "      <td>2023-09-01 00:20:21</td>\n",
       "      <td>1</td>\n",
       "      <td>0.80</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>163</td>\n",
       "      <td>230</td>\n",
       "      <td>2</td>\n",
       "      <td>6.5</td>\n",
       "      <td>3.5</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.50</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-09-01 00:18:40</td>\n",
       "      <td>2023-09-01 00:30:28</td>\n",
       "      <td>2</td>\n",
       "      <td>2.34</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>236</td>\n",
       "      <td>233</td>\n",
       "      <td>1</td>\n",
       "      <td>14.2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21.20</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-09-01 00:35:01</td>\n",
       "      <td>2023-09-01 00:39:04</td>\n",
       "      <td>1</td>\n",
       "      <td>1.62</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>162</td>\n",
       "      <td>236</td>\n",
       "      <td>1</td>\n",
       "      <td>8.6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.60</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-09-01 00:45:45</td>\n",
       "      <td>2023-09-01 00:47:37</td>\n",
       "      <td>1</td>\n",
       "      <td>0.74</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>141</td>\n",
       "      <td>229</td>\n",
       "      <td>1</td>\n",
       "      <td>5.1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.10</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-09-01 00:01:23</td>\n",
       "      <td>2023-09-01 00:38:05</td>\n",
       "      <td>1</td>\n",
       "      <td>9.85</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>138</td>\n",
       "      <td>230</td>\n",
       "      <td>1</td>\n",
       "      <td>45.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>17.02</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>73.77</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-09-02 09:55:17</td>\n",
       "      <td>2023-09-02 10:01:45</td>\n",
       "      <td>2</td>\n",
       "      <td>1.48</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>163</td>\n",
       "      <td>164</td>\n",
       "      <td>1</td>\n",
       "      <td>9.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.66</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.96</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-09-02 09:25:34</td>\n",
       "      <td>2023-09-02 09:55:20</td>\n",
       "      <td>3</td>\n",
       "      <td>17.49</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>132</td>\n",
       "      <td>164</td>\n",
       "      <td>1</td>\n",
       "      <td>70.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>24.28</td>\n",
       "      <td>6.94</td>\n",
       "      <td>1.0</td>\n",
       "      <td>106.97</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-09-02 09:57:55</td>\n",
       "      <td>2023-09-02 10:04:52</td>\n",
       "      <td>1</td>\n",
       "      <td>1.73</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>164</td>\n",
       "      <td>249</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>2.80</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>16.80</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-09-02 09:35:02</td>\n",
       "      <td>2023-09-02 09:43:28</td>\n",
       "      <td>1</td>\n",
       "      <td>1.32</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>113</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>4.20</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.0</td>\n",
       "      <td>18.20</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>2</td>\n",
       "      <td>2023-09-02 09:46:09</td>\n",
       "      <td>2023-09-02 10:03:58</td>\n",
       "      <td>1</td>\n",
       "      <td>8.79</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td>138</td>\n",
       "      <td>170</td>\n",
       "      <td>1</td>\n",
       "      <td>35.9</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>10.37</td>\n",
       "      <td>6.94</td>\n",
       "      <td>1.0</td>\n",
       "      <td>63.96</td>\n",
       "      <td>2.5</td>\n",
       "      <td>1.75</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100000 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
       "0             1  2023-09-01 00:15:37   2023-09-01 00:20:21                1   \n",
       "1             2  2023-09-01 00:18:40   2023-09-01 00:30:28                2   \n",
       "2             2  2023-09-01 00:35:01   2023-09-01 00:39:04                1   \n",
       "3             2  2023-09-01 00:45:45   2023-09-01 00:47:37                1   \n",
       "4             2  2023-09-01 00:01:23   2023-09-01 00:38:05                1   \n",
       "...         ...                  ...                   ...              ...   \n",
       "99995         2  2023-09-02 09:55:17   2023-09-02 10:01:45                2   \n",
       "99996         2  2023-09-02 09:25:34   2023-09-02 09:55:20                3   \n",
       "99997         2  2023-09-02 09:57:55   2023-09-02 10:04:52                1   \n",
       "99998         2  2023-09-02 09:35:02   2023-09-02 09:43:28                1   \n",
       "99999         2  2023-09-02 09:46:09   2023-09-02 10:03:58                1   \n",
       "\n",
       "       trip_distance  RatecodeID store_and_fwd_flag  PULocationID  \\\n",
       "0               0.80           1                  N           163   \n",
       "1               2.34           1                  N           236   \n",
       "2               1.62           1                  N           162   \n",
       "3               0.74           1                  N           141   \n",
       "4               9.85           1                  N           138   \n",
       "...              ...         ...                ...           ...   \n",
       "99995           1.48           1                  N           163   \n",
       "99996          17.49           2                  N           132   \n",
       "99997           1.73           1                  N           164   \n",
       "99998           1.32           1                  N           113   \n",
       "99999           8.79           1                  N           138   \n",
       "\n",
       "       DOLocationID  payment_type  fare_amount  extra  mta_tax  tip_amount  \\\n",
       "0               230             2          6.5    3.5      0.5        0.00   \n",
       "1               233             1         14.2    1.0      0.5        2.00   \n",
       "2               236             1          8.6    1.0      0.5        2.00   \n",
       "3               229             1          5.1    1.0      0.5        1.00   \n",
       "4               230             1         45.0    6.0      0.5       17.02   \n",
       "...             ...           ...          ...    ...      ...         ...   \n",
       "99995           164             1          9.3    0.0      0.5        2.66   \n",
       "99996           164             1         70.0    0.0      0.5       24.28   \n",
       "99997           249             1         10.0    0.0      0.5        2.80   \n",
       "99998           170             1         10.0    0.0      0.5        4.20   \n",
       "99999           170             1         35.9    5.0      0.5       10.37   \n",
       "\n",
       "       tolls_amount  improvement_surcharge  total_amount  \\\n",
       "0              0.00                    1.0         11.50   \n",
       "1              0.00                    1.0         21.20   \n",
       "2              0.00                    1.0         15.60   \n",
       "3              0.00                    1.0         11.10   \n",
       "4              0.00                    1.0         73.77   \n",
       "...             ...                    ...           ...   \n",
       "99995          0.00                    1.0         15.96   \n",
       "99996          6.94                    1.0        106.97   \n",
       "99997          0.00                    1.0         16.80   \n",
       "99998          0.00                    1.0         18.20   \n",
       "99999          6.94                    1.0         63.96   \n",
       "\n",
       "       congestion_surcharge  Airport_fee  \n",
       "0                       2.5         0.00  \n",
       "1                       2.5         0.00  \n",
       "2                       2.5         0.00  \n",
       "3                       2.5         0.00  \n",
       "4                       2.5         1.75  \n",
       "...                     ...          ...  \n",
       "99995                   2.5         0.00  \n",
       "99996                   2.5         1.75  \n",
       "99997                   2.5         0.00  \n",
       "99998                   2.5         0.00  \n",
       "99999                   2.5         1.75  \n",
       "\n",
       "[100000 rows x 19 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This part is for testing\n",
    "\n",
    "\n",
    "# Creating batches of 100,000 for the paraquet file\n",
    "batches_iter = file.iter_batches(batch_size=100000)\n",
    "batches_iter\n",
    "\n",
    "# Take the first batch for testing\n",
    "df = next(batches_iter).to_pandas()\n",
    "df\n",
    "\n",
    "# Creating just the table in postgres\n",
    "#df.head(0).to_sql(name='ny_taxi_data',con=engine, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7fdda025",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-04T00:08:07.651559Z",
     "start_time": "2023-12-04T00:02:35.940526Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "inserting batch 1...\n",
      "inserted! time taken     12.916 seconds.\n",
      "\n",
      "inserting batch 2...\n",
      "inserted! time taken     11.782 seconds.\n",
      "\n",
      "inserting batch 3...\n",
      "inserted! time taken     11.854 seconds.\n",
      "\n",
      "inserting batch 4...\n",
      "inserted! time taken     11.753 seconds.\n",
      "\n",
      "inserting batch 5...\n",
      "inserted! time taken     12.034 seconds.\n",
      "\n",
      "inserting batch 6...\n",
      "inserted! time taken     11.742 seconds.\n",
      "\n",
      "inserting batch 7...\n",
      "inserted! time taken     12.351 seconds.\n",
      "\n",
      "inserting batch 8...\n",
      "inserted! time taken     11.052 seconds.\n",
      "\n",
      "inserting batch 9...\n",
      "inserted! time taken     12.167 seconds.\n",
      "\n",
      "inserting batch 10...\n",
      "inserted! time taken     12.335 seconds.\n",
      "\n",
      "inserting batch 11...\n",
      "inserted! time taken     11.375 seconds.\n",
      "\n",
      "inserting batch 12...\n",
      "inserted! time taken     10.937 seconds.\n",
      "\n",
      "inserting batch 13...\n",
      "inserted! time taken     12.208 seconds.\n",
      "\n",
      "inserting batch 14...\n",
      "inserted! time taken     11.542 seconds.\n",
      "\n",
      "inserting batch 15...\n",
      "inserted! time taken     11.460 seconds.\n",
      "\n",
      "inserting batch 16...\n",
      "inserted! time taken     11.868 seconds.\n",
      "\n",
      "inserting batch 17...\n",
      "inserted! time taken     11.162 seconds.\n",
      "\n",
      "inserting batch 18...\n",
      "inserted! time taken     11.774 seconds.\n",
      "\n",
      "inserting batch 19...\n",
      "inserted! time taken     11.772 seconds.\n",
      "\n",
      "inserting batch 20...\n",
      "inserted! time taken     10.971 seconds.\n",
      "\n",
      "inserting batch 21...\n",
      "inserted! time taken     11.483 seconds.\n",
      "\n",
      "inserting batch 22...\n",
      "inserted! time taken     11.718 seconds.\n",
      "\n",
      "inserting batch 23...\n",
      "inserted! time taken     11.628 seconds.\n",
      "\n",
      "inserting batch 24...\n",
      "inserted! time taken     11.622 seconds.\n",
      "\n",
      "inserting batch 25...\n",
      "inserted! time taken     11.236 seconds.\n",
      "\n",
      "inserting batch 26...\n",
      "inserted! time taken     11.258 seconds.\n",
      "\n",
      "inserting batch 27...\n",
      "inserted! time taken     11.746 seconds.\n",
      "\n",
      "inserting batch 28...\n",
      "inserted! time taken     10.031 seconds.\n",
      "\n",
      "inserting batch 29...\n",
      "inserted! time taken      5.077 seconds.\n",
      "\n",
      "Completed! Total time taken was    331.674 seconds for 29 batches.\n"
     ]
    }
   ],
   "source": [
    "# Insert values into the table \n",
    "t_start = time()\n",
    "count = 0\n",
    "for batch in file.iter_batches(batch_size=100000):\n",
    "    count+=1\n",
    "    batch_df = batch.to_pandas()\n",
    "    print(f'inserting batch {count}...')\n",
    "    b_start = time()\n",
    "    \n",
    "    batch_df.to_sql(name='ny_taxi_data',con=engine, if_exists='append')\n",
    "    b_end = time()\n",
    "    print(f'inserted! time taken {b_end-b_start:10.3f} seconds.\\n')\n",
    "    \n",
    "t_end = time()   \n",
    "print(f'Completed! Total time taken was {t_end-t_start:10.3f} seconds for {count} batches.')    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7c102be",
   "metadata": {},
   "source": [
    "## Extra bit\n",
    "\n",
    "While trying to do the SQL Refresher, there was a need to add a lookup zones table but the file is in ```.csv``` format. \n",
    "\n",
    "Let's code to handle both ```.csv``` and ```.paraquet``` files!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a643d171",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-05T20:59:29.236458Z",
     "start_time": "2023-12-05T20:59:28.551221Z"
    }
   },
   "outputs": [],
   "source": [
    "from time import time\n",
    "import pandas as pd \n",
    "import pyarrow.parquet as pq\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "62c9040a",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-05T21:18:11.346552Z",
     "start_time": "2023-12-05T21:18:11.337475Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'yellow_tripdata_2023-09.parquet'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "url = 'https://d37ci6vzurychx.cloudfront.net/misc/taxi+_zone_lookup.csv'\n",
    "url = 'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2023-09.parquet'\n",
    "\n",
    "file_name = url.rsplit('/', 1)[-1].strip()\n",
    "file_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e495fa96",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2023-12-05T21:18:33.001561Z",
     "start_time": "2023-12-05T21:18:32.844872Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "oh yea\n"
     ]
    }
   ],
   "source": [
    "if '.csv' in file_name:\n",
    "    print('yay') \n",
    "    df = pd.read_csv(file_name, nrows=10)\n",
    "    df_iter = pd.read_csv(file_name, iterator=True, chunksize=100000)\n",
    "elif '.parquet' in file_name:\n",
    "    print('oh yea')\n",
    "    file = pq.ParquetFile(file_name)\n",
    "    df = next(file.iter_batches(batch_size=10)).to_pandas()\n",
    "    df_iter = file.iter_batches(batch_size=100000)\n",
    "else: \n",
    "    print('Error. Only .csv or .parquet files allowed.')\n",
    "    sys.exit() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7556748f",
   "metadata": {},
   "source": [
    "This code is a rough code and seems to be working. The cleaned up version will be in `data-loading-parquet.py` file."
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.9.5"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}