Google uses BigQuery for Automation in exporting Mails

Data accessibility and analysis may be a crucial part of getting value from your data. While there are many methods to look at data when it involves BigQuery, one common way is to export query results as an email on a scheduled basis. This lets end-users get an email with a link to the foremost recent query results, and maybe a good solution for anyone trying to find daily statistics on business processes, monthly summaries of website metrics, or weekly business reviews. regardless of the query could also be, stakeholders who need the knowledge can access data easily via email for relevant insights.

In this post, we’ll describe how to simply automate exporting results from BigQuery to email.

Design considerations for results emails

An important design consideration is that the size and complexity of the info. confine mind the dimensions constraints for email attachments and for exporting large queries from within BigQuery. within the case that the query results are over 1 GB, BigQuery will output the results into multiple tables. As a result, you’d got to send multiple CSV attachments within the email.

If you’ve got G Suite access, you’ll do that using scheduled Apps Script, which uses the BigQuery API to run the query and export the results to a Google Sheet. A time-based trigger on the script will refresh the info at scheduled intervals. With this, you’ll easily send an email with a link to the Sheet using the Gmail Service.

This method depends on G Suite. For a more general solution, we recommend using Google Cloud because the primary solution to automate BigQuery exports to an email. It involves a few Google Cloud products and therefore the SendGrid API for sending the emails. Here’s the way to do this.

Automating BigQuery results to an email

We’ll walk you thru the way to build an automatic process to export BigQuery results into the email, starting with the steps and a glance at the architecture diagram.

1. Create a Pub/Sub topic that will trigger your Cloud Functions code to run.
2. found out a BigQuery dataset and Cloud Storage bucket for your exports.
3. Build a Cloud Function with the code that runs the query, export results, and sends an email.
4. Create a Cloud Scheduler job tied to the Pub/Sub topic to automatically run the function on a scheduled basis.

Here’s a glance at the architecture of this process

Within this architecture, you’ll see:

1. Cloud Scheduler: A Cloud Scheduler job invokes the Pub/Sub topic to schedule the e-mail export periodically.
2. Pub/Sub: A Pub/Sub topic triggers the Cloud Function.
3. Cloud Function: A Cloud Function subscribes to the Pub/Sub topic and runs the code calling the BigQuery and Cloud Storage APIs.
4. BigQuery: The BigQuery API generates the query results, stores them during a table, then exports the results as a CSV into Cloud Storage.
5. Cloud Storage: A Cloud Storage bucket stores the CSV file. The Cloud Storage API generates a signed URL for the CSV that’s sent out as an email to users.

Last, the SendGrid API sends an email with the link to the signed URL to the required recipients.
Getting started with email exports

There are a couple of one-time setup steps associated with storing data and sending emails once you begin this process. First, create a BigQuery dataset which will host the tables created for every export. for instance, if you would like to receive an email a day, this dataset would have a table for every daily export with a naming convention like “daily_export_${TIMESTAMP}.” Since this dataset can quickly increase in size, we recommend setting a default table expiration time. This way, the tables holding outdated data are often deleted.

Next, create a Cloud Storage bucket to host the exported CSV files from BigQuery. almost like the dataset expiration time, the bucket lifecycle management configuration can automatically delete the CSV or move the file to a special storage class after the time-frame defined within the “Age” condition.

The final setup step involves configuring access to the SendGrid API. to try to do this, create an account and generate a SendGrid API key, which can allow the Cloud Function to authenticate with the e-mail API and send an email. The free tier pricing for SendGrid applies for 40,000 messages per day for the primary 30 days, then 100 per day forever. (We’ll get to the implementation of the API within the next section.)

How to reduce cost of Google Cloud

Google announces another API for Cloud Healthcare

Google’s AI-powered product for banks processing PPP loans

Top 5 Use case of Big Data and AI in Real Life

Gaurav Lokhande

See author's posts