02_Register_Parquet_Glue_Athena.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Query the Dataset in S3 using Amazon Athena and AWS Glue Catalog\n",
    "This will create an Athena table in the Glue Catalog (Hive Metastore) which allows us to query the dataset files in S3.  We will create a table in the Glue Catalog based on the `Amazon Customer Reviews Dataset` in S3.  We define the columns that map to the data, specify how the data is formatted, and provide the location in Amazon S3 for the file(s).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<img src=\"img/athena_register_parquet.png\" width=\"60%\" align=\"left\">"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "svmem(total=32877658112, available=12550275072, percent=61.8, used=19907919872, free=2867982336, active=20596514816, inactive=8226992128, buffers=1634304, cached=10100121600, shared=970752, slab=624959488)\n"
     ]
    }
   ],
   "source": [
    "import psutil\n",
    "\n",
    "notebook_memory = psutil.virtual_memory()\n",
    "print(notebook_memory)\n",
    "\n",
    "if notebook_memory.total < 32 * 1000 * 1000 * 1000:\n",
    "    print('*******************************************')    \n",
    "    print('YOU ARE NOT USING THE CORRECT INSTANCE TYPE')\n",
    "    print('PLEASE CHANGE INSTANCE TYPE TO  m5.2xlarge ')\n",
    "    print('*******************************************')\n",
    "else:\n",
    "    correct_instance_type=True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import boto3\n",
    "import sagemaker\n",
    "\n",
    "sess = sagemaker.Session()\n",
    "bucket = sess.default_bucket()\n",
    "region = boto3.Session().region_name\n",
    "account_id = boto3.client(\"sts\").get_caller_identity().get(\"Account\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyathena import connect\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "%store -r setup_dependencies_passed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    setup_dependencies_passed\n",
    "except NameError:\n",
    "    print(\"++++++++++++++++++++++++++++++++++++++++++++++\")\n",
    "    print(\"[ERROR] YOU HAVE TO RUN THE PREVIOUS NOTEBOOK \")\n",
    "    print(\"You did not install the required libraries.   \")\n",
    "    print(\"++++++++++++++++++++++++++++++++++++++++++++++\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "print(setup_dependencies_passed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[OK]\n"
     ]
    }
   ],
   "source": [
    "if not setup_dependencies_passed:\n",
    "    print(\"++++++++++++++++++++++++++++++++++++++++++++++\")\n",
    "    print(\"[ERROR] YOU HAVE TO RUN THE PREVIOUS NOTEBOOK \")\n",
    "    print(\"You did not install the required libraries.   \")\n",
    "    print(\"++++++++++++++++++++++++++++++++++++++++++++++\")\n",
    "else:\n",
    "    print(\"[OK]\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "%store -r role"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create Athena Table for Parquet Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Dataset columns\n",
    "\n",
    "- `marketplace`: 2-letter country code (in this case all \"US\").\n",
    "- `customer_id`: Random identifier that can be used to aggregate reviews written by a single author.\n",
    "- `review_id`: A unique ID for the review.\n",
    "- `product_id`: The Amazon Standard Identification Number (ASIN).  `http://www.amazon.com/dp/<ASIN>` links to the product's detail page.\n",
    "- `product_parent`: The parent of that ASIN.  Multiple ASINs (color or format variations of the same product) can roll up into a single parent.\n",
    "- `product_title`: Title description of the product.\n",
    "- `product_category`: Broad product category that can be used to group reviews (in this case digital videos).\n",
    "- `star_rating`: The review's rating (1 to 5 stars).\n",
    "- `helpful_votes`: Number of helpful votes for the review.\n",
    "- `total_votes`: Number of total votes the review received.\n",
    "- `vine`: Was the review written as part of the [Vine](https://www.amazon.com/gp/vine/help) program?\n",
    "- `verified_purchase`: Was the review from a verified purchase?\n",
    "- `review_headline`: The title of the review itself.\n",
    "- `review_body`: The text of the review.\n",
    "- `review_date`: The date the review was written.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "s3_staging_dir = \"s3://{}/athena/query-cache\".format(bucket)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "database_name = \"default\"\n",
    "table_name = \"amazon_reviews_parquet\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = connect(region_name=region, s3_staging_dir=s3_staging_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    CREATE EXTERNAL TABLE IF NOT EXISTS default.amazon_reviews_parquet (\n",
      "      marketplace string, \n",
      "      customer_id string, \n",
      "      review_id string, \n",
      "      product_id string, \n",
      "      product_parent string, \n",
      "      product_title string, \n",
      "      star_rating int, \n",
      "      helpful_votes int, \n",
      "      total_votes int, \n",
      "      vine string, \n",
      "      verified_purchase string, \n",
      "      review_headline string, \n",
      "      review_body string, \n",
      "      review_date int, \n",
      "      year int)\n",
      "    PARTITIONED BY (product_category string)\n",
      "    ROW FORMAT SERDE \n",
      "      'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' \n",
      "    STORED AS INPUTFORMAT \n",
      "      'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' \n",
      "    OUTPUTFORMAT \n",
      "      'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'\n",
      "    LOCATION\n",
      "      's3://amazon-reviews-pds/parquet/'\n",
      "\n"
     ]
    },
    {
     "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# SQL statement to execute\n",
    "statement = \"\"\"\n",
    "    CREATE EXTERNAL TABLE IF NOT EXISTS {}.{} (\n",
    "      marketplace string, \n",
    "      customer_id string, \n",
    "      review_id string, \n",
    "      product_id string, \n",
    "      product_parent string, \n",
    "      product_title string, \n",
    "      star_rating int, \n",
    "      helpful_votes int, \n",
    "      total_votes int, \n",
    "      vine string, \n",
    "      verified_purchase string, \n",
    "      review_headline string, \n",
    "      review_body string, \n",
    "      review_date int, \n",
    "      year int)\n",
    "    PARTITIONED BY (product_category string)\n",
    "    ROW FORMAT SERDE \n",
    "      'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' \n",
    "    STORED AS INPUTFORMAT \n",
    "      'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' \n",
    "    OUTPUTFORMAT \n",
    "      'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'\n",
    "    LOCATION\n",
    "      's3://amazon-reviews-pds/parquet/'\n",
    "\"\"\".format(\n",
    "    database_name, table_name\n",
    ")\n",
    "\n",
    "print(statement)\n",
    "\n",
    "pd.read_sql(statement, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analyze and Build the Partitions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    MSCK REPAIR TABLE default.amazon_reviews_parquet\n",
      "\n"
     ]
    },
    {
     "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = \"\"\"\n",
    "    MSCK REPAIR TABLE {}.{}\n",
    "\"\"\".format(\n",
    "    database_name, table_name\n",
    ")\n",
    "\n",
    "print(statement)\n",
    "\n",
    "pd.read_sql(statement, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Verify The Table Has Been Created Succesfully"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>tab_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>amazon_reviews_parquet</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 tab_name\n",
       "0  amazon_reviews_parquet"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statement = \"SHOW TABLES in {}\".format(database_name)\n",
    "\n",
    "df_tables = pd.read_sql(statement, conn)\n",
    "df_tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Run A Sample Query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "    SELECT * FROM default.amazon_reviews_parquet\n",
      "    WHERE product_category = 'Digital_Software' LIMIT 100\n",
      "\n"
     ]
    }
   ],
   "source": [
    "product_category = \"Digital_Software\"\n",
    "\n",
    "statement = \"\"\"\n",
    "    SELECT * FROM {}.{}\n",
    "    WHERE product_category = '{}' LIMIT 100\n",
    "\"\"\".format(\n",
    "    database_name, table_name, product_category\n",
    ")\n",
    "\n",
    "print(statement)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>product_title</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",
       "      <th>product_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US</td>\n",
       "      <td>52227601</td>\n",
       "      <td>R3I00Z3RS1192X</td>\n",
       "      <td>B000YMR5X4</td>\n",
       "      <td>234295632</td>\n",
       "      <td>TurboTax Premier Federal + State 2007</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Great download experience</td>\n",
       "      <td>I have used used Turbotax to do my taxes for 1...</td>\n",
       "      <td>13923</td>\n",
       "      <td>2008</td>\n",
       "      <td>Digital_Software</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US</td>\n",
       "      <td>50526871</td>\n",
       "      <td>R1JKRJ049ZHGV9</td>\n",
       "      <td>B008S0IMCC</td>\n",
       "      <td>534964191</td>\n",
       "      <td>Quicken Deluxe 2013</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Make sure your bank(s) support this version</td>\n",
       "      <td>I've been using Quicken to track my finances f...</td>\n",
       "      <td>15898</td>\n",
       "      <td>2013</td>\n",
       "      <td>Digital_Software</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US</td>\n",
       "      <td>52604041</td>\n",
       "      <td>R3C9V2TITW26CR</td>\n",
       "      <td>B000YMR5X4</td>\n",
       "      <td>234295632</td>\n",
       "      <td>TurboTax Premier Federal + State 2007</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Excellent, (speed issues not always the software)</td>\n",
       "      <td>It took 10 minutes to download the installer, ...</td>\n",
       "      <td>13968</td>\n",
       "      <td>2008</td>\n",
       "      <td>Digital_Software</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US</td>\n",
       "      <td>16645429</td>\n",
       "      <td>RHJIVJBE11FMG</td>\n",
       "      <td>B00A42LWHO</td>\n",
       "      <td>981240357</td>\n",
       "      <td>H&amp;R Block At Home Deluxe + State 2012</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>N</td>\n",
       "      <td>Y</td>\n",
       "      <td>Great</td>\n",
       "      <td>Convenient download and works well for my spec...</td>\n",
       "      <td>15898</td>\n",
       "      <td>2013</td>\n",
       "      <td>Digital_Software</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US</td>\n",
       "      <td>34963673</td>\n",
       "      <td>R2GWPR0XNFA9CG</td>\n",
       "      <td>B001M4JFOA</td>\n",
       "      <td>546690459</td>\n",
       "      <td>TurboTax Deluxe Federal + State + eFile 2008</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>Do NOT trust this program entirely - be AWARE</td>\n",
       "      <td>Even though I indicated that I was a first tim...</td>\n",
       "      <td>14269</td>\n",
       "      <td>2009</td>\n",
       "      <td>Digital_Software</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  marketplace customer_id       review_id  product_id product_parent  \\\n",
       "0          US    52227601  R3I00Z3RS1192X  B000YMR5X4      234295632   \n",
       "1          US    50526871  R1JKRJ049ZHGV9  B008S0IMCC      534964191   \n",
       "2          US    52604041  R3C9V2TITW26CR  B000YMR5X4      234295632   \n",
       "3          US    16645429   RHJIVJBE11FMG  B00A42LWHO      981240357   \n",
       "4          US    34963673  R2GWPR0XNFA9CG  B001M4JFOA      546690459   \n",
       "\n",
       "                                  product_title  star_rating  helpful_votes  \\\n",
       "0         TurboTax Premier Federal + State 2007            5              4   \n",
       "1                           Quicken Deluxe 2013            4              0   \n",
       "2         TurboTax Premier Federal + State 2007            5              2   \n",
       "3         H&R Block At Home Deluxe + State 2012            5              0   \n",
       "4  TurboTax Deluxe Federal + State + eFile 2008            2              9   \n",
       "\n",
       "   total_votes vine verified_purchase  \\\n",
       "0            5    N                 N   \n",
       "1            0    N                 Y   \n",
       "2            2    N                 N   \n",
       "3            0    N                 Y   \n",
       "4           10    N                 N   \n",
       "\n",
       "                                     review_headline  \\\n",
       "0                          Great download experience   \n",
       "1        Make sure your bank(s) support this version   \n",
       "2  Excellent, (speed issues not always the software)   \n",
       "3                                              Great   \n",
       "4      Do NOT trust this program entirely - be AWARE   \n",
       "\n",
       "                                         review_body  review_date  year  \\\n",
       "0  I have used used Turbotax to do my taxes for 1...        13923  2008   \n",
       "1  I've been using Quicken to track my finances f...        15898  2013   \n",
       "2  It took 10 minutes to download the installer, ...        13968  2008   \n",
       "3  Convenient download and works well for my spec...        15898  2013   \n",
       "4  Even though I indicated that I was a first tim...        14269  2009   \n",
       "\n",
       "   product_category  \n",
       "0  Digital_Software  \n",
       "1  Digital_Software  \n",
       "2  Digital_Software  \n",
       "3  Digital_Software  \n",
       "4  Digital_Software  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_sql(statement, conn)\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[OK]\n"
     ]
    }
   ],
   "source": [
    "if df.empty:\n",
    "    print(\"++++++++++++++++++++++++++++++++++++++++++++++++++++++\")\n",
    "    print(\"[ERROR] YOUR DATA HAS NOT BEEN REGISTERED WITH ATHENA.\")\n",
    "    print(\"LOOK IN PREVIOUS CELLS TO FIND THE ISSUE.             \")\n",
    "    print(\"++++++++++++++++++++++++++++++++++++++++++++++++++++++\")\n",
    "else:\n",
    "    print(\"[OK]\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "if \"amazon_reviews_parquet\" in df_tables.values and not df.empty:\n",
    "    ingest_create_athena_table_parquet_passed = True"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stored 'ingest_create_athena_table_parquet_passed' (bool)\n"
     ]
    }
   ],
   "source": [
    "%store ingest_create_athena_table_parquet_passed"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Review the New Athena Table in the Glue Catalog"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Review <a target=\"top\" href=\"https://console.aws.amazon.com/glue/home?region=us-east-1#table:catalog=124111238543;name=amazon_reviews_parquet;namespace=default\">AWS Glue Catalog</a></b>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "\n",
    "display(\n",
    "    HTML(\n",
    "        '<b>Review <a target=\"top\" href=\"https://console.aws.amazon.com/glue/home?region={}#table:catalog={};name=amazon_reviews_parquet;namespace=default\">AWS Glue Catalog</a></b>'.format(\n",
    "            region, account_id\n",
    "        )\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Store Variables for the Next Notebooks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stored variables and their in-db values:\n",
      "ingest_create_athena_table_parquet_passed             -> True\n",
      "local_data_processed_path                             -> './data-summarization-processed/'\n",
      "model_checkpoint                                      -> 'google/flan-t5-base'\n",
      "processed_test_data_s3_uri                            -> 's3://sagemaker-us-east-1-124111238543/sagemaker-s\n",
      "processed_train_data_s3_uri                           -> 's3://sagemaker-us-east-1-124111238543/sagemaker-s\n",
      "processed_validation_data_s3_uri                      -> 's3://sagemaker-us-east-1-124111238543/sagemaker-s\n",
      "raw_input_data_s3_uri                                 -> 's3://sagemaker-us-east-1-124111238543/data-summar\n",
      "role                                                  -> 'arn:aws:iam::124111238543:role/sagemaker-studio-v\n",
      "s3_private_path_tsv                                   -> 's3://sagemaker-us-east-1-124111238543/amazon-revi\n",
      "s3_public_path_tsv                                    -> 's3://amazon-reviews-pds/tsv'\n",
      "setup_dependencies_passed                             -> True\n",
      "supervised_fine_tuned_model_path                      -> './flan-dialogue-summary-checkpoint'\n",
      "test_split_percentage                                 -> 0.05\n",
      "train_split_percentage                                -> 0.9\n",
      "validation_split_percentage                           -> 0.05\n"
     ]
    }
   ],
   "source": [
    "%store"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Release Resources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<p><b>Shutting down your kernel for this notebook to release resources.</b></p>\n",
       "<button class=\"sm-command-button\" data-commandlinker-command=\"kernelmenu:shutdown\" style=\"display:none;\">Shutdown Kernel</button>\n",
       "        \n",
       "<script>\n",
       "try {\n",
       "    els = document.getElementsByClassName(\"sm-command-button\");\n",
       "    els[0].click();\n",
       "}\n",
       "catch(err) {\n",
       "    // NoOp\n",
       "}    \n",
       "</script>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "\n",
    "<p><b>Shutting down your kernel for this notebook to release resources.</b></p>\n",
    "<button class=\"sm-command-button\" data-commandlinker-command=\"kernelmenu:shutdown\" style=\"display:none;\">Shutdown Kernel</button>\n",
    "        \n",
    "<script>\n",
    "try {\n",
    "    els = document.getElementsByClassName(\"sm-command-button\");\n",
    "    els[0].click();\n",
    "}\n",
    "catch(err) {\n",
    "    // NoOp\n",
    "}    \n",
    "</script>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "availableInstances": [
   {
    "_defaultOrder": 0,
    "_isFastLaunch": true,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 4,
    "name": "ml.t3.medium",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 1,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.t3.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 2,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.t3.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 3,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.t3.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 4,
    "_isFastLaunch": true,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.m5.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 5,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.m5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 6,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.m5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 7,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.m5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 8,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.m5.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 9,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.m5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 10,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.m5.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 11,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.m5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 12,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.m5d.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 13,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.m5d.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 14,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.m5d.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 15,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.m5d.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 16,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.m5d.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 17,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.m5d.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 18,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.m5d.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 19,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.m5d.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 20,
    "_isFastLaunch": false,
    "category": "General purpose",
    "gpuNum": 0,
    "hideHardwareSpecs": true,
    "memoryGiB": 0,
    "name": "ml.geospatial.interactive",
    "supportedImageNames": [
     "sagemaker-geospatial-v1-0"
    ],
    "vcpuNum": 0
   },
   {
    "_defaultOrder": 21,
    "_isFastLaunch": true,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 4,
    "name": "ml.c5.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 22,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 8,
    "name": "ml.c5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 23,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.c5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 24,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.c5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 25,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 72,
    "name": "ml.c5.9xlarge",
    "vcpuNum": 36
   },
   {
    "_defaultOrder": 26,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 96,
    "name": "ml.c5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 27,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 144,
    "name": "ml.c5.18xlarge",
    "vcpuNum": 72
   },
   {
    "_defaultOrder": 28,
    "_isFastLaunch": false,
    "category": "Compute optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.c5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 29,
    "_isFastLaunch": true,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.g4dn.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 30,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.g4dn.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 31,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.g4dn.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 32,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.g4dn.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 33,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.g4dn.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 34,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.g4dn.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 35,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 61,
    "name": "ml.p3.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 36,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 244,
    "name": "ml.p3.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 37,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 488,
    "name": "ml.p3.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 38,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 768,
    "name": "ml.p3dn.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 39,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.r5.large",
    "vcpuNum": 2
   },
   {
    "_defaultOrder": 40,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.r5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 41,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.r5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 42,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.r5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 43,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.r5.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 44,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.r5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 45,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 512,
    "name": "ml.r5.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 46,
    "_isFastLaunch": false,
    "category": "Memory Optimized",
    "gpuNum": 0,
    "hideHardwareSpecs": false,
    "memoryGiB": 768,
    "name": "ml.r5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 47,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 16,
    "name": "ml.g5.xlarge",
    "vcpuNum": 4
   },
   {
    "_defaultOrder": 48,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 32,
    "name": "ml.g5.2xlarge",
    "vcpuNum": 8
   },
   {
    "_defaultOrder": 49,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 64,
    "name": "ml.g5.4xlarge",
    "vcpuNum": 16
   },
   {
    "_defaultOrder": 50,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 128,
    "name": "ml.g5.8xlarge",
    "vcpuNum": 32
   },
   {
    "_defaultOrder": 51,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 1,
    "hideHardwareSpecs": false,
    "memoryGiB": 256,
    "name": "ml.g5.16xlarge",
    "vcpuNum": 64
   },
   {
    "_defaultOrder": 52,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 192,
    "name": "ml.g5.12xlarge",
    "vcpuNum": 48
   },
   {
    "_defaultOrder": 53,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 4,
    "hideHardwareSpecs": false,
    "memoryGiB": 384,
    "name": "ml.g5.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 54,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 768,
    "name": "ml.g5.48xlarge",
    "vcpuNum": 192
   },
   {
    "_defaultOrder": 55,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 1152,
    "name": "ml.p4d.24xlarge",
    "vcpuNum": 96
   },
   {
    "_defaultOrder": 56,
    "_isFastLaunch": false,
    "category": "Accelerated computing",
    "gpuNum": 8,
    "hideHardwareSpecs": false,
    "memoryGiB": 1152,
    "name": "ml.p4de.24xlarge",
    "vcpuNum": 96
   }
  ],
  "instance_type": "ml.t3.2xlarge",
  "kernelspec": {
   "display_name": "Python 3 (Data Science)",
   "language": "python",
   "name": "python3__SAGEMAKER_INTERNAL__arn:aws:sagemaker:us-east-1:081325390199:image/datascience-1.0"
  },
  "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.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}