Table Iterator
    • Dark
      Light

    Table Iterator

    • Dark
      Light

    Article Summary

    This article is specific to the following platforms - Snowflake - Redshift - BigQuery.

    Table Iterator

    Loop over rows of data within a table or view and run another component for each row.

    This component implements a simple loop over rows of data in an existing table. It enables you to run an attached component multiple times, each time with different values set on any number of variables taken from columns of that table. Those variables can then be referenced from the attached component.

    To attach the iterator to another component, use the blue output connector and link to the desired component. To detach, right-click on the attached component and click Disconnect from Iterator.

    If you need to iterate more than one component, put them into a separate orchestration job or transformation job and use a Run Transformation or Run Orchestration component attached to the iterator. In this way, you can run an entire ETL flow multiple times, once for each row of variable values.

    The iterations are set up in advance, so the connection to the input table can be closed before any iterations are performed. If the attached component modifies the iteration table, those changes will not be reflected during the current run. Furthermore, to control runaway processes and control resources, only a limited number of rows of a table are considered for iteration. If you are iterating many rows, it is instead recommended that you use the table in a transformation job, and join the table being iterated instead.

    All iterator components are limited to a maximum 5000 iterations.


    Properties

    Snowflake Properties

    PropertySettingDescription
    NameTextA human-readable name for the component.
    ModeSelectBasic: Construct the query using the clickable properties. This is the default setting.
    Advanced: Manually write the SQL query.
    QuerySQL EditorManually write the SQL query. The Metadata Explorer can be used, and the query can be sampled as it is written.
    DatabaseSelectSelect the Snowflake database. The special value, [Environment Default], will use the database defined in the Matillion ETL environment. For more information, read Databases, Tables, & Views.
    SchemaSelectSelect the Snowflake schema. The special value, [Environment Default], will use the schema defined in the Matillion ETL environment. For more information, read Database, Schema, & Share DDL.
    Target Table NameSelectThe name of the target table.
    ConcurrencySelectConcurrent: Iterations are run concurrently. This requires all "Variables to Iterate" to be defined as copied variables, so that each iteration gets its own copy of the variable isolated from the same variable being used by other concurrent executions.
    Sequential: Iterations are done in sequence, waiting for each to complete before starting the next. This is the default setting.
    Note: The maximum concurrency is limited by the number of available threads (2x the number of processors on your cloud instance).
    Column MappingColumn NameThe table column name.
    Variable NameChoose an existing variable name. If you wish to define a new environment variable, click Project and then click Manage Environment Variables.
    To learn more, read Environment Variables.
    Order ByColumn SelectorMove columns to the right-hand box to define the "order by" sequence.
    Note: This property is only available when Concurrency is set to Sequential.
    SortSelectChoose to order by ascending or descending. The default is ascending.
    Note: This property is only available when Concurrency is set to Sequential.
    Break on FailureSelectNo: Attempt to run the attached component for each iteration, regardless of success or failure. This is the default setting.
    Yes: If the attached component does not run successfully, fail immediately.
    Note i: If a failure occurs during any iteration, the failure link is followed. This parameter controls whether it is followed immediately, or after all iterations have been attempted.
    Note ii: This property is only available when Concurrency is set to Sequential. When set to Concurrent, all iterations will be attempted.
    Record Values In Task HistorySelectChoose whether to record iteration values in the Matillion ETL Task History. The default setting is Yes.
    Stop On ConditionSelectSelect Yes to stop the iteration based on a condition specified in the Condition property. The default setting is No.
    For this property to be available, set Concurrency to Sequential.
    ModeSelectSelect the method of creating the condition.
    Simple: A no-code Condition UI will open, where users must specify an Input Variable, Qualifier, Comparator, and Value using drop-down menus and text fields. This is the default setting.
    Advanced: An editor will open, where users must write the condition manually using SQL.
    Condition (Simple mode)Input VariableAn input variable to form a condition around.
    QualifierIs: Compares the input variable to the value using the comparator.
    Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
    ComparatorSelect the comparator. Available comparison operators include "Less than", "Less than or equal to", "Equal to", "Greater than or equal to", "Greater than", and "Blank".
    ValueSpecify the value to be compared.
    Condition (Advanced mode)Text EditorManually write the condition in the editor. This editor accepts conditions written in JavaScript.
    Combine ConditionsSelectUse the defined conditions in combination with one another according to either And or Or.
    This property is only available when Mode is set to Simple.

    Redshift Properties

    PropertySettingDescription
    NameTextA human-readable name for the component.
    ModeSelectBasic: Construct the query using the clickable properties. This is the default setting.
    Advanced: Manually write the SQL query.
    QuerySQL EditorManually write the SQL query. The Metadata Explorer can be used, and the query can be sampled as it is written.
    SchemaSelectSelect the Redshift schema. The special value, [Environment Default], will use the schema defined in the Matillion ETL environment. For more information, read Schemas.
    Target Table NameSelectThe name of the target table.
    ConcurrencySelectConcurrent: Iterations are run concurrently. This requires all "Variables to Iterate" to be defined as copied variables, so that each iteration gets its own copy of the variable isolated from the same variable being used by other concurrent executions.
    Sequential: Iterations are done in sequence, waiting for each to complete before starting the next. This is the default setting.
    Note: The maximum concurrency is limited by the number of available threads (2x the number of processors on your cloud instance).
    Column MappingColumn NameThe table column name.
    Variable NameChoose an existing variable name. If you wish to define a new environment variable, click Project and then click Manage Environment Variables.
    To learn more, read Environment Variables.
    Order ByColumn SelectorMove columns to the right-hand box to define the "order by" sequence.
    Note: This property is only available when Concurrency is set to Sequential.
    SortSelectChoose to order by ascending or descending. The default is ascending.
    Note: This property is only available when Concurrency is set to Sequential.
    Break on FailureSelectNo: Attempt to run the attached component for each iteration, regardless of success or failure. This is the default setting.
    Yes: If the attached component does not run successfully, fail immediately.
    Note i: If a failure occurs during any iteration, the failure link is followed. This parameter controls whether it is followed immediately, or after all iterations have been attempted.
    Note ii: This property is only available when Concurrency is set to Sequential. When set to Concurrent, all iterations will be attempted.
    Record Values In Task HistorySelectChoose whether to record iteration values in the Matillion ETL Task History. The default setting is Yes.
    Stop On ConditionSelectSelect Yes to stop the iteration based on a condition specified in the Condition property. The default setting is No.
    For this property to be available, set Concurrency to Sequential.
    ModeSelectSelect the method of creating the condition.
    Simple: A no-code Condition UI will open, where users must specify an Input Variable, Qualifier, Comparator, and Value using drop-down menus and text fields. This is the default setting.
    Advanced: An editor will open, where users must write the condition manually using SQL.
    Condition (Simple mode)Input VariableAn input variable to form a condition around.
    QualifierIs: Compares the input variable to the value using the comparator.
    Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
    ComparatorSelect the comparator. Available comparison operators include "Less than", "Less than or equal to", "Equal to", "Greater than or equal to", "Greater than", and "Blank".
    ValueSpecify the value to be compared.
    Condition (Advanced mode)Text EditorManually write the condition in the editor. This editor accepts conditions written in JavaScript.
    Combine ConditionsSelectUse the defined conditions in combination with one another according to either And or Or.
    This property is only available when Mode is set to Simple.

    BigQuery Properties

    PropertySettingDescription
    NameTextA human-readable name for the component.
    ModeSelectBasic: Construct the query using the clickable properties. This is the default setting.
    Advanced: Manually write the SQL query.
    QuerySQL EditorManually write the SQL query. The Metadata Explorer can be used, and the query can be sampled as it is written.
    ProjectSelectSelect the Google Cloud project. The special value, [Environment Default], will use the project defined in the environment.
    For more information, read Creating and managing projects.
    DatasetSelectSelect the Google Cloud dataset to load data into. The special value, [Environment Default], will use the dataset defined in the environment.
    For more information, read Introduction to datasets.
    Target Table NameSelectThe name of the target table.
    ConcurrencySelectConcurrent: Iterations are run concurrently. This requires all "Variables to Iterate" to be defined as copied variables, so that each iteration gets its own copy of the variable isolated from the same variable being used by other concurrent executions.
    Sequential: Iterations are done in sequence, waiting for each to complete before starting the next. This is the default setting.
    Note: The maximum concurrency is limited by the number of available threads (2x the number of processors on your cloud instance).
    Column MappingColumn NameThe table column name.
    Variable NameChoose an existing variable name. If you wish to define a new environment variable, click Project and then click Manage Environment Variables.
    To learn more, read Environment Variables.
    Order ByColumn SelectorMove columns to the right-hand box to define the "order by" sequence.
    Note: This property is only available when Concurrency is set to Sequential.
    SortSelectChoose to order by ascending or descending. The default is ascending.
    Note: This property is only available when Concurrency is set to Sequential.
    Break on FailureSelectNo: Attempt to run the attached component for each iteration, regardless of success or failure. This is the default setting.
    Yes: If the attached component does not run successfully, fail immediately.
    Note i: If a failure occurs during any iteration, the failure link is followed. This parameter controls whether it is followed immediately, or after all iterations have been attempted.
    Note ii: This property is only available when Concurrency is set to Sequential. When set to Concurrent, all iterations will be attempted.
    Record Values In Task HistorySelectChoose whether to record iteration values in the Matillion ETL Task History. The default setting is Yes.
    Stop On ConditionSelectSelect Yes to stop the iteration based on a condition specified in the Condition property. The default setting is No.
    For this property to be available, set Concurrency to Sequential.
    ModeSelectSelect the method of creating the condition.
    Simple: A no-code Condition UI will open, where users must specify an Input Variable, Qualifier, Comparator, and Value using drop-down menus and text fields. This is the default setting.
    Advanced: An editor will open, where users must write the condition manually using SQL.
    Condition (Simple mode)Input VariableAn input variable to form a condition around.
    QualifierIs: Compares the input variable to the value using the comparator.
    Not: Reverses the effect of the comparison, so "Equals" becomes "Not equals", "Less than" becomes "Greater than or equal to", etc.
    ComparatorSelect the comparator. Available comparison operators include "Less than", "Less than or equal to", "Equal to", "Greater than or equal to", "Greater than", and "Blank".
    ValueSpecify the value to be compared.
    Condition (Advanced mode)Text EditorManually write the condition in the editor. This editor accepts conditions written in JavaScript.
    Combine ConditionsSelectUse the defined conditions in combination with one another according to either And or Or.
    This property is only available when Mode is set to Simple.

    Variable Exports

    This component makes the following values available to export into variables:

    SourceDescription
    Iteration AttemptedThe number of iterations that this component attempts to reach (Max Iterations parameter).
    Iteration GeneratedThe number of iterations that have been initiated. Iterators terminate after failure so this number will be the successful iterations plus any potential failure.
    Iteration SuccessfulThe number of iterations successfully performed. This is the max iteration number, minus failures and any unattempted iterations (since the component terminates after failure).


    Example

    This example iterates through a table of values. For each row of the table, the attached transformation component runs. The variables are made available to use in that transformation job to filter the incoming data.

    We need to define the variable we intend to iterate, and provide some sensible default values.

    In the configuration for the Iterator, an input table is selected. This has columns for Carrier and State.

    Those table columns are mapping to the defined variables:

    When the transformation runs, the AnalyseFlightData transformation job is executed 4 times, each time setting the carrier_code and state variables.


    Video


    What's Next