Amazon_Reviews_ETL.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Amazon_Reviews_ETL_starter_code.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "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",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "dad41261-7867-4580-915a-b7247ffee672"
      },
      "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.2'\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": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\r0% [Working]\r            \rHit:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.152)] [Connecting to security.u\r0% [1 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (91.189.88.152)\r                                                                               \rIgn:2 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "\r0% [1 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (91.189.88.152)\r                                                                               \rIgn:3 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "\r0% [1 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (91.189.88.152)\r                                                                               \rHit:4 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release\n",
            "\r0% [1 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (91.189.88.152)\r                                                                               \rHit:5 http://security.ubuntu.com/ubuntu bionic-security InRelease\n",
            "Hit:6 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Hit:7 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "Hit:8 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Hit:9 http://archive.ubuntu.com/ubuntu bionic-updates InRelease\n",
            "Hit:11 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Hit:12 http://archive.ubuntu.com/ubuntu bionic-backports InRelease\n",
            "Hit:13 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Hit:15 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Reading package lists... Done\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_xKwTpATHqSe",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "21bbc26c-d15c-4d1e-cff8-dee98c771197"
      },
      "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": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2021-06-18 22:44:29--  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  4.77MB/s    in 0.2s    \n",
            "\n",
            "2021-06-18 22:44:29 (4.77 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": 4,
      "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": "14de8732-8ca1-47ba-d15c-4edc8139440d"
      },
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Software_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": 5,
      "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|   42605767|R3EFW2STIYIY0I|B00MUTIDKI|     248732228|McAfee 2015 Inter...|        Software|          1|            2|          2|   N|                Y|I was very disapp...|I was very disapp...| 2015-08-31|\n",
            "|         US|   51771800|R12NR0R5A9F7FT|B00EPACNUG|     531462352|Hallmark Card Stu...|        Software|          5|            0|          0|   N|                Y|          Five Stars|I had a little st...| 2015-08-31|\n",
            "|         US|   16053526|R1LSH74R9XAP59|B00164AZA4|     473982505| Search and Rescue 4|        Software|          2|            0|          1|   N|                Y|    Have windows 10?|Tried to download...| 2015-08-31|\n",
            "|         US|   15319481|R1QXUNTF76K7L6|B00E6LIEFM|     189774198|      Quickbooks Pro|        Software|          2|            0|          0|   N|                Y|Disc was corrupt,...|Disc was corrupt,...| 2015-08-31|\n",
            "|         US|    1441820|R2F7DR75PS8NKT|B00VWEBG06|     852470365|Windows 7 Profess...|        Software|          5|            0|          0|   N|                Y|          Five Stars|Just what I neede...| 2015-08-31|\n",
            "|         US|   37107850|R2C1DJSCC8UFS6|B00EP7AP7C|     279360628|Family Tree Maker...|        Software|          3|            0|          0|   N|                Y|         Three Stars|Didn't like havin...| 2015-08-31|\n",
            "|         US|     302120|R1AXGS1W4YFXMX|B00OW2PET4|     729971168|World of Farming:...|        Software|          1|            0|          2|   N|                Y|            One Star|it does not work ...| 2015-08-31|\n",
            "|         US|   20193077|R1XU1B93402SYJ|B00N4OLCRO|     776572654|Photoshop Element...|        Software|          1|            1|          1|   N|                Y|Can't load to my ...|I have tried for ...| 2015-08-31|\n",
            "|         US|   13106017|R2U432NB3OPVR0|B005CELN8W|     222071424|Phantom EFX WMS S...|        Software|          5|            0|          0|   N|                Y|          Five Stars|       love the game| 2015-08-31|\n",
            "|         US|   32587108|R3R6FIMIOQ5SP9|B005CELL1G|     168801430|PaperPort Profess...|        Software|          5|            0|          0|   N|                Y|Meets our small b...|Great software. A...| 2015-08-31|\n",
            "|         US|   32020017|R12TX6V09C9QNQ|B00H09BOXQ|     455502995|Windows 7 Profess...|        Software|          5|            0|          0|   N|                Y|          Five Stars|             Awesome| 2015-08-31|\n",
            "|         US|   13376158|R33UCII6YKUMKV|B00MCLGAAO|     652069521| QuickBooks Pro 2015|        Software|          3|            2|          2|   N|                Y|Nothing New in Th...|To start this rev...| 2015-08-31|\n",
            "|         US|   47098110| RZKDAB9TGO053|B00P6U8C5Y|     871602905|Block Financial H...|        Software|          1|            0|          0|   N|                Y|Missing key infor...|Some key provisio...| 2015-08-31|\n",
            "|         US|   24083274|R2EMN2EEDN73ZA|B00MA5TPN6|     299038085|Parallels Desktop...|        Software|          4|            0|          0|   N|                Y|Probably the best...|Probably the best...| 2015-08-31|\n",
            "|         US|   41137196| RU9LHN1NJDVOI|B000EQ868I|     534229510|Cosmi Print Perfe...|        Software|          5|            0|          0|   N|                Y|          Five Stars|I was pleased wit...| 2015-08-31|\n",
            "|         US|   18003829|R19910SO17BQUE|B00MYXTCMI|      42668324|TurboTax Business...|        Software|          5|            0|          0|   N|                Y|... what it is su...|This program does...| 2015-08-31|\n",
            "|         US|   14460749|R32N10BW8MH847|B008PIQXP0|     116724371|I.R.I.S. Readiris...|        Software|          4|            2|          4|   N|                Y|          Four Stars|Again it should h...| 2015-08-31|\n",
            "|         US|   12641855| R2DNM85DH7FGK|B00KAFOXXU|     471261945|Finale PrintMusic...|        Software|          5|            0|          2|   N|                Y|          Five Stars|               great| 2015-08-31|\n",
            "|         US|   13775565|R2G9RFFW051RWY|B00OKSOLE4|     694520847|Microsoft Office ...|        Software|          5|            0|          0|   N|                Y|          Five Stars|        Easy to use.| 2015-08-31|\n",
            "|         US|   31044774|R26CC1STPSUHBS|B01019T6O0|     510976915|Microsoft Windows...|        Software|          1|           27|         39|   N|                N|Do NOT take the \"...|Do NOT take the &...| 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",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "6378d32e-7947-4c44-fa1d-eb0eded305a0"
      },
      "source": [
        "from pyspark.sql.functions import to_date\n",
        "# Read in the Review dataset as a DataFrame\n",
        "df.show(5)"
      ],
      "execution_count": 9,
      "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|   42605767|R3EFW2STIYIY0I|B00MUTIDKI|     248732228|McAfee 2015 Inter...|        Software|          1|            2|          2|   N|                Y|I was very disapp...|I was very disapp...| 2015-08-31|\n",
            "|         US|   51771800|R12NR0R5A9F7FT|B00EPACNUG|     531462352|Hallmark Card Stu...|        Software|          5|            0|          0|   N|                Y|          Five Stars|I had a little st...| 2015-08-31|\n",
            "|         US|   16053526|R1LSH74R9XAP59|B00164AZA4|     473982505| Search and Rescue 4|        Software|          2|            0|          1|   N|                Y|    Have windows 10?|Tried to download...| 2015-08-31|\n",
            "|         US|   15319481|R1QXUNTF76K7L6|B00E6LIEFM|     189774198|      Quickbooks Pro|        Software|          2|            0|          0|   N|                Y|Disc was corrupt,...|Disc was corrupt,...| 2015-08-31|\n",
            "|         US|    1441820|R2F7DR75PS8NKT|B00VWEBG06|     852470365|Windows 7 Profess...|        Software|          5|            0|          0|   N|                Y|          Five Stars|Just what I neede...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B0TESUDRY-90",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "0f474336-faf1-4a5e-c686-86de117e6c88"
      },
      "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": 16,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------+--------------+\n",
            "|customer_id|customer_count|\n",
            "+-----------+--------------+\n",
            "|   15634680|             1|\n",
            "|   44696507|             1|\n",
            "|   19901367|             1|\n",
            "|   11337682|             1|\n",
            "|   52021773|             1|\n",
            "|   29916198|             1|\n",
            "|   42030944|             1|\n",
            "|   52765209|             1|\n",
            "|   13519509|             2|\n",
            "|   39320350|             5|\n",
            "|   23249582|             1|\n",
            "|   46351928|             3|\n",
            "|   44179620|             1|\n",
            "|     637252|             1|\n",
            "|   34038793|             1|\n",
            "|   51136490|             1|\n",
            "|   49354815|             1|\n",
            "|     149761|             2|\n",
            "|   43333798|             1|\n",
            "|    4657027|             1|\n",
            "+-----------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4FwXA6UvY-96",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9bd959d8-4b30-4eaf-e4bf-7a10e1096391"
      },
      "source": [
        "# Create the products_table DataFrame and drop duplicates. \n",
        "products_df = df.select(['product_id', 'product_title']).drop_duplicates()\n",
        "products_df.show()"
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+--------------------+\n",
            "|product_id|       product_title|\n",
            "+----------+--------------------+\n",
            "|B00TQ71BXI|Moonlight Mysteri...|\n",
            "|B00CHZG1FE|Image Line FL Stu...|\n",
            "|B002JB3BC2|Anti-Virus & Anti...|\n",
            "|B0113MZ1AC|Windows 8 64 Bit ...|\n",
            "|B00003069T|ClueFinders 5th G...|\n",
            "|B008EQUD4U|   Camtasia Studio 8|\n",
            "|B008IVNFDM|Learn Adobe InDes...|\n",
            "|B001Q24UAM|Play! The Ultimat...|\n",
            "|B005HMUUSI|Mariner StoryMill...|\n",
            "|B00005MG8G|My Deluxe MailLis...|\n",
            "|B003L51CZ8|Sony Vegas Movie ...|\n",
            "|B00JFYWY2Y|Learning SQL - Tr...|\n",
            "|B00DDUDTZ2|Anno 2070 Complet...|\n",
            "|B004625PV2|My Golf Game feat...|\n",
            "|B0002TXT7K|Epic Pinball: The...|\n",
            "|B001J8KF4I|Early Learning Fu...|\n",
            "|B000KUWQ98|Extreme Behavior ...|\n",
            "|B00O2QS9GU|Access 2013 Train...|\n",
            "|B00NNU0C3Y|MAGIX Music Maker...|\n",
            "|B00SLV70HQ|Can-Am Commander ...|\n",
            "+----------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MkqyCuNQY-9-",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "46e01dcf-c282-42cb-f4ee-6b818d84170b"
      },
      "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": 24,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|     review_id|customer_id|product_id|product_parent|review_date|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|R3EFW2STIYIY0I|   42605767|B00MUTIDKI|     248732228| 2015-08-31|\n",
            "|R12NR0R5A9F7FT|   51771800|B00EPACNUG|     531462352| 2015-08-31|\n",
            "|R1LSH74R9XAP59|   16053526|B00164AZA4|     473982505| 2015-08-31|\n",
            "|R1QXUNTF76K7L6|   15319481|B00E6LIEFM|     189774198| 2015-08-31|\n",
            "|R2F7DR75PS8NKT|    1441820|B00VWEBG06|     852470365| 2015-08-31|\n",
            "|R2C1DJSCC8UFS6|   37107850|B00EP7AP7C|     279360628| 2015-08-31|\n",
            "|R1AXGS1W4YFXMX|     302120|B00OW2PET4|     729971168| 2015-08-31|\n",
            "|R1XU1B93402SYJ|   20193077|B00N4OLCRO|     776572654| 2015-08-31|\n",
            "|R2U432NB3OPVR0|   13106017|B005CELN8W|     222071424| 2015-08-31|\n",
            "|R3R6FIMIOQ5SP9|   32587108|B005CELL1G|     168801430| 2015-08-31|\n",
            "|R12TX6V09C9QNQ|   32020017|B00H09BOXQ|     455502995| 2015-08-31|\n",
            "|R33UCII6YKUMKV|   13376158|B00MCLGAAO|     652069521| 2015-08-31|\n",
            "| RZKDAB9TGO053|   47098110|B00P6U8C5Y|     871602905| 2015-08-31|\n",
            "|R2EMN2EEDN73ZA|   24083274|B00MA5TPN6|     299038085| 2015-08-31|\n",
            "| RU9LHN1NJDVOI|   41137196|B000EQ868I|     534229510| 2015-08-31|\n",
            "|R19910SO17BQUE|   18003829|B00MYXTCMI|      42668324| 2015-08-31|\n",
            "|R32N10BW8MH847|   14460749|B008PIQXP0|     116724371| 2015-08-31|\n",
            "| R2DNM85DH7FGK|   12641855|B00KAFOXXU|     471261945| 2015-08-31|\n",
            "|R2G9RFFW051RWY|   13775565|B00OKSOLE4|     694520847| 2015-08-31|\n",
            "|R26CC1STPSUHBS|   31044774|B01019T6O0|     510976915| 2015-08-31|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lzMmkdKmY--D",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "baff3b68-a7a0-4235-8940-b5be7884b3e2"
      },
      "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": 25,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R3EFW2STIYIY0I|          1|            2|          2|   N|                Y|\n",
            "|R12NR0R5A9F7FT|          5|            0|          0|   N|                Y|\n",
            "|R1LSH74R9XAP59|          2|            0|          1|   N|                Y|\n",
            "|R1QXUNTF76K7L6|          2|            0|          0|   N|                Y|\n",
            "|R2F7DR75PS8NKT|          5|            0|          0|   N|                Y|\n",
            "|R2C1DJSCC8UFS6|          3|            0|          0|   N|                Y|\n",
            "|R1AXGS1W4YFXMX|          1|            0|          2|   N|                Y|\n",
            "|R1XU1B93402SYJ|          1|            1|          1|   N|                Y|\n",
            "|R2U432NB3OPVR0|          5|            0|          0|   N|                Y|\n",
            "|R3R6FIMIOQ5SP9|          5|            0|          0|   N|                Y|\n",
            "|R12TX6V09C9QNQ|          5|            0|          0|   N|                Y|\n",
            "|R33UCII6YKUMKV|          3|            2|          2|   N|                Y|\n",
            "| RZKDAB9TGO053|          1|            0|          0|   N|                Y|\n",
            "|R2EMN2EEDN73ZA|          4|            0|          0|   N|                Y|\n",
            "| RU9LHN1NJDVOI|          5|            0|          0|   N|                Y|\n",
            "|R19910SO17BQUE|          5|            0|          0|   N|                Y|\n",
            "|R32N10BW8MH847|          4|            2|          4|   N|                Y|\n",
            "| R2DNM85DH7FGK|          5|            0|          2|   N|                Y|\n",
            "|R2G9RFFW051RWY|          5|            0|          0|   N|                Y|\n",
            "|R26CC1STPSUHBS|          1|           27|         39|   N|                N|\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://vine-review-db.cekydcuymnnm.us-east-1.rds.amazonaws.com:5432/vine\"\n",
        "config = {\"user\":\"postgres\", \n",
        "          \"password\": \"password\", \n",
        "          \"driver\":\"org.postgresql.Driver\"}"
      ],
      "execution_count": 30,
      "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": 31,
      "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": 32,
      "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": 33,
      "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": 34,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Exuo6ebUsCqW"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}