S3_Postgres.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "S3_Postgres",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "SIig2aoa9kRU",
        "outputId": "a31f8fca-a423-4d0e-f276-f686993dd8ae"
      },
      "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.0.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()"
      ],
      "execution_count": 90,
      "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 (91.189.88.152)] [1 InRelease 14.2 kB/88.7\r                                                                               \rHit:2 https://cloud.r-project.org/bin/linux/ubuntu bionic-cran40/ InRelease\n",
            "\r0% [Connecting to archive.ubuntu.com (91.189.88.152)] [1 InRelease 14.2 kB/88.7\r0% [2 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (91.189.88.152)\r0% [2 InRelease gpgv 3,626 B] [Connecting to archive.ubuntu.com (91.189.88.152)\r                                                                               \rIgn:3 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  InRelease\n",
            "\r0% [2 InRelease gpgv 3,626 B] [Waiting for headers] [Waiting for headers] [Wait\r                                                                               \rGet:4 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic InRelease [15.9 kB]\n",
            "Hit:5 http://archive.ubuntu.com/ubuntu bionic InRelease\n",
            "Ign:6 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  InRelease\n",
            "Hit:7 https://developer.download.nvidia.com/compute/cuda/repos/ubuntu1804/x86_64  Release\n",
            "Hit:8 https://developer.download.nvidia.com/compute/machine-learning/repos/ubuntu1804/x86_64  Release\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",
            "Hit:11 http://ppa.launchpad.net/deadsnakes/ppa/ubuntu bionic InRelease\n",
            "Get:12 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
            "Hit:13 http://ppa.launchpad.net/graphics-drivers/ppa/ubuntu bionic InRelease\n",
            "Get:16 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main Sources [1,813 kB]\n",
            "Get:17 http://ppa.launchpad.net/c2d4u.team/c2d4u4.0+/ubuntu bionic/main amd64 Packages [928 kB]\n",
            "Fetched 3,009 kB in 3s (1,031 kB/s)\n",
            "Reading package lists... Done\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-eG9V7Kc-RKM",
        "outputId": "35aba09f-c303-4bbc-a39d-e762faba16d8"
      },
      "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\n"
      ],
      "execution_count": 91,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "--2021-11-07 22:36:43--  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.3’\n",
            "\n",
            "postgresql-42.2.16. 100%[===================>] 979.38K  5.63MB/s    in 0.2s    \n",
            "\n",
            "2021-11-07 22:36:43 (5.63 MB/s) - ‘postgresql-42.2.16.jar.3’ saved [1002883/1002883]\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xMwQfaKF-RZa"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "spark = SparkSession.builder.appName(\"CloudETL\").config(\"spark.driver.extraClassPath\",\"/content/postgresql-42.2.16.jar\").getOrCreate()"
      ],
      "execution_count": 92,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "kwLjoQdV-RdD",
        "outputId": "59307f42-45c7-40fb-c05c-f4d1b2a256f3"
      },
      "source": [
        "# Read in data from S3 Buckets\n",
        "from pyspark import SparkFiles\n",
        "from pyspark import SparkContext\n",
        "from pyspark.sql import SparkSession\n",
        "url =\"https://initial-datasets.s3.amazonaws.com/cases_deaths_norm.csv\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"cases_deaths_norm.csv\"), sep=\",\", header=True, inferSchema=True)\n",
        "df.show()\n",
        "df.columns"
      ],
      "execution_count": 103,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------+---------+----+--------+--------+-----------+---------------------+----------------+---------+--------------+------------+----------------+-----------+----------+---------------+\n",
            "|submission_date|mmwr_week|year|location|  concat|total_cases|total_confirmed_cases|total_prob_cases|new_cases|prob_new_cases|total_deaths|confirmed_deaths|prob_deaths|new_deaths|prob_new_deaths|\n",
            "+---------------+---------+----+--------+--------+-----------+---------------------+----------------+---------+--------------+------------+----------------+-----------+----------+---------------+\n",
            "|     2020-12-19|       51|2020|      AK|202051AK|      42659|                 null|            null|      325|             0|         267|            null|       null|         1|              0|\n",
            "|     2020-12-26|       52|2020|      AK|202052AK|      44394|                 null|            null|      188|             0|         271|            null|       null|         1|              0|\n",
            "|     2021-01-02|       53|2021|      AK|202153AK|      46530|                 null|            null|      289|             0|         287|            null|       null|         2|              0|\n",
            "|     2021-01-09|        1|2021|      AK| 20211AK|      48571|                 null|            null|      374|             0|         297|            null|       null|         1|              0|\n",
            "|     2021-01-16|        2|2021|      AK| 20212AK|      50264|                 null|            null|      195|             0|         300|            null|       null|         0|              0|\n",
            "|     2021-01-23|        3|2021|      AK| 20213AK|      51663|                 null|            null|      241|             0|         306|            null|       null|         0|              0|\n",
            "|     2021-01-30|        4|2021|      AK| 20214AK|      52636|                 null|            null|      129|             0|         308|            null|       null|         0|              0|\n",
            "|     2021-02-06|        5|2021|      AK| 20215AK|      53557|                 null|            null|      124|             0|         309|            null|       null|         1|              0|\n",
            "|     2021-02-13|        6|2021|      AK| 20216AK|      54488|                 null|            null|       94|             0|         311|            null|       null|         1|              0|\n",
            "|     2021-02-20|        7|2021|      AK| 20217AK|      55392|                 null|            null|      136|             0|         318|            null|       null|         3|              0|\n",
            "|     2021-02-27|        8|2021|      AK| 20218AK|      56212|                 null|            null|      160|             0|         323|            null|       null|         1|              0|\n",
            "|     2021-03-06|        9|2021|      AK| 20219AK|      57088|                 null|            null|      129|             0|         328|            null|       null|         0|              0|\n",
            "|     2021-03-13|       10|2021|      AK|202110AK|      58010|                 null|            null|      169|             0|         331|            null|       null|         0|              0|\n",
            "|     2021-03-20|       11|2021|      AK|202111AK|      58893|                 null|            null|      185|             0|         333|            null|       null|         1|              0|\n",
            "|     2021-03-27|       12|2021|      AK|202112AK|      59965|                 null|            null|      199|             0|         338|            null|       null|         1|              0|\n",
            "|     2021-04-03|       13|2021|      AK|202113AK|      61197|                 null|            null|      233|             0|         340|            null|       null|         0|              0|\n",
            "|     2021-04-10|       14|2021|      AK|202114AK|      62391|                 null|            null|      179|             0|         341|            null|       null|         0|              0|\n",
            "|     2021-04-17|       15|2021|      AK|202115AK|      63439|                 null|            null|      177|             0|         341|            null|       null|         0|              0|\n",
            "|     2021-04-24|       16|2021|      AK|202116AK|      64503|                 null|            null|      136|             0|         341|            null|       null|         0|              0|\n",
            "|     2021-05-01|       17|2021|      AK|202117AK|      65498|                 null|            null|       89|             0|         341|            null|       null|         0|              0|\n",
            "+---------------+---------+----+--------+--------+-----------+---------------------+----------------+---------+--------------+------------+----------------+-----------+----------+---------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<bound method DataFrame.printSchema of DataFrame[submission_date: string, mmwr_week: int, year: int, location: string, concat: string, total_cases: int, total_confirmed_cases: int, total_prob_cases: int, new_cases: int, prob_new_cases: int, total_deaths: int, confirmed_deaths: int, prob_deaths: int, new_deaths: int, prob_new_deaths: int]>"
            ]
          },
          "metadata": {},
          "execution_count": 103
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "Ac7NxkLk-Rfh",
        "outputId": "a8d19003-2452-40ac-eff4-c3dd743b65a2"
      },
      "source": [
        "url =\"https://initial-datasets.s3.amazonaws.com/vaccinations_norm.csv\"\n",
        "spark.sparkContext.addFile(url)\n",
        "df_2 = spark.read.option(\"encoding\", \"UTF-8\").csv(SparkFiles.get(\"vaccinations_norm.csv\"), sep=\",\", header=True, inferSchema=True)\n",
        "df_2.show()\n",
        "df_2.columns"
      ],
      "execution_count": 104,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+---------+----+--------+--------+-----------+------------+------------------+------------------+---------------+----------------+\n",
            "|      Date|MMWR_week|Year|Location|  Concat|Distributed|Administered|Recip_Administered|Administered_Dose1|Series_Complete|Additional_Doses|\n",
            "+----------+---------+----+--------+--------+-----------+------------+------------------+------------------+---------------+----------------+\n",
            "|2020-12-19|       51|2020|      AK|202051AK|      26325|        1607|                 0|                 0|              0|            null|\n",
            "|2020-12-26|       52|2020|      AK|202052AK|      45250|       11427|                 0|                 0|              0|            null|\n",
            "|2021-01-02|       53|2021|      AK|202153AK|      54975|       18401|                 0|                 0|              0|            null|\n",
            "|2021-01-09|        1|2021|      AK| 20211AK|      92875|       28539|                 0|                 0|              0|            null|\n",
            "|2021-01-16|        2|2021|      AK| 20212AK|     149650|       54193|                 0|                 0|              0|            null|\n",
            "|2021-01-23|        3|2021|      AK| 20213AK|     155700|       88344|                 0|                 0|              0|            null|\n",
            "|2021-01-30|        4|2021|      AK| 20214AK|     193150|      115268|                 0|                 0|              0|            null|\n",
            "|2021-02-06|        5|2021|      AK| 20215AK|     248500|      139147|                 0|                 0|              0|            null|\n",
            "|2021-02-13|        6|2021|      AK| 20216AK|     271550|      175138|            174962|            122518|              0|            null|\n",
            "|2021-02-20|        7|2021|      AK| 20217AK|     299935|      227403|            227363|            148497|              0|            null|\n",
            "|2021-02-27|        8|2021|      AK| 20218AK|     342535|      259538|            259706|            163739|              0|            null|\n",
            "|2021-03-06|        9|2021|      AK| 20219AK|     426715|      288625|            289004|            178237|         110053|            null|\n",
            "|2021-03-13|       10|2021|      AK|202110AK|     472395|      324271|            324820|            197436|         126647|            null|\n",
            "|2021-03-20|       11|2021|      AK|202111AK|     520685|      362698|            363011|            221157|         143349|            null|\n",
            "|2021-03-27|       12|2021|      AK|202112AK|     570465|      395650|            395987|            242733|         156698|            null|\n",
            "|2021-04-03|       13|2021|      AK|202113AK|     636665|      420238|            419902|            254263|         170747|            null|\n",
            "|2021-04-10|       14|2021|      AK|202114AK|     706185|      455719|            454937|            268587|         194814|            null|\n",
            "|2021-04-17|       15|2021|      AK|202115AK|     746705|      509843|            508588|            288040|         231393|            null|\n",
            "|2021-04-24|       16|2021|      AK|202116AK|     759125|      536961|            535338|            299312|         246821|            null|\n",
            "|2021-05-01|       17|2021|      AK|202117AK|     769205|      560662|            558538|            307711|         262217|            null|\n",
            "+----------+---------+----+--------+--------+-----------+------------+------------------+------------------+---------------+----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<bound method DataFrame.printSchema of DataFrame[Date: string, MMWR_week: int, Year: int, Location: string, Concat: string, Distributed: int, Administered: int, Recip_Administered: int, Administered_Dose1: int, Series_Complete: int, Additional_Doses: int]>"
            ]
          },
          "metadata": {},
          "execution_count": 104
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "b6lsFeWODfio",
        "outputId": "c478b3f6-60bf-49f1-8ea5-b92b0539b053"
      },
      "source": [
        "cases_deaths_df = df.select([to_date(\"submission_date\", \"yyyy-MM-dd\").alias(\"submission_date\"),\"mmwr_week\",\"year\",\"location\",\"concat\",\"total_cases\",\"total_confirmed_cases\",\"total_prob_cases\",\"new_cases\",\"prob_new_cases\",\"total_deaths\",\"confirmed_deaths\",\"prob_deaths\",\"new_deaths\",\"prob_new_deaths\"])\n",
        "cases_deaths_df.show()\n",
        "cases_deaths_df.columns"
      ],
      "execution_count": 106,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+---------------+---------+----+--------+--------+-----------+---------------------+----------------+---------+--------------+------------+----------------+-----------+----------+---------------+\n",
            "|submission_date|mmwr_week|year|location|  concat|total_cases|total_confirmed_cases|total_prob_cases|new_cases|prob_new_cases|total_deaths|confirmed_deaths|prob_deaths|new_deaths|prob_new_deaths|\n",
            "+---------------+---------+----+--------+--------+-----------+---------------------+----------------+---------+--------------+------------+----------------+-----------+----------+---------------+\n",
            "|     2020-12-19|       51|2020|      AK|202051AK|      42659|                 null|            null|      325|             0|         267|            null|       null|         1|              0|\n",
            "|     2020-12-26|       52|2020|      AK|202052AK|      44394|                 null|            null|      188|             0|         271|            null|       null|         1|              0|\n",
            "|     2021-01-02|       53|2021|      AK|202153AK|      46530|                 null|            null|      289|             0|         287|            null|       null|         2|              0|\n",
            "|     2021-01-09|        1|2021|      AK| 20211AK|      48571|                 null|            null|      374|             0|         297|            null|       null|         1|              0|\n",
            "|     2021-01-16|        2|2021|      AK| 20212AK|      50264|                 null|            null|      195|             0|         300|            null|       null|         0|              0|\n",
            "|     2021-01-23|        3|2021|      AK| 20213AK|      51663|                 null|            null|      241|             0|         306|            null|       null|         0|              0|\n",
            "|     2021-01-30|        4|2021|      AK| 20214AK|      52636|                 null|            null|      129|             0|         308|            null|       null|         0|              0|\n",
            "|     2021-02-06|        5|2021|      AK| 20215AK|      53557|                 null|            null|      124|             0|         309|            null|       null|         1|              0|\n",
            "|     2021-02-13|        6|2021|      AK| 20216AK|      54488|                 null|            null|       94|             0|         311|            null|       null|         1|              0|\n",
            "|     2021-02-20|        7|2021|      AK| 20217AK|      55392|                 null|            null|      136|             0|         318|            null|       null|         3|              0|\n",
            "|     2021-02-27|        8|2021|      AK| 20218AK|      56212|                 null|            null|      160|             0|         323|            null|       null|         1|              0|\n",
            "|     2021-03-06|        9|2021|      AK| 20219AK|      57088|                 null|            null|      129|             0|         328|            null|       null|         0|              0|\n",
            "|     2021-03-13|       10|2021|      AK|202110AK|      58010|                 null|            null|      169|             0|         331|            null|       null|         0|              0|\n",
            "|     2021-03-20|       11|2021|      AK|202111AK|      58893|                 null|            null|      185|             0|         333|            null|       null|         1|              0|\n",
            "|     2021-03-27|       12|2021|      AK|202112AK|      59965|                 null|            null|      199|             0|         338|            null|       null|         1|              0|\n",
            "|     2021-04-03|       13|2021|      AK|202113AK|      61197|                 null|            null|      233|             0|         340|            null|       null|         0|              0|\n",
            "|     2021-04-10|       14|2021|      AK|202114AK|      62391|                 null|            null|      179|             0|         341|            null|       null|         0|              0|\n",
            "|     2021-04-17|       15|2021|      AK|202115AK|      63439|                 null|            null|      177|             0|         341|            null|       null|         0|              0|\n",
            "|     2021-04-24|       16|2021|      AK|202116AK|      64503|                 null|            null|      136|             0|         341|            null|       null|         0|              0|\n",
            "|     2021-05-01|       17|2021|      AK|202117AK|      65498|                 null|            null|       89|             0|         341|            null|       null|         0|              0|\n",
            "+---------------+---------+----+--------+--------+-----------+---------------------+----------------+---------+--------------+------------+----------------+-----------+----------+---------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['submission_date',\n",
              " 'mmwr_week',\n",
              " 'year',\n",
              " 'location',\n",
              " 'concat',\n",
              " 'total_cases',\n",
              " 'total_confirmed_cases',\n",
              " 'total_prob_cases',\n",
              " 'new_cases',\n",
              " 'prob_new_cases',\n",
              " 'total_deaths',\n",
              " 'confirmed_deaths',\n",
              " 'prob_deaths',\n",
              " 'new_deaths',\n",
              " 'prob_new_deaths']"
            ]
          },
          "metadata": {},
          "execution_count": 106
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bQEh4sB5Df5Z",
        "outputId": "694d3a49-d426-483f-f7ac-7fd9c6070e7a"
      },
      "source": [
        "vaccines_df = df_2.select([to_date(\"Date\", 'yyyy-MM-dd').alias(\"Date\"),\"MMWR_week\",\"Year\",\"Location\",\"Concat\",\"Distributed\",\"Administered\",\"Recip_Administered\",\"Administered_Dose1\",\"Series_Complete\",\"Additional_Doses\"])\n",
        "vaccines_df.show()"
      ],
      "execution_count": 107,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+----------+---------+----+--------+--------+-----------+------------+------------------+------------------+---------------+----------------+\n",
            "|      Date|MMWR_week|Year|Location|  Concat|Distributed|Administered|Recip_Administered|Administered_Dose1|Series_Complete|Additional_Doses|\n",
            "+----------+---------+----+--------+--------+-----------+------------+------------------+------------------+---------------+----------------+\n",
            "|2020-12-19|       51|2020|      AK|202051AK|      26325|        1607|                 0|                 0|              0|            null|\n",
            "|2020-12-26|       52|2020|      AK|202052AK|      45250|       11427|                 0|                 0|              0|            null|\n",
            "|2021-01-02|       53|2021|      AK|202153AK|      54975|       18401|                 0|                 0|              0|            null|\n",
            "|2021-01-09|        1|2021|      AK| 20211AK|      92875|       28539|                 0|                 0|              0|            null|\n",
            "|2021-01-16|        2|2021|      AK| 20212AK|     149650|       54193|                 0|                 0|              0|            null|\n",
            "|2021-01-23|        3|2021|      AK| 20213AK|     155700|       88344|                 0|                 0|              0|            null|\n",
            "|2021-01-30|        4|2021|      AK| 20214AK|     193150|      115268|                 0|                 0|              0|            null|\n",
            "|2021-02-06|        5|2021|      AK| 20215AK|     248500|      139147|                 0|                 0|              0|            null|\n",
            "|2021-02-13|        6|2021|      AK| 20216AK|     271550|      175138|            174962|            122518|              0|            null|\n",
            "|2021-02-20|        7|2021|      AK| 20217AK|     299935|      227403|            227363|            148497|              0|            null|\n",
            "|2021-02-27|        8|2021|      AK| 20218AK|     342535|      259538|            259706|            163739|              0|            null|\n",
            "|2021-03-06|        9|2021|      AK| 20219AK|     426715|      288625|            289004|            178237|         110053|            null|\n",
            "|2021-03-13|       10|2021|      AK|202110AK|     472395|      324271|            324820|            197436|         126647|            null|\n",
            "|2021-03-20|       11|2021|      AK|202111AK|     520685|      362698|            363011|            221157|         143349|            null|\n",
            "|2021-03-27|       12|2021|      AK|202112AK|     570465|      395650|            395987|            242733|         156698|            null|\n",
            "|2021-04-03|       13|2021|      AK|202113AK|     636665|      420238|            419902|            254263|         170747|            null|\n",
            "|2021-04-10|       14|2021|      AK|202114AK|     706185|      455719|            454937|            268587|         194814|            null|\n",
            "|2021-04-17|       15|2021|      AK|202115AK|     746705|      509843|            508588|            288040|         231393|            null|\n",
            "|2021-04-24|       16|2021|      AK|202116AK|     759125|      536961|            535338|            299312|         246821|            null|\n",
            "|2021-05-01|       17|2021|      AK|202117AK|     769205|      560662|            558538|            307711|         262217|            null|\n",
            "+----------+---------+----+--------+--------+-----------+------------+------------------+------------------+---------------+----------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wj1crth3-Rh3"
      },
      "source": [
        "# Store environmental variable\n",
        "# from getpass import getpass\n",
        "# password = getpass('project-one')\n",
        "# Configure settings for RDS\n",
        "mode = \"append\"\n",
        "jdbc_url=\"jdbc:postgresql://mypostgresdb.cin6h4edfcqj.us-east-2.rds.amazonaws.com:5432/my_data_class_db\"\n",
        "config = {\"user\":\"root\",\n",
        "          \"password\": \"project-one\",\n",
        "          \"driver\":\"org.postgresql.Driver\"}"
      ],
      "execution_count": 108,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "RhwvyEyg-Rkg"
      },
      "source": [
        "# Write cases_deaths_df to casesdeaths table in RDS\n",
        "cases_deaths_df.write.jdbc(url=jdbc_url, table = 'casesdeaths', mode = mode,properties = config)\n"
      ],
      "execution_count": 110,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "neL0sHPO-Rm2"
      },
      "source": [
        "# Write vaccines_df to vaccinations table in RDS\n",
        "vaccines_df.write.jdbc(url=jdbc_url,table='vaccinations', mode=mode,properties=config)\n"
      ],
      "execution_count": 112,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "UOlgslhd-RpW"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MDDeBM3V-RsC"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MG5ThcaL-Rw5"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ar7iwkSZ-RzF"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "n4c6y6TW-R14"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "d7xiCb_N-R8F"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jimnb89u-R-e"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vw-4hh1G-SBG"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MPGRRjry-SEC"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}