Custom Variable Loop Execution
    • PDF

    Custom Variable Loop Execution

    • PDF

    Article summary

    summary

    Custom Variable Loop Execution is a feature that can be set when an ETL Configuration/Data Mart Configuration with embedded Custom Variables is embedded on a Workflow definition.
    This page provides an overview of Custom Variable loop execution and its settings with use cases.

    What is Custom Variable Loop Execution?

    Custom Variable Loop Execution can be set to expand multiple values for a Custom Variable embedded in a task.
    When a task with this Job Setting is executed in a Workflow Job, the process of "expanding the value to a Custom Variable and executing the Job in a manner that takes this into account" is repeated in turn.
    As a result, multiple jobs can be executed in a single task run.

    About Custom Variables and Workflow Definitions

    See below for an overview of each and details on how to set them up.

    use case

    Here are some typical use cases for Custom Variable loop execution.

    Data Source Advertising System Connector to transfer data from multiple account IDs at once.

    When multiple ad account IDs are used in an ad-based Connector, data from multiple ad account IDs can be transferred together.

    Setting Example

    When creating ETL Configuration, embed Custom Variables in the Ad Account ID.
    Incorporate the above ETL Configuration into your Workflow Definition and set it to expand the list of ad account IDs to be forwarded to a Custom Variable.

    Data Source File Storage System Connector to transfer a file with a specific date and time.

    If the file name prefix or suffix contains a date and time, files containing the specified date and time can be transferred together.
    Similarly, files under a folder containing a specific date and time can be transferred together.

    Setting Example

    Embed Custom Variables in the path prefix when creating ETL Configuration.
    Incorporate the above ETL Configuration into your Workflow Definition and set it to expand the list of dates and times to be transferred into a Custom Variable.

    Split and transfer large files from the Data Source database-based Connector.

    When you want to transfer a large amount of data from a database-based Connector, you can split the file and transfer it.
    By splitting the ETL Job into separate jobs, the transfer may be completed in less time than if the jobs were combined into a single job.

    Setup procedure

    Embed Custom Variables in the query when creating ETL Configuration.
    For example, to split a column based on a column of type timestamp, the following WHERE clause would be used in the query

    WHERE column_timestamp BETWEEN "$start_date$" AND "$end_date$"
    

    Incorporate the above ETL Configuration into your Workflow Definition and set a Custom Variable to the date to be deployed so that the Job will be split any number of times.

    Transfer a group of tables with the same schema from the Data Source Database Connector.

    When a database-based Connector has multiple tables with the same schema, multiple tables can be transferred together.

    Setup procedure

    When creating an ETL Configuration, embed Custom Variables in the FROM clause in the query.
    Incorporate the above ETL Configuration into your Workflow Definition and set it to expand the list of table names to be transferred into a Custom Variable.

    Prevent sampling of data transferred from Google Analytics 4

    Due to a specification on the API side of Google Analytics 4, sampling occurs when the size of the data to be acquired in Data Source Google Analytics 4 increases beyond a certain size.
    Sampling acquisition can be avoided by dividing the data to be acquired.

    Setup procedure

    When creating an ETL Configuration, Custom Variables are embedded in the start and end dates of the Data Retrieval Period.
    Incorporate the above ETL Configuration into your Workflow Definition and set a Custom Variable for the date to be deployed so that the Job is divided into small enough pieces that it is not sampled. (e.g., split in 1-day increments)

    Using Data Mart Configuration, partition a table with a specific column as a key.

    A table can be partitioned using specific columns contained in the table as keys.
    For example, if you had a table containing column company_id with values such as Company A, Company B, Company C... and the table contains a column company_id with a value such as "Company A, Company B, Company C...", the table can be split into separate tables for each company.

    Setup procedure

    Custom Variables are embedded in the query and destination tables when creating Data Mart Configurations.
    Below is an example query and destination table.

    • Query Example
    SELECT *
    FROM
      <Ex-Table>
    WHERE
      company_id == $companyId$
    
    • Example of output destination table
    $date$_$companyId$
    

    Incorporate the above Data Mart Configuration into the Workflow Setting to expand the list of values contained in the key columns into Custom Variables.


    In addition to the above, there are many other use cases for Custom Variable loop execution.
    Contact Customer Success for more information.

    Setup procedure

    1. Create ETL Configuration and Data Mart Configuration. At this time, Custom Variables are embedded as appropriate.
      image.png
    Value of a Custom Variable of type String

    To embed a Custom Variable of type string, enter a value. This value is overwritten by the value that is expanded during loop execution.
    Therefore, this value is not used during loop execution. Please note

    1. Create a Workflow definition.

    2. Incorporate Tasks (ETL Configuration and Data Mart Configuration) with Custom Variables embedded in them on the Flow Edit screen.
      image.png

    3. Click on the three-point reader > pencil icon on the right side of the task. The task edit screen will appear.
      image.png

    4. On the Edit Task screen, enable Loop Execution in Custom Variable and select the type of loop.
      image.png

    5. Select the target Custom Variable and set each accordingly.
      For details on each setting item, see "Setting Items for Each Loop Type" below.

    6. Execute Workflow Job.
      Task jobs with Loop Execution enabled for Custom Variables are executed in a loop, with values being expanded in turn in the Custom Variable.
      The following is an execution log of a looped task job.
      image.png

    Setting items for each loop type

    Loop with string expansion

    Enter the string you wish to expand into a Custom Variable.
    Click on Add Loop if you wish to add the string you wish to expand.
    When looping, strings are expanded in the order in which they are entered.
    image.png

    Copy and paste for batch input

    The following text can be batch-entered by copying and pasting

    hoge
    fuga
    piyo
    

    Loop by period (relative specification)

    Set the period, loop interval, and deployment settings accordingly.
    Note that when a loop is executed, the date is expanded in the order of the Custom Variable output image.

    loop-using-custom-variables-2024-08-29-7-5-6

    Loop in query results

    You can run a query on the DWH and expand the resulting values into Custom Variables.
    Loops in query results are supported by the following DWHs.

    • Amazon Redshift
    • Google BigQuery
    • Snowflake

    The following screen capture is an example of "looping through BigQuery query results.

    image.png


    Was this article helpful?