Create Step
Summary
Use the CREATE STEP command to create a new step object within your Connect environment. Steps are objects stored within your Connect instance and can contain one or more XSQL/XCL command expressions. For example, instead of writing a SQL SELECT statement and executing it, developers can wrap the same SQL within a step and later invoke the step to run the command. Once created, a step object will exist in your Connect environment until someone explicitly drops the step object. Developers can bundle multiple steps into a larger pipeline to perform complex data integration and ETL logic.
Typically, a step object will contain only a single XSQL command. In some cases, it may be desirable to place multiple commands into a single step to reduce the number of discrete step objects needed to perform a sequence of operations. A step with multiple commands runs each one serially in the order given when the step was created.
Syntax
create step [if not exists] <step_name> [as (<command1>[;<command2>][;<command3..n>;])]; |
Parameters
|
Parameter
|
Description
|
|---|---|
<step_name> |
The name of the step. |
| [if not exists] | Suppresses error messages thrown when a user attempts to create a step that already exists. |
<command> |
The command or commands to run when the step is invoked. |
Details
The step command syntax is validated when it is created, but the step is not evaluated. A step is evaluated when invoked by a user, pipeline, or schedule. If no command is specified in the CREATE STEP command, you can add one later using the ALTER STEP command.
Examples
Create a step without an expression definition. Use the alter step command to later add an expression to the step.
create step s_1;
Creates the step only if it doesn’t already exist on the Connect server.
create step if not exists s_2;
Step when invoked will insert one row with the literal string ‘1’ into the delta.foo table
create step s_insert_foo as (insert into delta.foo values('1'));
Step when invoked will insert the value of variable, :variable1, into the delta.foo table.
create step s_insert_using_var as (insert into delta.foo values(:variable1));
Step when invoked will set the variable, v_period_name.
create step if not exists s_set_var_period_name as (set v_period_name = LookupPeriodName('2015-06-03','MONTHLY'));
Step when invoked will set the variable, v_period_end_date.
create step s_set_period_end_date as (set v_period_end_date = select end_date from xactly.xc_period where name = :v_period_name);
Create a step containing multiple commands to set some variables. Each command runs serially in the listed order.
create step s_many_commands as (set v1 *= 1; set v2 *= 'abc'; set v_current_instant = Now(););