About Custom Variables
- Print
- PDF
About Custom Variables
- Print
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
summary
Custom Variables are variables that can be embedded in ETL Configuration, Data Mart Configuration, dbt Job Setting, and other configuration items.
Custom Variable values are expanded when the job is run.
Examples of Use
For example, it may be used for the following purposes
- Embedded in the SQL statement on the Data Source side.
The date and time of the ETL Job execution is expanded to filter the rows to be transferred.
- Embedded in the Data Destination table name in the form of a suffix.
The sharding table can be created by expanding the job execution date and time.
Custom Variable Loop Execution
Workflow definitions allow for pseudo-loop processing by sequentially expanding multiple values for tasks with embedded Custom Variables.
See Custom Variable Loop Execution for more information.
Warning when embedding Custom Variables in ETL Configuration
- Custom Variables defined on the Data****Source side cannot be embedded in the ETL Configuration on the Data Destination side. Similarly, Custom Variables defined on the Data Destination side cannot be embedded in ETL Configuration items on the Data Source side.
- If Custom Variables with the same name are defined in both Data****Source and Data Destination, the Custom Variable in the Data Destination takes precedence in the following cases. In this case, the expanded value of the Custom Variable on the Data Destination side is also expanded to the Custom Variable on the Data Source side.
- During workflow loop execution
- Default Value of Column Setting in ETL Configuration STEP2
- Job execution using the TROCCO API
- Execution of a job with a value for each variable
Custom Variable Types
String Custom Variable
You can expand strings at job execution time.
When defining, set a fixed string.
Cases where fixed strings are used
In some cases, Custom Variables of strings are not expanded (overwritten) when the job is executed, and a fixed string is used as is.
For example, fixed strings are used in the following cases
- When the Custom Variable expansion method selected at job execution is either expansionbased onthe current time or expansion based on a specified date and time.
- If the job is scheduled to run
- When a loop is selected for a period of time (relative specification) in****the Loop Execution by Custom Variable of the Workflow Task.
Custom Variables for Time and Date
You can expand the time relative tothe base date and time when expanding a value into a Custom Variable.
The date and time to be expanded is determined by the time zone specified here, and the values are expanded in the specified date format.
For example, if the base date and time is "2024-04-01 00:00 : 00 (JST)" in the following case, the value 2024-03-31 00:00:00 is expanded.
Note that there is a difference between Time/Date (queued) and Time/Date (runtime) in terms of the base date/time for expanding values into Custom Variables.
For more information, please refer to the reference date and time below.
How to Expand Custom Variables
When executing a job, you can choose from the following three options
- Expand based on the current time: The starting time of the job execution is used as the base date and time.
- Expand based on a specified date and time: The reference date and time can be defined manually.
- Value Per Variable: You can manually define the value to be expanded into a Custom Variable.
Note that when a Job is executed according to Schedule Setting, the start time of the Job execution becomes the reference date and time.
Additional information on specifications
Reference Date and Time
There is a difference between Time/Date (queued) and Time/Date (runtime) in terms of the base date/time for expanding values into Custom Variables.
- Time and date (runtime):
- The value is expanded based on the start date and time of the job in question.
- For time and date (at queueing time):
- The value is expanded based on the date and time when the execution of the corresponding job was triggered.
Difference between the two reference dates and times for Workflow Jobs
The difference between the base date and time**(at queueing time)** and the base date and time**(at runtime)** is more pronounced at the time the Workflow Job is executed.
- Time and date (at queueing time)
- If there are tasks (including tasks defined in child Workflows) with embedded time/date (queuing time) that exist in the Workflow definition, their base date/time will all be the date/time when the Workflow Job first started executing.
- The base date and time of the task that is executed as a result of the automatic retry of the Workflow Job is also the date and time when the Workflow Job first started executing.
- Time and date (at runtime)
- On the other hand, if a task has a time/date (runtime) embedded in the Workflow definition, the base date/time for those tasks is the date/time when the job in question actually started executing.
In the case of an operation that uses Job Settings to execute jobs, the use of time and date (when queuing) is recommended.
This is because in the unlikely event that a job fails and is rerun the next day or later, the Custom Variable can be expanded with the base date and time of the previous job.
Custom Variable Deployment Specifications for Workflow Jobs
When Custom Variable Loop Execution is set up.
Custom Variable Loop Executionexpands the value of a Custom Variable that is the target of a loop in a Custom Variable Loop Executionbased on the settings of the loop executor.
That is, the Custom Variable expansion method selected at the time the Workflow Job is executed is overridden by the value expansion settings of the loop executor.
When a Workflow definition is embedded within a Workflow definition
The former is defined as the parent Workflow and the latter as the child Workflow.
When a child workflow is looped with a Custom Variable, the expanded value of the Custom Variable that was the target of the loop is inherited when the child workflow is executed.
That is, the expanded value of a Custom Variable is expanded to a Custom Variable of the same name in the child workflow task.
Was this article helpful?