1_snowpark_housing_data_ingest.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "cd188e46",
   "metadata": {},
   "source": [
    "# NOTEBOOK 1: INGEST HOUSING DATA INTO SNOWFLAKE"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "62451bca",
   "metadata": {},
   "source": [
    "#### Download the housing dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "132cb7aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# We will load some data from internet which we will then load into Snowflake \n",
    "import os\n",
    "import tarfile\n",
    "import urllib.request\n",
    "\n",
    "DOWNLOAD_ROOT = \"https://raw.githubusercontent.com/ageron/handson-ml2/master/\"\n",
    "HOUSING_PATH = os.path.join(\"datasets\", \"housing\")\n",
    "HOUSING_URL = DOWNLOAD_ROOT + \"datasets/housing/housing.tgz\"\n",
    "\n",
    "def fetch_housing_data(housing_url=HOUSING_URL, housing_path=HOUSING_PATH):\n",
    "    if not os.path.isdir(housing_path):\n",
    "        os.makedirs(housing_path)\n",
    "    tgz_path = os.path.join(housing_path, \"housing.tgz\")\n",
    "    urllib.request.urlretrieve(housing_url, tgz_path)\n",
    "    housing_tgz = tarfile.open(tgz_path)\n",
    "    housing_tgz.extractall(path=housing_path)\n",
    "    housing_tgz.close()\n",
    "\n",
    "fetch_housing_data()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "461d1e78",
   "metadata": {},
   "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>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>housing_median_age</th>\n",
       "      <th>total_rooms</th>\n",
       "      <th>total_bedrooms</th>\n",
       "      <th>population</th>\n",
       "      <th>households</th>\n",
       "      <th>median_income</th>\n",
       "      <th>median_house_value</th>\n",
       "      <th>ocean_proximity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-122.23</td>\n",
       "      <td>37.88</td>\n",
       "      <td>41.0</td>\n",
       "      <td>880.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>8.3252</td>\n",
       "      <td>452600.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-122.22</td>\n",
       "      <td>37.86</td>\n",
       "      <td>21.0</td>\n",
       "      <td>7099.0</td>\n",
       "      <td>1106.0</td>\n",
       "      <td>2401.0</td>\n",
       "      <td>1138.0</td>\n",
       "      <td>8.3014</td>\n",
       "      <td>358500.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-122.24</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1467.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>496.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>7.2574</td>\n",
       "      <td>352100.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-122.25</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1274.0</td>\n",
       "      <td>235.0</td>\n",
       "      <td>558.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>5.6431</td>\n",
       "      <td>341300.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-122.25</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1627.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>565.0</td>\n",
       "      <td>259.0</td>\n",
       "      <td>3.8462</td>\n",
       "      <td>342200.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   longitude  latitude  housing_median_age  total_rooms  total_bedrooms  \\\n",
       "0    -122.23     37.88                41.0        880.0           129.0   \n",
       "1    -122.22     37.86                21.0       7099.0          1106.0   \n",
       "2    -122.24     37.85                52.0       1467.0           190.0   \n",
       "3    -122.25     37.85                52.0       1274.0           235.0   \n",
       "4    -122.25     37.85                52.0       1627.0           280.0   \n",
       "\n",
       "   population  households  median_income  median_house_value ocean_proximity  \n",
       "0       322.0       126.0         8.3252            452600.0        NEAR BAY  \n",
       "1      2401.0      1138.0         8.3014            358500.0        NEAR BAY  \n",
       "2       496.0       177.0         7.2574            352100.0        NEAR BAY  \n",
       "3       558.0       219.0         5.6431            341300.0        NEAR BAY  \n",
       "4       565.0       259.0         3.8462            342200.0        NEAR BAY  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Import Snowpark libraries \n",
    "from snowflake.snowpark.session import Session\n",
    "\n",
    "#Snowflake connection info is saved in config.py\n",
    "# Please change config.py to point to your Snowflake instance before running this step\n",
    "# Use user which has account admin role or equivalent to create DB, Schema, warehouse etc.\n",
    "\n",
    "from config import snowflake_conn_prop\n",
    "\n",
    "import pandas as pd \n",
    "\n",
    "def load_housing_data ( housing_path = HOUSING_PATH ): \n",
    "    csv_path = os.path.join ( housing_path , \"housing.csv\" ) \n",
    "    return pd.read_csv ( csv_path ) \n",
    "\n",
    "housing = load_housing_data()\n",
    "housing.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "27447aaa",
   "metadata": {},
   "source": [
    "Let's configure our Snowpark Session and initialize the database, warehouse, and schema that we will use for the remainder of the quickstart."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d4b15e07",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 23, 0)\n"
     ]
    }
   ],
   "source": [
    "from snowflake.snowpark import version\n",
    "print(version.VERSION)\n",
    "# Snowflake Python libs\n",
    "from snowflake.core import Root\n",
    "from snowflake.core.database import Database\n",
    "from snowflake.core.schema import Schema\n",
    "from snowflake.core.warehouse import Warehouse\n",
    "\n",
    "session = Session.builder.configs(snowflake_conn_prop).create()\n",
    "root = Root(session)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e7780f9",
   "metadata": {},
   "source": [
    "Create the databse, schema and virtual warehous we will use for the lab using the Snowflake Python API."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "001ce837",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[Row(CURRENT_WAREHOUSE()='LAB_SCIKIT_WH', CURRENT_DATABASE()='SCIKIT_LEARN', CURRENT_SCHEMA()='PUBLIC')]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "db_name = snowflake_conn_prop['database']\n",
    "schema_name = snowflake_conn_prop['schema']\n",
    "wh_name = snowflake_conn_prop['warehouse']\n",
    "\n",
    "# Create the database\n",
    "lab_db = Database(name=db_name)\n",
    "lab_db = root.databases.create(lab_db, mode='if_not_exists')\n",
    "\n",
    "# Create the schema\n",
    "lab_schema = Schema(name=schema_name)\n",
    "lab_schema = lab_db.schemas.create(lab_schema, mode='if_not_exists')\n",
    "\n",
    "# Create warehouse\n",
    "lab_wh = Warehouse(\n",
    "    name=wh_name, \n",
    "    warehouse_size=\"XSMALL\", \n",
    "    auto_suspend=600, \n",
    "    auto_resume='true', \n",
    ")\n",
    "warehouses = root.warehouses\n",
    "ml_wh = warehouses.create(lab_wh, mode='or_replace')\n",
    "\n",
    "# Set context\n",
    "session.use_schema(f'{db_name}.{schema_name}')\n",
    "session.use_warehouse(wh_name)\n",
    "\n",
    "print(session.sql('select current_warehouse(), current_database(), current_schema()').collect())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "286eb867",
   "metadata": {},
   "source": [
    "### Write the data into a Snowflake table named `HOUSING_DATA`\n",
    "\n",
    "Use the **write_pandas** method to create a table with the data in the Pandas dataframe, housing. The table will be automatically created, if it exists it will be dropped and recreated (auto_create_table and overwrite controls this)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d974b7ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "snowpark_df = session.write_pandas(housing, \"HOUSING_DATA\", quote_identifiers=False, auto_create_table=True, overwrite=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "18fa8967",
   "metadata": {},
   "source": [
    "Check that we have data in the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0426367e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|\"LONGITUDE\"  |\"LATITUDE\"  |\"HOUSING_MEDIAN_AGE\"  |\"TOTAL_ROOMS\"  |\"TOTAL_BEDROOMS\"  |\"POPULATION\"  |\"HOUSEHOLDS\"  |\"MEDIAN_INCOME\"  |\"MEDIAN_HOUSE_VALUE\"  |\"OCEAN_PROXIMITY\"  |\n",
      "---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "|-122.23      |37.88       |41.0                  |880.0          |129.0             |322.0         |126.0         |8.3252           |452600.0              |NEAR BAY           |\n",
      "|-122.22      |37.86       |21.0                  |7099.0         |1106.0            |2401.0        |1138.0        |8.3014           |358500.0              |NEAR BAY           |\n",
      "|-122.24      |37.85       |52.0                  |1467.0         |190.0             |496.0         |177.0         |7.2574           |352100.0              |NEAR BAY           |\n",
      "|-122.25      |37.85       |52.0                  |1274.0         |235.0             |558.0         |219.0         |5.6431           |341300.0              |NEAR BAY           |\n",
      "|-122.25      |37.85       |52.0                  |1627.0         |280.0             |565.0         |259.0         |3.8462           |342200.0              |NEAR BAY           |\n",
      "|-122.25      |37.85       |52.0                  |919.0          |213.0             |413.0         |193.0         |4.0368           |269700.0              |NEAR BAY           |\n",
      "|-122.25      |37.84       |52.0                  |2535.0         |489.0             |1094.0        |514.0         |3.6591           |299200.0              |NEAR BAY           |\n",
      "|-122.25      |37.84       |52.0                  |3104.0         |687.0             |1157.0        |647.0         |3.12             |241400.0              |NEAR BAY           |\n",
      "|-122.26      |37.84       |42.0                  |2555.0         |665.0             |1206.0        |595.0         |2.0804           |226700.0              |NEAR BAY           |\n",
      "|-122.25      |37.84       |52.0                  |3549.0         |707.0             |1551.0        |714.0         |3.6912           |261100.0              |NEAR BAY           |\n",
      "---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n",
      "\n"
     ]
    }
   ],
   "source": [
    "snowpark_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "862d9ac2",
   "metadata": {},
   "outputs": [],
   "source": [
    "session.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f419e0e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "snowpark_scikit_learn",
   "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.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}