Getting Weather Data

I decided that I wanted to ingest some weather data through a different means, this time combining Google API scripts to retrieve the data from the API and another script to connect to the MySQL Database and deposit the data. Both steps are setup on a schedule. 

Setting up the API Call: 

I followed the instructions from the following medium post to create a function in Google Apps Scripts that would call an API for me. I use it to call the weather API website and retrieve the weather data for where I live. 

These 2 things combined import the data into my Google Sheet as per: 

I have then set this up to run on a 4 hour schedule within Google Sheets using one of their triggers. 

Getting the data into MySQL:

So now that I have the data in Google Sheets I want to regularly import this data into a database as it only stores a single row in google sheet, though I could probably get it to persist here as well. Looking at my options with the JDBC drivers available MySQL could work and Snowflake couldn't easily and I didn't really want to use a Keboola Flow several times a day to track this. 

I found the following post and modified the code as needed for my weather data and MySQL credentials. I then set that to run on a schedule as well so the data would get imported into the MySQL database on a regular basis and I can look to integrate it with my parkrun and step data to look for correlations between times / number of steps and the weather. 

Weather API: 

MySQL Table Script:

	Create TABLE weather (
	LocationName VARCHAR(100),
	LocationRegion VARCHAR(100),
	LocationCountry VARCHAR(100),
	LocationLat VARCHAR(100),
	LocationLon VARCHAR(100),
	LocationTzId VARCHAR(100),
	LocationLocaltimeEpoch VARCHAR(100),
	LocationLocaltime VARCHAR(100),
	CurrentLastUpdatedEpoch VARCHAR(100),
	CurrentLastUpdated VARCHAR(100),
	CurrentTempC VARCHAR(100),
	CurrentTempF VARCHAR(100),
	CurrentIsDay VARCHAR(100),
	CurrentConditionText VARCHAR(100),
	CurrentConditionIcon VARCHAR(200),
	CurrentConditionCode VARCHAR(100),
	CurrentWindMph VARCHAR(100),
	CurrentWindKph VARCHAR(100),
	CurrentWindDegree VARCHAR(100),
	CurrentWindDir VARCHAR(100),
	CurrentPressureMb VARCHAR(100),
	CurrentPressureIn VARCHAR(100),
	CurrentPrecipMm VARCHAR(100),
	CurrentPrecipIn VARCHAR(100),
	CurrentHumidity VARCHAR(100),
	CurrentCloud VARCHAR(100),
	CurrentFeelslikeC VARCHAR(100),
	CurrentFeelslikeF VARCHAR(100),
	CurrentVisKm VARCHAR(100),
	CurrentVisMiles VARCHAR(100),
	CurrentUv VARCHAR(100),
	CurrentGustMph VARCHAR(100),
	CurrentGustKph VARCHAR(100),
	CurrentAirQualityCo VARCHAR(100),
	CurrentAirQualityNo2 VARCHAR(100),
	CurrentAirQualityO3 VARCHAR(100),
	CurrentAirQualitySo2 VARCHAR(100),
	CurrentAirQualityPm25 VARCHAR(100),
	CurrentAirQualityPm10 VARCHAR(100),
	CurrentAQUsepaindex VARCHAR(100),
	CurrentAQGbdefraindex VARCHAR(100));

view raw weatherApi.sql hosted with ❤ by GitHub