Introduction

In the previous tutorial, BigQuery Task, you learned how to create a workflow and use BigQuery Task to save query results to a table. We can expand on the workflow by adding parameters. In our example a parameterized query can be used to filter station names and destination table

By end of the tutorial you will know

What parameters are
How to create Custom Parameters
How to use parameters
parameter limitations and resetting

Scenario

Templating the workflow increases usability. This can be accomplished by using parameters.

Steps

The previous query we used counted trips by station

SELECT
  start_station_name,
  COUNT(1) AS trips
FROM `bigquery-public-data.san_francisco.bikeshare_trips`
GROUP BY 
    start_station_name
ORDER BY
    start_station_name

What can we do if we want to obtain information for just one station? We could add a WHERE clause to filter for the station

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

If we want to filter for a different station we would have to identify the filter in the query and update it each time. Another way to accomplish this is by parameterizing the query.

Magnus has built-in and custom parameters. Built-in parameters are parameters that are always available in every workflow. You can view the list by clicking on Show More under Builtin Parameters. These include var_date, var_daydate, etc.

Custom parameters are parameters created by a user specific for the workflow. Clicking on Add under Custom parameters will prompt to name the parameter. All custom parameter names begin with var_. If you exclude var_ when naming the parameter, it will be automatically added for you.

In the example we want to replace filtered station 'Redwood City Caltrain Station' with a parameter. Under Custom parameter click Add and name the parameter start_station_name. Notice after clicking ok the parameter is listed and prepended with var_. In the parameter input field type the name of the station: Redwood City Caltrain Station.

Parameters have different data types. These include common types such as STRING and NUMBER. When setting data type for parameter think of what values will be set for the parameter and select the data type appropriately.

In addition to parameterizing the station name we can parameterize the source table. Follow the above steps to add a custom parameter named var_source_table with a value of bigquery-public-data.san_francisco.bikeshare_trips

Parameters can be added to a query by typing the parameter name in angle brackets <>. To add the parameter to the query replacebigquery-public-data.san_francisco.bikeshare_trips with `<var_source_table>` and Redwood City Caltrain Station with the <var_start_station_name>.

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

Now whenever the workflow is run it will replace the parameters with the values in the corresponding parameter field.

Parameters can be used throughout the query. Builtin parameters can be used to include date in any portion of the query. Custom parameters can be created to further template the query. Think about how you can modify your own queries to incorporate parameters.

Also, parameters can used in most fields available in a workflow. For example, in the BigQuery Task parameters can be used in the Destination Table field.

Add a custom parameter named var_destination_table. Set the value to a dataset you have access to with the full path being project:dataset.table. Update the destination table in the BigQuery Task by replacing it with <var_destination_table>. When finished save and run your workflow.

Review

This tutorial walked you through templating a query by introducing parameters. You now know how to add a custom parameter and use it in your query along with destination table field.

Additional Documentation

Parameters: https://potensio.zendesk.com/hc/en-us/articles/115003529371-Parameters

View Completed Workflow

A completed version of the workflow in this tutorial can be found at https://magnus.potens.io/?l=dHV0b3JpYWxfcGFyYW1ldGVycw==

What's Next?

Next, we will schedule the workflow and send the query results via email by introducing the Email Task.

Articles > Parameters Tutorial