 archived/ingest_tabular_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ingest Tabular Data\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "This notebook's CI test result for us-west-2 is as follows. CI test results in other regions can be found at the end of the notebook. \n",
    "\n",
    "![This us-west-2 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/us-west-2/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "When ingesting structured data from an existing S3 bucket into a SageMaker Notebook, there are multiple ways to handle it. We will introduce the following methods to access your data from the notebook:\n",
    "\n",
    "* Copying your data to your instance. If you are dealing with a normal size of data or are simply experimenting, you can copy the files into the SageMaker instance and just use it as a file system in your local machine. \n",
    "* Using Python packages to directly access your data without copying it. One downside of copying your data to your instance is: if you are done with your notebook instance and delete it, all the data is gone with it unless you store it elsewhere. We will introduce several methods to solve this problem in this notebook, and using python packages is one of them. Also, if you have large data sets (for example, with millions of rows), you can directly read data from S3 utilizing S3 compatible python libraries with built-in functions.\n",
    "* Using AWS native methods to directly access your data. You can also use AWS native packages like `s3fs` and `aws data wrangler` to access your data directly.  \n",
    "\n",
    "We will demonstrate how to ingest the following tabular (structured) into a notebook for further analysis:\n",
    "## Tabular data: California Housing Data\n",
    "The [California Housing](https://scikit-learn.org/stable/modules/generated/sklearn.datasets.fetch_california_housing.html) dataset contains  information from the 1990 California census. We will use the data set to showcase how to ingest tabular data into S3, and for further pre-processing and feature engineering. The dataset contains the following columns:\n",
    "\n",
    "* `MedInc` - average income.\n",
    "* `HouseAge` - housing average age.\n",
    "* `AveRooms` - average rooms.\n",
    "* `AveBedrms` - average bedrooms.\n",
    "* `Population` - population.\n",
    "* `AveOccup` - average occupation.\n",
    "* `Latitude` - latitude.\n",
    "* `Longitude` - longitude.\n",
    "\n",
    "The California Housing dataset was originally published in:\n",
    "\n",
    "> Pace, R. Kelley, and Ronald Barry. \"Sparse spatial autoregressions.\" Statistics & Probability Letters 33.3 (1997): 291-297."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download data from online resources and write data to S3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install -qU 'sagemaker>=2.15.0' 's3fs==0.4.2' 'awswrangler==1.2.0'\n",
    "# you would need s3fs version > 0.4.0 for aws data wrangler to work correctly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import awswrangler as wr\n",
    "import pandas as pd\n",
    "import s3fs\n",
    "import sagemaker\n",
    "\n",
    "# to load the California housing dataset\n",
    "from sklearn.datasets import fetch_california_housing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get SageMaker session & default S3 bucket\n",
    "sagemaker_session = sagemaker.Session()\n",
    "s3 = sagemaker_session.boto_session.resource(\"s3\")\n",
    "bucket = sagemaker_session.default_bucket()  # replace with your own bucket name if you have one\n",
    "prefix = \"data/tabular/california_housing\"\n",
    "filename = \"california_housing.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# helper functions to upload data to s3\n",
    "def write_to_s3(filename, bucket, prefix):\n",
    "    # put one file in a separate folder. This is helpful if you read and prepare data with Athena\n",
    "    filename_key = filename.split(\".\")[0]\n",
    "    key = \"{}/{}/{}\".format(prefix, filename_key, filename)\n",
    "    return s3.Bucket(bucket).upload_file(filename, key)\n",
    "\n",
    "\n",
    "def upload_to_s3(bucket, prefix, filename):\n",
    "    url = \"s3://{}/{}/{}\".format(bucket, prefix, filename)\n",
    "    print(\"Writing to {}\".format(url))\n",
    "    write_to_s3(filename, bucket, prefix)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# download files from tabular data source location\n",
    "tabular_data = fetch_california_housing()\n",
    "tabular_data_full = pd.DataFrame(tabular_data.data, columns=tabular_data.feature_names)\n",
    "tabular_data_full[\"target\"] = pd.DataFrame(tabular_data.target)\n",
    "tabular_data_full.to_csv(\"california_housing.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "upload_to_s3(bucket, \"data/tabular\", filename)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ingest Tabular Data from S3 bucket\n",
    "### Method 1: Copying data to the Instance\n",
    "You can use AWS Command Line Interface (CLI) to copy your data from s3 to your SageMaker instance and copy files between your S3 buckets. This is a quick and easy approach when you are dealing with medium-sized data files, or you are experimenting and doing exploratory analysis. The documentation can be found [here](https://docs.aws.amazon.com/cli/latest/reference/s3/cp.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# copy data to your sagemaker instance using AWS CLI\n",
    "!aws s3 cp s3://$bucket/$prefix/ $prefix/ --recursive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_location = \"{}/{}\".format(prefix, filename)\n",
    "tabular_data = pd.read_csv(data_location, nrows=5)\n",
    "tabular_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 2: Use AWS compatible Python Packages\n",
    "When you are dealing with large data sets, or do not want to lose any data when you delete your SageMaker Notebook Instance, you can use pre-built packages to access your files in S3 without copying files into your instance. These packages, such as `Pandas`, have implemented options to access data with a specified path string: while you will use `file://` on your local file system, you will use `s3://` instead to access the data through the AWS boto library. For `pandas`, any valid string path is acceptable. The string could be a URL. Valid URL schemes include http, ftp, s3, and file. For file URLs, a host is expected.You can find additional documentation [here](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_s3_location = \"s3://{}/{}/{}\".format(bucket, prefix, filename)  # S3 URL\n",
    "s3_tabular_data = pd.read_csv(data_s3_location, nrows=5)\n",
    "s3_tabular_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Method 3: Use AWS native methods\n",
    "#### 3.1 s3fs \n",
    "\n",
    "[S3Fs](https://s3fs.readthedocs.io/en/latest/) is a Pythonic file interface to S3. It builds on top of botocore. The top-level class S3FileSystem holds connection information and allows typical file-system style operations like cp, mv, ls, du, glob, etc., as well as put/get of local files to/from S3. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fs = s3fs.S3FileSystem()\n",
    "data_s3fs_location = \"s3://{}/{}/\".format(bucket, prefix)\n",
    "# To List all files in your accessible bucket\n",
    "fs.ls(data_s3fs_location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# open it directly with s3fs\n",
    "data_s3fs_location = \"s3://{}/{}/{}\".format(bucket, prefix, filename)  # S3 URL\n",
    "with fs.open(data_s3fs_location) as f:\n",
    "    print(pd.read_csv(f, nrows=5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3.2 AWS Data Wrangler\n",
    "[AWS Data Wrangler](https://github.com/awslabs/aws-data-wrangler) is an open-source Python library that extends the power of the Pandas library to AWS connecting DataFrames and AWS data related services (Amazon Redshift, AWS Glue, Amazon Athena, Amazon EMR, Amazon QuickSight, etc), which we will cover in later sections. It is built on top of other open-source projects like Pandas, Apache Arrow, Boto3, s3fs, SQLAlchemy, Psycopg2 and PyMySQL, and offers abstracted functions to execute usual ETL tasks like load/unload data from Data Lakes, Data Warehouses and Databases. Note that you would need `s3fs version > 0.4.0` for the `awswrangler csv reader` to work."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_wr_location = \"s3://{}/{}/{}\".format(bucket, prefix, filename)  # S3 URL\n",
    "wr_data = wr.s3.read_csv(path=data_wr_location, nrows=5)\n",
    "wr_data.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Notebook CI Test Results\n",
    "\n",
    "This notebook was tested in multiple regions. The test results are as follows, except for us-west-2 which is shown at the top of the notebook.\n",
    "\n",
    "![This us-east-1 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/us-east-1/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This us-east-2 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/us-east-2/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This us-west-1 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/us-west-1/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This ca-central-1 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/ca-central-1/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This sa-east-1 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/sa-east-1/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This eu-west-1 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/eu-west-1/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This eu-west-2 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/eu-west-2/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This eu-west-3 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/eu-west-3/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This eu-central-1 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/eu-central-1/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This eu-north-1 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/eu-north-1/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This ap-southeast-1 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/ap-southeast-1/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This ap-southeast-2 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/ap-southeast-2/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This ap-northeast-1 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/ap-northeast-1/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This ap-northeast-2 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/ap-northeast-2/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n",
    "\n",
    "![This ap-south-1 badge failed to load. Check your device's internet connectivity, otherwise the service is currently unavailable](https://prod.us-west-2.tcx-beacon.docs.aws.dev/sagemaker-nb/ap-south-1/ingest_data|ingest-data-types|ingest_tabular_data.ipynb)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (Data Science 2.0)",
   "language": "python",
   "name": "python3__SAGEMAKER_INTERNAL__arn:aws:sagemaker:us-west-2:236514542706:image/sagemaker-data-science-38"
  },
  "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.8.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}