Health_Personal_Care_Level_1.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Health_Personal_Care_Level_1.ipynb",
      "provenance": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 1,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "xxowdH3w5J6O",
        "outputId": "54ffe549-a0ca-4a9e-97de-9aeed5c93afb"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\r0% [Working]\r            \rGet:1 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]\n",
            "\r0% [Connecting to archive.ubuntu.com (185.125.190.36)] [1 InRelease 0 B/88.7 kB\r                                                                               \rGet:2 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease [1,581 B]\n",
            "Get:3 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease [3,626 B]\n",
            "Hit:4 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Ign:5 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:6 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\n",
            "Get:7 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Packages [813 kB]\n",
            "Get:8 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "Get:9 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]\n",
            "Hit:10 http://ppa.launchpad.net/cran/libgit2/ubuntu bionic InRelease\n",
            "Get:11 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Get:12 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease [15.9 kB]\n",
            "Get:13 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [2,830 kB]\n",
            "Get:14 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease [21.3 kB]\n",
            "Get:15 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [1,517 kB]\n",
            "Get:16 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [982 kB]\n",
            "Get:18 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [2,022 kB]\n",
            "Get:19 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [2,293 kB]\n",
            "Get:20 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [3,262 kB]\n",
            "Get:21 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [1,036 kB]\n",
            "Get:22 http://archive.ubuntu.com/ubuntu bionic-updates/restricted amd64 Packages [1,015 kB]\n",
            "Get:23 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic/main amd64 Packages [45.3 kB]\n",
            "Get:24 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic/main amd64 Packages [47.7 kB]\n",
            "Fetched 16.2 MB in 5s (3,434 kB/s)\n",
            "Reading package lists... Done\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.2.1'\n",
        "os.environ['SPARK_VERSION']=spark_version\n",
        "\n",
        "# Install Spark and Java\n",
        "!apt-get update\n",
        "!apt-get install openjdk-8-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",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-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",
      "source": [
        "# Connect to Postgres\n",
        "!wget https://jdbc.postgresql.org/download/postgresql-42.2.9.jar"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "DoO3Yn7F5QcS",
        "outputId": "17336307-1747-4af3-d7e4-987b31e5721c"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2022-06-15 05:48:51--  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.71MB/s    in 0.2s    \n",
            "\n",
            "2022-06-15 05:48:52 (5.71 MB/s) - ‘postgresql-42.2.9.jar’ saved [914037/914037]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Start Spark Session\n",
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"CloudETL\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.9.jar\").getOrCreate()"
      ],
      "metadata": {
        "id": "WMD885mz5Qgx"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Read in the Video Mobile app\n",
        "from pyspark import SparkFiles\n",
        "# Load data from S3 into a DataFrame\n",
        "url = \"https://s3.amazonaws.com/amazon-reviews-pds/tsv/amazon_reviews_us_Health_Personal_Care_v1_00.tsv.gz\"\n",
        "spark.sparkContext.addFile(url)\n",
        "\n",
        "df = spark.read.option('header', 'true').csv(SparkFiles.get(\"amazon_reviews_us_Health_Personal_Care_v1_00.tsv.gz\"), inferSchema=True, sep='\\t')\n",
        "df.show(10)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FN9z10-o5QiT",
        "outputId": "11f046de-02ca-4b5b-8d33-5e0fc31904c4"
      },
      "execution_count": 4,
      "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|     650634| R3EQSTM9PWRAL|B0091LBZSU|     578484426|Demograss Capsule...|Health & Personal...|          3|            0|          0|   N|                Y|         Three Stars|Only came with 30...| 2015-08-31|\n",
            "|         US|   19827510| RBWPRK17XKIXD|B00PWW3LQ6|     456433146|Viva Labs #1 Prem...|Health & Personal...|          5|            0|          0|   N|                Y|A good portable s...|Really yummy and ...| 2015-08-31|\n",
            "|         US|    1520474| RRSLOAF273XFC|B00DKEWA92|     460764511|     Whip-it Cleaner|Health & Personal...|          5|            1|          2|   N|                Y|       You need this|You cannot go wro...| 2015-08-31|\n",
            "|         US|   23905905|R3S8W9Q6SWIT8O|B0015R3A7M|     135102038|Optimum Nutrition...|Health & Personal...|          4|            0|          0|   N|                Y|this is a good vi...|This is a good da...| 2015-08-31|\n",
            "|         US|   28215779|R3QQ6NSLRVBFJC|B006B8U8BG|     200666829|Wet-Stop3 Bedwett...|Health & Personal...|          4|            0|          0|   N|                Y|progress is defin...|We have been usin...| 2015-08-31|\n",
            "|         US|   23260912|R2XYDBMHUVJCSX|B00PFZFD8Y|     344168617|NatraCure Plantar...|Health & Personal...|          2|            0|          1|   N|                Y|           Two Stars|I wear it a few t...| 2015-08-31|\n",
            "|         US|    7965616|R1L94ESVC657A9|B0034792JG|     291831570|Glade Sense & Spr...|Health & Personal...|          5|            0|          0|   N|                Y|          Five Stars|Love this product...| 2015-08-31|\n",
            "|         US|    3443178|R3DI4B8LDWFQ3K|B000052XIA|     493678550|Vagisil Anti-Itch...|Health & Personal...|          1|            3|          4|   N|                Y|It still is itchi...|It still is itchi...| 2015-08-31|\n",
            "|         US|   20838781|R1G7VV4HCXUQQL|B004GCUK8A|       3902598|Glade Automatic S...|Health & Personal...|          5|            0|          0|   N|                Y|          Five Stars|               Good!| 2015-08-31|\n",
            "|         US|   15267517|R3TTNYN1TAMUSU|B00K7W1QAO|     633971374|Kirkland Signatur...|Health & Personal...|          5|            0|          0|   N|                Y|      Will buy again|These bags are ma...| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+--------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 10 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KGLULNCk5QkB",
        "outputId": "abc05b58-a05f-4d4a-c840-51b8eca40abf"
      },
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "5331449"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## **Examine the schema**\n"
      ],
      "metadata": {
        "id": "acfA3wVH5gAq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.printSchema()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0meFh9jS5QpB",
        "outputId": "5533da4e-f484-48e3-f6b9-78e75685ec6a"
      },
      "execution_count": 6,
      "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: integer (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: string (nullable = true)\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Drop null values\n",
        "dropna_df = df.dropna()\n",
        "dropna_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "X6DyyQ4j5Qqi",
        "outputId": "d397a2b6-f60e-4756-b5b1-f076620a05d0"
      },
      "execution_count": 7,
      "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|     650634| R3EQSTM9PWRAL|B0091LBZSU|     578484426|Demograss Capsule...|Health & Personal...|          3|            0|          0|   N|                Y|         Three Stars|Only came with 30...| 2015-08-31|\n",
            "|         US|   19827510| RBWPRK17XKIXD|B00PWW3LQ6|     456433146|Viva Labs #1 Prem...|Health & Personal...|          5|            0|          0|   N|                Y|A good portable s...|Really yummy and ...| 2015-08-31|\n",
            "|         US|    1520474| RRSLOAF273XFC|B00DKEWA92|     460764511|     Whip-it Cleaner|Health & Personal...|          5|            1|          2|   N|                Y|       You need this|You cannot go wro...| 2015-08-31|\n",
            "|         US|   23905905|R3S8W9Q6SWIT8O|B0015R3A7M|     135102038|Optimum Nutrition...|Health & Personal...|          4|            0|          0|   N|                Y|this is a good vi...|This is a good da...| 2015-08-31|\n",
            "|         US|   28215779|R3QQ6NSLRVBFJC|B006B8U8BG|     200666829|Wet-Stop3 Bedwett...|Health & Personal...|          4|            0|          0|   N|                Y|progress is defin...|We have been usin...| 2015-08-31|\n",
            "|         US|   23260912|R2XYDBMHUVJCSX|B00PFZFD8Y|     344168617|NatraCure Plantar...|Health & Personal...|          2|            0|          1|   N|                Y|           Two Stars|I wear it a few t...| 2015-08-31|\n",
            "|         US|    7965616|R1L94ESVC657A9|B0034792JG|     291831570|Glade Sense & Spr...|Health & Personal...|          5|            0|          0|   N|                Y|          Five Stars|Love this product...| 2015-08-31|\n",
            "|         US|    3443178|R3DI4B8LDWFQ3K|B000052XIA|     493678550|Vagisil Anti-Itch...|Health & Personal...|          1|            3|          4|   N|                Y|It still is itchi...|It still is itchi...| 2015-08-31|\n",
            "|         US|   20838781|R1G7VV4HCXUQQL|B004GCUK8A|       3902598|Glade Automatic S...|Health & Personal...|          5|            0|          0|   N|                Y|          Five Stars|               Good!| 2015-08-31|\n",
            "|         US|   15267517|R3TTNYN1TAMUSU|B00K7W1QAO|     633971374|Kirkland Signatur...|Health & Personal...|          5|            0|          0|   N|                Y|      Will buy again|These bags are ma...| 2015-08-31|\n",
            "|         US|   30105089|R3AMX2D9HMWV6Z|B0034KYA36|     854858899|Zyrtec Prescripti...|Health & Personal...|          5|            0|          0|   N|                Y|         Good price!|Delivered product...| 2015-08-31|\n",
            "|         US|    1622207|R3PO3K321GY8HI|B00C7UZTT8|     264608272|Pure Garcinia Cam...|Health & Personal...|          1|            0|          0|   N|                Y|       Doesn't work.|So far I've felt ...| 2015-08-31|\n",
            "|         US|   29856075| RVQ0UNYX6IEVK|B005F9XFN0|     638667060|Now  Better Stevi...|Health & Personal...|          1|            0|          1|   N|                Y|           not quite|Not nearly as goo...| 2015-08-31|\n",
            "|         US|   52319463|R25E9NIGO2XQ3D|B00Q8YSPTU|     130354267|Now Foods Stevia ...|Health & Personal...|          5|            0|          0|   N|                Y|Reasonably priced...|Reasonably priced...| 2015-08-31|\n",
            "|         US|   16857013|R3GIEXIIIR2G9Y|B00N34HBVG|     700530604|Compression Thigh...|Health & Personal...|          1|            0|          0|   N|                Y|DO NOT BUY!!!! Ve...|They are sooooooo...| 2015-08-31|\n",
            "|         US|   12269680|R264BH3U417NXF|B002UN2UKS|     731161248|Brownmed SEAL-TIG...|Health & Personal...|          5|            0|          0|   N|                Y|Worked Great!! Ve...|Worked Great!!  V...| 2015-08-31|\n",
            "|         US|   29116955|R2C6S0XJIMP0Z2|B003265KYK|     719831457|Botanic Choice Gl...|Health & Personal...|          5|            0|          0|   N|                Y|          Five Stars|Great price, on t...| 2015-08-31|\n",
            "|         US|   14533949|R2LRJYGC2YYR2F|B00939J5T6|     377517622|Shibari SHIB-WHT ...|Health & Personal...|          2|            0|          0|   N|                Y|Cant handle long ...|Broke down after ...| 2015-08-31|\n",
            "|         US|   11274313|R3SVW9ZP1CZPW7|B00LJOQKRI|     376583016|Eyekepper 5-pack ...|Health & Personal...|          1|            0|          1|   N|                Y|            One Star|I think the presc...| 2015-08-31|\n",
            "|         US|   14861684|R1GLA6RQUF4Q79|B002TG3QPO|     813956658|Isopure Zero Carb...|Health & Personal...|          4|            0|          0|   N|                Y|          Four Stars|            Loved it| 2015-08-31|\n",
            "+-----------+-----------+--------------+----------+--------------+--------------------+--------------------+-----------+-------------+-----------+----+-----------------+--------------------+--------------------+-----------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "dropna_df.count()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "XahCq1HM5QsT",
        "outputId": "6a02e251-3ce2-41e5-8286-8cd6159bc0ab"
      },
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "5330701"
            ]
          },
          "metadata": {},
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Configuration for RDS instance\n",
        "mode=\"overwrite\"\n",
        "jdbc_url = \"jdbc:postgresql://database-1.c3cli578fb9j.us-west-2.rds.amazonaws.com:5432/Personal_Health_db\"\n",
        "config = {\"user\":\"postgres\",\n",
        "          \"password\": \"postgres\",\n",
        "          \"driver\":\"org.postgresql.Driver\"}"
      ],
      "metadata": {
        "id": "XB_iTzfF5Qt9"
      },
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Select the column headers in the product table schema\n",
        "product_df = dropna_df.select([\"product_id\", \"product_title\"])\n",
        "product_df.show(5)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FPBU1sMA5QxV",
        "outputId": "aa4a1a5b-bc90-4e1e-f18c-33e27e050b28"
      },
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+--------------------+\n",
            "|product_id|       product_title|\n",
            "+----------+--------------------+\n",
            "|B0091LBZSU|Demograss Capsule...|\n",
            "|B00PWW3LQ6|Viva Labs #1 Prem...|\n",
            "|B00DKEWA92|     Whip-it Cleaner|\n",
            "|B0015R3A7M|Optimum Nutrition...|\n",
            "|B006B8U8BG|Wet-Stop3 Bedwett...|\n",
            "+----------+--------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Write DataFrame to table\n",
        "product_df.write.jdbc(url=jdbc_url, table='products', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "dlZlSBfv5Qy8"
      },
      "execution_count": 11,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Select the column headers in the customer table schema\n",
        "customers_df = dropna_df.groupby(\"customer_id\").agg({\"customer_id\": \"count\"}).withColumnRenamed(\"count(customer_id)\", \"customer_count\")\n",
        "customers_df.show()"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Ok6hHAj75Q0p",
        "outputId": "ea5680c9-04d2-42e5-aa27-d86103651e63"
      },
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-----------+--------------+\n",
            "|customer_id|customer_count|\n",
            "+-----------+--------------+\n",
            "|    4919528|             5|\n",
            "|   51451778|             3|\n",
            "|   12713799|             1|\n",
            "|    8673341|             1|\n",
            "|   42146698|             2|\n",
            "|    1117644|             2|\n",
            "|   28058398|             1|\n",
            "|   14375645|             1|\n",
            "|   24540309|             1|\n",
            "|   39715602|             1|\n",
            "|     654272|             1|\n",
            "|    9015608|             5|\n",
            "|   43920023|            42|\n",
            "|   38209321|             1|\n",
            "|   12328685|             1|\n",
            "|    2866605|             2|\n",
            "|   38273165|             5|\n",
            "|   41066514|             1|\n",
            "|   28377689|             2|\n",
            "|   17018784|             2|\n",
            "+-----------+--------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Write DataFrame to table\n",
        "customers_df.write.jdbc(url=jdbc_url, table='customers', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "hxECJRhI5Q2b"
      },
      "execution_count": 13,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Select the column headers in the review_id_table schema\n",
        "from pyspark.sql.functions import to_date\n",
        "\n",
        "to_date(\"review_date\", 'yyyy-MM-dd').alias()\n",
        "\n",
        "review_df = dropna_df.select([\"review_id\", \"customer_id\", \"product_id\", \"product_parent\", to_date(\"review_date\", 'yyyy-MM-dd').alias(\"review_date\")])\n",
        "review_df.show(5)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Td5WRggH5vtc",
        "outputId": "9edcb99b-0edc-49dd-c3eb-6fa961f0817a"
      },
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+----------+--------------+-----------+\n",
            "|     review_id|customer_id|product_id|product_parent|review_date|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "| R3EQSTM9PWRAL|     650634|B0091LBZSU|     578484426| 2015-08-31|\n",
            "| RBWPRK17XKIXD|   19827510|B00PWW3LQ6|     456433146| 2015-08-31|\n",
            "| RRSLOAF273XFC|    1520474|B00DKEWA92|     460764511| 2015-08-31|\n",
            "|R3S8W9Q6SWIT8O|   23905905|B0015R3A7M|     135102038| 2015-08-31|\n",
            "|R3QQ6NSLRVBFJC|   28215779|B006B8U8BG|     200666829| 2015-08-31|\n",
            "+--------------+-----------+----------+--------------+-----------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Check the data types in the dataframe \n",
        "review_df.dtypes"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "FfqdBnAw5vvG",
        "outputId": "2b1f1704-0477-46ed-ee15-5ce28cdd2cf0"
      },
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[('review_id', 'string'),\n",
              " ('customer_id', 'int'),\n",
              " ('product_id', 'string'),\n",
              " ('product_parent', 'int'),\n",
              " ('review_date', 'date')]"
            ]
          },
          "metadata": {},
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Write DataFrame to table\n",
        "review_df.write.jdbc(url=jdbc_url, table='review_id_table', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "SatFOBmu5vwq"
      },
      "execution_count": 17,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "# Select the column headers in the vine table schema\n",
        "vine_df = dropna_df.select([\"review_id\", \"star_rating\", \"helpful_votes\", \"total_votes\", \"vine\"])\n",
        "vine_df.show(5)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Fs-o2-iI5vz-",
        "outputId": "f89787d1-6052-4706-918b-66bad20b1903"
      },
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+--------------+-----------+-------------+-----------+----+\n",
            "|     review_id|star_rating|helpful_votes|total_votes|vine|\n",
            "+--------------+-----------+-------------+-----------+----+\n",
            "| R3EQSTM9PWRAL|          3|            0|          0|   N|\n",
            "| RBWPRK17XKIXD|          5|            0|          0|   N|\n",
            "| RRSLOAF273XFC|          5|            1|          2|   N|\n",
            "|R3S8W9Q6SWIT8O|          4|            0|          0|   N|\n",
            "|R3QQ6NSLRVBFJC|          4|            0|          0|   N|\n",
            "+--------------+-----------+-------------+-----------+----+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "# Write DataFrame to table\n",
        "vine_df.write.jdbc(url=jdbc_url, table='vine_table', mode=mode, properties=config)"
      ],
      "metadata": {
        "id": "NCLkH_uD5v1a"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}