OpenWeatherMapAPI_ETL_FullRefresh.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "OpenWeatherMapAPI-ETL-FullRefresh",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "K0aAOiIXQKOg"
      },
      "source": [
        "####**Define ETL functions**\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZrRNAhI0lx9v"
      },
      "source": [
        "import psycopg2\n",
        "\n",
        "# Redshift connection function\n",
        "def get_Redshift_connection():\n",
        "    host = \"ssde.cnqux5xggmn5.us-east-2.redshift.amazonaws.com\"\n",
        "    redshift_user = \"choyoura\"\n",
        "    redshift_pass = \"password\"\n",
        "    port = 5439\n",
        "    dbname = \"dev\"\n",
        "\n",
        "    conn = psycopg2.connect(\"dbname={dbname} user={user} host={host} password={password} port={port}\".format(\n",
        "        dbname=dbname,\n",
        "        user=redshift_user,\n",
        "        password=redshift_pass,\n",
        "        host=host,\n",
        "        port=port\n",
        "    ))\n",
        "\n",
        "    conn.set_session(autocommit=True)\n",
        "    return conn.cursor()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LsSiD0YFmJ4X"
      },
      "source": [
        "import requests\n",
        "import json\n",
        "\n",
        "def extract(url):\n",
        "    response = requests.get(url)\n",
        "    print(response)\n",
        "    data = response.json()['daily']\n",
        "    print(\"Extract Done\")\n",
        "    print(data)\n",
        "    return data"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gjDwY95epWDi"
      },
      "source": [
        "from datetime import datetime\n",
        "\n",
        "def transform(data):\n",
        "  ret = []\n",
        "\n",
        "  for d in data[1:]:\n",
        "    date = datetime.fromtimestamp(d[\"dt\"]).strftime('%Y-%m-%d')\n",
        "    avg_temp = d[\"temp\"][\"day\"]\n",
        "    min_temp = d[\"temp\"][\"min\"]\n",
        "    max_temp = d[\"temp\"][\"max\"]\n",
        "\n",
        "    ret.append([date, avg_temp, min_temp, max_temp])\n",
        "  for row in ret:\n",
        "    print(row)\n",
        "\n",
        "  print(\"Transform Done\")\n",
        "  return ret"
      ],
      "execution_count": 39,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "lRgij8s9ChsW"
      },
      "source": [
        "from datetime import datetime\n",
        "\n",
        "def load(data):\n",
        "\n",
        "  cur = get_Redshift_connection()\n",
        "\n",
        "  try:\n",
        "    sql = \"\"\"BEGIN;\n",
        "            CREATE TABLE IF NOT EXISTS choyoura.weather_forecast (\n",
        "              date date primary key,\n",
        "              temp float,\n",
        "              min_temp float,\n",
        "              max_temp float,\n",
        "              updated_date timestamp default GETDATE()\n",
        "            );\n",
        "            \n",
        "            DELETE FROM choyoura.weather_forecast;\"\"\"\n",
        "\n",
        "    cur.execute(sql)\n",
        "\n",
        "    for date, temp, min_temp, max_temp in week_data:\n",
        "      sql = f\"\"\"\n",
        "            INSERT INTO choyoura.weather_forecast (date, temp, min_temp, max_temp)\n",
        "            VALUES ('{date}', '{temp}', '{min_temp}', '{max_temp}');\n",
        "            \"\"\"\n",
        "      cur.execute(sql)\n",
        "    \n",
        "    cur.execute(\"END\")\n",
        "    print(\"Load Done\")\n",
        "\n",
        "  except:\n",
        "    print(\"Error! ROLLBACK\")\n",
        "    cur.execute(\"ROLLBACK\")\n",
        "\n",
        "  else:\n",
        "    cur.execute(\"SELECT count(1) FROM choyoura.weather_forecast;\")\n",
        "    count = cur.fetchone()[0]\n",
        "    print(\"Number of data inserted: \", count)      "
      ],
      "execution_count": 59,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nOOqLjKsWj7g"
      },
      "source": [
        "#### **Run the ETL Process**\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JtBaGseEQxYV"
      },
      "source": [
        "https://colab.research.google.com/github/apolitical/colab-env/blob/master/colab_env_testbed.ipynb"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wGiaQm62A3aT"
      },
      "source": [
        "Seoul/Coordinates: 37.5665° N, 126.9780° E"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "hAS2ENnRfqRm",
        "outputId": "1e42596d-83c5-4f50-c487-430df12de85d"
      },
      "source": [
        "!pip install colab-env --upgrade"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Collecting colab-env\n",
            "  Downloading colab-env-0.2.0.tar.gz (4.7 kB)\n",
            "Collecting python-dotenv<1.0,>=0.10.0\n",
            "  Downloading python_dotenv-0.19.2-py2.py3-none-any.whl (17 kB)\n",
            "Building wheels for collected packages: colab-env\n",
            "  Building wheel for colab-env (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for colab-env: filename=colab_env-0.2.0-py3-none-any.whl size=3836 sha256=0ac62c30455eb64a65c5f434f72735c6e400dbf33b331eae9304701e38046e40\n",
            "  Stored in directory: /root/.cache/pip/wheels/bb/ca/e8/3d25b6abb4ac719ecb9e837bb75f2a9b980430005fb12a9107\n",
            "Successfully built colab-env\n",
            "Installing collected packages: python-dotenv, colab-env\n",
            "Successfully installed colab-env-0.2.0 python-dotenv-0.19.2\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BqYkhXVXkc70",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "b7c3ecd1-d25a-4214-bd0d-19d0c145c3c5"
      },
      "source": [
        "import colab_env\n",
        "import os \n",
        "# !more gdrive/My\\ Drive/vars.env\n",
        "\n",
        "API_key = os.getenv(\"API_key\")"
      ],
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/gdrive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4jLkXdwYrqXL"
      },
      "source": [
        "params = {'lat':37.57, 'lon':126.98, 'part':'current,minutely,hourly', 'unit':'metric', 'key': API_key}\n",
        "API_url = 'https://api.openweathermap.org/data/2.5/onecall?lat={lat}&lon={lon}&exclude={part}&units={unit}&appid={key}'.format(**params)"
      ],
      "execution_count": 35,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5vIYGsELaB7l",
        "outputId": "bdd9f597-84b8-4a4f-f383-4cc7b1d0a547"
      },
      "source": [
        "json_data = extract(API_url) # Temperature in Celsius "
      ],
      "execution_count": 41,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "<Response [200]>\n",
            "Extract Done\n",
            "[{'dt': 1637550000, 'sunrise': 1637533144, 'sunset': 1637569054, 'moonrise': 1637575920, 'moonset': 1637542080, 'moon_phase': 0.58, 'temp': {'day': 5.37, 'min': 1.14, 'max': 8.99, 'night': 1.14, 'eve': 3.78, 'morn': 4.5}, 'feels_like': {'day': 0.71, 'night': -3.97, 'eve': -0.72, 'morn': 0.69}, 'pressure': 1015, 'humidity': 44, 'dew_point': -5.23, 'wind_speed': 8.49, 'wind_deg': 300, 'wind_gust': 14.79, 'weather': [{'id': 616, 'main': 'Snow', 'description': 'rain and snow', 'icon': '13d'}], 'clouds': 66, 'pop': 1, 'rain': 4.28, 'snow': 0.43, 'uvi': 1.91}, {'dt': 1637636400, 'sunrise': 1637619607, 'sunset': 1637655425, 'moonrise': 1637665380, 'moonset': 1637631600, 'moon_phase': 0.61, 'temp': {'day': 3.2, 'min': -0.16, 'max': 4.73, 'night': 2.1, 'eve': 2.99, 'morn': -0.09}, 'feels_like': {'day': -1.91, 'night': -2.09, 'eve': -1.4, 'morn': -4.88}, 'pressure': 1018, 'humidity': 39, 'dew_point': -9.6, 'wind_speed': 7.58, 'wind_deg': 296, 'wind_gust': 14.57, 'weather': [{'id': 803, 'main': 'Clouds', 'description': 'broken clouds', 'icon': '04d'}], 'clouds': 57, 'pop': 0.13, 'uvi': 1.85}, {'dt': 1637722800, 'sunrise': 1637706069, 'sunset': 1637741798, 'moonrise': 1637755140, 'moonset': 1637720880, 'moon_phase': 0.64, 'temp': {'day': 5.44, 'min': 1.25, 'max': 6.84, 'night': 3.88, 'eve': 5.59, 'morn': 1.62}, 'feels_like': {'day': 1.33, 'night': 3.88, 'eve': 2.66, 'morn': -1.68}, 'pressure': 1019, 'humidity': 37, 'dew_point': -8.13, 'wind_speed': 6.67, 'wind_deg': 292, 'wind_gust': 11.81, 'weather': [{'id': 800, 'main': 'Clear', 'description': 'clear sky', 'icon': '01d'}], 'clouds': 2, 'pop': 0.19, 'uvi': 1.94}, {'dt': 1637809200, 'sunrise': 1637792530, 'sunset': 1637828172, 'moonrise': 1637845140, 'moonset': 1637809740, 'moon_phase': 0.67, 'temp': {'day': 7.22, 'min': 2.93, 'max': 8.64, 'night': 5.32, 'eve': 6.79, 'morn': 2.93}, 'feels_like': {'day': 5.89, 'night': 4.17, 'eve': 4.59, 'morn': 2.93}, 'pressure': 1020, 'humidity': 46, 'dew_point': -3.69, 'wind_speed': 4.36, 'wind_deg': 304, 'wind_gust': 7.18, 'weather': [{'id': 803, 'main': 'Clouds', 'description': 'broken clouds', 'icon': '04d'}], 'clouds': 51, 'pop': 0, 'uvi': 1.91}, {'dt': 1637895600, 'sunrise': 1637878991, 'sunset': 1637914549, 'moonrise': 1637935260, 'moonset': 1637898300, 'moon_phase': 0.71, 'temp': {'day': 6.97, 'min': 3.01, 'max': 8.42, 'night': 5.88, 'eve': 7.3, 'morn': 3.01}, 'feels_like': {'day': 5.18, 'night': 3.45, 'eve': 5.42, 'morn': 3.01}, 'pressure': 1026, 'humidity': 31, 'dew_point': -9.12, 'wind_speed': 3.55, 'wind_deg': 304, 'wind_gust': 7.62, 'weather': [{'id': 800, 'main': 'Clear', 'description': 'clear sky', 'icon': '01d'}], 'clouds': 0, 'pop': 0, 'uvi': 1.91}, {'dt': 1637982000, 'sunrise': 1637965451, 'sunset': 1638000927, 'moonrise': 0, 'moonset': 1637986680, 'moon_phase': 0.75, 'temp': {'day': 6.21, 'min': 2.73, 'max': 8.07, 'night': 5.72, 'eve': 7.05, 'morn': 2.73}, 'feels_like': {'day': 5.25, 'night': 5.72, 'eve': 5.82, 'morn': 2.73}, 'pressure': 1032, 'humidity': 29, 'dew_point': -10.67, 'wind_speed': 2.91, 'wind_deg': 315, 'wind_gust': 8.52, 'weather': [{'id': 800, 'main': 'Clear', 'description': 'clear sky', 'icon': '01d'}], 'clouds': 0, 'pop': 0, 'uvi': 2}, {'dt': 1638068400, 'sunrise': 1638051911, 'sunset': 1638087308, 'moonrise': 1638025440, 'moonset': 1638074820, 'moon_phase': 0.77, 'temp': {'day': 8.12, 'min': 3.13, 'max': 10.11, 'night': 7.87, 'eve': 8.88, 'morn': 3.13}, 'feels_like': {'day': 7.52, 'night': 7.87, 'eve': 8.88, 'morn': 1.93}, 'pressure': 1036, 'humidity': 35, 'dew_point': -6.52, 'wind_speed': 1.58, 'wind_deg': 61, 'wind_gust': 2.19, 'weather': [{'id': 800, 'main': 'Clear', 'description': 'clear sky', 'icon': '01d'}], 'clouds': 2, 'pop': 0, 'uvi': 2}, {'dt': 1638154800, 'sunrise': 1638138370, 'sunset': 1638173690, 'moonrise': 1638115740, 'moonset': 1638162840, 'moon_phase': 0.8, 'temp': {'day': 9.53, 'min': 6.32, 'max': 10.98, 'night': 8.97, 'eve': 9.9, 'morn': 6.32}, 'feels_like': {'day': 9.53, 'night': 7.84, 'eve': 8.79, 'morn': 6.32}, 'pressure': 1027, 'humidity': 41, 'dew_point': -3.24, 'wind_speed': 2.37, 'wind_deg': 111, 'wind_gust': 5.79, 'weather': [{'id': 804, 'main': 'Clouds', 'description': 'overcast clouds', 'icon': '04d'}], 'clouds': 100, 'pop': 0, 'uvi': 2}]\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NqcBrQz6dzZ8",
        "outputId": "22c6e69d-0862-4694-e239-e661fc9d2af3"
      },
      "source": [
        "week_data = transform(json_data)"
      ],
      "execution_count": 57,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "['2021-11-23', 3.2, -0.16, 4.73]\n",
            "['2021-11-24', 5.44, 1.25, 6.84]\n",
            "['2021-11-25', 7.22, 2.93, 8.64]\n",
            "['2021-11-26', 6.97, 3.01, 8.42]\n",
            "['2021-11-27', 6.21, 2.73, 8.07]\n",
            "['2021-11-28', 8.12, 3.13, 10.11]\n",
            "['2021-11-29', 9.53, 6.32, 10.98]\n",
            "Transform Done\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QWO09xcU4Fae",
        "outputId": "2f100951-ac27-4bcf-c731-b036bbb935eb"
      },
      "source": [
        "load(week_data)"
      ],
      "execution_count": 60,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Load Done\n",
            "Number of data inserted:  7\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QoifrJyJge7-"
      },
      "source": [
        "# !pip install sqlalchemy==1.3.2"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zEHiSs627Y9Q"
      },
      "source": [
        "import sqlalchemy"
      ],
      "execution_count": 52,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "15qFhvnT7hrD",
        "outputId": "3805d198-cdd6-4329-faf2-2e3c152cbb23"
      },
      "source": [
        "%load_ext sql\n",
        "\n",
        "%sql postgresql://choyoura:Choyoura!1@ssde.cnqux5xggmn5.us-east-2.redshift.amazonaws.com:5439/dev"
      ],
      "execution_count": 53,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'Connected: choyoura@dev'"
            ]
          },
          "metadata": {},
          "execution_count": 53
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 225
        },
        "id": "EFTZoM8y7lGZ",
        "outputId": "693ab9e8-cfe1-4165-df5d-e2b3f5c22e95"
      },
      "source": [
        "%%sql\n",
        "\n",
        "select * from choyoura.weather_forecast;"
      ],
      "execution_count": 61,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * postgresql://choyoura:***@ssde.cnqux5xggmn5.us-east-2.redshift.amazonaws.com:5439/dev\n",
            "7 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<table>\n",
              "    <thead>\n",
              "        <tr>\n",
              "            <th>date</th>\n",
              "            <th>temp</th>\n",
              "            <th>min_temp</th>\n",
              "            <th>max_temp</th>\n",
              "            <th>updated_date</th>\n",
              "        </tr>\n",
              "    </thead>\n",
              "    <tbody>\n",
              "        <tr>\n",
              "            <td>2021-11-23</td>\n",
              "            <td>3.2</td>\n",
              "            <td>-0.16</td>\n",
              "            <td>4.73</td>\n",
              "            <td>2021-11-22 06:09:59</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2021-11-24</td>\n",
              "            <td>5.44</td>\n",
              "            <td>1.25</td>\n",
              "            <td>6.84</td>\n",
              "            <td>2021-11-22 06:09:59</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2021-11-25</td>\n",
              "            <td>7.22</td>\n",
              "            <td>2.93</td>\n",
              "            <td>8.64</td>\n",
              "            <td>2021-11-22 06:09:59</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2021-11-26</td>\n",
              "            <td>6.97</td>\n",
              "            <td>3.01</td>\n",
              "            <td>8.42</td>\n",
              "            <td>2021-11-22 06:09:59</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2021-11-27</td>\n",
              "            <td>6.21</td>\n",
              "            <td>2.73</td>\n",
              "            <td>8.07</td>\n",
              "            <td>2021-11-22 06:09:59</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2021-11-28</td>\n",
              "            <td>8.12</td>\n",
              "            <td>3.13</td>\n",
              "            <td>10.11</td>\n",
              "            <td>2021-11-22 06:09:59</td>\n",
              "        </tr>\n",
              "        <tr>\n",
              "            <td>2021-11-29</td>\n",
              "            <td>9.53</td>\n",
              "            <td>6.32</td>\n",
              "            <td>10.98</td>\n",
              "            <td>2021-11-22 06:09:59</td>\n",
              "        </tr>\n",
              "    </tbody>\n",
              "</table>"
            ],
            "text/plain": [
              "[(datetime.date(2021, 11, 23), 3.2, -0.16, 4.73, datetime.datetime(2021, 11, 22, 6, 9, 59)),\n",
              " (datetime.date(2021, 11, 24), 5.44, 1.25, 6.84, datetime.datetime(2021, 11, 22, 6, 9, 59)),\n",
              " (datetime.date(2021, 11, 25), 7.22, 2.93, 8.64, datetime.datetime(2021, 11, 22, 6, 9, 59)),\n",
              " (datetime.date(2021, 11, 26), 6.97, 3.01, 8.42, datetime.datetime(2021, 11, 22, 6, 9, 59)),\n",
              " (datetime.date(2021, 11, 27), 6.21, 2.73, 8.07, datetime.datetime(2021, 11, 22, 6, 9, 59)),\n",
              " (datetime.date(2021, 11, 28), 8.12, 3.13, 10.11, datetime.datetime(2021, 11, 22, 6, 9, 59)),\n",
              " (datetime.date(2021, 11, 29), 9.53, 6.32, 10.98, datetime.datetime(2021, 11, 22, 6, 9, 59))]"
            ]
          },
          "metadata": {},
          "execution_count": 61
        }
      ]
    }
  ]
}