Amazon_Reviews_ETL.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyMjAtW11XHHiNfICf5VzL8B",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ADMaple/Amazon_Vine_Analysis/blob/main/Amazon_Reviews_ETL.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "id": "x4rLUoJm4QZy"
      },
      "outputs": [],
      "source": [
        "import os\n",
        "# Find the latest version of spark 3.0  from http://www.apache.org/dist/spark/ and enter as the spark version\n",
        "# For example:\n",
        "spark_version = 'spark-3.2.2'\n",
        "os.environ['SPARK_VERSION']=spark_version"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-11-jdk-headless -qq > /dev/null\n",
        "\n",
        "# install spark (change the version number if needed)\n",
        "!wget -q http://www.apache.org/dist/spark/$SPARK_VERSION/$SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "\n",
        "# unzip the spark file to the current folder\n",
        "!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!pip install -q findspark"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jtO1IZ7F4TWO",
        "outputId": "52ba077f-4b4b-41b2-e2bd-c18a3ea371d7"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rIgn:1 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "\r0% [Connecting to archive.ubuntu.com] [Connecting to security.ubuntu.com] [Conn\r                                                                               \rHit:2 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "\r0% [Connecting to archive.ubuntu.com] [Connecting to security.ubuntu.com] [Conn\r0% [2 InRelease gpgv 1,581 B] [Connecting to archive.ubuntu.com] [Connecting to\r                                                                               \rHit:3 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Hit:4 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease\n",
            "Hit:5 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Get:6 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Get:7 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Hit:8 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "Get:10 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [83.3 kB]\n",
            "Hit:11 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Hit:12 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Hit:13 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Fetched 261 kB in 3s (90.3 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Set Environment Variables\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = f\"/content/{spark_version}-bin-hadoop2.7\""
      ],
      "metadata": {
        "id": "o9W7oLiK4YPl"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ],
      "metadata": {
        "id": "uYOcGlNV4f5z"
      },
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "!wget https://jdbc.postgresql.org/download/postgresql-42.2.16.jar"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "skmlL5zT5ZNt",
        "outputId": "d478666d-a60b-4a00-a9fa-81419f75252d"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2022-10-22 00:55:06--  https://jdbc.postgresql.org/download/postgresql-42.2.16.jar\n",
            "Resolving jdbc.postgresql.org (jdbc.postgresql.org)... 72.32.157.228, 2001:4800:3e1:1::228\n",
            "Connecting to jdbc.postgresql.org (jdbc.postgresql.org)|72.32.157.228|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 1002883 (979K) [application/java-archive]\n",
            "Saving to: ‘postgresql-42.2.16.jar.2’\n",
            "\n",
            "postgresql-42.2.16. 100%[===================>] 979.38K  6.10MB/s    in 0.2s    \n",
            "\n",
            "2022-10-22 00:55:06 (6.10 MB/s) - ‘postgresql-42.2.16.jar.2’ saved [1002883/1002883]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"CloudETL\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"
      ],
      "metadata": {
        "id": "T-XtDBIU5xxW"
      },
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Read in data from S3 Buckets\n",
        "from pyspark import SparkFiles\n",
        "url =\"https://adrianmaplebucket.s3.amazonaws.com/amazon_reviews_us_Outdoors_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "amazon_data_df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"\"), sep=\"\\t\", header=True, inferSchema=True)"
      ],
      "metadata": {
        "id": "-nhy3ZIb6SnR"
      },
      "execution_count": 7,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Show DataFrame\n",
        "amazon_data_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LuSTeOpR_n_I",
        "outputId": "2cd7ec66-4e90-45b9-e16b-bf0d20a490d2"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|marketplace|customer_id|     review_id|product_id|product_parent|       product_title|product_category|star_rating|helpful_votes|total_votes|vine|verified_purchase|     review_headline|         review_body|review_date|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|         US|   18446823|R35T75OLUGHL5C|B000NV6H94|     110804376|Stearns Youth Boa...|        Outdoors|          4|            0|          0|   N|                Y|          Four Stars|          GOOD VALUE| 2015-08-31|\n",
            "|         US|   13724367|R2BV735O46BN33|B000IN0W3Y|     624096774|Primal Wear Men's...|        Outdoors|          5|            0|          0|   N|                Y|          Five Stars|  Excellent quality.| 2015-08-31|\n",
            "|         US|   51001958|R2NBEUGPQQGXP1|B008RBJXFM|     278970944|Osprey Hydraulics...|        Outdoors|          4|            0|          0|   N|                Y|Only Flaw Is The Cap|3rd season using ...| 2015-08-31|\n",
            "|         US|   32866903|R17LLAOJ8ITK0S|B00FK8WUQY|     312877650|CamelBak eddy .75...|        Outdoors|          3|            1|          1|   N|                Y|Poor design leads...|poor construction...| 2015-08-31|\n",
            "|         US|   30907790|R39PEQBT5ISEF4|B00EZA3VW0|     305567912|Children Black Re...|        Outdoors|          1|            0|          0|   N|                Y|Very bad quality,...|Very bad quality,...| 2015-08-31|\n",
            "|         US|   20232229|R3GNM3SU9VHJFT|B006JA8WEG|     842306035|Ibera Bicycle Tri...|        Outdoors|          4|            1|          1|   N|                Y|Nice bag. Should ...|Nice bag. Should ...| 2015-08-31|\n",
            "|         US|   17698862| R2Y81OP0EK467|B002PWFSEO|     451480122|Therm-a-Rest Comp...|        Outdoors|          5|            0|          0|   N|                Y|Very comfortable ...|Gave this to my s...| 2015-08-31|\n",
            "|         US|   38486114|R2LFGSI6HAYH5F|B002DZGKHW|     124386306|Sawyer Products P...|        Outdoors|          5|            1|          1|   N|                Y| Worked like a charm|Went on vacation ...| 2015-08-31|\n",
            "|         US|   26319572|R297G6ED1IQO7W|B00ABA08F6|     991442421|Zippo Hand Warmer...|        Outdoors|          5|            1|          1|   N|                Y|Great item. Gets ...|Great item. Gets ...| 2015-08-31|\n",
            "|         US|   27152337| RE27RFC6101N6|B003Z8WIHC|     886483892|Camp Chef Dutch O...|        Outdoors|          5|            0|          0|   N|                Y|Great value for t...|I am so glad I bo...| 2015-08-31|\n",
            "|         US|   12516845|R3BPDME6E94W8Z|B007CP6UK0|     150224054|3CERA Portable Wi...|        Outdoors|          5|            0|          0|   N|                Y|          Five Stars|        good to have| 2015-08-31|\n",
            "|         US|    3225242|R2P08O1RILUOX3|B003V3U9JK|     343847969|Texsport King Kot...|        Outdoors|          3|            0|          0|   N|                Y|Cot set up inconv...|VERY difficult to...| 2015-08-31|\n",
            "|         US|     961839|R37CVAB03PTDVI|B00Y846HN8|     858088629|Wallygadgets 2 Wh...|        Outdoors|          5|            0|          1|   N|                Y|          Five Stars|Thanks excellent ...| 2015-08-31|\n",
            "|         US|   47796452| RAWNWOGXPCPMD|B00IYQ84VY|     474493517|RainStoppers 34-I...|        Outdoors|          5|            0|          0|   N|                Y|          Five Stars|This umbrella is ...| 2015-08-31|\n",
            "|         US|   32004835| R5DYGP6ASX77M|B002MYCKLY|     920014456|Alpha Deluxe Port...|        Outdoors|          5|            0|          0|   N|                Y|          Five Stars|Love it !! I even...| 2015-08-31|\n",
            "|         US|   23972939|R1O0SAOOGF2KG7|B00EZV69JG|     128489321|Speedfil Z4 BTA B...|        Outdoors|          4|            0|          0|   N|                Y|        Good enough.|This is a fine mo...| 2015-08-31|\n",
            "|         US|   40889047|R35NJUT0U3MU3V|B00AWOT3T8|     571303876|O'Brien Kids Plat...|        Outdoors|          5|            0|          0|   N|                Y| Got Up on First Try|We just bought th...| 2015-08-31|\n",
            "|         US|   11244387|R242C08MF9D1AH|B0000AXTID|     739769424|Kwik-Tek F-5R Pla...|        Outdoors|          5|            0|          0|   N|                Y|They go over an a...|I have these on m...| 2015-08-31|\n",
            "|         US|   20121211| R3RYG8TJTO4E2|B00IFHFJXI|     984009972|Ivation Portable ...|        Outdoors|          5|            0|          0|   N|                Y|Greatest Item I b...|This is the best ...| 2015-08-31|\n",
            "|         US|   25657249|R3IKH1DNY0CP9F|B00KFILTWU|     405521681|GreenInsync Repla...|        Outdoors|          2|            0|          0|   N|                Y|I received this p...|I received this p...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "***Create DataFrames to match tables***"
      ],
      "metadata": {
        "id": "ujVEkEIVIDvg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.functions import to_date\n",
        "# Read in the Review dataset as a DataFrame\n",
        "amazon_data_df.show(5)"
      ],
      "metadata": {
        "id": "5PikqRe3_xb2",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "7937ed55-9a38-4d4f-fa69-39de60dddcaf"
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|marketplace|customer_id|     review_id|product_id|product_parent|       product_title|product_category|star_rating|helpful_votes|total_votes|vine|verified_purchase|     review_headline|         review_body|review_date|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "|         US|   18446823|R35T75OLUGHL5C|B000NV6H94|     110804376|Stearns Youth Boa...|        Outdoors|          4|            0|          0|   N|                Y|          Four Stars|          GOOD VALUE| 2015-08-31|\n",
            "|         US|   13724367|R2BV735O46BN33|B000IN0W3Y|     624096774|Primal Wear Men's...|        Outdoors|          5|            0|          0|   N|                Y|          Five Stars|  Excellent quality.| 2015-08-31|\n",
            "|         US|   51001958|R2NBEUGPQQGXP1|B008RBJXFM|     278970944|Osprey Hydraulics...|        Outdoors|          4|            0|          0|   N|                Y|Only Flaw Is The Cap|3rd season using ...| 2015-08-31|\n",
            "|         US|   32866903|R17LLAOJ8ITK0S|B00FK8WUQY|     312877650|CamelBak eddy .75...|        Outdoors|          3|            1|          1|   N|                Y|Poor design leads...|poor construction...| 2015-08-31|\n",
            "|         US|   30907790|R39PEQBT5ISEF4|B00EZA3VW0|     305567912|Children Black Re...|        Outdoors|          1|            0|          0|   N|                Y|Very bad quality,...|Very bad quality,...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the customers_table DataFrame\n",
        "customers_df = amazon_data_df.groupby(\"customer_id\").agg({\"customer_id\":\"count\"}).withColumnRenamed(\"count(customer_id)\", \"customer_count\")\n",
        "customers_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "TG5hiJLz_4Xg",
        "outputId": "9f4f827d-5779-4d95-99f0-fde65b82bb6a"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+--------------+\n",
            "|customer_id|customer_count|\n",
            "+-----------+--------------+\n",
            "|   43679767|             1|\n",
            "|   32024654|             1|\n",
            "|   52913169|             1|\n",
            "|   24297214|             1|\n",
            "|   26096454|             1|\n",
            "|   38247118|             1|\n",
            "|   44009906|             1|\n",
            "|    1753876|             1|\n",
            "|   26195644|             1|\n",
            "|   23042837|             1|\n",
            "|   11487525|             2|\n",
            "|   28258386|             1|\n",
            "|   42560427|             2|\n",
            "|   24540309|             1|\n",
            "|    1967239|             1|\n",
            "|   14217455|             1|\n",
            "|   20289484|            26|\n",
            "|   47103434|             1|\n",
            "|   28638887|             1|\n",
            "|     740678|             1|\n",
            "+-----------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the products_table DataFrame and drop duplicates. \n",
        "products_df = amazon_data_df.select([\"product_id\",\"product_title\"]).drop_duplicates()\n",
        "products_df.show()"
      ],
      "metadata": {
        "id": "Tu_eMV9RHEmJ",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "8972dc2f-8cdb-4bf3-9051-1f49012c5ef7"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+--------------------+\n",
            "|product_id|       product_title|\n",
            "+----------+--------------------+\n",
            "|B00IFHFJXI|Ivation Portable ...|\n",
            "|B00WG0J0D0|JanSport Superbre...|\n",
            "|B00V15AUN0|Nickelodeon Paw P...|\n",
            "|B00FUWSTI8|Bago Lightweight ...|\n",
            "|B003FV94NA|Michelin Lithion ...|\n",
            "|B00WIK04HO|Ultra Bright Camp...|\n",
            "|B00J2HSCM0|High Sierra Tank ...|\n",
            "|B009I6NSR4|Black Veil Brides...|\n",
            "|B001GSHSLE|Stansport 191 App...|\n",
            "|B00L2IO9M4|Columbia Sportswe...|\n",
            "|B00KY7IM7W|Nalgene 32 Oz Wid...|\n",
            "|B00TV5JCTK|Rollerblade ABEC ...|\n",
            "|B00B9D071Y|BUFF UV Multifunc...|\n",
            "|B00F9IGIKO|Condor Tactical F...|\n",
            "|B004X55L9I|Hydro Flask Insul...|\n",
            "|B00LORROIY|Scuba Choice Divi...|\n",
            "|B00AATRU8G|Kelty Redwing 44 ...|\n",
            "|B00HMCYWEO|Dakine Explorer L...|\n",
            "|B004DK1CM8|Hot Headz 12V Hea...|\n",
            "|B00T4W6SSS|Fits Sock Light H...|\n",
            "+----------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the review_id_table DataFrame. \n",
        "# Convert the 'review_date' column to a date datatype with to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")\n",
        "review_id_df = amazon_data_df.select([\"review_id\", \"customer_id\", \"product_id\", \"product_parent\", to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")])\n",
        "review_id_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "uXQUPLLzBo3S",
        "outputId": "92d7777e-705d-47d8-c716-7353f3feb94a"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|     review_id|customer_id|product_id|product_parent|review_date|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|R35T75OLUGHL5C|   18446823|B000NV6H94|     110804376| 2015-08-31|\n",
            "|R2BV735O46BN33|   13724367|B000IN0W3Y|     624096774| 2015-08-31|\n",
            "|R2NBEUGPQQGXP1|   51001958|B008RBJXFM|     278970944| 2015-08-31|\n",
            "|R17LLAOJ8ITK0S|   32866903|B00FK8WUQY|     312877650| 2015-08-31|\n",
            "|R39PEQBT5ISEF4|   30907790|B00EZA3VW0|     305567912| 2015-08-31|\n",
            "|R3GNM3SU9VHJFT|   20232229|B006JA8WEG|     842306035| 2015-08-31|\n",
            "| R2Y81OP0EK467|   17698862|B002PWFSEO|     451480122| 2015-08-31|\n",
            "|R2LFGSI6HAYH5F|   38486114|B002DZGKHW|     124386306| 2015-08-31|\n",
            "|R297G6ED1IQO7W|   26319572|B00ABA08F6|     991442421| 2015-08-31|\n",
            "| RE27RFC6101N6|   27152337|B003Z8WIHC|     886483892| 2015-08-31|\n",
            "|R3BPDME6E94W8Z|   12516845|B007CP6UK0|     150224054| 2015-08-31|\n",
            "|R2P08O1RILUOX3|    3225242|B003V3U9JK|     343847969| 2015-08-31|\n",
            "|R37CVAB03PTDVI|     961839|B00Y846HN8|     858088629| 2015-08-31|\n",
            "| RAWNWOGXPCPMD|   47796452|B00IYQ84VY|     474493517| 2015-08-31|\n",
            "| R5DYGP6ASX77M|   32004835|B002MYCKLY|     920014456| 2015-08-31|\n",
            "|R1O0SAOOGF2KG7|   23972939|B00EZV69JG|     128489321| 2015-08-31|\n",
            "|R35NJUT0U3MU3V|   40889047|B00AWOT3T8|     571303876| 2015-08-31|\n",
            "|R242C08MF9D1AH|   11244387|B0000AXTID|     739769424| 2015-08-31|\n",
            "| R3RYG8TJTO4E2|   20121211|B00IFHFJXI|     984009972| 2015-08-31|\n",
            "|R3IKH1DNY0CP9F|   25657249|B00KFILTWU|     405521681| 2015-08-31|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the vine_table. DataFrame\n",
        "vine_df = amazon_data_df.select([\"review_id\", \"star_rating\", \"helpful_votes\", \"total_votes\", \"vine\", \"verified_purchase\"])\n",
        "vine_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "09Pl9E4ONVLl",
        "outputId": "3b45f8ae-fdd6-477e-def0-7ba3e894f7e1"
      },
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R35T75OLUGHL5C|          4|            0|          0|   N|                Y|\n",
            "|R2BV735O46BN33|          5|            0|          0|   N|                Y|\n",
            "|R2NBEUGPQQGXP1|          4|            0|          0|   N|                Y|\n",
            "|R17LLAOJ8ITK0S|          3|            1|          1|   N|                Y|\n",
            "|R39PEQBT5ISEF4|          1|            0|          0|   N|                Y|\n",
            "|R3GNM3SU9VHJFT|          4|            1|          1|   N|                Y|\n",
            "| R2Y81OP0EK467|          5|            0|          0|   N|                Y|\n",
            "|R2LFGSI6HAYH5F|          5|            1|          1|   N|                Y|\n",
            "|R297G6ED1IQO7W|          5|            1|          1|   N|                Y|\n",
            "| RE27RFC6101N6|          5|            0|          0|   N|                Y|\n",
            "|R3BPDME6E94W8Z|          5|            0|          0|   N|                Y|\n",
            "|R2P08O1RILUOX3|          3|            0|          0|   N|                Y|\n",
            "|R37CVAB03PTDVI|          5|            0|          1|   N|                Y|\n",
            "| RAWNWOGXPCPMD|          5|            0|          0|   N|                Y|\n",
            "| R5DYGP6ASX77M|          5|            0|          0|   N|                Y|\n",
            "|R1O0SAOOGF2KG7|          4|            0|          0|   N|                Y|\n",
            "|R35NJUT0U3MU3V|          5|            0|          0|   N|                Y|\n",
            "|R242C08MF9D1AH|          5|            0|          0|   N|                Y|\n",
            "| R3RYG8TJTO4E2|          5|            0|          0|   N|                Y|\n",
            "|R3IKH1DNY0CP9F|          2|            0|          0|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Connect to the AWS RDS instance and write each DataFrame to its table.**\n",
        "---\n",
        "\n"
      ],
      "metadata": {
        "id": "AZBidpcFILCJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Store environmental variable\n",
        "from getpass import getpass\n",
        "password = getpass('Enter database password')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "w0Qi10PyIeFX",
        "outputId": "e23280ba-7dc8-4aa9-db97-93981a6adb0d"
      },
      "execution_count": 19,
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Enter database password··········\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Configure settings for RDS\n",
        "mode = \"append\"\n",
        "jdbc_url=\"jdbc:postgresql://challenge16.cpyab83ullug.us-east-1.rds.amazonaws.com:5432/\"\n",
        "config = {\"user\":\"postgres\",\n",
        "          \"password\": password, \n",
        "          \"driver\":\"org.postgresql.Driver\"}"
      ],
      "metadata": {
        "id": "GU5UoX4dSia1"
      },
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Write review_id_df to table in RDS\n",
        "review_id_df.write.jdbc(url=jdbc_url, table='review_id_table', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "QpKkoOsnTlos"
      },
      "execution_count": 27,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Write products_df to table in RDS\n",
        "# 3 min\n",
        "products_df.write.jdbc(url=jdbc_url, table='products_table', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "Xuc_fzCoaiKF"
      },
      "execution_count": 23,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Write customers_df to table in RDS\n",
        "# 7 min 14 s\n",
        "customers_df.write.jdbc(url=jdbc_url, table='customers_table', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "9O-cIzsAajNV"
      },
      "execution_count": 25,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Write vine_df to table in RDS\n",
        "# 10 min 35 secs\n",
        "vine_df.write.jdbc(url=jdbc_url, table='vine_table', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "wdWbG0kHaoQS"
      },
      "execution_count": 26,
      "outputs": []
    }
  ]
}