Amazon_Reviews_ETL.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Amazon_Reviews_ETL.ipynb",
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "PythonData",
      "language": "python",
      "name": "pythondata"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.7"
    },
    "nteract": {
      "version": "0.12.3"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "id": "V58rxea0HqSa",
        "outputId": "e446786e-1116-4814-df0e-69d096499e46",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 218
        }
      },
      "source": [
        "import os\n",
        "# Find the latest version of spark 2.0  from http://www-us.apache.org/dist/spark/ and enter as the spark version\n",
        "# For example:\n",
        "# spark_version = 'spark-3.0.0'\n",
        "spark_version = 'spark-3.0.1'\n",
        "os.environ['SPARK_VERSION']=spark_version\n",
        "\n",
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-11-jdk-headless -qq > /dev/null\n",
        "!wget -q http://www-us.apache.org/dist/spark/$SPARK_VERSION/$SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!tar xf $SPARK_VERSION-bin-hadoop2.7.tgz\n",
        "!pip install -q findspark\n",
        "\n",
        "# Set Environment Variables\n",
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-11-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = f\"/content/{spark_version}-bin-hadoop2.7\"\n",
        "\n",
        "# Start a SparkSession\n",
        "import findspark\n",
        "findspark.init()"
      ],
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\r0% [Working]\r            \rHit:1 http://security.ubuntu.com/ubuntu bionic-security InRelease\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.142)] [Connected to cloud.r-pro\r                                                                               \rHit:2 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease\n",
            "\r0% [Waiting for headers] [Connected to cloud.r-project.org (54.192.86.26)] [Wai\r0% [1 InRelease gpgv 88.7 kB] [Waiting for headers] [Waiting for headers] [Wait\r                                                                               \rHit:3 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease\n",
            "\r0% [1 InRelease gpgv 88.7 kB] [Waiting for headers] [Waiting for headers] [Wait\r                                                                               \rHit:4 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "\r0% [1 InRelease gpgv 88.7 kB] [Waiting for headers] [Waiting for headers] [Wait\r                                                                               \rHit:5 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Hit:6 http://archive.ubuntu.com/ubuntu bionic-updates InRelease\n",
            "Ign:7 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:8 http://archive.ubuntu.com/ubuntu bionic-backports InRelease\n",
            "Ign:9 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:10 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release\n",
            "Hit:11 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Reading package lists... Done\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_xKwTpATHqSe",
        "outputId": "17b808c0-b8de-4f6f-df3a-7462c928e7ea",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 202
        }
      },
      "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": 19,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2020-10-14 04:16:20--  https://jdbc.postgresql.org/download/postgresql-42.2.16.jar\n",
            "Resolving jdbc.postgresql.org (jdbc.postgresql.org)... 72.32.157.228, 2001:4800:3e1:1::228\n",
            "Connecting to jdbc.postgresql.org (jdbc.postgresql.org)|72.32.157.228|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 1002883 (979K) [application/java-archive]\n",
            "Saving to: ‘postgresql-42.2.16.jar.1’\n",
            "\n",
            "postgresql-42.2.16. 100%[===================>] 979.38K  1.37MB/s    in 0.7s    \n",
            "\n",
            "2020-10-14 04:16:22 (1.37 MB/s) - ‘postgresql-42.2.16.jar.1’ 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": 20,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cyBsySGuY-9V"
      },
      "source": [
        "### Load Amazon Data into Spark DataFrame"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CtCmBhQJY-9Z",
        "outputId": "0f2c2532-96bc-4bd3-ccc4-5ad762e1d85c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 474
        }
      },
      "source": [
        "from pyspark import SparkFiles\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Mobile_Electronics_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": 21,
      "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|   20422322| R8MEA6IGAHO0B|B00MC4CED8|     217304173|BlackVue DR600GW-PMP|Mobile_Electronics|          5|            0|          0|   N|                Y|         Very Happy!|As advertised. Ev...| 2015-08-31|\n",
            "|         US|   40835037|R31LOQ8JGLPRLK|B00OQMFG1Q|     137313254|GENSSI GSM / GPS ...|Mobile_Electronics|          5|            0|          1|   N|                Y|           five star|          it's great| 2015-08-31|\n",
            "|         US|   51469641|R2Y0MM9YE6OP3P|B00QERR5CY|      82850235|iXCC Multi pack L...|Mobile_Electronics|          5|            0|          0|   N|                Y|        great cables|These work great ...| 2015-08-31|\n",
            "|         US|    4332923| RRB9C05HDOD4O|B00QUFTPV4|     221169481|abcGoodefg® FBI C...|Mobile_Electronics|          4|            0|          0|   N|                Y|Work very well bu...|Work very well bu...| 2015-08-31|\n",
            "|         US|   44855305|R26I2RI1GFV8QG|B0067XVNTG|     563475445|Generic Car Dashb...|Mobile_Electronics|          2|            0|          0|   N|                Y|Cameras has batte...|Be careful with t...| 2015-08-31|\n",
            "|         US|    7846966| RY8DDL22YG4R5|B00KA6CCVY|     713636156|Aweek® Air Acoust...|Mobile_Electronics|          3|            0|          1|   N|                Y|It appears to be ...|It appears to be ...| 2015-08-31|\n",
            "|         US|   21299354|R2AT2426ZHFUHH|B00MJCDPM2|     754171872|Sentey LS-4460 B-...|Mobile_Electronics|          3|            0|          1|   N|                Y|Didn't love the f...|First one arrived...| 2015-08-31|\n",
            "|         US|   28902968|R3RRXU2R23NMQ9|B00ET5AWBY|     508373185|iPad Car Headrest...|Mobile_Electronics|          5|            0|          0|   N|                Y|          Five Stars|Worked great for ...| 2015-08-31|\n",
            "|         US|    2074438|R250PR8VJUZ62F|B00Z9K379C|     759387370|ANWING Waterproof...|Mobile_Electronics|          4|            0|          2|   N|                Y|          Four Stars|this is a very go...| 2015-08-31|\n",
            "|         US|   48701722| RBEMQ29WJBHYG|B00YO3UYXW|     913911909|Jensen MCR-100 Ca...|Mobile_Electronics|          5|          164|        168|   N|                Y|  I LOVE my recorder|I LOVE my recorde...| 2015-08-31|\n",
            "|         US|    2990247|R19VVIUT4BZCMT|B011W26BU2|     938399424|Bluetooth Speaker...|Mobile_Electronics|          5|            2|          2|   N|                Y|          Five Stars|Great sound great...| 2015-08-31|\n",
            "|         US|   52946117|R1DT8JJUQHUKUL|B00H8PI78C|     661515294|15Pcs DC 5V 85dB ...|Mobile_Electronics|          3|            0|          1|   N|                Y|More of a beeper ...|If what you want ...| 2015-08-31|\n",
            "|         US|   49359994| RLIL6S9OGM3YA|B00M49C6MK|      91176851|      Sentey Ls 4162|Mobile_Electronics|          5|            0|          0|   N|                Y|          Five Stars|             Love it| 2015-08-31|\n",
            "|         US|    4158732|R34EZZ68VYPHO0|B011Q291PK|     891142775|IcePACK 8 Can Ins...|Mobile_Electronics|          1|            1|          1|   N|                Y|            One Star|               Sucks| 2015-08-31|\n",
            "|         US|   18944032|R3TZNSOXS13SIG|B00W6OLUGQ|     577462547|DTK® AMI MDI MMI ...|Mobile_Electronics|          2|            1|          3|   N|                Y|           Two Stars|u need to plug in...| 2015-08-31|\n",
            "|         US|     279606|R12UBZT87UX3AP|B008MY1F8W|     680240984|INPA K+CAN K+DCAN...|Mobile_Electronics|          5|            2|          2|   N|                Y|          Five Stars|Worked with MHD F...| 2015-08-31|\n",
            "|         US|   10947966| R5KCRE927NYMU|B00KW3XNUE|     219920841|Magicfly Wireless...|Mobile_Electronics|          2|            0|          0|   N|                N|Battery Cover Poo...|Just arrived,  re...| 2015-08-31|\n",
            "|         US|   18387442| RT12RQFU0V2AX|B009NGR0AK|     254898339|Humminbird XNT 9 ...|Mobile_Electronics|          5|            1|          1|   N|                Y|        Works great.|I bought this uni...| 2015-08-31|\n",
            "|         US|   48950322|R2K0YQPV5W2SJ1|B011W26BU2|     938399424|Bluetooth Speaker...|Mobile_Electronics|          4|            2|          2|   N|                Y|A Lot of Sound In...|I am giving the s...| 2015-08-31|\n",
            "|         US|   48148197|R104MY31OKNGXH|B00PAP68WO|     366592530|Foxnovo 3.7V 380m...|Mobile_Electronics|          5|            0|          0|   N|                Y|          Five Stars|Met my applicatio...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2yUSe55VY-9t"
      },
      "source": [
        "### Create DataFrames to match tables"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "C8REmY1aY-9u"
      },
      "source": [
        "from pyspark.sql.functions import to_date\n",
        "# Read in the Review dataset as a DataFrame\n"
      ],
      "execution_count": 22,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B0TESUDRY-90",
        "outputId": "f984a598-929d-4e08-8eed-23bfae74b6a9",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 454
        }
      },
      "source": [
        "# Create the customers_table DataFrame\n",
        "# customers_df = df.groupby(\"\").agg({\"\"}).withColumnRenamed(\"\", \"customer_count\")\n",
        "customers_df = df.groupby(\"customer_id\").agg({'customer_id':'count'}).withColumnRenamed(\"count(customer_id)\", \"customer_count\")\n",
        "customers_df.show()"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-----------+--------------+\n",
            "|customer_id|customer_count|\n",
            "+-----------+--------------+\n",
            "|     176073|             1|\n",
            "|   20992492|             1|\n",
            "|    1397686|             1|\n",
            "|   50371142|             1|\n",
            "|     235096|             1|\n",
            "|   12320806|             1|\n",
            "|   19707203|             1|\n",
            "|   15207172|             1|\n",
            "|     538703|             1|\n",
            "|   11223135|             1|\n",
            "|   31447151|             1|\n",
            "|   35961247|             1|\n",
            "|   17835519|             1|\n",
            "|   15404885|             1|\n",
            "|   10182610|             1|\n",
            "|   52399092|             1|\n",
            "|   17133428|             1|\n",
            "|   27870423|             1|\n",
            "|   11574168|             1|\n",
            "|   30685607|             1|\n",
            "+-----------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4FwXA6UvY-96",
        "outputId": "4aec5355-6f0c-4288-e999-3840e11d476b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 454
        }
      },
      "source": [
        "# Create the products_table DataFrame and drop duplicates. \n",
        "# products_df = df.select([]).drop_duplicates()\n",
        "products_df = df.select([\"product_id\", \"product_title\"]).drop_duplicates()\n",
        "products_df.show()"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+----------+--------------------+\n",
            "|product_id|       product_title|\n",
            "+----------+--------------------+\n",
            "|B01075GRAO|ANYSECU QYT KT890...|\n",
            "|B00QVXNV60|BRG AH2000G-ANR A...|\n",
            "|B004ECO5VA|Holux Mini Smart ...|\n",
            "|B00LGNKLXG|Aweek® Car Antenn...|\n",
            "|B00M1Y87I0|      sentey ls 4063|\n",
            "|B002ABX29I|Micca Slim Portab...|\n",
            "|B00XXA2T2G|Centurion GT1 Car...|\n",
            "|B00Q5A5PD6|HYUNDAI HCR-580 V...|\n",
            "|B00VFEK8IY|Z ZTDM Mini Stere...|\n",
            "|B00L85M8VU|Settonbrothers Ul...|\n",
            "|B00SUXD5GU|[NEW RELEASE] JEB...|\n",
            "|B00KJ7MVYW|Fincibo (TM) Appl...|\n",
            "|B00A6YQQS0|Minisuit Aero Fol...|\n",
            "|B009S99IYI|PIO TSG1644R 6.5 ...|\n",
            "|B006PAZCXY|eForCity® Anti-Gl...|\n",
            "|B00OXD61OE|SHE'O® 2x Cool No...|\n",
            "|B000IBMI1K|RCA 200-Watt Mono...|\n",
            "|B00UN6955M|Aisster(TM) Lot 1...|\n",
            "|B00V684NCQ|LSD 8GB Flash Dri...|\n",
            "|B00A609WOO|eForCity Hybrid S...|\n",
            "+----------+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MkqyCuNQY-9-",
        "outputId": "3dc03e97-496a-4ea7-e24c-e52e43c5915b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 454
        }
      },
      "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([, to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")])\n",
        "review_id_df = df.select([\"review_id\", \"customer_id\", \"product_id\", \"product_parent\", to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")])\n",
        "review_id_df.show()"
      ],
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|     review_id|customer_id|product_id|product_parent|review_date|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "| R8MEA6IGAHO0B|   20422322|B00MC4CED8|     217304173| 2015-08-31|\n",
            "|R31LOQ8JGLPRLK|   40835037|B00OQMFG1Q|     137313254| 2015-08-31|\n",
            "|R2Y0MM9YE6OP3P|   51469641|B00QERR5CY|      82850235| 2015-08-31|\n",
            "| RRB9C05HDOD4O|    4332923|B00QUFTPV4|     221169481| 2015-08-31|\n",
            "|R26I2RI1GFV8QG|   44855305|B0067XVNTG|     563475445| 2015-08-31|\n",
            "| RY8DDL22YG4R5|    7846966|B00KA6CCVY|     713636156| 2015-08-31|\n",
            "|R2AT2426ZHFUHH|   21299354|B00MJCDPM2|     754171872| 2015-08-31|\n",
            "|R3RRXU2R23NMQ9|   28902968|B00ET5AWBY|     508373185| 2015-08-31|\n",
            "|R250PR8VJUZ62F|    2074438|B00Z9K379C|     759387370| 2015-08-31|\n",
            "| RBEMQ29WJBHYG|   48701722|B00YO3UYXW|     913911909| 2015-08-31|\n",
            "|R19VVIUT4BZCMT|    2990247|B011W26BU2|     938399424| 2015-08-31|\n",
            "|R1DT8JJUQHUKUL|   52946117|B00H8PI78C|     661515294| 2015-08-31|\n",
            "| RLIL6S9OGM3YA|   49359994|B00M49C6MK|      91176851| 2015-08-31|\n",
            "|R34EZZ68VYPHO0|    4158732|B011Q291PK|     891142775| 2015-08-31|\n",
            "|R3TZNSOXS13SIG|   18944032|B00W6OLUGQ|     577462547| 2015-08-31|\n",
            "|R12UBZT87UX3AP|     279606|B008MY1F8W|     680240984| 2015-08-31|\n",
            "| R5KCRE927NYMU|   10947966|B00KW3XNUE|     219920841| 2015-08-31|\n",
            "| RT12RQFU0V2AX|   18387442|B009NGR0AK|     254898339| 2015-08-31|\n",
            "|R2K0YQPV5W2SJ1|   48950322|B011W26BU2|     938399424| 2015-08-31|\n",
            "|R104MY31OKNGXH|   48148197|B00PAP68WO|     366592530| 2015-08-31|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lzMmkdKmY--D",
        "outputId": "66d41a64-3d8e-479f-d8c7-284ee9b644cc",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 454
        }
      },
      "source": [
        "# Create the vine_table. DataFrame\n",
        "# vine_df = df.select([])\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": 26,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|     review_id|customer_id|product_id|product_parent|review_date|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "| R8MEA6IGAHO0B|   20422322|B00MC4CED8|     217304173| 2015-08-31|\n",
            "|R31LOQ8JGLPRLK|   40835037|B00OQMFG1Q|     137313254| 2015-08-31|\n",
            "|R2Y0MM9YE6OP3P|   51469641|B00QERR5CY|      82850235| 2015-08-31|\n",
            "| RRB9C05HDOD4O|    4332923|B00QUFTPV4|     221169481| 2015-08-31|\n",
            "|R26I2RI1GFV8QG|   44855305|B0067XVNTG|     563475445| 2015-08-31|\n",
            "| RY8DDL22YG4R5|    7846966|B00KA6CCVY|     713636156| 2015-08-31|\n",
            "|R2AT2426ZHFUHH|   21299354|B00MJCDPM2|     754171872| 2015-08-31|\n",
            "|R3RRXU2R23NMQ9|   28902968|B00ET5AWBY|     508373185| 2015-08-31|\n",
            "|R250PR8VJUZ62F|    2074438|B00Z9K379C|     759387370| 2015-08-31|\n",
            "| RBEMQ29WJBHYG|   48701722|B00YO3UYXW|     913911909| 2015-08-31|\n",
            "|R19VVIUT4BZCMT|    2990247|B011W26BU2|     938399424| 2015-08-31|\n",
            "|R1DT8JJUQHUKUL|   52946117|B00H8PI78C|     661515294| 2015-08-31|\n",
            "| RLIL6S9OGM3YA|   49359994|B00M49C6MK|      91176851| 2015-08-31|\n",
            "|R34EZZ68VYPHO0|    4158732|B011Q291PK|     891142775| 2015-08-31|\n",
            "|R3TZNSOXS13SIG|   18944032|B00W6OLUGQ|     577462547| 2015-08-31|\n",
            "|R12UBZT87UX3AP|     279606|B008MY1F8W|     680240984| 2015-08-31|\n",
            "| R5KCRE927NYMU|   10947966|B00KW3XNUE|     219920841| 2015-08-31|\n",
            "| RT12RQFU0V2AX|   18387442|B009NGR0AK|     254898339| 2015-08-31|\n",
            "|R2K0YQPV5W2SJ1|   48950322|B011W26BU2|     938399424| 2015-08-31|\n",
            "|R104MY31OKNGXH|   48148197|B00PAP68WO|     366592530| 2015-08-31|\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://dataviz.cr9nfiosi1bm.us-east-2.rds.amazonaws.com:5432/postgres\"\n",
        "config = {\"user\":\"postgres\", \n",
        "          \"password\": \"1Qaz2Wsx\", \n",
        "          \"driver\":\"org.postgresql.Driver\"}"
      ],
      "execution_count": 29,
      "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": 30,
      "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": 31,
      "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": 32,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XdQknSHLY--e",
        "outputId": "4bcfac70-6669-44d1-b88e-929f4dddfef2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 195
        }
      },
      "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": 33,
      "outputs": [
        {
          "output_type": "error",
          "ename": "NameError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-33-d50cb9e944fc>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Write vine_df to table in RDS\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;31m# 11 minutes\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mvine_df\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwrite\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mjdbc\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0murl\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mjdbc_url\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtable\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'vine_table'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmode\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mmode\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mproperties\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mconfig\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;31mNameError\u001b[0m: name 'vine_df' is not defined"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Exuo6ebUsCqW"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}