Introduction

In the previous tutorial, Parameters, you learned how to template a workflow by using custom parameters. In this tutorial you will send the query results via email.

By the end of the tutorial you will know

How to prepare query output for email
How to send text Email
How to send html Email
How to send attachments in Email
How to use parameters as destination of BigQuery Task
How to identify and rest dynamically set custom parameters
How to schedule workflow

Scenario

Prior tutorial workflows were manually run with query results saved to a BigQuery table. What if we wanted data to be sent via email on a daily basis? Email Task will send results to email recipients and using the workflow schedule will enable the email to be sent daily.

Steps

Open your existing workflow and add an E-Mail Task. E-Mail task sends an email when the workflow is executed. The task can be modified to include recipients, body messages, and attachments.

Provide a name for the E-Mail Task. Various email attributes such as subject, to, and body can be assigned. You can either type the values directly into the appropriate field or add parameters. Let’s add parameters.

Create custom parameters var_email_subject and var_email_to. Both with datatype string. Populate the custom parameter fields. Set var_email_subject as My Daily Report and add your email address to var_email_to.

In the E-Mail Task replace the Subject content with the custom parameter <var_email_subject> and To with custom parameter <var_email_to>.

Now we want to send data with the email. Data can be prepared in numerous ways. For our tutorial we will simply send the query result of our BigQuery Task via email by assigning the destination to a parameter.

You previously learned how to create parameters by adding them and setting their value. Custom parameter values can also be set dynamically and populated during the workflow run. One way to set a custom parameter’s value is by setting BigQuery Task Destination to your custom parameter.

Add a custom parameter named var_email_body that will be used for the email body. In the BigQuery Task click on the Destination drop down and select Parameter from the list. Notice that the destination field switched to a drop down. Click on the drop down then look for and select the custom parameter var_email_body.

Now when the workflow is run the output of this Task will be saved to the parameter. However, the parameter value is a single row field, so the output needs to be concatenated to a single line. Update the query in BigQuery Task so that the output is a single line. For example:

SELECT CONCAT('Station Name: ', start_station_name, ' / Trips: ', CAST(trips AS STRING)) AS message
FROM (
    SELECT
        start_station_name,
        COUNT(1) AS trips
    FROM `<var_source_table>`
    WHERE start_station_name = '<var_start_station_name>'
    GROUP BY
        start_station_name
    ORDER BY
        start_station_name
)

This parameter can be used like any other parameter in the workflow. In this tutorial we will use it in the body of the email. Replace the text in E-Mail Task body with the custom parameter <var_email_body>.

Save and run the workflow.

After the workflow successfully runs you will receive the email from the E-Mail Task you created. Notice that the custom parameters used in the E-Mail Task are replaced in the email by the assigned values.

Also notice in the Parameters section of the workflow that var_email_body now have values populated from the previous workflow run. A refresh icon is also present by the custom parameter. Clicking on this icon will reset any value to blank. Click on the reset icon now to clear out the values. Tip: The reset icon is also a good way to visually identify which parameters are dynamically assigned values

The email was pretty simple. It showed a single line with station name and the count of trips. Let’s see how we can enrich the message.

E-Mail Task allows the body to be text or HTML. Click on the dropdown option by Body and select HTML. When the email is sent it the body will now be rendered in HTML.

Let’s enrich the query result to include some HTML.

Back in BigQuery Task the query is currently displaying results for a single table. Let’s change the query to show all start stations and trip counts. Then let’s format the result so it displays in a table when rendered in HTML.

First remove the WHERE clause so that the start station isn’t filtered and add LIMIT 100. Now 100 stations will be listed. Next, we need to prepare to format the results for an HTML table. A simplified HTML table format is: <table><tr><td></td></tr></table>. Where the table wraps the rows <tr> and columns <td>. We have two columns; station name and trips. The first row should be the header and the rest the results of the query. Finally concatenate the result into one row so we can save it to a parameter. The query should look like this

SELECT CONCAT('<table><tr><td>Station Name</td><td>Trips</td></tr>',STRING_AGG(CONCAT('<tr><td>', start_station_name, '</td><td>', CAST(trips AS STRING), '</td></tr>'),''),'</table>') AS message
FROM (
    SELECT
        start_station_name,
        COUNT(1) AS trips
    FROM 
        `<var_source_table>`
    GROUP BY
        start_station_name
    ORDER BY
        start_station_name
    LIMIT 100
)

Save and run the workflow.

If your email application renders results in HTML, you will see a list of station names and trips listed in the email. You can enhance the results further by adding borders, bolding and coloring text, and many other enhancements.

The last workflow run added a good amount of data to the custom parameter var_email_body. There is a workflow size limitation of 5 MB so it is good practice to clear out any dynamically set custom parameters at the end of the workflow.

In the workflow add a Script Task named clear_email_body. In a Script Task you can write a script to manipulate data. For our purpose we want to clear out the parameter so in the Script query area type return null and in Return Parameter click the drop-down list and select var_email_body. When run the Script Task will set var_email_body to null clearing out any values during the workflow run.

We might want to send the data as an attachment to the email. In the E-Mail Task click on the Attachment link. The panel will flip to Advanced Properties. For the Attachment File Name field type MyAttachment. In the dropdown click and select HTML. In the query text field replace any text with the custom parameter <var_email_body>.

Save and run the workflow.

The workflow will now include the data as an attachment. In this tutorial we attached the same data as in the body but when creating your own workflows you can create different BigQuery Tasks and assign to multiple parameters to construct different types of messages and attachments in the E-Mail Task.

Finally, we don’t want to have to manually run the workflow every time. Especially now that we can get the data emailed. It would be nice to get the data every morning. The workflow can be scheduled.

In the Workflow section at the top a workflow can be set as Enabled. Enabling a workflow qualifies it to be scheduled. A workflow that is not enabled will not run on a schedule regardless if a schedule is set.

Click on the Lifetime field. This will bring up a calendar with a start and end date range for when the workflow can be scheduled. Date ranges are limited to 90 days. They cannot be set forever. Update any of your scheduled workflows periodically so that they do not end when the initial 90 days are up. Select a start and end date. Then click Done.

Click on Schedule. This will bring up a window prompting for the frequency and time of when the workflow should be run. Select Each Day for frequency and 5 AM for time. This will set the schedule to be run every day at 5 AM. You can set your own schedule for workflows. For your own workflows you would save the workflow after setting a schedule.

For this tutorial instead unchecked Enabled. Even though we set a schedule unchecking Enabled will disable the schedule for the workflow. Save the workflow.

Review

In this tutorial we introduced E-Mail task and the many ways that an email can be created. We set custom parameters as destination of BigQuery Tasks to dynamically set values that were later used in the E-Mail Task body and attachments. We also learned how to enable and schedule a workflow.