内置时间变量

1. 用户希望Linkis能够提供一些公共变量然后执行的时候进行替换，比如用户每天都会批量跑同一段sql，需要指定上一天的分区时间，如果基于sql去写会比较复杂如果系统提供一个run_date的变量将会非常方便使用。
2. 用户希望Linkis支持date pattern计算，支持在代码中写&{YYYY-MM-DD}等变量计算时间变量

3. 用户希望自己定义变量，比如定一个一个float变量，然后在代码中进行使用

4. 支持任务代码的变量替换

5. 支持自定义变量，支持用户在脚本和提交给Linkis的任务参数定义自定义变量，支持简单的+，-等计算
6. 预设置系统变量：run_date,run_month，run_today等系统变量
7. 支持date pattern变量，支持pattern的+，-操作

具体技术架构可以参考： Linkis自定义变量

 Linkis支持的变量类型分为自定义变量和系统内置变量，内部变量是Linkis预先定义好的，可以直接进行使用。然后不同的变量类型支持不同的计算格式：String支持+、整数小数支持+-\*/,日期支持+-。

目前已支持的内置变量如下： （下表中的变量值举例以闰年的20240229作为特殊日期）

具体细节：

1、run_date为核心自带日期变量，支持用户自定义日期，如果不指定默认为当前系统时间的前一天。 2、其他衍生内置日期变量定义：其他日期内置变量都是相对run_date计算出来的，一旦run_date变化，其他变量值也会自动跟着变化，其他日期变量不支持设置初始值，只能通过修改run_date进行修改。 3、内置变量支持更加丰富的使用场景：${run_date-1}为run_data的前一天；${run_month_begin-1}为run_month_begin的上个月的第一天，这里的-1表示减一个月。

以sql为例的例子：

--@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
编译替换后的内容：
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

 什么是自定义变量？先定义，后使用的用户变量。用户自定义变量暂时支持字符串，整数，浮点数变量的定义，其中字符串支持+法，整数和浮点数支持+-\*/方法。用户自定义变量与SparkSQL和HQL本身支持的set变量语法不冲突，但是不允许同名。如何定义和使用自定义变量？如下：

## 代码中定义，在任务代码前进行指定
sql类型定义方式：
--@set f=20.1
python/Shell类型定义如下：
#@set f=20.1
注意：只支持一行定义一个变量

使用都是直接在代码中使用通过 {varName表达式},如${f*2}

在linkis中自定义变量也有作用域，优先级为脚本中定义的变量大于在任务参数中定义的Variable大于内置的run_date变量。任务参数中定义如下：

## 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

• 支持Pattern格式时间且用户可以自行随意指定
• Pattern日期变量都是相对run_date计算出来的
• 支持±y/±M/±d/±H等等
• 其中+-是先对linkis内置参数run_date进行运算,然后将%之前的pattern字段进行替换,非Pattern字符不支持运算替换。

Pattern格式对照表:

可以根据自己的喜好/业务实际情况定义需要动态渲染的参数

• 例子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')

渲染后

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')

• 例子2: shell

aws s3 ls  s3://***/ads/tmp/dws_member_active_detail_d_20210601_20211231/pt=&{yyyyMMdd%-1d}/

渲染后

aws s3 ls  s3://***/ads/tmp/dws_member_active_detail_d_20210601_20211231/pt=20220705/

• 例子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
          }
        }
      }
    ]
  }
}

渲染后

{
  "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
          }
        }
      }
    ]
  }
}

• 例子4:python

print(&{yyyyMMdd%-1d})

渲染后

 20220705