Scheduling Connect Jobs

Connect allows you to create, monitor, and manage schedules of your business tasks, that is, the steps and pipelines where you store your Incent data integration processes. You configure schedule domain objects to execute a step or a pipeline at a given time or frequency.

Schedule Syntax

To create a new schedule in Connect, use:

create schedule if not exists schedule_name as (parameter_list);

Where schedule_name (the name of the schedule domain object) is required and if not exists is an optional clause that allows the schedule object to be created only if it does not currently exist in Connect. A schedule can only fire a pipeline or a step

And where parameter_list includes:

• “Step” = step_name
  (required) The name of the step to be invoked. Not required if “Pipeline” is specified, instead.
• “Pipeline” = pipeline_name
  (optional) The name of the pipeline to be invoked. If set, “Step” is ignored.
• “Cron” = cron_string
  (required) A cron string expression with a maximum granularity of 1 hour. In other words, you may schedule a job to run once an hour at the most. Connect does not support second or minute intervals. You can use the cron expression to tell which hour of the day you want the schedule to fire. When you create or alter the schedule domain object with a specified hour, Connect will randomly assign the schedule to one of the following minute buckets: hour:00, hour:10, hour:20 , hour:30, hour:40, hour:50. Once set, the schedule will always fire at the assigned time, unless the schedule object is in a suspended state.

The cron string has five fields, separated by spaces:

“Cron” format =’Hour    Day_of_Month    Month    Day_of_Week    Year‘

For example, to invoke the step or pipeline every hour, use:

“Cron“=‘0/1 1/1 * ? *‘

TIP: CronMaker.com makes it easy to build cron-format expressions.

• “Condition” = expression
  (optional) An xSQL expression that evaluates to true (default) or false. The scheduled step or pipeline is invoked if true. If false, it is not executed for the given schedule invocation.
• “Retries” = integer
  (optional) Specifies how many times to retry, when Condition evaluates to false, before proceeding to the OnConditionFalse step.
• “RetryInterval” = integer
  (optional) Specifies the number of minutes to wait for each retry.
• “OnConditionFalse” = step_name
  (optional) Indicates the step to invoke if Condition still evaluates to false after all retries.

 

Create a Schedule

Let’s create a schedule to manage our steps and pipelines.

Connect tracks each invocation of a step or pipeline that is attached to a schedule, but for our exercise, we’ll create a table to track our executions:

create table delta.schedule_example(inserted_at instant);

 

Now we’ll create a step to attach to a schedule:

create step s_run_insert as 
(insert into delta.schedule_example 
values (Now()));

NOTE: A given schedule can hold one step or one pipeline, not both.

 

We need to create a dummy trigger file to see the scheduler’s conditional logic in our simple example:

call TouchFile(FilePath= 'dummy_trigger.txt');

 

And just to be sure, let’s confirm it’s there:

call Exists(FilePath= 'dummy_trigger.txt');

 

At last, we can create a schedule, to run our step every hour:

create schedule sch_run_insert as 
("Step"=s_run_insert, 
"Cron"='0/1 1/1 * ? *', 
"Condition"=(call Exists(FilePath= 'dummy_trigger.txt')));

Because we added the Condition parameter, the schedule will invoke the s_run_insert step only if the dummy_trigger.txt file exists. The example mimics a real-world example where a schedule should proceed only if a particular source data file is present.

 

Let’s make sure it’s there. Use SHOW SCHEDULES to list the schedule objects in your environment:

show schedules;

Show Invocated Steps, Pipelines

Once we’ve let a few hours pass, there should be several invocations of the step we scheduled to run hourly. Then we can show the invocations for the step in our schedule object:

select id, object_name, 
state, status, created_instant, 
completed_instant 
from (show invocations) 
where object_name = 's_run_insert' 
order by completed_instant;

 

After a day, we should have 23 or 24 invocations. Let’s check the target table we created:

select * 
from delta.schedule_example 
order by inserted_at;

We can see that our job is running every hour.

Suspend a Schedule

When you create a schedule, it is actively running every minute. If you won’t need the step or pipeline to invoke for the upcoming period, you can suspend it:

suspend schedule sch_run_insert;

 

We can confirm that our schedule has been disabled by querying the suspended field:

select name, object_name, cron, suspended 
from (show schedules);

Resume a Schedule

When you’re ready to resume the automated invocation of the steps or pipelines in your schedule:

resume schedule sch_run_insert;

Just as when we disabled the schedule, we can confirm that our schedule has been enabled by querying the suspended field, and ensuring it resolves to false now:

select name, object_name, cron, suspended 
from (show schedules);

Delete a Schedule

To remove a schedule from Connect, use DROP SCHEDULE:

drop schedule sch_run_insert;

 

And lastly, we can use SHOW SCHEDULES to confirm that our schedule is gone:

show schedules;

 

Indeed, as that was the only schedule in our environment, our request shows there are none.