Skip to main content
Version: Next(1.7.0)

built-in time variable

1. Overview

need

  1. The user hopes that Linkis can provide some public variables and then replace them during execution. For example, the user runs the same SQL in batches every day, and needs to specify the partition time of the previous day. Writing based on SQL will be more complicated if the system provides a run_date variable It will be very convenient to use.
  2. The user hopes that Linkis supports date pattern calculation, supports writing variables such as &{YYYY-MM-DD} in the code to calculate time variables
  3. The user wants to define variables by himself, such as setting a float variable, and then use it in the code

Target

  1. Support variable replacement of task code
  2. Support custom variables, support users to define custom variables in scripts and task parameters submitted to Linkis, support simple +, - and other calculations
  3. Preset system variables: run_date, run_month, run_today and other system variables
  4. Support date pattern variable, support +, - operation of pattern

The specific technical architecture can refer to: Linkis Custom Variables

2 Custom variable function introduction

     The variable types supported by Linkis are divided into custom variables and system built-in variables. The internal variables are pre-defined by Linkis and can be used directly. Then different variable types support different calculation formats: String supports +, integers and decimals support +-*/, and dates support +-.

2.1 Built-in variables

Currently supported built-in variables are as follows: (The variable values in the following table take 20240229 in leap year as a special date as an example)

variable namevariable typevariable meaningvariable value example
run_dateStringData statistics time (users can set it themselves, the default setting is the day before the current time), if you execute yesterday’s data today, it will be yesterday’s time, the format is yyyyMMdd20240229
run_date_stdStringData statistics time (standard date format), if yesterday’s data is executed today, it is yesterday’s time, the format is yyyy-MM-dd2024-02-29
run_todayStringThe day after run_date (data statistics time), the format is yyyyMMdd20240301
run_today_stdStringThe day after run_date (data statistics time) (standard format), the format is yyyy-MM-dd2024-03-01
run_monStringThe month of run_date, the format is yyyyMM202402
run_mon_stdStringThe month of run_date (standard format), the format is yyyy-MM2024-02
run_month_beginStringThe first day of the month where the data statistics time is located, the format is yyyyMMdd20240201
run_month_begin_stdStringThe first day of the month where run_date is located (standard date format), the format is yyyy-MM-dd2024-02-01
run_month_now_beginStringthe first day of the previous month where run_today is located, the format is yyyyMMdd20240201
run_month_now_begin_stdStringThe first day of the previous month where run_today is located (standard format), the format is yyyy-MM-dd2024-02-01
run_month_endStringThe last day of the month where run_date belongs, in the format of yyyyMMdd20240229
run_month_end_stdStringThe last day of the month of run_date (standard date format), the format is yyyy-MM-dd2024-02-29
run_month_now_endStringthe last day of the previous month where run_today is located, the format is yyyyMMdd20240229
run_month_now_end_stdStringThe last day of the previous month where run_today is located (standard date format), the format is yyyy-MM-dd2024-02-29
run_quarter_beginStringThe first day of the quarter where run_date belongs, the format is yyyyMMdd20240101
run_quarter_endStringThe last day of the quarter where run_date is located, in the format of yyyyMMdd20240331
run_half_year_beginStringThe first day of the half year where run_date is located, the format is yyyyMMdd20240101
run_half_year_endStringThe last day of the half year where run_date is located, the format is yyyyMMdd20240630
run_year_beginStringThe first day of the year where run_date is located, the format is yyyyMMdd20240101
run_year_endStringThe last day of the year where run_date is located, the format is yyyyMMdd20241231
run_quarter_begin_stdStringThe first day of the quarter where run_date belongs (standard format), the format is yyyy-MM-dd2024-01-01
run_quarter_end_stdStringThe last day of the quarter where run_date belongs (standard format), the format is yyyy-MM-dd2024-03-31
run_half_year_begin_stdStringThe first day of the half year where run_date is located (standard format), the format is yyyy-MM-dd2024-01-01
run_half_year_end_stdStringThe last day of the half year where run_date is located (standard format), the format is yyyy-MM-dd2024-06-30
run_year_begin_stdStringThe first day of the year where run_date is located (standard format), the format is yyyy-MM-dd2024-01-01
run_year_end_stdStringThe last day of the year of run_date (standard format), the format is yyyy-MM-dd2024-12-31
run_tody_hStringrun_today task running time, yyyyMMddHH2024030111
run_tody_h_stdStringThe running time of the run_today task yyyy-MM-dd HH2024-03-01 11

details:

  1. run_date is a date variable that comes with the core, and supports user-defined dates. If not specified, it defaults to the day before the current system time.
  2. Definition of other derived built-in date variables: other date built-in variables are calculated relative to run_date. Once run_date changes, the values of other variables will also change automatically. Other date variables do not support setting initial values and can only be modified by modifying run_date .
  3. The built-in variables support richer usage scenarios: ${run_date-1} is the day before run_data; ${run_month_begin-1} is the first day of the previous month of run_month_begin, where -1 means minus one month.

Take sql as an example:

--@set run_date=20240229
select
"${run_date}" as run_date,
"${run_date_std}" as run_date_std,
"${run_today}" as run_today,
"${run_today_std}" as run_today_std,
"${run_mon}" as run_mon,
"${run_mon_std}" as run_mon_std,
"${run_month_begin}" as run_month_begin,
"${run_month_begin_std}" as run_month_begin_std,
"${run_month_now_begin}" as run_month_now_begin,
"${run_month_now_begin_std}" as run_month_now_begin_std,
"${run_month_end}" as run_month_end,
"${run_month_end_std}" as run_month_end_std,
"${run_month_now_end}" as run_month_now_end,
"${run_month_now_end_std}" as run_month_now_end_std,
"${run_quarter_begin}" as run_quarter_begin,
"${run_quarter_end}" as run_quarter_end,
"${run_half_year_begin}" as run_half_year_begin,
"${run_half_year_end}" as run_half_year_end,
"${run_year_begin}" as run_year_begin,
"${run_year_end}" as run_year_end,
"${run_quarter_begin_std}" as run_quarter_begin_std,
"${run_quarter_end_std}" as run_quarter_end_std,
"${run_half_year_begin_std}" as run_half_year_begin_std,
"${run_half_year_end_std}" as run_half_year_end_std,
"${run_year_begin_std}" as run_year_begin_std,
"${run_year_end_std}" as run_year_end_std,
"${run_today_h}" as run_tody_h,
"${run_today_h_std}" as run_tody_h_std

Compile the replaced content:
select
"20240229" as run_date,
"2024-02-29" as run_date_std,
"20240301" as run_today,
"2024-03-01" as run_today_std,
"202402" as run_mon,
"2024-02" as run_mon_std,
"20240201" as run_month_begin,
"2024-02-01" as run_month_begin_std,
"20240201" as run_month_now_begin,
"2024-02-01" as run_month_now_begin_std,
"20240229" as run_month_end,
"2024-02-29" as run_month_end_std,
"20240229" as run_month_now_end,
"2024-02-29" as run_month_now_end_std,
"20240101" as run_quarter_begin,
"20240331" as run_quarter_end,
"20240101" as run_half_year_begin,
"20240630" as run_half_year_end,
"20240101" as run_year_begin,
"20241231" as run_year_end,
"2024-01-01" as run_quarter_begin_std,
"2024-03-31" as run_quarter_end_std,
"2024-01-01" as run_half_year_begin_std,
"2024-06-30" as run_half_year_end_std,
"2024-01-01" as run_year_begin_std,
"2024-12-31" as run_year_end_std,
"2024030111" as run_tody_h,
"2024-03-01 11" as run_tody_h_std

2.2 Custom variables

     What are custom variables? User variables that are defined first and used later. User-defined variables temporarily support the definition of strings, integers, and floating-point variables. Strings support the + method, and integers and floating-point numbers support the +-*/ method. User-defined variables do not conflict with the set variable syntax supported by SparkSQL and HQL itself, but the same name is not allowed. How to define and use custom variables? as follows:

## Defined in the code, specify before the task code
sql type definition method:
--@set f=20.1
The python/Shell type is defined as follows:
#@set f=20.1
Note: Only one line to define a variable is supported

The use is directly used in the code through {varName expression}, such as ${f*2}

2.3 Variable scope

Custom variables also have a scope in linkis, and the priority is that the variable defined in the script is greater than the Variable defined in the task parameter and greater than the built-in run_date variable. The task parameters are defined as follows:

## restful
{
"executionContent": {"code": "select \"${f-1}\";", "runType": "sql"},
"params": {
"variable": {f: "20.1"},
"configuration": {
"runtime": {
"linkis.openlookeng.url":"http://127.0.0.1:9090"
}
}
},
"source": {"scriptPath": "file:///mnt/bdp/hadoop/1.sql"},
"labels": {
"engineType": "spark-2.4.3",
"userCreator": "hadoop-IDE"
}
}
## java SDK
JobSubmitAction. builder
.addExecuteCode(code)
.setStartupParams(startupMap)
.setUser(user) //submit user
.addExecuteUser(user) //execute user
.setLabels(labels)
.setVariableMap(varMap) //setVar
.build

3. Date Pattern variable introduction

3.1 Features

  • Support Pattern format time and users can specify it at will
  • Pattern date variables are calculated relative to run_date
  • Support ±y/±M/±d/±H etc.
  • Among them, +- is to operate on the linkis built-in parameter run_date first, and then replace the pattern field before %. Non-Pattern characters do not support operation and replacement.

Pattern format comparison table:

LetterDate or Time ComponentPresentationExamples
GEra designatorTextAD
yYearYear1996; 96
YWeek yearYear2009; 09
MMonth in yearMonthJuly; Jul; 07
wWeek in yearNumber27
WWeek in monthNumber2
DDay in yearNumber189
dDay in monthNumber10
FDay of week in monthNumber2
EDay name in weekTextTuesday; Tue
uDay number of week (1 = Monday, …, 7 = Sunday)Number1
aAm/pm markerTextPM
HHour in day (0-23)Number0
kHour in day (1-24)Number24
KHour in am/pm (0-11)Number0
hHour in am/pm (1-12)Number12
mMinute in hourNumber30
sSecond in minuteNumber55
SMillisecondNumber978
zTime zoneGeneral time zonePacific Standard Time; PST; GMT-08:00
ZTime zoneRFC 822 time zone-0800
XTime zoneISO 8601 time zone-08; -0800; -08:00

3.2 Date Pattern variable example

You can define the parameters that need dynamic rendering according to your own preferences/business actual situation

variableresult
&{yyyy-01-01}2021-01-01
&{yyyy-01-01%-2y}2019-01-01
&{yyyy-MM-01%-2M}2021-02-01
&{yyyy-MM-dd%-2d}2021-03-31
&{yyyy MM ----- HH%-1H}2021 04 ----- 14
&{yyyyMMdd%-1d}20210401
&{yyyyMM01%-1M}20210301
&{HH%-1H}14

3.3 Date Pattern variable usage examples

  • Example 1: sql
SELECT * FROM hive.tmp.fund_nav_histories
WHERE dt <= DATE_FORMAT(DATE_ADD('day', -1, DATE(Date_parse('&{yyyyMMdd%-1d}', '%Y%m%d'))), '%Y%m%d')

after rendering

SELECT * FROM hive.tmp.fund_nav_histories
WHERE dt <= DATE_FORMAT(DATE_ADD('day', -1, DATE(Date_parse('20220705', '%Y%m%d'))), '%Y%m%d')
  • Example 2: shell
aws s3 ls s3://***/ads/tmp/dws_member_active_detail_d_20210601_20211231/pt=&{yyyyMMdd%-1d}/

after rendering

aws s3 ls s3://***/ads/tmp/dws_member_active_detail_d_20210601_20211231/pt=20220705/
  • Example 3: datax json
{
"job": {
"setting": {
"speed": {
"channel": 1
}
},
"content": [
{
"reader": {
"name": "s3reader",
"parameter": {
"bucket": "**************",
"path": [
"ads/tmp/ccass_tm_announcements/&{yyyyMMdd%-1d}/"
],
"stored": "parquet",
"compression": "NONE",
"column": [
{
"index": 0,
"type": "int"
},
{
"index": 1,
"type": "string",
"constant": "&{yyyyMMdd%-1d}"
}
]
}
},
"writer": {
"name": "streamwriter",
"parameter": {
"print": true
}
}
}
]
}
}

after rendering

{
"job": {
"setting": {
"speed": {
"channel": 1
}
},
"content": [
{
"reader": {
"name": "s3reader",
"parameter": {
"bucket": "**************",
"path": [
"ads/tmp/ccass_tm_announcements/20220705/"
],
"stored": "parquet",
"compression": "NONE",
"column": [
{
"index": 0,
"type": "int"
},
{
"index": 1,
"type": "string",
"constant": "20220705"
}
]
}
},
"writer": {
"name": "streamwriter",
"parameter": {
"print": true
}
}
}
]
}
}
  • Example 4:python
print(&{yyyyMMdd%-1d})

after rendering

  20220705