Amazon_Reviews_ETL.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/g626s/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": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "jnulvEALizab",
        "outputId": "3ac6d096-269b-4449-ea12-ba168630e06c"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Get:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "Ign:2 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:3 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Get:4 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "Get:5 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease [1,581 B]\n",
            "Hit:6 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:7 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Get:8 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Get:9 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [913 kB]\n",
            "Hit:11 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Get:12 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [83.3 kB]\n",
            "Hit:13 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Get:14 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,422 kB]\n",
            "Get:15 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease [21.3 kB]\n",
            "Get:16 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,546 kB]\n",
            "Get:17 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [2,155 kB]\n",
            "Get:18 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,323 kB]\n",
            "Get:19 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,990 kB]\n",
            "Get:20 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [1,104 kB]\n",
            "Get:21 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic/main amd64 Packages [48.3 kB]\n",
            "Fetched 14.8 MB in 7s (2,121 kB/s)\n",
            "Reading package lists... Done\n",
            "--2022-10-03 15:45:26--  https://jdbc.postgresql.org/download/postgresql-42.5.0.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: 1046274 (1022K) [application/java-archive]\n",
            "Saving to: ‘postgresql-42.5.0.jar’\n",
            "\n",
            "postgresql-42.5.0.j 100%[===================>]   1022K  1.23MB/s    in 0.8s    \n",
            "\n",
            "2022-10-03 15:45:28 (1.23 MB/s) - ‘postgresql-42.5.0.jar’ saved [1046274/1046274]\n",
            "\n"
          ]
        }
      ],
      "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.3.0'\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-hadoop3.tgz\n",
        "!tar xf $SPARK_VERSION-bin-hadoop3.tgz\n",
        "!pip install -q findspark\n",
        "\n",
        "# Install Postgres Driver\n",
        "!wget https://jdbc.postgresql.org/download/postgresql-42.5.0.jar\n",
        "\n",
        "# Set Environment Variables\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = f\"/content/{spark_version}-bin-hadoop3\"\n",
        "\n",
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "_xKwTpATHqSe",
        "outputId": "95b4dd66-c262-43f0-d83b-698d3a99f6cd"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "--2022-10-03 15:45: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  1.17MB/s    in 0.8s    \n",
            "\n",
            "2022-10-03 15:45:30 (1.17 MB/s) - ‘postgresql-42.2.16.jar’ saved [1002883/1002883]\n",
            "\n"
          ]
        }
      ],
      "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"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MMqDAjVS0KN9"
      },
      "outputs": [],
      "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()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cyBsySGuY-9V"
      },
      "source": [
        "### Load Amazon Data into Spark DataFrame"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CtCmBhQJY-9Z",
        "outputId": "9b5bee86-7d1d-453e-94ed-00c904cf3a8b"
      },
      "outputs": [
        {
          "name": "stdout",
          "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|   32158956|R1KKOXHNI8MSXU|B01KL6O72Y|      24485154|Easy Tool Stainle...|         Apparel|          4|            0|          0|   N|                Y|★ THESE REALLY DO...|These Really Do W...|2013-01-14 00:00:00|\n",
            "|         US|    2714559|R26SP2OPDK4HT7|B01ID3ZS5W|     363128556|V28 Women Cowl Ne...|         Apparel|          5|            1|          2|   N|                Y|Favorite for wint...|I love this dress...|2014-03-04 00:00:00|\n",
            "|         US|   12608825| RWQEDYAX373I1|B01I497BGY|     811958549|James Fiallo Men'...|         Apparel|          5|            0|          0|   N|                Y|Great Socks for t...|Nice socks, great...|2015-07-12 00:00:00|\n",
            "|         US|   25482800|R231YI7R4GPF6J|B01HDXFZK6|     692205728|Belfry Gangster 1...|         Apparel|          5|            0|          0|   N|                Y|          Slick hat!|I bought this for...|2015-06-03 00:00:00|\n",
            "|         US|    9310286|R3KO3W45DD0L1K|B01G6MBEBY|     431150422|JAEDEN Women's Be...|         Apparel|          5|            0|          0|   N|                Y|I would do it again!|Perfect dress and...|2015-06-12 00:00:00|\n",
            "|         US|   26631939|R1C4QH63NFL5NJ|B01FWRXN0Y|     366144407|Levi's Boys' 514 ...|         Apparel|          5|            0|          0|   N|                Y|          Five Stars|Excellent for my ...|2014-04-22 00:00:00|\n",
            "|         US|   48785098|R2GP65O1U9N7BP|B01EXNH1HE|     786052021|Minimalist Wallet...|         Apparel|          5|            0|          0|   N|                Y|            Love it!|Raw is the only w...|2015-07-28 00:00:00|\n",
            "|         US|   39548589|R3O29CT5MQQ3XQ|B01E7OL09O|     108920964|Harriton Men's Ba...|         Apparel|          4|            0|          0|   N|                Y|         Three Stars|        A bit large.|2015-07-10 00:00:00|\n",
            "|         US|   29355866|R1ZECD2AA8QFF6|B01DXHX81O|     317132458|Jockey Women's Un...|         Apparel|          5|            0|          0|   N|                Y|          Five Stars|          Great fit!|2015-08-09 00:00:00|\n",
            "|         US|   27477484|R2S79GCF6J89OA|B01DDULIJK|     110598191|Alexander Del Ros...|         Apparel|          3|            0|          0|   N|                Y|    Not my favorite.|Shirt a bit too l...|2014-05-24 00:00:00|\n",
            "|         US|   17685865|R1CBCUGNP37MPK|B01BOKOL4A|     121408017|Jockey Women's Un...|         Apparel|          5|            0|          0|   N|                Y|The Jockey Women'...|The Jockey Women'...|2015-06-10 00:00:00|\n",
            "|         US|   19787539|R3NU4WUR5ZNV1V|B01B3Q4Q0O|     134506082|Bali Passion For ...|         Apparel|          5|            0|          0|   N|                Y|          Five Stars|cup size is just ...|2013-09-06 00:00:00|\n",
            "|         US|   44196725|R32EPCJ3XF8LGE|B01ADDSL9U|     747539045|Lilyette Women's ...|         Apparel|          5|            0|          0|   N|                Y|          Five Stars|          Perfect...|2014-11-17 00:00:00|\n",
            "|         US|   45510794|R1XIBC6WQBW31M|B019P1X5XI|     696379847|Vanity Fair Women...|         Apparel|          5|            0|          0|   N|                Y|best ever4 for me...|best  ever4 for m...|2013-05-12 00:00:00|\n",
            "|         US|   42025740|R1P11MVDZ65LMH|B019MDXIXG|     387286513|Warner's Women's ...|         Apparel|          5|            0|          0|   N|                Y|          Five Stars|          Great fit.|2014-02-14 00:00:00|\n",
            "|         US|   27097189|R1OJA3DJL0VDDK|B019438FEG|     764190574|Columbia Women's ...|         Apparel|          5|            0|          0|   N|                Y|          Five Stars|I have this Colum...|2014-10-20 00:00:00|\n",
            "|         US|   13808904|R1THWA5YRJLOOF|B0178HGNIA|     816266887|Robes King RK Cla...|         Apparel|          4|            0|          0|   N|                Y|          Four Stars|My husband found ...|2015-07-05 00:00:00|\n",
            "|         US|   30225894| R738LCNRSJVXP|B016VIU0QI|     323924698|VIV Collection Be...|         Apparel|          5|            6|          7|   N|                Y|   Awesome leggings!|Awesome leggings!...|2015-07-29 00:00:00|\n",
            "|         US|   45223824|R1N3Z13931J3O9|B016PUU3VO|     893588059|Fruit of the Loom...|         Apparel|          2|            0|          0|   N|                Y|Sizes not correct...|I ordered the sam...|2015-01-01 00:00:00|\n",
            "|         US|   22300783|R1LBNTP7E8N89Z|B016AQNDM4|     917361125|Jockey Scrubs Wom...|         Apparel|          1|            0|          0|   N|                Y|            One Star| not exact in sizing|2015-02-12 00:00:00|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Apparel_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"amazon_reviews_us_Apparel_v1_00.tsv.gz\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2yUSe55VY-9t"
      },
      "source": [
        "### Create DataFrames to match tables"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "C8REmY1aY-9u",
        "outputId": "21aaca94-7a0a-483c-f73d-43425dcfc031"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "[('marketplace', 'string'),\n",
              " ('customer_id', 'int'),\n",
              " ('review_id', 'string'),\n",
              " ('product_id', 'string'),\n",
              " ('product_parent', 'int'),\n",
              " ('product_title', 'string'),\n",
              " ('product_category', 'string'),\n",
              " ('star_rating', 'int'),\n",
              " ('helpful_votes', 'int'),\n",
              " ('total_votes', 'int'),\n",
              " ('vine', 'string'),\n",
              " ('verified_purchase', 'string'),\n",
              " ('review_headline', 'string'),\n",
              " ('review_body', 'string'),\n",
              " ('review_date', 'timestamp')]"
            ]
          },
          "execution_count": 5,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "from pyspark.sql.functions import to_date\n",
        "# Read in the Review dataset as a DataFrame\n",
        "df.dtypes"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "B0TESUDRY-90",
        "outputId": "4ce43a35-bdb9-4df2-aa31-04fbffa68374"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-----------+--------------+\n",
            "|customer_id|customer_count|\n",
            "+-----------+--------------+\n",
            "|   13780696|             1|\n",
            "|   48489393|             8|\n",
            "|   43380682|             1|\n",
            "|   17133428|             1|\n",
            "|    9731896|             2|\n",
            "|    1797768|             1|\n",
            "|   27446622|             1|\n",
            "|     120706|             3|\n",
            "|   15915947|             2|\n",
            "|   37520920|             1|\n",
            "|   43920023|             9|\n",
            "|   14309190|             8|\n",
            "|   22316505|             1|\n",
            "|   43949054|             2|\n",
            "|   21102747|             1|\n",
            "|   19021463|             1|\n",
            "|   16075459|             8|\n",
            "|   51475782|             2|\n",
            "|   11555870|             5|\n",
            "|   35471383|             3|\n",
            "+-----------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "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()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4FwXA6UvY-96",
        "outputId": "c66bb076-5146-4c77-d392-563e10db2ef0"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+----------+--------------------+\n",
            "|product_id|       product_title|\n",
            "+----------+--------------------+\n",
            "|0000032034|Adult Ballet Tutu...|\n",
            "|0000047228|Patriotic Stars &...|\n",
            "|0615595154|Time You Can't Ge...|\n",
            "|1465014578|2013 Desk Pad Cal...|\n",
            "|1607056992|Eco Tote - Skinny...|\n",
            "|1608322114|Bambino Balls Sho...|\n",
            "|1608322122|Bambino Balls Sho...|\n",
            "|1608322130|Bambino Balls Sho...|\n",
            "|1608322157|Bambino Balls Sho...|\n",
            "|1608322173|Bambino Balls Sho...|\n",
            "|160832222X|Bambino Balls Sho...|\n",
            "|1608322254|Bambino Balls Uni...|\n",
            "|1608322262|Bambino Balls Uni...|\n",
            "|1608322610|Bambino Balls Uni...|\n",
            "|1608324710|Bambino Balls Uni...|\n",
            "|1608324745|Bambino Balls Lit...|\n",
            "|1608325156|Bambino Balls Bab...|\n",
            "|1608325229|Bambino Balls Uni...|\n",
            "|1616593989|Grimm Key Replica...|\n",
            "|1848572972|      Baby Kiss Book|\n",
            "+----------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Create the products_table DataFrame and drop duplicates. \n",
        "products_df = df.select([\"product_id\", \"product_title\"]).dropDuplicates([\"product_id\"])\n",
        "products_df.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MmT_T73DGGDR",
        "outputId": "2faf5dd3-8fba-4906-8ba2-f61ac9ff8e20"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "2305627"
            ]
          },
          "execution_count": 8,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "products_df.count()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MkqyCuNQY-9-",
        "outputId": "b48c93d7-c681-4404-fbca-9cf921f85363"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|     review_id|customer_id|product_id|product_parent|review_date|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|R1KKOXHNI8MSXU|   32158956|B01KL6O72Y|      24485154| 2013-01-14|\n",
            "|R26SP2OPDK4HT7|    2714559|B01ID3ZS5W|     363128556| 2014-03-04|\n",
            "| RWQEDYAX373I1|   12608825|B01I497BGY|     811958549| 2015-07-12|\n",
            "|R231YI7R4GPF6J|   25482800|B01HDXFZK6|     692205728| 2015-06-03|\n",
            "|R3KO3W45DD0L1K|    9310286|B01G6MBEBY|     431150422| 2015-06-12|\n",
            "|R1C4QH63NFL5NJ|   26631939|B01FWRXN0Y|     366144407| 2014-04-22|\n",
            "|R2GP65O1U9N7BP|   48785098|B01EXNH1HE|     786052021| 2015-07-28|\n",
            "|R3O29CT5MQQ3XQ|   39548589|B01E7OL09O|     108920964| 2015-07-10|\n",
            "|R1ZECD2AA8QFF6|   29355866|B01DXHX81O|     317132458| 2015-08-09|\n",
            "|R2S79GCF6J89OA|   27477484|B01DDULIJK|     110598191| 2014-05-24|\n",
            "|R1CBCUGNP37MPK|   17685865|B01BOKOL4A|     121408017| 2015-06-10|\n",
            "|R3NU4WUR5ZNV1V|   19787539|B01B3Q4Q0O|     134506082| 2013-09-06|\n",
            "|R32EPCJ3XF8LGE|   44196725|B01ADDSL9U|     747539045| 2014-11-17|\n",
            "|R1XIBC6WQBW31M|   45510794|B019P1X5XI|     696379847| 2013-05-12|\n",
            "|R1P11MVDZ65LMH|   42025740|B019MDXIXG|     387286513| 2014-02-14|\n",
            "|R1OJA3DJL0VDDK|   27097189|B019438FEG|     764190574| 2014-10-20|\n",
            "|R1THWA5YRJLOOF|   13808904|B0178HGNIA|     816266887| 2015-07-05|\n",
            "| R738LCNRSJVXP|   30225894|B016VIU0QI|     323924698| 2015-07-29|\n",
            "|R1N3Z13931J3O9|   45223824|B016PUU3VO|     893588059| 2015-01-01|\n",
            "|R1LBNTP7E8N89Z|   22300783|B016AQNDM4|     917361125| 2015-02-12|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "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()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lzMmkdKmY--D",
        "outputId": "2c279043-70f7-4f0f-d3cc-ff38256a73f0"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R1KKOXHNI8MSXU|          4|            0|          0|   N|                Y|\n",
            "|R26SP2OPDK4HT7|          5|            1|          2|   N|                Y|\n",
            "| RWQEDYAX373I1|          5|            0|          0|   N|                Y|\n",
            "|R231YI7R4GPF6J|          5|            0|          0|   N|                Y|\n",
            "|R3KO3W45DD0L1K|          5|            0|          0|   N|                Y|\n",
            "|R1C4QH63NFL5NJ|          5|            0|          0|   N|                Y|\n",
            "|R2GP65O1U9N7BP|          5|            0|          0|   N|                Y|\n",
            "|R3O29CT5MQQ3XQ|          4|            0|          0|   N|                Y|\n",
            "|R1ZECD2AA8QFF6|          5|            0|          0|   N|                Y|\n",
            "|R2S79GCF6J89OA|          3|            0|          0|   N|                Y|\n",
            "|R1CBCUGNP37MPK|          5|            0|          0|   N|                Y|\n",
            "|R3NU4WUR5ZNV1V|          5|            0|          0|   N|                Y|\n",
            "|R32EPCJ3XF8LGE|          5|            0|          0|   N|                Y|\n",
            "|R1XIBC6WQBW31M|          5|            0|          0|   N|                Y|\n",
            "|R1P11MVDZ65LMH|          5|            0|          0|   N|                Y|\n",
            "|R1OJA3DJL0VDDK|          5|            0|          0|   N|                Y|\n",
            "|R1THWA5YRJLOOF|          4|            0|          0|   N|                Y|\n",
            "| R738LCNRSJVXP|          5|            6|          7|   N|                Y|\n",
            "|R1N3Z13931J3O9|          2|            0|          0|   N|                Y|\n",
            "|R1LBNTP7E8N89Z|          1|            0|          0|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "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()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "jITZhLkmY--J"
      },
      "source": [
        "### Connect to the AWS RDS instance and write each DataFrame to its table. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7jiUvs1aY--L"
      },
      "outputs": [],
      "source": [
        "# Configure settings for RDS\n",
        "mode = \"append\"\n",
        "jdbc_url=\"jdbc:postgresql://dataviz.ci9riw2xiyhd.us-west-2.rds.amazonaws.com:5432/dataviz\"\n",
        "config = {\"user\":\"postgres\", \n",
        "          \"password\": \"<redactedawspassword>\", \n",
        "          \"driver\":\"org.postgresql.Driver\"}"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "T2zgZ-aKY--Q"
      },
      "outputs": [],
      "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)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "1m3yzn-LY--U"
      },
      "outputs": [],
      "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)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "metadata": {
        "id": "KbXri15fY--Z"
      },
      "outputs": [],
      "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)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "metadata": {
        "id": "XdQknSHLY--e"
      },
      "outputs": [],
      "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)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [],
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    },
    "nteract": {
      "version": "0.12.3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}