Environment Variables

This article is part of a series on Variables.


Overview

Environment variables are name:value pairs that are stored inside the Matillion ETL client and fully configurable by its users. Unlike Job Variables, Environment Variables can be used throughout the client, in configurations and in all jobs through many components.
 

Creating Environment Variables

Environment variables must be declared before being used. You set a variable by selecting ProjectManage Environment Variables.

The Manage Environment Variables dialog lists all variables along with their details:

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.

Settings for each variable can be seen in the right-hand panel when selected and can be edited using the pencil icon.

Note: A variable's default value can be left blank. However, failing to give a reasonable default value may cause problems in validating components that use that variable. For example, validating an SQL Query component that expects a datetime variable that does not have a datetime format default value. In such a case, the component may run in spite of the error, especially if that default value is not used (if the value has been updated before that part of the job run).

A variable can be added by clicking the icon and edited by clicking on a field. A new dialog will appear.

Variables can also be edited via Text Mode by clicking the checkbox.

The above properties (Name, Type, Behaviour) of each variable can be set here. In addition to those, a description can be written for the variable. This description has no consequence or functionality beyond being present in the dialog for editing the variable.



A default value for this variable in each available environment can also be entered on the right-hand side.

 

Setting Environment Variable Values

When a job begins, all variables are initialised with their default value that is set in the Project→Manage Environment Variables menu. The real power of a variable is that its value can be updated through the following methods:

 

Iteration Components

Iteration components work by setting variables to a new value for each iteration. So you must define variables you wish to iterate in advance, and then use them when configuring iteration components. Iterations can be run in parallel too - in that case the variable scope must be set to Copied to ensure each parallel components sees its own Copied copy of the variable.

 
Python Scripts

Python scripts can push new values into variable using their built-in context object following the structure below:

context.updateVariable("variable", "new value")

See the python component documentation for more information.

 
SQS Runs

Job executions triggered by SQS messages can also set variable values using the optional 'variables' item when sending the message. The syntax shows in the SQS Integration page shows where variables can be set on the incoming message.
 

See the Integration with Amazon SQS documentation for more information.
 
Variable Exports

The "Export" tab allows you to edit mappings between run time information that the component makes available, and variables you have already defined.

All orchestration components and some transformation components support exporting runtime information into variables during job execution. A list of common exports are given below.
 

Exported Value Description Type
Component Name of the component. Text
Completed At Time the component finished running in the job. DateTime
Duration Wall-clock time the component ran for. Numeric
Filename The filename created during staging the data. This will only return a value if the staging objects are NOT cleaned at the end of the load (set in the Load Options) property. Text
Iteration Attempted The number of iterations that this component attempts. This is akin to its successes plus its failures. Numeric
Iterations Generated The number of iterations that have been initiated. Iterations are generated when the component runs and so this will always be the maximum requested iterations (Number of allowed retries + 1) Numeric
Iterations Successful The number of iterations successfully performed. This will either be 0 or 1 since the Retry component is escaped when an iteration succeeds. Numeric
Message Output message from component (usually empty).  Text
Row Count Number of rows the component has run over.
Note: BigQuery does not always report row counts but attempting to retrieve the count will not cause an error.
Numeric
Started At Time the component began running in the job. DateTime
Status Status of the component. Text
Time Taken To Load The amount of time (in seconds) taken to execute the COPY statement to load the data into the target table from storage. Numeric
Time Taken To Stage The amount of time (in seconds) taken to fetch the data from the data source and upload it to storage. Numeric


Component export values are also used to populate the Tasks view and the Run history. To export a value, it must be mapped to an existing variable through the component's 'Export' tab. It is important to map the value to a variable of the correct type according to the above table.

 

Automatic Variables

The following Environment Variables are automatically available without first needing to be defined:
 
Variable Description Type
project_group_name Name of the current project group. Can be set through Project → Rename Project Group. Text
project_group_id Internal ID of the current project group. Numeric
project_name Name of the current project. Can be set through Project → Manage Project. Text
project_id Internal ID of the current project. Numeric
version_name Name of the current version. Versions can be renamed through Project → Rename Version unless locked. See Version Control for more... Text
version_id The internal ID of the current version. Numeric
environment_name Name of the current environment. Can be set by right-clicking the environment in the Environments panel and selecting 'Edit Environment'. See Managing Environments for more... Text
environment_default_schema The name of the default schema for the current environment. Text
environment_database The name of the database for this environment. Text
environment_id The internet ID of the current environment. Numeric
job_name Name of the current job. Can be set by right-clicking the job in the Explorer panel and selecting 'Manage Job'. Text
job_id The internal ID of the current job.  All jobs have a unique ID that can be used to prefer to it within a project. Note that this is not the ID of a particular run of a job. Numeric
component_name The name of the current component, as defined by the user. Components can be renamed by selecting them and editing the 'Name' property. Text
component_id The internal ID of a given component in Matillion ETL. Numeric
run_history_id The ID of a task in Matillion ETL. These can also be viewed via Task History. Numeric
 

All of the above also have an id variable that is an internally generated ID and should be avoided in most cases.

The filename created during staging the data. This will only return a value if the staging objects are NOT cleaned at the end of the load (set in the Load Options) property.
 


Manipulating via the v1 API


Below are some examples for common ways to manipulate Environment Variables via the v1 API. They can be used through cURL by replacing <InstanceAddress>, <GroupName>, <ProjectName>, <EnvironmentName> and <VariableName> with details correct to your resources.

api-user and api-password should be replacement with the username and password you use to access the instance. This user will require API access as well as any permissions required to alter environment variables.


To list all environment variables in an environment:

curl -X GET -o varlist.json -u api-user:api-password "http://<InstanceAddress>/rest/v1/group/name/<GroupName>/project/name/<ProjectName>/environment/name/<EnvironmentName>/variable"
 

Using that list of names, you can add /name/<VariableName> to the cURL command to get the default value for variable <VariableName>

curl -X GET -o varlist.json -u api-user:api-password "http://<InstanceAddress>/rest/v1/group/name/<GroupName>/project/name/<ProjectName>/environment/name/<EnvironmentName>/variable/name/<VariableName>"


This is not useful on its own but can be used to manipulate that Environment Variable in the ways shown below.

Add /value to get the default value for variable <VariableName>

curl -X GET -o varlist.json -u api-user:api-password "http://<InstanceAddress>/rest/v1/group/name/<GroupName>/project/name/<ProjectName>/environment/name/<EnvironmentName>/variable/name/<VariableName>/value"
 

Or add /delete to delete the environment variable

curl -X POST -u api-user:api-password "http://<InstanceAddress>/rest/v1/group/name/<GroupName>/project/name/<ProjectName>/environment/name/<EnvironmentName>/variable/name/<VariableName>/delete" 
 

Or /set/value/<NewValue> to update this Environment Variable to a new default value 

curl -X POST -u api-user:api-password "http://<InstanceAddress>/rest/v1/group/name/<GroupName>/project/name/<ProjectName>/environment/name/<EnvironmentName>/variable/name/<VariableName>/set/value/<NewValue>"