<jupyter_start><jupyter_text>Class 5 Lab: Databases and ETL  Objectives- Configure Google Cloud SQL Databases- Discover Database Security Options - Connect to a MySQL DB via Python- Generate UUIDs in Python- Normalize API request payload- Insert API request payloads into DB tables  RequirementsIn order to follow along, the following will need to be installed:- Python libraries:  - notebook  - [jupyterlab](https://jupyter.org/install)  - pyyaml  - [mysql](https://dev.mysql.com/doc/connector-python/en/connector-python-installation-binary.html)We will also be using the [WeatherAPI](https://www.weatherapi.com/). While not required, it is recommended to sign up for a free account to acquire an API Key and follow along with the lab.  LabIn this lab, we will write an ETL script that extracts data from an API, transforms the payload, and loads it into a MySQL database. We will first create a MySQL database via Google Cloud SQL.Then, we will once again be using the free WeatherAPI. We will use the historical weather API to get historical weather for the last 7 days from New York, Mexico City, and Houston. Feel free to use different cities, if you so choose. We will also write Finally, we'll transform our API payload   I. Configure Google Cloud SQL DatabaseFirst, let's create a new database instance via [Google Cloud SQL](https://cloud.google.com/sql). Cloud SQL is a fully managed, scaleable solution for hosting MySQL, Postgres, and SQL Server Databases. Specifically, the following are managed for you:- Provisioning Hardware- Installing DBMS run time on VMs- Backups- Encryption- User Generation- Networking and SecurityLet's navigate to the [Cloud Console](https://console.cloud.google.com/sql) and begin.0. If you do not already have the Cloud SQL API enabled on your Project, you will need to do this first. Don't worry: you will not be billed for enabling the API. 1. Select Create Instance2. Select your instance type and run time    1. For this lab, we will be using a MySQL Database    2. And we'll use version 8.03. Fill in your Instance Info    1. Select an instance ID. This will be the name of your Cloud SQL Instance, not of the database itself. Don't worry too much about this ID.     2. Select the password for the root user. In general, I would recommend letting Google generate the password for you and do not store it. We will provision user accounts later. Do NOT share root user passwords with anyone.    3. Under Database Version, select MySQL 8.04. Choose region and zonal availability    1. We'll be using us-central1 for environmental reasons, but feel free to choose a region that is closer to you.    2. For zonal availability, we will choose single zone as we don't need high availability for this demo and it costs less. In general, we do recommend multi-zone available when creating production databases, however.5. Customize your instance    1. Machine type: for this demo, let's use a lightweight instance with 1 CPU. For large databases, high memory is a better option.    2. Storage: Select SSD and 10 GB. HDD is a cheaper, lower performance option. Also note that while we can enable automatic storage increases, you cannot decrease the size of your storage. Also note the "Advanced Encryption Option" to provide a customer-managed encryption key.    3. Connections: In production, it is generally preferable to only allow private IP addresses. This restricts access to only computers on the same VPC as your database. For this demo, we will allow a public IP address. But, we can still restrict access through the allowed networks. Right now we will leave this blank and come back later when we create our DB users.    4. Automated Backups: we will leave this on the default settings. Note how GCP manages DB backups for you. In an on-prem set up or user managed DB instance, you would have to create your own backups.    5. Maintenance: we will leave this on the default settins. Note how GCP automates maitenance for you. In an on-prem set up or user managed DB instance, you would have to schedule and perform your own maitenance.    6. We will leave flags empty for now.    7. Under labels, let's add an environment label with the key-value `env: dev` so we now this is not a production database.Now we can press `CREATE INSTNACE` and wait 3-5 minutes for our hardware to be provisioned and for our DB software to be installed. While we wait, let's dive into our security options. Now would also be a good time to install the python-mysql-connector if you have not already done so.  II. Discover Database Security OptionsLet's talk a little bit about our options for securing our database. In general, we would recommend one of four patterns:1. Private IP Address, only2. Public IP Address with Cloud IAM3. Public IP Address with SSL4. Pulic IP Address with whitelisting (encryption optional)Only allowing a Private IP Address restricts access to your DB from computers in the same VPC. Access can also be allowed by authenticating through a VPN. This is the strictest security option you can select, and is generally recommended for Enterprise Companies. It is more strict than anything we will need today.Allowing a Public IP Address allows access to your DB from the Internet. This is not as scary as it sounds. By default, no IP Addresses are whitelisted, which means even though your DB is accessible by the internet, no device may access it. You have three options for access control with a publically accessible database: IAM Roles, SSL, and whitelisting.IAM Roles allow you to provision access to members of your GCP Project, only. This is a great option for accessing your DB from other GCP services as you can provide your VMs with the IAM Roles needed for querying your data. It also allows you to limit what actions certain users can take. For example, you can have read-only roles that can query data, but not create, edit, or delete tables. Remote (i.e. local) access is also feasible, but be careful with storing keys for doing so.  SSL (Secure Socket Layer) is a technology for creating an encrypted link for transfering data. We can require SSL connections to our database, which will require all traffic to include SSL certification. This comes in the form of key files you can downloand from Google Cloud SQL. In general, we prefer security options that do not rely on key files as they can easily be lost or accidentally shared. Always, always store key files in secured Cloud Storage Buckets.Finally, we can restrict traffic to our database by IP Address. We will be using this option for this demo. You can find your IP Address from [here](https://whatismyipaddress.com/). Then we'll create a new user in Google Cloud SQL that can only access our database from our IPv4 Address. Remember your username and password. We'll need that for our connection.  III. Connect to a MySQL Database via PythonNow we're ready to connect to our Google Cloud SQL Instance. Let's take a look at [python-mysql-connector](https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html) documentation.In order to connect to our database, we'll need to:1. Instantiate a mysql connector object2. And pass it the public IP Address for our DB, along with our username and password```pythonimport mysql.connectorcnx = mysql.connector.connect(    user='scott',     password='password',    host='127.0.0.1')```Recall from the APIs for Data lab that including passwords in code is a terrible practice. So we will include this information, as well as our API Keys from the WeatherAPI in a yaml file. Create a YAML File with that information and in the cell below, write the code needed to import our YAML file. You should:1. Open the path to your yaml file2. Store your yaml as a dict called "config"<jupyter_code>import yaml

config_file = open('labClass5.yaml', 'r')
config = yaml.safe_load(config_file)<jupyter_output><empty_output><jupyter_text>In the cell below, import the mysql connector and instantiate a new connection with your config information from above.<jupyter_code>import mysql.connector

client = mysql.connector.connect(**config['connection'])<jupyter_output><empty_output><jupyter_text>In the cell below, instantiate a new `cursor` object and execute the following query:```sqlCREATE DATABASE weather```<jupyter_code>cursor = client.cursor()
cursor.execute('CREATE DATABASE weather')<jupyter_output><empty_output><jupyter_text>IV. Generate UUIDs via PythonRecall from the Class 5 that we want need to replace repeated fields with an id. [UUIDs](https://docs.python.org/3/library/uuid.html) are a common and effective ID format. In the cell below, create a cities list where each element in the list is an object with two keys, `name` and `id` where the ID is a UUID. I will be using three cities: New York, Houston, and Mexico City. You may use any number of cities.<jupyter_code>import uuid

### Your Code Here<jupyter_output><empty_output><jupyter_text>[Data Definition Language](https://www.techopedia.com/definition/1175/data-definition-language-ddl), or DDL, is SQL for creating new database tables. In the cell below, you will see the DDL needed to create a `cities` table with two fields: name and id. Both fields are of type variable character (VARCHAR), which are strings that can have up to a specified number of characters. Also note that to execute the query, we will use the our cursor's `execute()` method.<jupyter_code>ddl = (
    "CREATE TABLE weather.cities("
    "name VARCHAR(255),"
    "id VARCHAR(40)"
    ")"
)
cursor.execute(ddl)<jupyter_output><empty_output><jupyter_text>[Data Manipulation Language](https://www.techopedia.com/definition/1179/data-manipulation-language-dml), or DML, is SQL for adding or updating data in database tables. In the cell below, you will see the code needed to insert each of our cities into our new cities table. Note that in this case, we will pass to our cursor both our DML string and a tuple containing the names and IDs for each city.<jupyter_code>dml = (
    "INSERT INTO weather.cities ("
    "name,"
    "id"
    ")"
    "VALUES ("
    "%s,"
    "%s"
    ")"
)
for city in cities:
    cursor.execute(dml, (city['name'], city['id']))<jupyter_output><empty_output><jupyter_text>To validate that our DML was successful, we will run a query to select each row from our cities table in the cell below. Note that we are using the `fetchall()` method to extract the results of our query.<jupyter_code>cursor.execute("SELECT * FROM weather.cities")
cursor.fetchall()<jupyter_output><empty_output><jupyter_text>Finally, after verifying the data in our cities table, we have to commit the changes to our table using our MySQL client's `commit()` method.<jupyter_code>client.commit()<jupyter_output><empty_output><jupyter_text>BREAK  V. Normalize API Request PayloadsLet's review. To this point we have:1. Created a MySQL database via Google Cloud SQL2. Connected to our database via python3. Created a cities table in our databaseNow that we know how to interact with our database, let's replicate the ETL process we ran in Lab 4, but write our API Request Payloads to a database table, rather than a CSV. Let's start by generating our request bodies. Free accounts for the Weather API can query historical data up to 7 days. The following code creates a list with the last 7 days as well as the components you need to create API calls for the history endpoint.Where indicated, please append to the request_bodies list an API call body for each city in your cities dict and date in the dates list. Note that requests to the history endpoint are formatted as https://api.weatherapi.com/v1/history.json?key={weather_key}&q={city}&dt={date}<jupyter_code>import requests
from datetime import date, timedelta

weather_key = config['weather_key']
end = date.today()
start = end - timedelta(7)
dates = [str(start+timedelta(days=x)) for x in range((end-start).days)]
base_url = 'http://api.weatherapi.com/v1/'
history_api = 'history.json?' 
auth = f'key={weather_key}'


request_bodies = []
### Your Code Here<jupyter_output><empty_output><jupyter_text>Great. Now that we have our request bodies, in the cell below:1. Loop through each request body2. Make a get request3. Convert the request payload to a dict using the .json() method4. And append the payload to the data list<jupyter_code>import requests


data = []
### Your Code Here<jupyter_output><empty_output><jupyter_text>Now that we have our payloads, we need to once again parse them into the daily and hourly forecast. The following code loops through each element in our data and pulls out the city, date, and daily forecast. Given that we have three cities and seven days per city, we should end up with 21 elements in our forecast day list.<jupyter_code>forecast_day = []
for row in data:
    for day in row['forecast']['forecastday']:
        forecast_day.append(
            {'city': row['location']['name'], 'date': day['date'], 'forecast': day['day']}      
        )
print(len(forecast_day))<jupyter_output><empty_output><jupyter_text>In the cell below, replicate the process above for the hourly forecasts. Each element in the forecast_horu list should be a dict with the following keys:- city- date- hour- forecastBecause we have three cities, seven days per city, and 24 hours per day, we should end up with 504 elements in forecast_hour.<jupyter_code>forecast_hour = []

### Your Code here

###
print(len(forecast_hour))<jupyter_output><empty_output><jupyter_text>To normalize our request payloads, we need to add the corresponding city id to each object in our forecast_day and forecast_hour lists. In the cell below, write a for loop that for each object in forecast_day, appends a key `city_id` equal to the city_id corresponding to the city of that forecast.<jupyter_code>### Your Code Here<jupyter_output><empty_output><jupyter_text>In the cell below, write a for loop that for each object in forecast_hour, appends a key city_id equal to the city_id corresponding to the city of that forecast.<jupyter_code>### Your Code Here<jupyter_output><empty_output><jupyter_text>VI. Insert API Request Payloads into Database TablesOk, now we're ready to write our request payloads to our MySQL Database. In the cell below, I am reading from the "create_daily_forecast.sql" file for my DDL. You may instead choose to write your DDL as a string like above. But it is generally preferable to read SQL files into your code than to include the SQL directly as string.<jupyter_code>with open("create_daily_forecast.sql") as ddl:
    cursor.execute(ddl.read())<jupyter_output><empty_output><jupyter_text>Remember from the DML for our cities table that we need to pass our data into the cursor as a tuple. In the cell below, I am looping through the forecast_day list and for each day, creating a tuple with:- A UUID to act as the primary key- city_id- Date- Max Temp- Min TempWe would probably want to include additional fields if we were creating a full weather database, but this is enough for a demo.Once we have our tuples ready, we can use the `executemany()` method, which applies a list of tuples to the our dml code. Also note that we are reading from the "insert_daily_forecast.sql" file for our DML.<jupyter_code>daily_forecast = []
for day in forecast_day:
    daily_forecast.append((
        str(uuid.uuid4()),
        day['city_id'], 
        day['date'], 
        day['forecast']['maxtemp_f'], 
        day['forecast']['mintemp_f'])
    )
    
with open("insert_daily_forecast.sql") as dml:
    cursor.executemany(dml.read(), daily_forecast)<jupyter_output><empty_output><jupyter_text>Once we insert our rows, we'll need to commit the changes to our DB<jupyter_code>client.commit()<jupyter_output><empty_output><jupyter_text>Now to verify the success of our DML, we'll run the following query to get the number of rows in our daily_forecast table. It should match the number of elements in our forecast_day list (21).Note that the cursor.fetchall() method always returns a list of tuples. As such, we will need to first take the first element of the returned list and than the first element of that tuple to get a single number.<jupyter_code>query = 'SELECT COUNT(*) FROM weather.daily_forecast'
cursor.execute(query)
output = cursor.fetchall()
print(f"{output[0][0]} rows inserted")<jupyter_output><empty_output><jupyter_text>In the cell below, please run the DDL to create the hourly forecast table, found in "create_hourly_forecast.sql"<jupyter_code>### Your Code Here<jupyter_output><empty_output><jupyter_text>In the cell below, please run the DML to insert the data from forecast_hour into the newly created hourly_forecast table.<jupyter_code>### Your code Here<jupyter_output><empty_output><jupyter_text>After running your code below, commit the changes in the cell below.<jupyter_code>client.commit()<jupyter_output><empty_output><jupyter_text>Finally, run the cell below to verify your insert was successful. You should have inserted 504 rows (or however many records you had in your forecast_hour list.<jupyter_code>query = 'SELECT COUNT(*) FROM weather.hourly_forecast'
cursor.execute(query)
output = cursor.fetchall()
print(f"{output[0][0]} rows inserted")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.06681773284527337, 'fraction_numerical': 0.005403521004009064, 'mean_word_length': 4.708789386401326, 'pattern_counts': {'":': 0, '<': 89, '<?xml version=': 0, '>': 89, 'https://': 11, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '22', 'dir': 'jupyter-structured-clean-dedup', 'id': '31694', 'license': 'MIT', 'n_tokens_mistral': 4670, 'n_tokens_neox': 4502, 'n_words': 2391, 'path': 'course_labs/class5/labClass5.ipynb', 'repo_name': 'kennyldc/arquitectura-producto-datos', 'size': '17211'}