Big_Data__Books_Part1.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "authorship_tag": "ABX9TyMYMGBQamHwaogCuZR8ItBH",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/mychele-larson/Big_Data_ETL/blob/main/Big_Data__Books_Part1.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": {
        "id": "7w9PQt66BLVG"
      },
      "outputs": [],
      "source": [
        "# Book reviews on Amazon - Part 1 Module 22 Assignment - 2nd set of data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-8eFW_wl1n39",
        "outputId": "861d1a3d-daa0-4970-9fe1-21861771d7e1"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Get:1 https://cloud.r-project.org/bin/linux/ubuntu focal-cran40/ InRelease [3,622 B]\n",
            "Ign:2 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu2004/x86_64  InRelease\n",
            "Get:3 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu2004/x86_64  InRelease [1,581 B]\n",
            "Hit:4 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu2004/x86_64  Release\n",
            "Get:5 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]\n",
            "Get:6 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu focal InRelease [18.1 kB]\n",
            "Hit:7 http://archive.ubuntu.com/ubuntu focal InRelease\n",
            "Get:8 http://archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]\n",
            "Get:9 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu2004/x86_64  Packages [920 kB]\n",
            "Hit:11 http://ppa.launchpad.net/cran/libgit2/ubuntu focal InRelease\n",
            "Get:12 http://security.ubuntu.com/ubuntu focal-security/main amd64 Packages [2,544 kB]\n",
            "Hit:13 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu focal InRelease\n",
            "Get:14 http://archive.ubuntu.com/ubuntu focal-backports InRelease [108 kB]\n",
            "Hit:15 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu focal InRelease\n",
            "Get:16 http://archive.ubuntu.com/ubuntu focal-updates/universe amd64 Packages [1,312 kB]\n",
            "Hit:17 http://ppa.launchpad.net/ubuntugis/ppa/ubuntu focal InRelease\n",
            "Get:18 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu focal/main Sources [2,397 kB]\n",
            "Get:19 http://archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages [3,019 kB]\n",
            "Get:20 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu focal/main amd64 Packages [1,136 kB]\n",
            "Fetched 11.7 MB in 4s (2,942 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ],
      "source": [
        "# Activate Spark in our Colab notebook.\n",
        "import os\n",
        "# Find the latest version of spark 3.2  from http://www.apache.org/dist/spark/ and enter as the spark version\n",
        "# For example:\n",
        "# spark_version = 'spark-3.2.3'\n",
        "spark_version = 'spark-3.2.3'\n",
        "os.environ['SPARK_VERSION']=spark_version\n",
        "\n",
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-11-jdk-headless -qq > /dev/null\n",
        "!wget -q http://www.apache.org/dist/spark/$SPARK_VERSION/$SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!pip install -q findspark\n",
        "\n",
        "# Set Environment Variables\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = f\"/content/{spark_version}-bin-hadoop2.7\"\n",
        "\n",
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "BzCrgs0Z1rnw",
        "outputId": "6cfac655-24e7-452b-d5e2-9d2cd5f3bc15"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2023-03-15 00:08:24--  https://jdbc.postgresql.org/download/postgresql-42.2.9.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: 914037 (893K) [application/java-archive]\n",
            "Saving to: ‘postgresql-42.2.9.jar’\n",
            "\n",
            "postgresql-42.2.9.j 100%[===================>] 892.61K  5.45MB/s    in 0.2s    \n",
            "\n",
            "2023-03-15 00:08:24 (5.45 MB/s) - ‘postgresql-42.2.9.jar’ saved [914037/914037]\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# Get postgresql package\n",
        "!wget https://jdbc.postgresql.org/download/postgresql-42.2.9.jar"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "metadata": {
        "id": "0DuBth0V2PR8"
      },
      "outputs": [],
      "source": [
        "# Import Spark and create a SparkSession\n",
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"BigData-HW-1\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.9.jar\").getOrCreate()"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [],
      "metadata": {
        "id": "DOs1L0Wv_VRv"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D3W2XJVi2CU-"
      },
      "source": [
        "# Extract the Amazon Data into Spark DataFrame"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Read in the data from an S3 Bucket\n",
        "from pyspark import SparkFiles\n",
        "\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Books_v1_02.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "books_df = spark.read.csv(SparkFiles.get(\"amazon_reviews_us_Books_v1_02.tsv.gz\"), sep=\"\\t\", header=True)\n",
        "\n",
        "# Drop duplicates\n",
        "books_df = books_df.drop_duplicates()\n",
        "\n",
        "# Drop null values\n",
        "books_df = books_df.dropna()\n",
        "\n",
        "# Show DataFrame\n",
        "books_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LrSsP2X1-Z2E",
        "outputId": "c6e2b1bc-e6dd-46f6-b5be-916fe3e10780"
      },
      "execution_count": 5,
      "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|   52006632|R100JJDVONLM53|0826415725|      15610372|James Brown's Liv...|           Books|          5|            4|          5|   N|                N|it's a history le...|the cuban missile...| 2004-10-04|\n",
            "|         US|   15014641|R100O96NJ2IORV|038551316X|      86124043|The Book of Mormo...|           Books|          5|           19|         30|   N|                N|Best book I've ev...|I was born and ra...| 2004-12-21|\n",
            "|         US|   53035368|R101WACVOF1BXP|0380973871|     734682067| The October Country|           Books|          4|            0|          3|   N|                N|Not Bradbury's Be...|Not all the stori...| 2000-10-27|\n",
            "|         US|   51239523|R1020BZCVXAWTM|0804117950|     109929862| The Law of the Dead|           Books|          5|            3|          3|   N|                N|Couldn't put it down|Tess Collins has ...| 1999-05-04|\n",
            "|         US|   25704917|R102J7OPFNLGF8|0689854323|     491653739|Oswald's Camping ...|           Books|          5|            1|          1|   N|                N|         great value|Oswald is always ...| 2005-08-17|\n",
            "|         US|   40522448|R102O8FX1XT7FC|0658011456|     502114843|    The Fasting Diet|           Books|          5|           12|         16|   N|                Y|  A  fantastic book!|This is a great b...| 2003-05-24|\n",
            "|         US|   51281967|R102TBQWPZYV8C|1558671951|      38798234|The Dehydrator Co...|           Books|          2|           28|         29|   N|                N|       Way too basic|I just got a spar...| 2002-08-30|\n",
            "|         US|   39681662|R1038HO7UOMGB9|014028009X|     738261988|Bridget Jones's D...|           Books|          1|            8|         10|   N|                N|    Absolutely awful|I couldn't even g...| 2002-05-15|\n",
            "|         US|   49580275|R103LXZ49Q1X0A|0375703845|     939433309|Glamorama (Vintag...|           Books|          5|            0|          2|   N|                N|A Shallow Charact...|Victor Ward/Johns...| 2000-12-20|\n",
            "|         US|   53017408|R103UM505R1KFF|0375701214|     508561158|The Diving Bell a...|           Books|          5|            0|          0|   N|                N|a short, unsentim...|profoundly moving...| 1998-03-23|\n",
            "|         US|   37272906|R1041JAMQ22BS2|1885394187|     216992833|The Rohrig-Tarot ...|           Books|          5|            5|          5|   N|                N|Magic Brought To ...|I was skeptikal w...| 2003-09-18|\n",
            "|         US|   52561712|R1046IZEKO9W13|0374252874|      48810266|Running on Empty:...|           Books|          5|           12|         19|   N|                Y|    Required Reading|Mr. Peterson's bo...| 2004-09-03|\n",
            "|         US|   28648234|R104CN98MXM9AR|0786417862|     410956438|The Titanic in Pr...|           Books|          3|            4|          5|   N|                Y|Nice book - but t...|I would have like...| 2005-04-22|\n",
            "|         US|   51091531| R1052KCKIYPKF|0774033444|     808414399|Coles Notes: Kese...|           Books|          5|            4|          4|   N|                N|ATTENTION ALL PSY...|This is a great s...| 2000-05-29|\n",
            "|         US|   13421379|R1052LRLSJEPJ3|0976660350|     131817431|           Anecdotal|           Books|          5|            4|          4|   N|                N|         Hornby PLUS|First of all, I h...| 2005-05-23|\n",
            "|         US|   47433140|R105D7BKGFU70O|0826416357|     727002049|Jeff Buckley's Gr...|           Books|          5|           55|         64|   N|                N|As an arrogant mu...|Thomas Lhamon's r...| 2005-08-17|\n",
            "|         US|   35350954|R105RU93KY8ZY8|1400046610|     957571830|Slander: Liberal ...|           Books|          5|            7|         12|   N|                N|It just goes to s...|...how we've been...| 2003-07-16|\n",
            "|         US|   44927528|R106EZ398990R1|0486296024|     424288287|Common Sense (Dov...|           Books|          5|            3|          5|   N|                N|Long live the Rep...|When I went to hi...| 2001-08-17|\n",
            "|         US|   49073256|R106STE3Y1PJL6|0312868383|      57273906|The Forever Hero:...|           Books|          1|            7|         19|   N|                N|Forever Sociopath...|A hero? I don't t...| 2000-07-27|\n",
            "|         US|   41862012|R1071FOAXOOK3R|014027541X|     727745356|The Fabric of Rea...|           Books|          1|           19|         32|   N|                N|           sophistry|There is a fledgl...| 2001-09-21|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+----------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Get the number of rows in the DataFrame.\n",
        "books_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NIqgxKTG-Z5L",
        "outputId": "91a0de1c-2401-48c2-b06b-06c180d83a36"
      },
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "3105360"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Transform the data"
      ],
      "metadata": {
        "id": "lnjD1I1q_9oz"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Create the \"review_id_table\"."
      ],
      "metadata": {
        "id": "Z_clWLDpAboh"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.functions import to_date, col\n",
        "# Create the \"review_id_df\" DataFrame with the appropriate columns and data types.\n",
        "# Select the necessary columns\n",
        "\n",
        "books_review_id_df = books_df.select(\"review_id\", \"customer_id\", \"product_id\", \"product_parent\", to_date(\"review_date\", \"yyyy-MM-dd\").alias(\"review_date\"))\n",
        "\n",
        "# Cast \"customer_id\" and \"product_parent\" column to integer\n",
        "books_review_id_df = books_review_id_df.withColumn(\"customer_id\", col(\"customer_id\").cast(\"integer\")).withColumn(\"product_parent\", col(\"product_parent\").cast(\"integer\"))\n",
        "\n",
        "# Show the resulting DataFrame\n",
        "books_review_id_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "acXlk7R6-Z9f",
        "outputId": "50ccd436-52a9-487c-b0b5-906dd2579cdd"
      },
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|     review_id|customer_id|product_id|product_parent|review_date|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|R100JJDVONLM53|   52006632|0826415725|      15610372| 2004-10-04|\n",
            "|R100O96NJ2IORV|   15014641|038551316X|      86124043| 2004-12-21|\n",
            "|R101WACVOF1BXP|   53035368|0380973871|     734682067| 2000-10-27|\n",
            "|R1020BZCVXAWTM|   51239523|0804117950|     109929862| 1999-05-04|\n",
            "|R102J7OPFNLGF8|   25704917|0689854323|     491653739| 2005-08-17|\n",
            "|R102O8FX1XT7FC|   40522448|0658011456|     502114843| 2003-05-24|\n",
            "|R102TBQWPZYV8C|   51281967|1558671951|      38798234| 2002-08-30|\n",
            "|R1038HO7UOMGB9|   39681662|014028009X|     738261988| 2002-05-15|\n",
            "|R103LXZ49Q1X0A|   49580275|0375703845|     939433309| 2000-12-20|\n",
            "|R103UM505R1KFF|   53017408|0375701214|     508561158| 1998-03-23|\n",
            "|R1041JAMQ22BS2|   37272906|1885394187|     216992833| 2003-09-18|\n",
            "|R1046IZEKO9W13|   52561712|0374252874|      48810266| 2004-09-03|\n",
            "|R104CN98MXM9AR|   28648234|0786417862|     410956438| 2005-04-22|\n",
            "| R1052KCKIYPKF|   51091531|0774033444|     808414399| 2000-05-29|\n",
            "|R1052LRLSJEPJ3|   13421379|0976660350|     131817431| 2005-05-23|\n",
            "|R105D7BKGFU70O|   47433140|0826416357|     727002049| 2005-08-17|\n",
            "|R105RU93KY8ZY8|   35350954|1400046610|     957571830| 2003-07-16|\n",
            "|R106EZ398990R1|   44927528|0486296024|     424288287| 2001-08-17|\n",
            "|R106STE3Y1PJL6|   49073256|0312868383|      57273906| 2000-07-27|\n",
            "|R1071FOAXOOK3R|   41862012|014027541X|     727745356| 2001-09-21|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Create the \"products\" Table"
      ],
      "metadata": {
        "id": "bL_srUAAAwzg"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "from pyspark.sql.functions import to_date, col, substring\n",
        "# Create the \"products_df\" DataFrame that drops the duplicates in the \"product_id\" and \"product_title\" columns. \n",
        "books_products_df = books_df.select(\"product_id\", substring(col(\"product_title\"), 1,300).alias(\"product_title\"))\n",
        "\n",
        "# Drop duplicates\n",
        "books_products_df = books_products_df.drop_duplicates()\n",
        "\n",
        "# Show the resulting DataFrame\n",
        "books_products_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "8lBPQGfR-aEH",
        "outputId": "7dadc6a5-7194-41da-abcc-8f37476d5013"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+--------------------+\n",
            "|product_id|       product_title|\n",
            "+----------+--------------------+\n",
            "|0029148510|Acts of War: Beha...|\n",
            "|1844494896|Waiting For Kate ...|\n",
            "|0451216954|Dark Lover (Black...|\n",
            "|006056718X|Often Wrong, Neve...|\n",
            "|0965852768|101 Spooktacular ...|\n",
            "|141960435X|Flip Flop Spanish...|\n",
            "|0471282502|Experimental Orga...|\n",
            "|0756613647|Universe: The Def...|\n",
            "|B0006YGCE8|Seismic risk asse...|\n",
            "|207030602X|L Etranger (Folio...|\n",
            "|1569711224|Aliens: Hive  (2n...|\n",
            "|0756613744|Go Figure!: A Tot...|\n",
            "|0891073825|The Cross: God's ...|\n",
            "|1595140840|            Teach Me|\n",
            "|1844491412|Led Zeppelin: The...|\n",
            "|0060737980|Surrounded by Idi...|\n",
            "|0486265250|Ancient Egyptian ...|\n",
            "|0763627798|           Shoe Baby|\n",
            "|097559950X|Natural Cures \"Th...|\n",
            "|0595362478| Accursed Inhabitant|\n",
            "+----------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Group by columns to find duplicates\n",
        "duplicates = books_products_df.groupBy(\"product_id\", \"product_title\").count().filter(col(\"count\") > 1)\n",
        "\n",
        "# Show duplicates\n",
        "duplicates.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-ht1mhgg-aHw",
        "outputId": "a29dd93a-924c-4f52-b8d4-58f7590f08d8"
      },
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+-------------+-----+\n",
            "|product_id|product_title|count|\n",
            "+----------+-------------+-----+\n",
            "+----------+-------------+-----+\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Create the \"customers\" Table"
      ],
      "metadata": {
        "id": "U53dLf8cA-yd"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the \"customers_df\" DataFrame that groups the data on the \"customer_id\" by the number of times a customer reviewed a product. \n",
        "books_customers_df = books_df.groupBy(\"customer_id\").agg({\"customer_id\": \"count\"}).withColumnRenamed(\"count(customer_id)\", \"customer_count\").withColumn(\"customer_id\", col(\"customer_id\").cast(\"integer\"))\n",
        "\n",
        "# Show the resulting DataFrame\n",
        "books_customers_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "syzyTxbp-aLD",
        "outputId": "cb999f15-731c-4adc-d9aa-4763fc46b074"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+--------------+\n",
            "|customer_id|customer_count|\n",
            "+-----------+--------------+\n",
            "|   53045101|            38|\n",
            "|   37144346|            18|\n",
            "|   36847515|             3|\n",
            "|   52894069|             8|\n",
            "|   41553221|             2|\n",
            "|   52992798|            61|\n",
            "|   51930777|            18|\n",
            "|   51369269|             8|\n",
            "|   52815943|            26|\n",
            "|   24180231|             1|\n",
            "|   49257001|           207|\n",
            "|   39213659|             1|\n",
            "|   31056457|             1|\n",
            "|   51067556|            23|\n",
            "|   52626661|             1|\n",
            "|   37695339|             1|\n",
            "|   44768257|             2|\n",
            "|   48688230|             4|\n",
            "|   49737799|             2|\n",
            "|   52171393|             5|\n",
            "+-----------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Create the \"vine_table\"."
      ],
      "metadata": {
        "id": "oxGQWWTYBGRW"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Create the \"vine_df\" DataFrame that has the \"review_id\", \"star_rating\", \"helpful_votes\", \"total_votes\", and \"vine\" columns. \n",
        "books_vine_df = books_df.select(\"review_id\", \"star_rating\", \"helpful_votes\", \"total_votes\", \"vine\") \\\n",
        "            .withColumn(\"star_rating\", col(\"star_rating\").cast(\"integer\")) \\\n",
        "            .withColumn(\"helpful_votes\", col(\"helpful_votes\").cast(\"integer\")) \\\n",
        "            .withColumn(\"total_votes\", col(\"total_votes\").cast(\"integer\"))\n",
        "            \n",
        "# Show the resulting DataFrame\n",
        "books_vine_df.show()\n"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "X-FTazmVBIGc",
        "outputId": "51e3367b-5fe5-4250-9f30-b6c5b7bd5e06"
      },
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|\n",
            "+--------------+-----------+-------------+-----------+----+\n",
            "|R100JJDVONLM53|          5|            4|          5|   N|\n",
            "|R100O96NJ2IORV|          5|           19|         30|   N|\n",
            "|R101WACVOF1BXP|          4|            0|          3|   N|\n",
            "|R1020BZCVXAWTM|          5|            3|          3|   N|\n",
            "|R102J7OPFNLGF8|          5|            1|          1|   N|\n",
            "|R102O8FX1XT7FC|          5|           12|         16|   N|\n",
            "|R102TBQWPZYV8C|          2|           28|         29|   N|\n",
            "|R1038HO7UOMGB9|          1|            8|         10|   N|\n",
            "|R103LXZ49Q1X0A|          5|            0|          2|   N|\n",
            "|R103UM505R1KFF|          5|            0|          0|   N|\n",
            "|R1041JAMQ22BS2|          5|            5|          5|   N|\n",
            "|R1046IZEKO9W13|          5|           12|         19|   N|\n",
            "|R104CN98MXM9AR|          3|            4|          5|   N|\n",
            "| R1052KCKIYPKF|          5|            4|          4|   N|\n",
            "|R1052LRLSJEPJ3|          5|            4|          4|   N|\n",
            "|R105D7BKGFU70O|          5|           55|         64|   N|\n",
            "|R105RU93KY8ZY8|          5|            7|         12|   N|\n",
            "|R106EZ398990R1|          5|            3|          5|   N|\n",
            "|R106STE3Y1PJL6|          1|            7|         19|   N|\n",
            "|R1071FOAXOOK3R|          1|           19|         32|   N|\n",
            "+--------------+-----------+-------------+-----------+----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Load Postgres Set-up"
      ],
      "metadata": {
        "id": "k2ycUNn6BOs9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# Configure settings for RDS\n",
        "mode = \"append\"\n",
        "jdbc_url=\"jdbc:postgresql://rds-bigdata.cjysn375vm3e.us-east-2.rds.amazonaws.com:5432/RDS_Database\"\n",
        "config = {\"user\":\"postgres\", \n",
        "          \"password\": \"postgres\", \n",
        "          \"driver\":\"org.postgresql.Driver\"}"
      ],
      "metadata": {
        "id": "nit-fhsjBQT-"
      },
      "execution_count": 12,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Write review_id_df to table in RDS\n",
        "books_review_id_df.write.jdbc(url=jdbc_url, table='books_review_id_table', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "KQ-8CMqxBQZc"
      },
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Write products_df to table in RDS\n",
        "books_products_df.write.jdbc(url=jdbc_url, table='books_products', mode=mode, properties=config)\n"
      ],
      "metadata": {
        "id": "9JQcUfjxBUp0"
      },
      "execution_count": 14,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Write customers_df to table in RDS\n",
        "books_customers_df.write.jdbc(url=jdbc_url, table='books_customers', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "PJYRWLObBUuH"
      },
      "execution_count": 15,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Write vine_df to table in RDS\n",
        "books_vine_df.write.jdbc(url=jdbc_url, table='books_vine_table', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "zb7f8xSVBUyq"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}