Introduction

In the previous tutorial, Loop Task, we learned how to loop through data and use an Iteration Parameter to filter queries and add to file names and email subject. Our example used subscriber_type which has two values; Customer and Subscriber. We exported two files, for each type, to GCS and received an email with an attached for both.

Scenario

What if we only wanted to export data to GCS just for Subscribers and then email it as an attachment? What if we wanted to send Customer type data in the email body and not as an attachment? We can accomplish this by using Go-To Task.

By the end of the tutorial you will know

how to evaluate an expression
how to set Go-To Tasks on expression results
how to use Misc Task as a placeholder

Steps

Use the workflow created in the previous tutorial. In the Loop Task we are iterating through subscriber type. For subscriber type Subscriber we want to export to GCS. For subscriber type Customer we want to skip the export step. We can do this by adding a Go-To Task.

In the loop, near the bottom where tasks are added to the loop, click the drop down list and select Go-To and set To position to 1. Click Add to add the task in the first position within the loop. Name the Go-To Task as check_subscriber_type.

We want to evaluate the Iteration Parameter and check if the value is a Subscriber. If it is then we want to export the data to GCS. Otherwise we want to skip the export step. To set the evaluation leave Script as JavaScript. For the IF statement click the drop down and select Iteration Parameter var_subscriber_types. Set the field to 1 and click OK. Leave the evaluation as is equal to. Type Subscriber into the input field.

When the IF statement is evaluated as TRUE we want to export to GCS. In Go-To click the first/left most drop down and set task subscriber_type_data. When the IF statement is evaluated as FALSE we want to prepare the email message body. This is set by clicking the rightmost Go-To drop down list and selecting the task to skip to. However, we do not have the task yet and need to create.

Within the loop add BigQuery Task. Because we will skip to this task we want it to appear in the sequence after the exporting to GCS. So add the task to the end of the loop after the GS Export Task. When we skip to this task all tasks before it will not be run as part of the loop. Name the task prepare_message_body and prepare the email body by adding HTML to the query output and setting the WHERE clause to filter on the Iteration Parameter


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,
        trips
    FROM
        `<var_bq_table>`
    WHERE
        subscriber_type = '<var_subscriber_types[1]>'
    ORDER BY
        start_station_name
    LIMIT 100
)

Create a custom parameter var_email_body and in task prepare_message_body set Destination parameter to var_email_body.

Below BigQuery Task prepare_message_body and within the loop add E-Mail task. Name it emailtask_body. Set Subject to <var_email_subject> - <var_subscriber_types[1]>. Set To: to <var_email_to>. Set Body to HTML and in body area type <pre><var_email_body></pre>.

Back in Go-To Task check_subscriber_type set Go-To rightmost drop down to prepare_message_body. When subscriber type is not Subscriber the workflow will jump to preparing the message body and sending an email with the message in the body. Exporting and sending an email with an attachment will be skipped.

As previously mentioned when the IF statement is TRUE the workflow will move to task subscriber_type_data and execute all tasks after it. This includes prepare_message_body and emailtask_body. We want to skip these last two tasks, so we need to add another Go-To Task. We want the Go-To of the task to move to the end of the loop and skip these tasks. We need a place holder task at the end of the loop that we can specify in the Go-To. We can add this placeholder task by using Misc Task.

At the bottom of the loop, after E-Mail Task emailtask_body, add Misc Task and name it end_of_loop. Misc Task can be used to raise errors, provide status updates, and a variety of other actions. In this example we will use Misc Task as place holder in the loop that can be skipped to.

Add a Go-To Task in the loop between E-Mail Task emailtask and BigQuery Task prepare_message_body. Name the task skip_to_the_end. When we reach this point we want to skip over the remaining tasks and jump to end_of_loop task. We want this to happen every time so we can set the IF statement to expression to eval and have 1 = 1 which is always TRUE. Then set Go-To for both results to end_of_loop task.

Reset the value for dynamically set custom parameter var_email_body by adding a Script Task outside the loop and at the end of the workflow with return null and Return Parameter set to var_email_body.

Save and run the workflow.

Review

The workflow will loop through and evaluate the Iteration Parameter. When the value is Subscriber the workflow will go to subscriber_type_data where it will query and filter data for Subscriber types, export the data to GCS, and send the data as an attachment via email. The workflow will then skip to the end of the loop. If the Iteration Parameter in the loop is Customer, the workflow will skip to prepare_message_body task where it will prepare an HTML message that is sent in the body of an email.