This article is part of a series on Variables.
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 Project → Manage 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 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 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.
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.
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.
|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.
|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.
The following Environment Variables are automatically available without first needing to be defined:
|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>"