BigQuery/Cloud Function — Mail Daily Report

En este post les mostraré como hacer un reporte diario de una base de datos almacenada en BigQuery.

Usaremos 4 modulos de Cloud

• BigQuery
• Cloud Function
• Cloud Scheduler
• Pub/Sub

Adicional usaremos la API de sendgrid para poder ejecutar el envío de mails.

Será un ejemplo sencillo para poder obtener el estado de los datos que estamos almacenando, todo dependerá de la complejidad de la Query que hagamos.

En primer lugar iremos a la página de sendgrid sendgrid y nos crearemos una cuenta.

Una vez registrados iremos a “Email API” → “Integration Guide” → “Web API”

Seleccionamos Python

Y presionamos “Create Key”, esa key que nos genera la guardaremos ya que es lo que usaremos más adelante.

En lo que respecta a Cloud primero debemos activar los 4 módulos mencionados, para el ejemplo utilizaré una de las bases de datos públicas que ofrece Google.

Quiero recalcar que independientemente de que el almacenamiento en BigQuery supere el límite o las querys diarias procesen datos por encima del límite, el costo de la implementación será 0.

Lo primero que haremos será crear nuestro tópico en pub/sub como se ve en la foto

Esto lo que hará será suscribirse al scheduler (que haremos a continuación) y activará nuestra cloud function.

Get Gonzalo Galante’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

En Cloud Scheduler configuraremos el siguiente Chron

En Frequency podemos poner en el formato unix-cron cada cuanto queremos hacer que nuestro Scheduler active (a lo que le este pegando, en nuestro caso un pub/sub)

En la segunda parte el target será Pub/Sub lo cual habilitará otra sección donde elegiremos cual tópico de pub/sub activar.

El resto de la configuración en esta ocasión lo dejamos por default.

Ahora generamos nuestra Cloud Function de la siguiente manera

Runtime environment variables

Aqui pondremos en “Name” “EMAIL_API_KEY” y en value en api key que obtuvimos en sendgrid.

Todas las variables de entorno en Cloud Function deben instanciarse aca, si lo hacemos directamente dentro de la función no la tomara.

Por último configuraremos el código de nuestra Cloud Function

Seleccionaremos como “Runtime” Python 3.7

requirements.txt

# Function dependencies
google-cloud-bigquery
pandas
sendgrid
python_http_client

main.py

import base64
from google.cloud import bigquery
import pandas as pd

def hello_pubsub(data, context):
    import os
    from sendgrid import SendGridAPIClient
    from sendgrid.helpers.mail import Mail, Email
    from python_http_client.exceptions import HTTPError
    
    client  = bigquery.Client(project="project-id")

    df = client.query('''
    SELECT visitId
    FROM
        `projectid.dataset.table`
        #note that in the second . when copying the id originally is: you have to change it
        #"medium-336401.bigquery-public-data:chicago_taxi_trips.taxi_trips"
        "medium-336401.bigquery-public-data.chicago_taxi_trips.taxi_trips"
    LIMIT 10
    ''')
    
    log = pd.DataFrame(df)
    print(log)

    #Send mail after query

    message = Mail(
        from_email='mail@gmail.com',
        to_emails='mail@gmail.com',
        subject='subject mail',
        html_content='<strong>Body mail report</strong>'%log)
    try:
        sg = SendGridAPIClient(os.environ['EMAIL_API_KEY'])
        response = sg.send(message)
        print(response.status_code)
        print(response.body)
        print(response.headers)
    except Exception as e:
        print(e)

La función lo que hará será una query en la tabla que le seleccionamos y los datos los almacena en la variable log, luego llamara a la API de sendgrid y enviará un mail con esos datos más el mensaje que le seteamos.