Amazon_Reviews_ETL.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "nteract": {
      "version": "0.12.3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "V58rxea0HqSa",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e70c1338-d862-432d-b81c-e7111ffc9313"
      },
      "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.0.3'\n",
        "spark_version = 'spark-3.0.3'\n",
        "os.environ['SPARK_VERSION']=spark_version\n",
        "\n",
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-11-jdk-headless -qq > /dev/null\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",
        "!pip install -q findspark\n",
        "\n",
        "# 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\"\n",
        "\n",
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ],
      "execution_count": 47,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rGet:1 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "\r0% [Waiting for headers] [1 InRelease 14.2 kB/88.7 kB 16%] [Connected to cloud.\r                                                                               \rHit:2 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "\r0% [Waiting for headers] [1 InRelease 40.2 kB/88.7 kB 45%] [Connected to cloud.\r                                                                               \rGet:3 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "\r                                                                               \rHit:4 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease\n",
            "Get:5 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Ign:6 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:7 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:8 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "Hit:9 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Hit:10 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Hit:11 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Hit:12 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Fetched 252 kB in 1s (180 kB/s)\n",
            "Reading package lists... Done\n",
            "tar: spark-3.0.3-bin-hadoop2.7.tgz: Cannot open: No such file or directory\n",
            "tar: Error is not recoverable: exiting now\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_xKwTpATHqSe",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c2e1f31a-c057-4f3e-e95a-6c16f0503971"
      },
      "source": [
        "# Download the Postgres driver that will allow Spark to interact with Postgres.\n",
        "!wget https://jdbc.postgresql.org/download/postgresql-42.2.16.jar"
      ],
      "execution_count": 27,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2022-09-19 00:10:22--  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’\n",
            "\n",
            "postgresql-42.2.16. 100%[===================>] 979.38K  5.45MB/s    in 0.2s    \n",
            "\n",
            "2022-09-19 00:10:22 (5.45 MB/s) - ‘postgresql-42.2.16.jar’ saved [1002883/1002883]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MMqDAjVS0KN9"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"M16-Amazon-Challenge\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"
      ],
      "execution_count": 48,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cyBsySGuY-9V"
      },
      "source": [
        "### Load Amazon Data into Spark DataFrame"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CtCmBhQJY-9Z",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d24d5816-8568-45dc-fb8e-e6375e160a61"
      },
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Wireless_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "execution_count": 49,
      "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|   16414143|R3W4P9UBGNGH1U|B00YL0EKWE|     852431543|LG G4 Case Hard T...|        Wireless|          2|            1|          3|   N|                Y|Looks good, funct...|2 issues  -  Once...| 2015-08-31|\n",
            "|         US|   50800750|R15V54KBMTQWAY|B00XK95RPQ|     516894650|Selfie Stick Fibl...|        Wireless|          4|            0|          0|   N|                N| A fun little gadget|I’m embarrassed t...| 2015-08-31|\n",
            "|         US|   15184378| RY8I449HNXSVF|B00SXRXUKO|     984297154|Tribe AB40 Water ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|  Fits iPhone 6 well| 2015-08-31|\n",
            "|         US|   10203548|R18TLJYCKJFLSR|B009V5X1CE|     279912704|RAVPower® Element...|        Wireless|          5|            0|          0|   N|                Y|       Great charger|Great charger.  I...| 2015-08-31|\n",
            "|         US|     488280|R1NK26SWS53B8Q|B00D93OVF0|     662791300|Fosmon Micro USB ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|Great for the pri...| 2015-08-31|\n",
            "|         US|   13334021|R11LOHEDYJALTN|B00XVGJMDQ|     421688488|iPhone 6 Case, Vo...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|Great Case, bette...| 2015-08-31|\n",
            "|         US|   27520697|R3ALQVQB2P9LA7|B00KQW1X1C|     554285554|Nokia Lumia 630 R...|        Wireless|          4|            0|          0|   N|                Y|          Four Stars|Easy to set up an...| 2015-08-31|\n",
            "|         US|   48086021|R3MWLXLNO21PDQ|B00IP1MQNK|     488006702|Lumsing 10400mah ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|         Works great| 2015-08-31|\n",
            "|         US|   12738196|R2L15IS24CX0LI|B00HVORET8|     389677711|iPhone 5S Battery...|        Wireless|          5|            0|          0|   N|                Y|      So far so good|So far so good. I...| 2015-08-31|\n",
            "|         US|   15867807|R1DJ8976WPWVZU|B00HX3G6J6|     299654876|HTC One M8 Screen...|        Wireless|          3|            0|          0|   N|                Y|seems durable but...|seems durable but...| 2015-08-31|\n",
            "|         US|    1972249|R3MRWNNR8CBTB7|B00U4NATNQ|     577878727|S6 Case - Bear Mo...|        Wireless|          5|            0|          0|   N|                Y|Super thin, light...|Super thin, light...| 2015-08-31|\n",
            "|         US|   10956619|R1DS6DKTUXAQK3|B00SZEFDH8|     654620704|BLU Studio X, Unl...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|As good as the Sa...| 2015-08-31|\n",
            "|         US|   14805911| RWJM5E0TWUJD2|B00JRJUL9U|     391166958|EZOPower 5-Port U...|        Wireless|          5|            0|          0|   N|                Y|            EZOPower|I received this o...| 2015-08-31|\n",
            "|         US|   15611116|R1XTJKDYNCRGAC|B00KQ4T0HE|     481551630|iPhone 6S Case &i...|        Wireless|          1|            0|          0|   N|                Y|Very cheap case. ...|Very cheap case. ...| 2015-08-31|\n",
            "|         US|   39298603|R2UZL3DPWEU1XW|B00M0YWKPM|     685107474| iPhone 6s Plus Case|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|       son loves it.| 2015-08-31|\n",
            "|         US|   17552454|R2EZXET9KBFFU3|B00KDZEE68|     148320945|zBoost ZB575-A TR...|        Wireless|          1|            0|          0|   N|                Y|            One Star|you have to turn ...| 2015-08-31|\n",
            "|         US|   12218556|R26VY1L1FD3LPU|B00BJN45GM|      47788188|OtterBox Defender...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|  Awesome, thank you| 2015-08-31|\n",
            "|         US|   21872923|R2SSA4NSFCV18T|B00SA86SXW|     748759272|Aduro PowerUP 30W...|        Wireless|          5|            0|          0|   N|                N|          Five Stars|         nice, smart| 2015-08-31|\n",
            "|         US|   16264332|R1G6333JHJNEUQ|B00Q3I68TU|     974085141|LilGadgets Connec...|        Wireless|          5|            0|          0|   N|                Y|Great headphones ...|We love these hea...| 2015-08-31|\n",
            "|         US|    6042304|R2DRG0UZXJQ0PE|B00TN4J1TA|     716174627|Anker Aluminum Mu...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|This is well wort...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2yUSe55VY-9t"
      },
      "source": [
        "### Create DataFrames to match tables"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "C8REmY1aY-9u",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e02e9a47-6187-4c51-9ac1-f14119279255"
      },
      "source": [
        "from pyspark.sql.functions import to_date\n",
        "# Read in the Review dataset as a DataFrame\n",
        "df.show()"
      ],
      "execution_count": 50,
      "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|   16414143|R3W4P9UBGNGH1U|B00YL0EKWE|     852431543|LG G4 Case Hard T...|        Wireless|          2|            1|          3|   N|                Y|Looks good, funct...|2 issues  -  Once...| 2015-08-31|\n",
            "|         US|   50800750|R15V54KBMTQWAY|B00XK95RPQ|     516894650|Selfie Stick Fibl...|        Wireless|          4|            0|          0|   N|                N| A fun little gadget|I’m embarrassed t...| 2015-08-31|\n",
            "|         US|   15184378| RY8I449HNXSVF|B00SXRXUKO|     984297154|Tribe AB40 Water ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|  Fits iPhone 6 well| 2015-08-31|\n",
            "|         US|   10203548|R18TLJYCKJFLSR|B009V5X1CE|     279912704|RAVPower® Element...|        Wireless|          5|            0|          0|   N|                Y|       Great charger|Great charger.  I...| 2015-08-31|\n",
            "|         US|     488280|R1NK26SWS53B8Q|B00D93OVF0|     662791300|Fosmon Micro USB ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|Great for the pri...| 2015-08-31|\n",
            "|         US|   13334021|R11LOHEDYJALTN|B00XVGJMDQ|     421688488|iPhone 6 Case, Vo...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|Great Case, bette...| 2015-08-31|\n",
            "|         US|   27520697|R3ALQVQB2P9LA7|B00KQW1X1C|     554285554|Nokia Lumia 630 R...|        Wireless|          4|            0|          0|   N|                Y|          Four Stars|Easy to set up an...| 2015-08-31|\n",
            "|         US|   48086021|R3MWLXLNO21PDQ|B00IP1MQNK|     488006702|Lumsing 10400mah ...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|         Works great| 2015-08-31|\n",
            "|         US|   12738196|R2L15IS24CX0LI|B00HVORET8|     389677711|iPhone 5S Battery...|        Wireless|          5|            0|          0|   N|                Y|      So far so good|So far so good. I...| 2015-08-31|\n",
            "|         US|   15867807|R1DJ8976WPWVZU|B00HX3G6J6|     299654876|HTC One M8 Screen...|        Wireless|          3|            0|          0|   N|                Y|seems durable but...|seems durable but...| 2015-08-31|\n",
            "|         US|    1972249|R3MRWNNR8CBTB7|B00U4NATNQ|     577878727|S6 Case - Bear Mo...|        Wireless|          5|            0|          0|   N|                Y|Super thin, light...|Super thin, light...| 2015-08-31|\n",
            "|         US|   10956619|R1DS6DKTUXAQK3|B00SZEFDH8|     654620704|BLU Studio X, Unl...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|As good as the Sa...| 2015-08-31|\n",
            "|         US|   14805911| RWJM5E0TWUJD2|B00JRJUL9U|     391166958|EZOPower 5-Port U...|        Wireless|          5|            0|          0|   N|                Y|            EZOPower|I received this o...| 2015-08-31|\n",
            "|         US|   15611116|R1XTJKDYNCRGAC|B00KQ4T0HE|     481551630|iPhone 6S Case &i...|        Wireless|          1|            0|          0|   N|                Y|Very cheap case. ...|Very cheap case. ...| 2015-08-31|\n",
            "|         US|   39298603|R2UZL3DPWEU1XW|B00M0YWKPM|     685107474| iPhone 6s Plus Case|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|       son loves it.| 2015-08-31|\n",
            "|         US|   17552454|R2EZXET9KBFFU3|B00KDZEE68|     148320945|zBoost ZB575-A TR...|        Wireless|          1|            0|          0|   N|                Y|            One Star|you have to turn ...| 2015-08-31|\n",
            "|         US|   12218556|R26VY1L1FD3LPU|B00BJN45GM|      47788188|OtterBox Defender...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|  Awesome, thank you| 2015-08-31|\n",
            "|         US|   21872923|R2SSA4NSFCV18T|B00SA86SXW|     748759272|Aduro PowerUP 30W...|        Wireless|          5|            0|          0|   N|                N|          Five Stars|         nice, smart| 2015-08-31|\n",
            "|         US|   16264332|R1G6333JHJNEUQ|B00Q3I68TU|     974085141|LilGadgets Connec...|        Wireless|          5|            0|          0|   N|                Y|Great headphones ...|We love these hea...| 2015-08-31|\n",
            "|         US|    6042304|R2DRG0UZXJQ0PE|B00TN4J1TA|     716174627|Anker Aluminum Mu...|        Wireless|          5|            0|          0|   N|                Y|          Five Stars|This is well wort...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B0TESUDRY-90",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "2229dae3-ca78-4bff-8403-fde33a9ac0c2"
      },
      "source": [
        "# Create the customers_table DataFrame\n",
        "# customers_df = df.groupby(\"\").agg({\"\"}).withColumnRenamed(\"\", \"customer_count\")\n",
        "customers_df = df.groupby(\"customer_id\").agg({\"customer_id\":\"count\"}).withColumnRenamed(\"count(customer_id)\", \"customer_count\")\n",
        "customers_df.show(5)        "
      ],
      "execution_count": 51,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+--------------+\n",
            "|customer_id|customer_count|\n",
            "+-----------+--------------+\n",
            "|   46909180|             6|\n",
            "|   42560427|             7|\n",
            "|   43789873|             3|\n",
            "|   22037526|             2|\n",
            "|   34220092|             2|\n",
            "+-----------+--------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4FwXA6UvY-96",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c732e830-0906-4f59-b2cd-a4c8b1a85cb1"
      },
      "source": [
        "# Create the products_table DataFrame and drop duplicates. \n",
        "# products_df = df.select([]).drop_duplicates()\n",
        "products_df = df.select([\"product_id\", \"product_title\"]).drop_duplicates()\n",
        "products_df.show(5)"
      ],
      "execution_count": 52,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+--------------------+\n",
            "|product_id|       product_title|\n",
            "+----------+--------------------+\n",
            "|B010LVPJH6|LG G Watch Urbane...|\n",
            "|B00K5ZNXZ4|Minisuit Sporty A...|\n",
            "|B00S9RBQOK|iPhone 6 Plus Cas...|\n",
            "|B0116N7GYC|Selfie Stick, Por...|\n",
            "|B009UNH0CY|Plantronics Voyag...|\n",
            "+----------+--------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MkqyCuNQY-9-",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "a76df7ce-926a-4f62-e839-7acbb7724f57"
      },
      "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 = df.select([, to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")])\n",
        "review_id_df = 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(5)"
      ],
      "execution_count": 54,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|     review_id|customer_id|product_id|product_parent|review_date|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|R3W4P9UBGNGH1U|   16414143|B00YL0EKWE|     852431543| 2015-08-31|\n",
            "|R15V54KBMTQWAY|   50800750|B00XK95RPQ|     516894650| 2015-08-31|\n",
            "| RY8I449HNXSVF|   15184378|B00SXRXUKO|     984297154| 2015-08-31|\n",
            "|R18TLJYCKJFLSR|   10203548|B009V5X1CE|     279912704| 2015-08-31|\n",
            "|R1NK26SWS53B8Q|     488280|B00D93OVF0|     662791300| 2015-08-31|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lzMmkdKmY--D",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "96351f7f-e798-4cda-8bea-45258a1be9f5"
      },
      "source": [
        "# Create the vine_table. DataFrame\n",
        "# vine_df = df.select([])\n",
        "# Create the vine_table. DataFrame\n",
        "vine_df = df.select([\"review_id\", \"star_rating\", \"helpful_votes\", \"total_votes\", \"vine\", \"verified_purchase\"])\n",
        "vine_df.show(5)"
      ],
      "execution_count": 55,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3W4P9UBGNGH1U|          2|            1|          3|   N|                Y|\n",
            "|R15V54KBMTQWAY|          4|            0|          0|   N|                N|\n",
            "| RY8I449HNXSVF|          5|            0|          0|   N|                Y|\n",
            "|R18TLJYCKJFLSR|          5|            0|          0|   N|                Y|\n",
            "|R1NK26SWS53B8Q|          5|            0|          0|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jITZhLkmY--J"
      },
      "source": [
        "### Connect to the AWS RDS instance and write each DataFrame to its table. "
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from getpass import getpass\n",
        "\n",
        "password = getpass(\"Hazel23_07\")"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "H1DYTyq6EqGa",
        "outputId": "2687d1e4-869e-41b9-8fb0-65a6331c2b57"
      },
      "execution_count": 56,
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Hazel23_07··········\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7jiUvs1aY--L"
      },
      "source": [
        "# Configure settings for RDS\n",
        "mode = \"append\"\n",
        "jdbc_url=\"jdbc:postgresql://amazon-review.cwoxauzap7jq.us-east-2.rds.amazonaws.com:5432/amazon-review\"\n",
        "config = {\"user\":\"postgres\", \n",
        "          \"password\": \"password\", \n",
        "          \"driver\":\"org.postgresql.Driver\"}\n",
        "\n"
      ],
      "execution_count": 57,
      "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": "3PQLnkOuM6sI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Write products_df to table in RDS\n",
        "# about 3 min\n",
        "products_df.write.jdbc(url=jdbc_url, table='products_table', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "T-9EDWkqM86-"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Write customers_df to table in RDS\n",
        "# 5 min 14 s\n",
        "customers_df.write.jdbc(url=jdbc_url, table='customers_table', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "Pk-rcL2bM_FO"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XdQknSHLY--e"
      },
      "source": [
        "# Write vine_df to table in RDS\n",
        "# 11 minutes\n",
        "vine_df.write.jdbc(url=jdbc_url, table='vine_table', mode=mode, properties=config)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Exuo6ebUsCqW"
      },
      "source": [],
      "execution_count": null,
      "outputs": []
    }
  ]
}