Amazon_Reviews_ETL.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": []
    },
    "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": "3d126585-ecd7-4b1a-c432-a56efdf850c7"
      },
      "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-3.3.0/spark-3.3.0-bin-hadoop3.tgz\n",
        "!tar xf spark-3.3.0-bin-hadoop3.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-hadoop3\"\n",
        "\n",
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Hit:1 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease\n",
            "Get:2 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "Hit:3 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "Hit:4 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Get:5 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Ign:6 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:7 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Hit:8 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:9 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Hit:10 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Get:11 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [83.3 kB]\n",
            "Hit:13 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Fetched 261 kB in 3s (99.8 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_xKwTpATHqSe",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c091c824-edec-43bc-989a-c62d60d20893"
      },
      "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": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2022-10-02 21:04:46--  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.1’\n",
            "\n",
            "postgresql-42.2.16. 100%[===================>] 979.38K  1.18MB/s    in 0.8s    \n",
            "\n",
            "2022-10-02 21:04:48 (1.18 MB/s) - ‘postgresql-42.2.16.jar.1’ 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": 19,
      "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": "fb06c003-2750-483d-ad3d-2ebb1eaaaa8b"
      },
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://amazon-officereviews.s3.amazonaws.com/amazon_reviews_us_Office_Products_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"amazon_reviews_us_Office_Products_v1_00.tsv.gz\"), sep=\"\\t\", header=True, inferSchema=True)\n",
        "df.show()"
      ],
      "execution_count": 20,
      "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|   43081963|R18RVCKGH1SSI9|B001BM2MAC|     307809868|Scotch Cushion Wr...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|      Great product.|2015-08-31 00:00:00|\n",
            "|         US|   10951564|R3L4L6LW1PUOFY|B00DZYEXPQ|      75004341|Dust-Off Compress...| Office Products|          5|            0|          1|   N|                Y|Phffffffft, Phfff...|What's to say abo...|2015-08-31 00:00:00|\n",
            "|         US|   21143145|R2J8AWXWTDX2TF|B00RTMUHDW|     529689027|Amram Tagger Stan...| Office Products|          5|            0|          0|   N|                Y|but I am sure I w...|Haven't used yet,...|2015-08-31 00:00:00|\n",
            "|         US|   52782374|R1PR37BR7G3M6A|B00D7H8XB6|     868449945|AmazonBasics 12-S...| Office Products|          1|            2|          3|   N|                Y|and the shredder ...|Although this was...|2015-08-31 00:00:00|\n",
            "|         US|   24045652|R3BDDDZMZBZDPU|B001XCWP34|      33521401|Derwent Colored P...| Office Products|          4|            0|          0|   N|                Y|          Four Stars|Gorgeous colors a...|2015-08-31 00:00:00|\n",
            "|         US|   21751234| R8T6MO75ND212|B004J2NBCO|     214932869|Quartet Magnetic ...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|Perfect for plann...|2015-08-31 00:00:00|\n",
            "|         US|    9109358|R2YWMQT2V11XYZ|B00MOPAG8K|     863351797|KITLEX40X2592UNV2...| Office Products|          5|            0|          0|   N|                N|          Five Stars|Gold plated fuser...|2015-08-31 00:00:00|\n",
            "|         US|    9967215|R1V2HYL6OI9V39|B003AHIK7U|     383470576|Bible Dry Highlig...| Office Products|          5|            6|          6|   N|                Y|Won't bleed throu...|I have used these...|2015-08-31 00:00:00|\n",
            "|         US|   11234247|R3BLQBKUNXGFS4|B006TKH2RO|     999128878|Parker Ingenuity ...| Office Products|          5|            0|          0|   N|                Y|           Nice pen!|Heavy pen that wr...|2015-08-31 00:00:00|\n",
            "|         US|   12731488|R17MOWJCAR9Y8Q|B00W61M9K0|     622066861| RFID Card Protector| Office Products|          5|            0|          0|   N|                Y| Credit card sleeves|Not sure if they ...|2015-08-31 00:00:00|\n",
            "|         US|   49861762|R11EPG1GHOXMGB|B00BXOGI3A|     688569009|The Original Stan...| Office Products|          4|            0|          0|   N|                N|          Four Stars|Great product for...|2015-08-31 00:00:00|\n",
            "|         US|    1541556|R2797NKTEX5THN|B0018RHWGE|     513803406|Zebra Sparky-1 WK...| Office Products|          5|            0|          0|   N|                Y|Love these highli...|Love these highli...|2015-08-31 00:00:00|\n",
            "|         US|   10579374|R2RUTMJS50E4WF|B00IA8H2OG|     201212366|E-Z Ink (TM) Comp...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|Received on time,...|2015-08-31 00:00:00|\n",
            "|         US|   43622307|R1FZYL1HX7O4RM|B007TOIA66|     954138793|Eric 11\" Giant Si...| Office Products|          1|            0|          0|   N|                Y|            One Star|worked about a mo...|2015-08-31 00:00:00|\n",
            "|         US|   50773856|R22ENT0KU5CHPX|B00EANURCE|      43216076|Brother MFCJ450 W...| Office Products|          5|            0|          0|   N|                Y|Brothers Wireless...|I purchased this ...|2015-08-31 00:00:00|\n",
            "|         US|   10165223|R2E5M7FWJC9JLE|B00T3Q0EOO|     637096152|Clarity D613 DECT...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|Everything works ...|2015-08-31 00:00:00|\n",
            "|         US|   14744748|R31BNW3O2Q5J3E|B00WN0N818|     710040412|Permo 50 Feet Whi...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|          Thank you!|2015-08-31 00:00:00|\n",
            "|         US|   37536631|R1JMQF1OOMNF49|B001GAOTSW|     696023182|Pilot G2 Retracta...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|     Love these pens|2015-08-31 00:00:00|\n",
            "|         US|     126687| RJH6WQDOA6CZP|B003BNY7PW|     474277918|AT&T DECT 6.0 Acc...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|The product is ev...|2015-08-31 00:00:00|\n",
            "|         US|   31130095|R2EVL0Z5JVV86X|B00092RJX0|     887474295|Pilot G2 Retracta...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|           Great buy|2015-08-31 00:00:00|\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": "12a4b8fc-d968-462a-df57-d1b893cbd2cd"
      },
      "source": [
        "from pyspark.sql.functions import to_date\n",
        "# Read in the Review dataset as a DataFrame\n",
        "df.printSchema()"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "root\n",
            " |-- marketplace: string (nullable = true)\n",
            " |-- customer_id: integer (nullable = true)\n",
            " |-- review_id: string (nullable = true)\n",
            " |-- product_id: string (nullable = true)\n",
            " |-- product_parent: integer (nullable = true)\n",
            " |-- product_title: string (nullable = true)\n",
            " |-- product_category: string (nullable = true)\n",
            " |-- star_rating: string (nullable = true)\n",
            " |-- helpful_votes: integer (nullable = true)\n",
            " |-- total_votes: integer (nullable = true)\n",
            " |-- vine: string (nullable = true)\n",
            " |-- verified_purchase: string (nullable = true)\n",
            " |-- review_headline: string (nullable = true)\n",
            " |-- review_body: string (nullable = true)\n",
            " |-- review_date: timestamp (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B0TESUDRY-90",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "0c843998-e1c8-4740-a77d-1a23e778ea70"
      },
      "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(10)"
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+--------------+\n",
            "|customer_id|customer_count|\n",
            "+-----------+--------------+\n",
            "|   43622307|             1|\n",
            "|   33972406|             1|\n",
            "|     108460|             1|\n",
            "|   43515569|             1|\n",
            "|     132406|             1|\n",
            "|   42560427|             1|\n",
            "|    1673863|             2|\n",
            "|   14552054|             1|\n",
            "|   45632184|             1|\n",
            "|   14703850|             2|\n",
            "+-----------+--------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4FwXA6UvY-96",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "cd5a1bce-6407-454f-ac4f-39a6ba2d225e"
      },
      "source": [
        "# Create the products_table DataFrame and drop duplicates. \n",
        "products_df = df.select([\"product_id\",\"product_title\"]).drop_duplicates([\"product_id\"])\n",
        "\n",
        "products_df.show(10)"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+--------------------+\n",
            "|product_id|       product_title|\n",
            "+----------+--------------------+\n",
            "|0007262973|Pastels (Collins ...|\n",
            "|0113000316|123GetInk -14-pac...|\n",
            "|0136039847|Pearson MyHistory...|\n",
            "|0307382214|Happy Baby, Healt...|\n",
            "|0395742889|Webster's II New ...|\n",
            "|0439165210|Alpha Tales Learn...|\n",
            "|043928631X|Harry Potter Lent...|\n",
            "|0439394058|Scholastic SC9394...|\n",
            "|0439492092|Scholastic Teache...|\n",
            "|043949219X|Scholastic ¡Color...|\n",
            "+----------+--------------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MkqyCuNQY-9-",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "9e0a44e1-88fd-4a9b-d941-b78219189fe3"
      },
      "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(10)"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|     review_id|customer_id|product_id|product_parent|review_date|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|R18RVCKGH1SSI9|   43081963|B001BM2MAC|     307809868| 2015-08-31|\n",
            "|R3L4L6LW1PUOFY|   10951564|B00DZYEXPQ|      75004341| 2015-08-31|\n",
            "|R2J8AWXWTDX2TF|   21143145|B00RTMUHDW|     529689027| 2015-08-31|\n",
            "|R1PR37BR7G3M6A|   52782374|B00D7H8XB6|     868449945| 2015-08-31|\n",
            "|R3BDDDZMZBZDPU|   24045652|B001XCWP34|      33521401| 2015-08-31|\n",
            "| R8T6MO75ND212|   21751234|B004J2NBCO|     214932869| 2015-08-31|\n",
            "|R2YWMQT2V11XYZ|    9109358|B00MOPAG8K|     863351797| 2015-08-31|\n",
            "|R1V2HYL6OI9V39|    9967215|B003AHIK7U|     383470576| 2015-08-31|\n",
            "|R3BLQBKUNXGFS4|   11234247|B006TKH2RO|     999128878| 2015-08-31|\n",
            "|R17MOWJCAR9Y8Q|   12731488|B00W61M9K0|     622066861| 2015-08-31|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# We need to convert the Star Rating Column into an Interger to be able to write the vine_table in RDS\n",
        "# since the table in PgAdmin had star_rating as an in\n",
        "from pyspark.sql.types import IntegerType\n",
        "from pyspark.sql.functions import col\n",
        "\n",
        "vine1_df = df.withColumn(\"star_rating\",col(\"star_rating\").cast(\"Integer\"))\n",
        "vine1_df.dtypes"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wigwYuVxhXR_",
        "outputId": "031bf079-5ec3-454f-994b-60d6ed42a524"
      },
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "execute_result",
          "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')]"
            ]
          },
          "metadata": {},
          "execution_count": 41
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "vine1_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "JSH4izjIkO7j",
        "outputId": "8dc1eac4-9b25-49cf-d2a2-dd8dbc40c431"
      },
      "execution_count": 42,
      "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|   43081963|R18RVCKGH1SSI9|B001BM2MAC|     307809868|Scotch Cushion Wr...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|      Great product.|2015-08-31 00:00:00|\n",
            "|         US|   10951564|R3L4L6LW1PUOFY|B00DZYEXPQ|      75004341|Dust-Off Compress...| Office Products|          5|            0|          1|   N|                Y|Phffffffft, Phfff...|What's to say abo...|2015-08-31 00:00:00|\n",
            "|         US|   21143145|R2J8AWXWTDX2TF|B00RTMUHDW|     529689027|Amram Tagger Stan...| Office Products|          5|            0|          0|   N|                Y|but I am sure I w...|Haven't used yet,...|2015-08-31 00:00:00|\n",
            "|         US|   52782374|R1PR37BR7G3M6A|B00D7H8XB6|     868449945|AmazonBasics 12-S...| Office Products|          1|            2|          3|   N|                Y|and the shredder ...|Although this was...|2015-08-31 00:00:00|\n",
            "|         US|   24045652|R3BDDDZMZBZDPU|B001XCWP34|      33521401|Derwent Colored P...| Office Products|          4|            0|          0|   N|                Y|          Four Stars|Gorgeous colors a...|2015-08-31 00:00:00|\n",
            "|         US|   21751234| R8T6MO75ND212|B004J2NBCO|     214932869|Quartet Magnetic ...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|Perfect for plann...|2015-08-31 00:00:00|\n",
            "|         US|    9109358|R2YWMQT2V11XYZ|B00MOPAG8K|     863351797|KITLEX40X2592UNV2...| Office Products|          5|            0|          0|   N|                N|          Five Stars|Gold plated fuser...|2015-08-31 00:00:00|\n",
            "|         US|    9967215|R1V2HYL6OI9V39|B003AHIK7U|     383470576|Bible Dry Highlig...| Office Products|          5|            6|          6|   N|                Y|Won't bleed throu...|I have used these...|2015-08-31 00:00:00|\n",
            "|         US|   11234247|R3BLQBKUNXGFS4|B006TKH2RO|     999128878|Parker Ingenuity ...| Office Products|          5|            0|          0|   N|                Y|           Nice pen!|Heavy pen that wr...|2015-08-31 00:00:00|\n",
            "|         US|   12731488|R17MOWJCAR9Y8Q|B00W61M9K0|     622066861| RFID Card Protector| Office Products|          5|            0|          0|   N|                Y| Credit card sleeves|Not sure if they ...|2015-08-31 00:00:00|\n",
            "|         US|   49861762|R11EPG1GHOXMGB|B00BXOGI3A|     688569009|The Original Stan...| Office Products|          4|            0|          0|   N|                N|          Four Stars|Great product for...|2015-08-31 00:00:00|\n",
            "|         US|    1541556|R2797NKTEX5THN|B0018RHWGE|     513803406|Zebra Sparky-1 WK...| Office Products|          5|            0|          0|   N|                Y|Love these highli...|Love these highli...|2015-08-31 00:00:00|\n",
            "|         US|   10579374|R2RUTMJS50E4WF|B00IA8H2OG|     201212366|E-Z Ink (TM) Comp...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|Received on time,...|2015-08-31 00:00:00|\n",
            "|         US|   43622307|R1FZYL1HX7O4RM|B007TOIA66|     954138793|Eric 11\" Giant Si...| Office Products|          1|            0|          0|   N|                Y|            One Star|worked about a mo...|2015-08-31 00:00:00|\n",
            "|         US|   50773856|R22ENT0KU5CHPX|B00EANURCE|      43216076|Brother MFCJ450 W...| Office Products|          5|            0|          0|   N|                Y|Brothers Wireless...|I purchased this ...|2015-08-31 00:00:00|\n",
            "|         US|   10165223|R2E5M7FWJC9JLE|B00T3Q0EOO|     637096152|Clarity D613 DECT...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|Everything works ...|2015-08-31 00:00:00|\n",
            "|         US|   14744748|R31BNW3O2Q5J3E|B00WN0N818|     710040412|Permo 50 Feet Whi...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|          Thank you!|2015-08-31 00:00:00|\n",
            "|         US|   37536631|R1JMQF1OOMNF49|B001GAOTSW|     696023182|Pilot G2 Retracta...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|     Love these pens|2015-08-31 00:00:00|\n",
            "|         US|     126687| RJH6WQDOA6CZP|B003BNY7PW|     474277918|AT&T DECT 6.0 Acc...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|The product is ev...|2015-08-31 00:00:00|\n",
            "|         US|   31130095|R2EVL0Z5JVV86X|B00092RJX0|     887474295|Pilot G2 Retracta...| Office Products|          5|            0|          0|   N|                Y|          Five Stars|           Great buy|2015-08-31 00:00:00|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lzMmkdKmY--D",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c7662dcc-9e08-48c6-d291-85ede115b4ab"
      },
      "source": [
        "# Create the vine_table DataFrame\n",
        "vine_df = vine1_df.select([\"review_id\", \"star_rating\",\"helpful_votes\",\"total_votes\",\"vine\",\"verified_purchase\"])\n",
        "vine_df.show(10)"
      ],
      "execution_count": 43,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|verified_purchase|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "|R18RVCKGH1SSI9|          5|            0|          0|   N|                Y|\n",
            "|R3L4L6LW1PUOFY|          5|            0|          1|   N|                Y|\n",
            "|R2J8AWXWTDX2TF|          5|            0|          0|   N|                Y|\n",
            "|R1PR37BR7G3M6A|          1|            2|          3|   N|                Y|\n",
            "|R3BDDDZMZBZDPU|          4|            0|          0|   N|                Y|\n",
            "| R8T6MO75ND212|          5|            0|          0|   N|                Y|\n",
            "|R2YWMQT2V11XYZ|          5|            0|          0|   N|                N|\n",
            "|R1V2HYL6OI9V39|          5|            6|          6|   N|                Y|\n",
            "|R3BLQBKUNXGFS4|          5|            0|          0|   N|                Y|\n",
            "|R17MOWJCAR9Y8Q|          5|            0|          0|   N|                Y|\n",
            "+--------------+-----------+-------------+-----------+----+-----------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "vine_df.dtypes"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GpTxwoJ4jo4H",
        "outputId": "969ea650-664d-4632-dae5-24fa4c558e9b"
      },
      "execution_count": 44,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('review_id', 'string'),\n",
              " ('star_rating', 'int'),\n",
              " ('helpful_votes', 'int'),\n",
              " ('total_votes', 'int'),\n",
              " ('vine', 'string'),\n",
              " ('verified_purchase', 'string')]"
            ]
          },
          "metadata": {},
          "execution_count": 44
        }
      ]
    },
    {
      "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://mypostgresdb16.crre5eooasmv.us-east-1.rds.amazonaws.com:5432/Mod16challenge\"\n",
        "config = {\"user\":\"root\", \n",
        "          \"password\": \"root2022\", \n",
        "          \"driver\":\"org.postgresql.Driver\"}"
      ],
      "execution_count": 45,
      "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": 12,
      "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": 13,
      "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": 14,
      "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": 46,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Exuo6ebUsCqW"
      },
      "source": [],
      "execution_count": null,
      "outputs": []
    }
  ]
}