Architecture of the course

1. Set the environment

2. Create a GCP Project
   Project name: weather-api-project

3. Activate required APIs with

gcloud services enable cloudfunctions.googleapis.com sqladmin.googleapis.com run.googleapis.com cloudbuild.googleapis.com artifactregistry.googleapis.com eventarc.googleapis.com compute.googleapis.com servicenetworking.googleapis.com pubsub.googleapis.com logging.googleapis.com

4. Setting up a Scheduler

• Cron scheduler: Name: weather_call
  Region: us-central1
  Description: Information about the job
  Frequency: 0 * * * *
  Timezone: UTC
  

• Pub/Sub Topic ID: weather_calls
  Message body: update
  

5. Setting up CloudSQL(MySQL)

• Compute Engine
  Name: weather-vm
  Machine configuration >> general-purpose:
  Series N1
  Machine type: f1-micro
  Allow http and https traffic
  
• VPC Network
  Name: weather-vm-ip
  
• Cloud SQL Database Engine: MySQL 8.0
  Instance ID: weather-db
  Password: admin1234
  Preset: Development
  Machine type: Lightweight 1vCPU 3.75GB
  Storage: SSD 10GB with automatic storage increases
  Network >> Name: connection-db-vm
  --VM IP address reserved
  
• Compute Engine
  --install mysql server
  

sudo apt-get update
sudo apt-get install \
default-mysql-server

--log in to mysql database

mysql -h <weather-db> \
-u root -p

For example,

mysql -h 34.72.233.196 \
-u root -p

--create a weather_db database.

 CREATE DATABASE weather_db;
 SHOW DATABASES; 

--create a weather_db.weather_data table

CREATE TABLE IF NOT EXISTS weather_data (
                        id INT PRIMARY KEY AUTO_INCREMENT,
                        lat FLOAT NOT NULL,
                        lon FLOAT NOT NULL,
                        temperature_c FLOAT NOT NULL,
                        feelslike_c FLOAT NOT NULL,
                        humidity FLOAT NOT NULL,
                        last_updated timestamp,
                        wind_kph FLOAT,
                        name varchar(255));

6. Working-on-topic-subscription

• Pub/Sub topic
  Topic ID: apiweather-extract
  

• Pub/Sub subscription
  Subscription ID: apiweather-extract-subscription
  

7. Creating a Cloud Function

• Function name: pull-weather-data
  
  Region: us-central1
  Environment: 1st gen
  Memory: 512 MiB
  Environmental variables:
  -- api_token: your weather API token
  -- base_url: http://api.weatherapi.com/v1/current.json
  -- q: your country/city
  -- project_id: yourProjectId
  -- region: us-central1
  -- topic_id: apiweather-extract
  --- Entry point: pull_from_api
  --- main.py - https://github.com/team-data-science/course-gcp/blob/main/code/pull_from_api.py
  --- requirements.txt - https://github.com/team-data-science/course-gcp/blob/main/code/pull-weather-data_requirements.txt

• Function name: write-to-sql
  Region: us-central1
  Environment: 2st gen
  Memory: 256 MiB
  Environmental variables:
  -- project_id: yourProjectId
  -- region: us-central1
  -- db_user: root
  -- db_pass: admin1234
  -- db_name: weather_db
  -- instance_name: weather-db
  
  --- Entry point: write_to_database
  --- main.py - https://github.com/team-data-science/Data-Engineering-On-GCP/blob/main/code/write_to_sql.py
  --- requirements.txt - https://github.com/team-data-science/course-gcp/blob/main/code/weather-data-to-db_requirements.txt
  
  Testing - https://github.com/team-data-science/course-gcp/blob/main/code/testing.json

• Function name: weather-data-to-db
  Region: us-central1
  Environment: 1st gen
  Memory: 512 MiB
  Environmental variables:
  -- project_id: yourProjectId
  -- region: us-central1
  -- db_user: root
  -- db_pass: admin1234
  -- db_name: weather_db
  -- instance_name: weather-db
  -- subscription_id: apiweather-extract-subscription
  --- Entry point: push_to_database
  --- main.py - https://github.com/team-data-science/Data-Engineering-On-GCP/blob/main/code/push_to_database.py
  --- requirements.txt - https://github.com/team-data-science/course-gcp/blob/main/code/weather-data-to-db_requirements.txt
  
  Testing - https://github.com/team-data-science/course-gcp/blob/main/code/testing.json

8. Connect CloudSQL to Looker Studio - https://lookerstudio.google.com/
   Instance Connection Name
   Database name: weather_db
   Username: root
   Password: admin1234
   

9. Making Dashboards

CONCAT(lat,",",lon)