Grid Variables

This article is part of a series on Variables.

 

Overview

Grid Variables are a special type of Job Variable that can be declared in Matillion ETL. This can be done by right-clicking on any job and selecting 'Manage Grid Variables'.

Grid Variables are 2D arrays that hold scalar values. Headers for columns of the grid can be defined within Matillion but are separate from the data held in that grid. Grids can be used in many components (usually via the 'Use Grid Variable' checkbox in component property dialogs) to, for example, easily populating Table Metadata, passing lists of arrays or as matrices for use in Python scripts. . Below we give just a few examples of their uses.
 

Attached: At the bottom of this page you will find an attached job that can be downloaded and imported into your Matillion ETL instance. Simply select the job with the prefix appropriate for your Platform (RS, BQ, SF for Redshift, BigQuery, Snowflake respectively). This job demonstrates how to iterate over a date-range. It uses a Python script to populate a Grid variable with a list of dates before using that with a Grid Iterator component.

 

Creating Grid Variables

By right-clicking a job and selecting Manage Grid Variables you will be confronted by a dialog box listing all Grid Variables for that particular job. Grid Variables can be created and using the + button and edited using the pencil icon beside each Grid Variable. Grid Variables can also be deleted using the trashcan icon.

Adding or editing a Grid Variable will bring up another dialog box that allows you to name and determine the Grid Variable's properties.

Name: Name of the variable
Type: Matillion ETL Data Type of the variable. For more information on Variable types, please see here.
Behaviour: Determines its 'branch behaviour' inside of a job. That is, how the variable is updated when more than a single job branch is making use of it. For more information on Variable behaviour, please see here.
Visibility: If Private, this variable cannot be discovered and overwritten when this job is called from a Run Orchestration or Run Transformation component.
Description: Write a description for the Grid Variable. This description has no consequence or functionality beyond being present in the dialog for editing the variable and when this job is being called through the Run Transformation and Run Orchestration components.


Below the Grid Variable properties is a list of columns inside the Grid. Each column has a Name and Type. For more information on Types, see here.

Clicking the + button will create new columns. The columns can be further defined by pressing Next.

Here, each column can be given values. This is not always wanted as Grid can be populated in a variety of ways (for example, using the Metadata To Grid component). When you are happy with the Grid data, press 'OK'.

 

Using to include columns from Data Sources.

Grids can be used to populate choices in multiple properties within the Data Staging (Query) components; The Data Sources property, for example, as shown below.

To begin using a grid to fill this form, a new grid can be created for this job containing just a single column, here named 'Columns'.

We fill our lone column with the names of the columns we wish to include in the Data Selection property.

Back on the data staging component, in the Data Selection property the 'Use Grid Variable' checkbox can be filled and our new grid used to select the columns by name.

Using to populate Metadata

In the Create Table component (and Create External Table), Table Metadata can be assigned using Grid Variables by checking the 'Use Grid Variable' box available in the Table Metadata property. When checked, a new dialog box will prompt you for a Grid Variable to take data from, then give a list of metadata properties, each of which can be populated by a Grid column of your choosing.


Using to populate variables

Occasionally, you may find that you have a large number of variables to pass into a jobs along your production line and it is convenient to make a grid defining these variables that can then populate the lists for you. In either Run Transformation or Run Orchestration components, the 'Set Variables' property is used to define job variables for the job to be run. You may notice the 'Use Grid Variable' checkbox that transforms the dialog box when checked.

Simply select the appropriate Grid Variable and then choose from it one column that has values defining variable names and another column that has values defining the corresponding variable values.

 

Using to pass Grids.

Similar to passing regular variables, Grid Variables can also be passed on to jobs called using the Run Orchestration and Run Transformation components. Using the 'Set Grid Variables' property, a list of Grid Variables in the called job are listed. If no action is taken ('Enabled = false' on each) then this Grid Variable will exist in the called job exactly as it is defined in the job variables.

However, the 'Override Values' checkbox can be used to allow us to pass data from Grid Variables in the calling job to those in the called job.

On the 'Grid' setting, column names from the selected Job Grid Variable are listed beside dropdown menus. These menus are populated with column names from the selected Grid Variable from the calling job. In this way, Grid columns can be mapped from the calling job to the called job.

Using the 'Values' setting simply allows the user to manually enter their own data for the selected Job Grid Variable.

 

Grid Variables in Python

Warning: Variables become first class variables in Python and Bash scripts and great care should be taken to avoid naming them in a manner that clashes with key words in either language. We recommend using a prefix (for example, v_) to ensure no such conflicts occur.

It is possible to access and update Grid Variables in Python Scripts.

To get Grid Variable data and load it into a Python array:

context.getGridVariable('<GridName>')

And to place Python array data into a Grid Variable:
context.updateGridVariable('<GridName>', <values>)

Below is an example Python3 script that takes data from one Grid Variable 'people' into an array and prints it before updating a different Grid Variable 'names' using that array.
array = context.getGridVariable('people')

for data in array:
  print(data)

context.updateGridVariable('names', array)
  

Grid-Based Components


Table Metadata To Grid: Take metadata from a table and load it into a grid.
Remove From Grid: Remove rows from a preexisting grid.
Append To Grid: Add rows to a preexisting grid.
Query Result To Grid: Query a table and load resulting data into a grid.