Amazon_Reviews_ETL.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Amazon_Reviews_ETL.ipynb",
      "provenance": []
    },
    "kernelspec": {
      "display_name": "PythonData",
      "language": "python",
      "name": "pythondata"
    },
    "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.7"
    },
    "nteract": {
      "version": "0.12.3"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "V58rxea0HqSa",
        "outputId": "21f6d5a8-15c0-4d01-bcf6-04c90ea8ee8a",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "import os\n",
        "# Find the latest version of spark 2.0  from http://www-us.apache.org/dist/spark/ and enter as the spark version\n",
        "# For example:\n",
        "# spark_version = 'spark-3.0.0'\n",
        "spark_version = 'spark-3.0.1'\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-us.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": 1,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\r0% [Working]\r            \rGet:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "Ign:2 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "Ign:3 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Get:4 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release [697 B]\n",
            "Hit:5 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:6 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release.gpg [836 B]\n",
            "Hit:7 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "Get:8 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Hit:10 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Hit:11 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Get:12 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Ign:13 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages\n",
            "Get:13 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [405 kB]\n",
            "Get:14 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Get:15 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,353 kB]\n",
            "Get:16 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,115 kB]\n",
            "Get:17 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [1,748 kB]\n",
            "Get:18 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [2,165 kB]\n",
            "Fetched 8,043 kB in 5s (1,705 kB/s)\n",
            "Reading package lists... Done\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_xKwTpATHqSe",
        "outputId": "a0c6bf32-988d-4923-d5ea-8454bb51c3e7",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "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": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2020-10-29 20:16:20--  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  1.01MB/s    in 0.9s    \n",
            "\n",
            "2020-10-29 20:16:22 (1.01 MB/s) - ‘postgresql-42.2.16.jar’ saved [1002883/1002883]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MMqDAjVS0KN9"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"BigData-Challenge\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"
      ],
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cyBsySGuY-9V"
      },
      "source": [
        "### Load Amazon Data into Spark DataFrame"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CtCmBhQJY-9Z",
        "outputId": "ede56a09-f4d3-4235-8070-41ad73034b71",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Video_Games_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"amazon_reviews_us_Video_Games_v1_00.tsv.gz\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "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|   12039526| RTIS3L2M1F5SM|B001CXYMFS|     737716809|Thrustmaster T-Fl...|     Video Games|          5|            0|          0|   N|                Y|an amazing joysti...|Used this for Eli...| 2015-08-31|\n",
            "|         US|    9636577| R1ZV7R40OLHKD|B00M920ND6|     569686175|Tonsee 6 buttons ...|     Video Games|          5|            0|          0|   N|                Y|Definitely a sile...|Loved it,  I didn...| 2015-08-31|\n",
            "|         US|    2331478|R3BH071QLH8QMC|B0029CSOD2|      98937668|Hidden Mysteries:...|     Video Games|          1|            0|          1|   N|                Y|            One Star|poor quality work...| 2015-08-31|\n",
            "|         US|   52495923|R127K9NTSXA2YH|B00GOOSV98|      23143350|GelTabz Performan...|     Video Games|          3|            0|          0|   N|                Y|good, but could b...|nice, but tend to...| 2015-08-31|\n",
            "|         US|   14533949|R32ZWUXDJPW27Q|B00Y074JOM|     821342511|Zero Suit Samus a...|     Video Games|          4|            0|          0|   N|                Y|   Great but flawed.|Great amiibo, gre...| 2015-08-31|\n",
            "|         US|    2377552|R3AQQ4YUKJWBA6|B002UBI6W6|     328764615|Psyclone Recharge...|     Video Games|          1|            0|          0|   N|                Y|            One Star|The remote consta...| 2015-08-31|\n",
            "|         US|   17521011|R2F0POU5K6F73F|B008XHCLFO|      24234603|Protection for yo...|     Video Games|          5|            0|          0|   N|                Y|              A Must|I have a 2012-201...| 2015-08-31|\n",
            "|         US|   19676307|R3VNR804HYSMR6|B00BRA9R6A|     682267517|   Nerf 3DS XL Armor|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Perfect, kids lov...| 2015-08-31|\n",
            "|         US|     224068| R3GZTM72WA2QH|B009EPWJLA|     435241890|One Piece: Pirate...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|            Excelent| 2015-08-31|\n",
            "|         US|   48467989| RNQOY62705W1K|B0000AV7GB|     256572651|Playstation 2 Dan...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|Slippery but expe...| 2015-08-31|\n",
            "|         US|     106569|R1VTIA3JTYBY02|B00008KTNN|     384411423|Metal Arms: Glitc...|     Video Games|          5|            0|          0|   N|                N|          Five Stars|Love the game. Se...| 2015-08-31|\n",
            "|         US|   48269642|R29DOU8791QZL8|B000A3IA0Y|     472622859|72 Pin Connector ...|     Video Games|          1|            0|          0|   N|                Y| Game will get stuck|Does not fit prop...| 2015-08-31|\n",
            "|         US|   52738710|R15DUT1VIJ9RJZ|B0053BQN34|     577628462|uDraw Gametablet ...|     Video Games|          2|            0|          0|   N|                Y|We have tried it ...|This was way too ...| 2015-08-31|\n",
            "|         US|   10556786|R3IMF2MQ3OU9ZM|B002I0HIMI|     988218515|NBA 2K12(Covers M...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|Works great good ...| 2015-08-31|\n",
            "|         US|    2963837|R23H79DHOZTYAU|B0081EH12M|     770100932|New Trigger Grips...|     Video Games|          1|            1|          1|   N|                Y|Now i have to buy...|It did not fit th...| 2015-08-31|\n",
            "|         US|   23092109| RIV24EQAIXA4O|B005FMLZQQ|      24647669|Xbox 360 Media Re...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|perfect lightweig...| 2015-08-31|\n",
            "|         US|   23091728|R3UCNGYDVN24YB|B002BSA388|      33706205|Super Mario Galaxy 2|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|               great| 2015-08-31|\n",
            "|         US|   10712640| RUL4H4XTTN2DY|B00BUSLSAC|     829667834|Nintendo 3DS XL -...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Works beautifully...| 2015-08-31|\n",
            "|         US|   17455376|R20JF7Z4DHTNX5|B00KWF38AW|     110680188|Captain Toad:  Tr...|     Video Games|          5|            0|          0|   N|                Y|          Five Stars|Kids loved the ga...| 2015-08-31|\n",
            "|         US|   14754850|R2T1AJ5MFI2260|B00BRQJYA8|     616463426|Lego Batman 2: DC...|     Video Games|          4|            0|          0|   N|                Y|          Four Stars|           Goodngame| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2yUSe55VY-9t"
      },
      "source": [
        "### Create DataFrames to match tables"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "C8REmY1aY-9u"
      },
      "source": [
        "from pyspark.sql.functions import to_date\n",
        "# Read in the Review dataset as a DataFrame\n"
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B0TESUDRY-90",
        "outputId": "1e4f77e4-9b95-4c27-ec37-3dba00522e83",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "# Create the customers_table DataFrame\n",
        "customers_df = df.groupby(\"customer_id\").agg({'customer_id':'count'}).withColumnRenamed(\"count(customer_id)\", \"customer_count\")\n",
        "customers_df.show()"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------+--------------+\n",
            "|customer_id|customer_count|\n",
            "+-----------+--------------+\n",
            "|   48670265|             1|\n",
            "|   49103216|             2|\n",
            "|    1131200|             1|\n",
            "|   43076447|             2|\n",
            "|   46261368|             1|\n",
            "|    4883305|             5|\n",
            "|   41192649|             1|\n",
            "|   40985731|             7|\n",
            "|   10437900|             2|\n",
            "|   22245671|             1|\n",
            "|    2574873|             1|\n",
            "|    4696154|             1|\n",
            "|    5621202|             1|\n",
            "|    5871933|             2|\n",
            "|   44089812|             1|\n",
            "|    2845910|             1|\n",
            "|    5274369|             1|\n",
            "|   39069693|             2|\n",
            "|     137793|             1|\n",
            "|   31914942|             3|\n",
            "+-----------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4FwXA6UvY-96",
        "outputId": "1f539cac-3e02-4f1f-feaf-527ef96b7293",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "# Create the products_table DataFrame and drop duplicates. \n",
        "products_df = df.select(['product_id', 'product_title']).drop_duplicates()\n",
        "products_df.show(truncate=False)"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n",
            "|product_id|product_title                                                                                                                                                                                                           |\n",
            "+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n",
            "|B00CJ7IUI6|The Elder Scrolls V: Skyrim Legendary Edition                                                                                                                                                                           |\n",
            "|B00DHF39KS|Wolfenstein: The New Order                                                                                                                                                                                              |\n",
            "|B00MUTAVH6|Under Night In-Birth Exe:Late - PlayStation 3                                                                                                                                                                           |\n",
            "|B001AZSEUW|Peggle                                                                                                                                                                                                                  |\n",
            "|B00KVOVBGM|PlayStation 4 Console - Destiny Bundle [Discontinued]                                                                                                                                                                   |\n",
            "|B00O9VGH4Y|USPRO&reg; Headphone Audio Adapter for Xbox 360                                                                                                                                                                         |\n",
            "|B004OQNZY4|Phineas and Ferb: Across the 2nd Dimension                                                                                                                                                                              |\n",
            "|B00ZLN980O|Donop seablue 2.4g Wireless Controller for Ps2,game Pad Game Joysticks Gaming Controller Joypad Gamepad Console Controller for Sony Playstation 2 w/ Dual Shock Dual Vibration Feedback Motors Easy Installation for Ps2|\n",
            "|B002L8W5V6|Dotop Nintendo Gamecube Compatible Controller Pack 2 Black                                                                                                                                                              |\n",
            "|B007AJZ5PY|Nyko Game Case for Vita - PlayStation Vita                                                                                                                                                                              |\n",
            "|B000AOEU2K|Fire Emblem: Path of Radiance - Gamecube                                                                                                                                                                                |\n",
            "|B000H8BW7U|Tanarus (PC) (Computer Game)                                                                                                                                                                                            |\n",
            "|B013RADQOQ|Susenstone® 2400DPI LED Optical 6D USB Wired Gaming Game Mouse For PC Laptop Game                                                                                                                                       |\n",
            "|B00KQXKUJ2|FIFA 15 (Ultimate Edition) - PlayStation 4                                                                                                                                                                              |\n",
            "|B006W41X2C|Turtle Beach - Ear Force DP11 Gaming Headset - Dolby Surround Sound - PS3                                                                                                                                               |\n",
            "|B000KCX9M4|Grand Theft Auto:  The Trilogy                                                                                                                                                                                          |\n",
            "|B00YT90JWC|Red Wii Mini Console MotionPlus Bundle (Wii) (Bundle) (Certified Refurbished)                                                                                                                                           |\n",
            "|B0096KG6A8|Wii U Super Mario Starter Pack                                                                                                                                                                                          |\n",
            "|B00L6AVLB0|World of Tanks-X360 Xbox 360 English US NTSC DVD - Xbox 360                                                                                                                                                             |\n",
            "|B000IMYKQ0|Wii Nunchuk Controller                                                                                                                                                                                                  |\n",
            "+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MkqyCuNQY-9-",
        "outputId": "66371e74-4d81-4d12-a814-2dabe3b2294d",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "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([\"review_id\", \"customer_id\", \"product_id\", \"product_parent\", to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")])\n",
        "review_id_df.show()"
      ],
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|     review_id|customer_id|product_id|product_parent|review_date|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "| RTIS3L2M1F5SM|   12039526|B001CXYMFS|     737716809| 2015-08-31|\n",
            "| R1ZV7R40OLHKD|    9636577|B00M920ND6|     569686175| 2015-08-31|\n",
            "|R3BH071QLH8QMC|    2331478|B0029CSOD2|      98937668| 2015-08-31|\n",
            "|R127K9NTSXA2YH|   52495923|B00GOOSV98|      23143350| 2015-08-31|\n",
            "|R32ZWUXDJPW27Q|   14533949|B00Y074JOM|     821342511| 2015-08-31|\n",
            "|R3AQQ4YUKJWBA6|    2377552|B002UBI6W6|     328764615| 2015-08-31|\n",
            "|R2F0POU5K6F73F|   17521011|B008XHCLFO|      24234603| 2015-08-31|\n",
            "|R3VNR804HYSMR6|   19676307|B00BRA9R6A|     682267517| 2015-08-31|\n",
            "| R3GZTM72WA2QH|     224068|B009EPWJLA|     435241890| 2015-08-31|\n",
            "| RNQOY62705W1K|   48467989|B0000AV7GB|     256572651| 2015-08-31|\n",
            "|R1VTIA3JTYBY02|     106569|B00008KTNN|     384411423| 2015-08-31|\n",
            "|R29DOU8791QZL8|   48269642|B000A3IA0Y|     472622859| 2015-08-31|\n",
            "|R15DUT1VIJ9RJZ|   52738710|B0053BQN34|     577628462| 2015-08-31|\n",
            "|R3IMF2MQ3OU9ZM|   10556786|B002I0HIMI|     988218515| 2015-08-31|\n",
            "|R23H79DHOZTYAU|    2963837|B0081EH12M|     770100932| 2015-08-31|\n",
            "| RIV24EQAIXA4O|   23092109|B005FMLZQQ|      24647669| 2015-08-31|\n",
            "|R3UCNGYDVN24YB|   23091728|B002BSA388|      33706205| 2015-08-31|\n",
            "| RUL4H4XTTN2DY|   10712640|B00BUSLSAC|     829667834| 2015-08-31|\n",
            "|R20JF7Z4DHTNX5|   17455376|B00KWF38AW|     110680188| 2015-08-31|\n",
            "|R2T1AJ5MFI2260|   14754850|B00BRQJYA8|     616463426| 2015-08-31|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lzMmkdKmY--D",
        "outputId": "5cff6603-18d5-4c8b-c58d-43f8c337d806",
        "colab": {
          "base_uri": "https://localhost:8080/"
        }
      },
      "source": [
        "# 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()"
      ],
      "execution_count": 26,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "| RTIS3L2M1F5SM|          5|            0|          0|   N|                Y|\n",
            "| R1ZV7R40OLHKD|          5|            0|          0|   N|                Y|\n",
            "|R3BH071QLH8QMC|          1|            0|          1|   N|                Y|\n",
            "|R127K9NTSXA2YH|          3|            0|          0|   N|                Y|\n",
            "|R32ZWUXDJPW27Q|          4|            0|          0|   N|                Y|\n",
            "|R3AQQ4YUKJWBA6|          1|            0|          0|   N|                Y|\n",
            "|R2F0POU5K6F73F|          5|            0|          0|   N|                Y|\n",
            "|R3VNR804HYSMR6|          5|            0|          0|   N|                Y|\n",
            "| R3GZTM72WA2QH|          5|            0|          0|   N|                Y|\n",
            "| RNQOY62705W1K|          4|            0|          0|   N|                Y|\n",
            "|R1VTIA3JTYBY02|          5|            0|          0|   N|                N|\n",
            "|R29DOU8791QZL8|          1|            0|          0|   N|                Y|\n",
            "|R15DUT1VIJ9RJZ|          2|            0|          0|   N|                Y|\n",
            "|R3IMF2MQ3OU9ZM|          4|            0|          0|   N|                Y|\n",
            "|R23H79DHOZTYAU|          1|            1|          1|   N|                Y|\n",
            "| RIV24EQAIXA4O|          5|            0|          0|   N|                Y|\n",
            "|R3UCNGYDVN24YB|          5|            0|          0|   N|                Y|\n",
            "| RUL4H4XTTN2DY|          5|            0|          0|   N|                Y|\n",
            "|R20JF7Z4DHTNX5|          5|            0|          0|   N|                Y|\n",
            "|R2T1AJ5MFI2260|          4|            0|          0|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jITZhLkmY--J"
      },
      "source": [
        "### Connect to the AWS RDS instance and write each DataFrame to its table. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7jiUvs1aY--L"
      },
      "source": [
        "# Configure settings for RDS\n",
        "mode = \"append\"\n",
        "jdbc_url=\"jdbc:postgresql://amazonvinedb.czgvqrcdettr.us-east-2.rds.amazonaws.com:5432/AmazonVineAnalysis\"\n",
        "config = {\"user\":\"postgres\", \n",
        "          \"password\": \"Beatles123\", \n",
        "          \"driver\":\"org.postgresql.Driver\"}"
      ],
      "execution_count": 27,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "T2zgZ-aKY--Q"
      },
      "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)"
      ],
      "execution_count": 28,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1m3yzn-LY--U"
      },
      "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)"
      ],
      "execution_count": 29,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KbXri15fY--Z"
      },
      "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)"
      ],
      "execution_count": 30,
      "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": 31,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Exuo6ebUsCqW"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}