About Custom Variables
- 17 Jul 2024
- Print
- PDF
About Custom Variables
- Updated on 17 Jul 2024
- Print
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
summary
カスタム変数とは、転送設定・データマート定義・dbtジョブ設定などの設定項目に埋め込むことができる変数です。
カスタム変数の値は、ジョブの実行時に展開されます。
Examples of Use
For example, it may be used for the following purposes
- Embedded in the SQL statement on the source side.
The job execution date/time is expanded to filter the rows to be transferred.
- Embedded in the 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
In a workflow definition, multiple values can be sequentially expanded for tasks with embedded custom variables to create a pseudo-loop processing.
See Custom Variable Loop Execution for details.
Warning when embedding custom variables in transfer settings
- Custom variables defined on the source side cannot be embedded in configuration items on the destination side. Similarly, custom variables defined on the destination side cannot be embedded in configuration items on the source side.
- If custom variables with the same name are defined on the source and destination sides, the custom variable on the destination side takes precedence in the following cases. In this case, the expanded value of the destination custom variable is also expanded to the source custom variable.
- During workflow loop execution
- Default values for column definitions in Transfer Settings STEP2
- Job execution using TROCCO API
- Execution of a job with a value for each variable
Custom variable types
String Custom Variables
You can expand strings at job execution time.
When defining, set a fixed string.
Cases where fixed strings are used
In some cases, custom string variables 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 expanded based on the current time or 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 with a custom variable in the workflow task
Custom variables for time and date
You can expand the time relative to the 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 (queueing time) and time/date (runtime time ) in terms of the base date/time when 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.
- Specify a value for each variable: You can manually define the value to be expanded into a custom variable.
When a job is executed according to the 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 (queueing time) 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 in****time and date (queueing time) and time and date (runtime) is more pronounced when the workflow job is run.
- 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 workflow job's automatic retry is also the date and time when the workflow job first started executing.
- Time and date (at runtime)
- On the other hand, if there are tasks with time/date (runtime ) embedded in the workflow definition, their base date/time is the date/time when the job in the task actually started executing.
For operations that use scheduling to execute jobs, the use of time and date (when queuing ) is recommended.
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.
Expansion specifications for custom variables in workflow jobs
If you have set up a custom variable loop execution
Custom variables targeted for looping in a custom variable loop execution have their values expanded based 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 loop executor's value expansion settings.
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 became the loop target is inherited when the child workflow is executed.
That is, the expanded value of the custom variable is expanded to the custom variable of the same name in the child workflow task.
Was this article helpful?