CONFIG_LOAD_SCHEDULES

CONFIG_LOAD_SCHEDULES is a configuration package that is used to define load schedules. CONFIG_LOAD_SCHEDULES has a user interface that can be used for creating and editing schedules. Similar to other configuration packages, CONFIG_LOAD_SCHEDULES can also be exported as JSON, edited and imported back to Designer.

See also:

• Loads

• CONFIG_ENVIRONMENT_VARIABLES

Tutorials

See the below video for a quick tutorial on how to create a load schedule.

Usage

Using the configuration view (user interface)

1. Open CONFIG_LOAD_SCHEDULES from the Designer package list.

2. Open the CONFIGURATION view:
   

   

3. Create/edit load schedules.

4. Commit changes and deploy them to Runtime environments.

Editing the package JSON

1. Open the configuration package from the Designer package list.

2. Press Export package in the Actions tab.

3. Press Download.

4. Edit the JSON file.

5. Import the edited JSON back to Designer with Import Package.

After a successful import, commit the package and deploy the changes to Runtime environments.

Use Create new load schedule to create new schedules from the CONFIGURATION view:

In the package JSON, schedule configurations are managed inside the schedulings array block. Note that the package JSON will be populated with changes made in the CONFIGURATION view.

You can add a new schedule or update the contents of an existing schedule.

Example schedule configuration in the package JSON:

"schedulings": [
  . . .
  {
    "schedulingId": "20b652c5abd9dde26f5d74cf2a9b0b3e",
    "schedulingName": "1_DAY",
    "cronExpr": "30 2 * * *"
  }
  . . .
]

Existing schedules can be deleted in the CONFIGURATION view with the delete button:

An existing schedule can also be deleted by removing it from the configuration package JSON before importing it.

Schedule name cleanup during package import
Schedule names are verified and cleaned up during the package import

• Names will be transformed to uppercase, only characters from A to Z, numbers and underscores are allowed [A-Z0-9_].

• All other consecutive non-allowed characters will be substituted to one underscore.

Example 1: These examples would result in the same schedule name ADE_META_SCHEDULING_CRON:

• ADE_META_SCHEDULING_CRON

• ade meta scheduling cron

• Ade#meta&scheduling#Cron

Example 2: These examples would be transformed to underscores:

• myöhäistetty_lataus → will become scheduling MY_H_ISTETTY_LATAUS

• loading data */5 * * * 1,2,3,4,5 → will become scheduling LOADING_DATA_5_1_2_3_4_5

Triggered schedules

Setting up triggered schedules for a schedule will trigger selected schedules after the source workflow has finished. Triggered schedules can be added from the load schedule Summary view with add triggered schedule:

Select a schedule or multiple schedules from the list to be triggered.

In the package JSON, triggered schedules are managed inside the schedulingTriggers array block. Note that the package JSON will be populated with changes made in the user interface.

Example of schedulingTriggers in the package JSON:

  ...
  {
    "schedulingName": "TRIGGERING_SCHEDULE_A",
    "cronExpr": "*/15 * * * *",
    "loadPool": null
  },
  {
    "schedulingName": "TRIGGERED_SCHEDULE_B",
    "cronExpr": null,
    "loadPool": null
  }
],
"schedulingTriggers": [
  {
   "schedulingName": "TRIGGERING_SCHEDULE_A",
   "triggeredSchedulingName": "TRIGGERED_SCHEDULE_B"
  }
]

schedulingTriggers only accepts schedule names declared in schedulingName.

After making Configuration package changes, commit the package and deploy the changes to a target Runtime. The Designer user interface might need to be refreshed with CTRL-R in order to make new schedules visible in the drop down lists.

Setting multiple schedules for a workflow/DAG

With triggered schedules, it is possible to trigger a schedule (workflow) from multiple other schedules that have different cron expressions.

Example of triggering a workflow from multiple other workflows in the package JSON:

...
    {
      "schedulingName": "SCHEDULE_HOURLY",
      "cronExpr": "3 * * * *",
      "loadPool": null
    },
    {
      "schedulingName": "SCHEDULE_DAILY",
      "cronExpr": "30 2 * * ",
      "loadPool": null
    },
    {
      "schedulingName": "TRIGGERED_SCHEDULE",
      "cronExpr": null,
      "loadPool": null
    }
  ],
  "schedulingTriggers": [
    {
     "schedulingName": "SCHEDULE_DAILY",
     "triggeredSchedulingName": "TRIGGERED_SCHEDULE"
    },
    {
      "schedulingName": "SCHEDULE_HOURLY",
      "triggeredSchedulingName": "TRIGGERED_SCHEDULE"
    }
  ]
}

After making Configuration package changes, commit the package and deploy the changes to a target Runtime. The Designer user interface might need to be refreshed with CTRL-R in order to make new schedules visible in the drop down lists.

Schedule specific variables

Schedule specific variables can be used to define variables and variable values that are specific to the execution of a schedule. You can define new variables or refer to variables defined elsewhere such as environment variables and assign values to them which will be resolved when the schedule is executed in a Runtime environment.

Schedule variables can be added in the load schedule Summary view with Add schedule variable:

Define a new VARIABLE NAME or refer to an existing variable, set VARIABLE VALUE.

In the package JSON, schedule variables are managed inside the schedulingVariables array block. Note that the package JSON will be populated with changes made in the user interface.

You can combine existing environment variables from CONFIG_ENVIRONMENT_VARIABLES and schedule variables. Variables can be referenced with the following syntax:

<variable_in_config_environment_variables>

Examples

Note that this article contains examples also in the previous chapters.

Environment specific cron expression

With CONFIG_ENVIRONMENT_VARIABLES and CONFIG_LOAD_SCHEDULES, it is possible to define a Runtime environment specific cron expression for a schedule.

In this example, schedule ENVIRONMENT_BASED does not have a cron expression in the DEV environment but will execute every hour on the 5th minute in the PROD environment.

1. Define environment variables and their values in CONFIG_ENVIRONMENT_VARIABLES:

...
    "environments": [
    {
      "environmentName": "RUNTIME-DEV"
    },
    {
      "environmentName": "RUNTIME-PROD"
    }
  ],
  "environmentVariables": [
    {
      "environmentName": "RUNTIME-DEV",
      "variableName": "ENVIRONMENT_BASED_CRON",
      "variableValue": null
    },
    {
      "environmentName": "RUNTIME-PROD",
      "variableName": "ENVIRONMENT_BASED_CRON",
      "variableValue": "5 * * * *"
    }
  ]
 ...
}

2. Create a new schedule in CONFIG_LOAD_SCHEDULES, set the environment variable as the cron expression:

This translates to the following configuration package JSON:

...
    {
      "schedulingName": "ENVIRONMENT_BASED",
      "cronExpr": "<ENVIRONMENT_BASED_CRON>",
      "loadPool": null
    }
 ...
}

3. After deploying the configuration packages, select ENVIRONMENT_BASED as the schedule for loads to be run in this environment specific schedule.

Using schedule specific warehouses in Snowflake

This example combines variables defined in Agile Data Engine main configuration, in CONFIG_ENVIRONMENT_VARIABLES and in schedule specific variables (CONFIG_LOAD_SCHEDULES) to define a schedule and environment specific virtual warehouse in Snowflake.

Step 1 is necessary in the Private edition only. This is preconfigured in the SaaS edition.

1. First, define an environment variable as the warehouseName in the targetInstances block of all the Runtime environments configured in the main configuration. For example:

...
runtimes:
...
    targetInstances:
    - default:
        dbmsProduct: SNOWFLAKE
        jdbcUrl: jdbc:snowflake://xyz.snowflakecomputing.com/?db="ADE_DEV_DB"&schema="PUBLIC"&warehouse="ADE_DEV_WH"&role="ADE_DEV_ROLE"
        ...
        warehouseName: <warehouse_name>
        ...
    environmentVariables:
        warehouse_name: 'ADE_DEV_DB'
...

Where <warehouse_name> is the defined environment variable (note that it should not be used in the connection string). You may also define the default value for the variable in the environmentVariables block of each Runtime environment as shown in the example. This default setting can be overriden with CONFIG_ENVIRONMENT_VARIABLES.

Steps 2-3 are the same in Private and SaaS.

2. Define an environment variable for the schedule specific warehouse and set its values per environment in CONFIG_ENVIRONMENT_VARIABLES, for example:

...
   "environments": [
   {
     "environmentName": "RUNTIME-DEV"
   },
   {
     "environmentName": "RUNTIME-QA"
   },
   {
     "environmentName": "RUNTIME-PROD"
   }
 ],
 "environmentVariables": [
   {
     "environmentName": "RUNTIME-DEV",
     "variableName": "fina_warehouse_name",
     "variableValue": "FINA_DEV_WH"
   },
   {
     "environmentName": "RUNTIME-QA",
     "variableName": "fina_warehouse_name",
     "variableValue": "FINA_QA_WH"
   },
   {
     "environmentName": "RUNTIME-PROD",
     "variableName": "fina_warehouse_name",
     "variableValue": "FINA_PROD_WH"
   }
 ]
...

Where fina_warehouse_name is the environment variable and the variableValue values are the environment specific virtual warehouses.

3. In the load schedule Summary view, set the schedule specific variable with Add schedule variable:

The selection refers to the environment variable warehouse_name which was defined in the Agile Data Engine main configuration and sets its value to <fina_warehouse_name> which is defined per environment in CONFIG_ENVIRONMENT_VARIABLES.

This is translated to the following CONFIG_LOAD_SCHEDULES package JSON:

...
  "schedulings": [
    {
      "schedulingName": "FINA_HOURLY",
      "cronExpr": "0 * * * *",
      "description": "Example for public documentation"
      "loadPool": null,
      "dagGenerationMode": null
    }
  ],
  "schedulingVariables": [
    {
      "schedulingName": "FINA_HOURLY",
      "variableName": "warehouse_name",
      "variableValue": "<fina_warehouse_name>"
    }
  ]
}