{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[![AWS SDK for pandas](_static/logo.png \"AWS SDK for pandas\")](https://github.com/aws/aws-sdk-pandas)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 29 - S3 Select"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "AWS SDK for pandas supports [Amazon S3 Select](https://aws.amazon.com/blogs/aws/s3-glacier-select/), enabling applications to use SQL statements in order to query and filter the contents of a single S3 object. It works on objects stored in CSV, JSON or Apache Parquet, including compressed and large files of several TBs.\n",
    "\n",
    "With S3 Select, the query workload is delegated to Amazon S3, leading to lower latency and cost, and to higher performance (up to 400% improvement). This is in comparison with other awswrangler operations such as `read_parquet` where the S3 object is downloaded and filtered on the client-side.\n",
    "\n",
    "This feature has a number of limitations however:\n",
    "\n",
    "- The maximum length of a record in the input or result is 1 MB\n",
    "- The maximum uncompressed row group size is 256 MB (Parquet only)\n",
    "- It can only emit nested data in JSON format\n",
    "- Certain SQL operations are not supported (e.g. ORDER BY)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read multiple Parquet files from an S3 prefix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "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>marketplace</th>\n",
       "      <th>customer_id</th>\n",
       "      <th>review_id</th>\n",
       "      <th>product_id</th>\n",
       "      <th>product_parent</th>\n",
       "      <th>star_rating</th>\n",
       "      <th>helpful_votes</th>\n",
       "      <th>total_votes</th>\n",
       "      <th>vine</th>\n",
       "      <th>verified_purchase</th>\n",
       "      <th>review_headline</th>\n",
       "      <th>review_body</th>\n",
       "      <th>review_date</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US</td>\n",
       "      <td>52670295</td>\n",
       "      <td>RGPOFKORD8RTU</td>\n",
       "      <td>B0002CZPPG</td>\n",
       "      <td>867256265</td>\n",
       "      <td>5</td>\n",
       "      <td>105</td>\n",
       "      <td>107</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Excellent Gift Idea</td>\n",
       "      <td>I wonder if the other reviewer actually read t...</td>\n",
       "      <td>2005-02-08</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US</td>\n",
       "      <td>29964102</td>\n",
       "      <td>R2U8X8V5KPB4J3</td>\n",
       "      <td>B00H5BMF00</td>\n",
       "      <td>373287760</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Five Stars</td>\n",
       "      <td>convenience is the name of the game.</td>\n",
       "      <td>2015-05-03</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US</td>\n",
       "      <td>25173351</td>\n",
       "      <td>R15XV3LXUMLTXL</td>\n",
       "      <td>B00PG40CO4</td>\n",
       "      <td>137115061</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Birthday Gift</td>\n",
       "      <td>This gift card was handled with accuracy in de...</td>\n",
       "      <td>2015-05-03</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US</td>\n",
       "      <td>12516181</td>\n",
       "      <td>R3G6G7H8TX4H0T</td>\n",
       "      <td>B0002CZPPG</td>\n",
       "      <td>867256265</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Love 'em.</td>\n",
       "      <td>Gotta love these iTunes Prepaid Card thingys. ...</td>\n",
       "      <td>2005-10-15</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US</td>\n",
       "      <td>38355314</td>\n",
       "      <td>R2NJ7WNBU16YTQ</td>\n",
       "      <td>B00B2TFSO6</td>\n",
       "      <td>89375983</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Five Stars</td>\n",
       "      <td>perfect</td>\n",
       "      <td>2015-05-03</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  marketplace customer_id       review_id  product_id product_parent  \\\n",
       "0          US    52670295   RGPOFKORD8RTU  B0002CZPPG      867256265   \n",
       "1          US    29964102  R2U8X8V5KPB4J3  B00H5BMF00      373287760   \n",
       "2          US    25173351  R15XV3LXUMLTXL  B00PG40CO4      137115061   \n",
       "3          US    12516181  R3G6G7H8TX4H0T  B0002CZPPG      867256265   \n",
       "4          US    38355314  R2NJ7WNBU16YTQ  B00B2TFSO6       89375983   \n",
       "\n",
       "   star_rating  helpful_votes  total_votes vine verified_purchase  \\\n",
       "0            5            105          107    N                 N   \n",
       "1            5              0            0    N                 Y   \n",
       "2            5              0            0    N                 Y   \n",
       "3            5              6            6    N                 N   \n",
       "4            5              0            0    N                 Y   \n",
       "\n",
       "       review_headline                                        review_body  \\\n",
       "0  Excellent Gift Idea  I wonder if the other reviewer actually read t...   \n",
       "1           Five Stars               convenience is the name of the game.   \n",
       "2        Birthday Gift  This gift card was handled with accuracy in de...   \n",
       "3            Love 'em.  Gotta love these iTunes Prepaid Card thingys. ...   \n",
       "4           Five Stars                                            perfect   \n",
       "\n",
       "  review_date  year  \n",
       "0  2005-02-08  2005  \n",
       "1  2015-05-03  2015  \n",
       "2  2015-05-03  2015  \n",
       "3  2005-10-15  2005  \n",
       "4  2015-05-03  2015  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import awswrangler as wr\n",
    "\n",
    "df = wr.s3.select_query(\n",
    "    sql=\"SELECT * FROM s3object s where s.\\\"star_rating\\\" >= 5\",\n",
    "    path=\"s3://amazon-reviews-pds/parquet/product_category=Gift_Card/\",\n",
    "    input_serialization=\"Parquet\",\n",
    "    input_serialization_params={},\n",
    ")\n",
    "df.loc[:, df.columns != \"product_title\"].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read full CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>question</th>\n",
       "      <th>product_description</th>\n",
       "      <th>image_url</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Will the volca sample get me a girlfriend?</td>\n",
       "      <td>Korg Amplifier Part VOLCASAMPLE</td>\n",
       "      <td>http://ecx.images-amazon.com/images/I/81I1XZea...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Can u communicate with spirits even on Saturday?</td>\n",
       "      <td>Winning Moves Games Classic Ouija</td>\n",
       "      <td>http://ecx.images-amazon.com/images/I/81kcYEG5...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>I won't get hunted right?</td>\n",
       "      <td>Winning Moves Games Classic Ouija</td>\n",
       "      <td>http://ecx.images-amazon.com/images/I/81kcYEG5...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>I have a few questions.. Can you get possessed...</td>\n",
       "      <td>Winning Moves Games Classic Ouija</td>\n",
       "      <td>http://ecx.images-amazon.com/images/I/81kcYEG5...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Has anyone asked where the treasure is? What w...</td>\n",
       "      <td>Winning Moves Games Classic Ouija</td>\n",
       "      <td>http://ecx.images-amazon.com/images/I/81kcYEG5...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            question  \\\n",
       "0         Will the volca sample get me a girlfriend?   \n",
       "1   Can u communicate with spirits even on Saturday?   \n",
       "2                          I won't get hunted right?   \n",
       "3  I have a few questions.. Can you get possessed...   \n",
       "4  Has anyone asked where the treasure is? What w...   \n",
       "\n",
       "                 product_description  \\\n",
       "0    Korg Amplifier Part VOLCASAMPLE   \n",
       "1  Winning Moves Games Classic Ouija   \n",
       "2  Winning Moves Games Classic Ouija   \n",
       "3  Winning Moves Games Classic Ouija   \n",
       "4  Winning Moves Games Classic Ouija   \n",
       "\n",
       "                                           image_url label  \n",
       "0  http://ecx.images-amazon.com/images/I/81I1XZea...     1  \n",
       "1  http://ecx.images-amazon.com/images/I/81kcYEG5...     1  \n",
       "2  http://ecx.images-amazon.com/images/I/81kcYEG5...     1  \n",
       "3  http://ecx.images-amazon.com/images/I/81kcYEG5...     1  \n",
       "4  http://ecx.images-amazon.com/images/I/81kcYEG5...     1  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = wr.s3.select_query(\n",
    "    sql=\"SELECT * FROM s3object\",\n",
    "    path=\"s3://humor-detection-pds/Humorous.csv\",\n",
    "    input_serialization=\"CSV\",\n",
    "    input_serialization_params={\n",
    "        \"FileHeaderInfo\": \"Use\",\n",
    "        \"RecordDelimiter\": \"\\r\\n\",\n",
    "    },\n",
    "    scan_range_chunk_size=1024*1024*32,  # override range of bytes to query, by default 1Mb\n",
    "    use_threads=True,\n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter JSON file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>family_name</th>\n",
       "      <th>contact_details</th>\n",
       "      <th>name</th>\n",
       "      <th>links</th>\n",
       "      <th>gender</th>\n",
       "      <th>image</th>\n",
       "      <th>identifiers</th>\n",
       "      <th>other_names</th>\n",
       "      <th>sort_name</th>\n",
       "      <th>images</th>\n",
       "      <th>given_name</th>\n",
       "      <th>birth_date</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Biden</td>\n",
       "      <td>[{'type': 'twitter', 'value': 'joebiden'}]</td>\n",
       "      <td>Joseph Biden, Jr.</td>\n",
       "      <td>[{'note': 'Wikipedia (ace)', 'url': 'https://a...</td>\n",
       "      <td>male</td>\n",
       "      <td>https://theunitedstates.io/images/congress/ori...</td>\n",
       "      <td>[{'identifier': 'B000444', 'scheme': 'bioguide...</td>\n",
       "      <td>[{'lang': None, 'name': 'Joe Biden', 'note': '...</td>\n",
       "      <td>Biden, Joseph</td>\n",
       "      <td>[{'url': 'https://theunitedstates.io/images/co...</td>\n",
       "      <td>Joseph</td>\n",
       "      <td>1942-11-20</td>\n",
       "      <td>64239edf-8e06-4d2d-acc0-33d96bc79774</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  family_name                             contact_details               name  \\\n",
       "0       Biden  [{'type': 'twitter', 'value': 'joebiden'}]  Joseph Biden, Jr.   \n",
       "\n",
       "                                               links gender  \\\n",
       "0  [{'note': 'Wikipedia (ace)', 'url': 'https://a...   male   \n",
       "\n",
       "                                               image  \\\n",
       "0  https://theunitedstates.io/images/congress/ori...   \n",
       "\n",
       "                                         identifiers  \\\n",
       "0  [{'identifier': 'B000444', 'scheme': 'bioguide...   \n",
       "\n",
       "                                         other_names      sort_name  \\\n",
       "0  [{'lang': None, 'name': 'Joe Biden', 'note': '...  Biden, Joseph   \n",
       "\n",
       "                                              images given_name  birth_date  \\\n",
       "0  [{'url': 'https://theunitedstates.io/images/co...     Joseph  1942-11-20   \n",
       "\n",
       "                                     id  \n",
       "0  64239edf-8e06-4d2d-acc0-33d96bc79774  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wr.s3.select_query(\n",
    "    sql=\"SELECT * FROM s3object[*] s where s.\\\"family_name\\\" = \\'Biden\\'\",\n",
    "    path=\"s3://awsglue-datasets/examples/us-legislators/all/persons.json\",\n",
    "    input_serialization=\"JSON\",\n",
    "    input_serialization_params={\n",
    "        \"Type\": \"Document\",\n",
    "    },\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.14",
   "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.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}