Expression editors
Overview
The Expression Editor in Matillion ETL allows the user to create expressions in components. From the editor, users can browse from lists of available functions (dependent on platform) and data columns and use them to write and store their own expressions.
Note: There are several Expression Editors in Matillion, that differ depending on the type of component.
To add a new expression, click the [+] in the editor, then provide a name and expression for it. Existing expressions can be removed with [-].
List of available columns
Available input columns are listed. Double-clicking them will add them to the Expression Editor at the current cursor position, or you can drag-and-drop them into the editor. Note that if the list is empty, it is likely because the input component is currently not in a valid state. Re-validate your job and try again.
Name of the selected expression
The name of the current expression can be whatever you like. Although it is a column name, it does get quoted - this means that you may use spaces and other special characters, providing that they are all ASCII characters and the total length does not exceed 127 characters.
Expression
The expression is a valid SQL expression to compute values for the expression. Since this is SQL, references to columns should be enclosed in double-quotes whereas character and date constants should be enclosed in single quotes. Numeric constants should not be quoted.
List of Functions
The function list is provided to help you find the relevant functions you need. Once selected, brief help is displayed along with a link to the full documentation. Double-clicking a function will insert a template for that function call into the editor at the current cursor position.
Note: The list of available functions is platform-dependent. Functions of the same name may work differently depending on the platform. Documentation links will lead to the platform-specific documentation.
Query components
The Expression Editor can be found in Query components. If you go on Advanced mode when configuring the component's properties, the SQL query option will appear, written according to the data model. We are using the Twitter component as an example. Here you can manually choose which columns to read from and apply expressions to.
Calculator and Join components
For the Calculator component, the Expression Editor is found under 'Calculations' in the properties tab. In the example below, we are checking flight delay times and flagging delays as 'long'. We are using two expressions. The first, 'Long Delay Ratio' totals the departure delay and the arrival delay, then divides by the flight time, so delays are relative to the flight's total time. The flight time is set to a decimal. The second expression uses a CASE function to add a flag to flights with more than a 20% delay. They are flagged as a 'long' delay, and this flag is either True or False. Please see the Calculator component page for more information.
With the Calculator component, you may add and remove as many expressions as required. You may refer to previous expressions within the current expression by using its name, enclosed in single quotes.
Similarly, the Expression Editor can be found in the Join component. For Joins, the list of expressions is fixed, one per join. You may select which expression to edit but not add or remove expressions. This should be a Boolean expression, which determines whether rows are considered to match for the purpose of the join.
SQL component
This is how the Expression Editor looks in the SQL component:
In this example we have joined it to an input table called "airports". We use this as the data source to read from.
Bash Script
The image below demonstrates how the Expression Editor looks like in the Bash Script component:
In this example the Bash Script is backing up the table data loaded from a Query component. The Expression Editor in the Python component works in a similar way.