Alter Pipeline

Summary

The ALTER PIPELINE command modifies the attributes of an existing pipeline domain object. Use it to add member steps and child pipelines to a parent pipeline.

Syntax

// Add a step or child pipeline member to a parent pipeline
alter pipeline [if exists] <pipeline_name> 
   add step|pipeline <member_name> 
   [before|after <existing_member_name>]|[position <position_#>]
   [as ([Condition=(<boolean expression>),
      Retries=<#>,
      RetryInterval=<#>,
      OnConditionFalse=<step_name|pipeline_name>,
      AbortOnConditionFalse=<true|false)>)];
 
// Define how a pipeline behaves if any pipeline member throws an error during execution
alter pipeline [if exists] <pipeline_name> as
      (ContinueOnError=<boolean expression>,
      OnError= <step or pipeline to invoke>,
      Finally= <step or pipeline to invoke>)
 
// Rename the pipeline
alter pipeline <pipeline_name> rename <new_pipeline_name>;
 
// Manage pipeline labels
alter pipeline <pipeline_name> add|drop label <label_identifier>;
 
// Delete a member from a pipeline. The member step or pipeline remains in Connect. Only the parent pipeline definition is affected.
alter pipeline <pipeline_name> drop position <position_#>;

Parameters

Parameter	Description
`<pipeline_name>`	(Required) The name of an existing pipeline object to modify.
`[if exists]`	(Optional) Suppresses the step not found error when attempting to alter a step that does not exist in Connect.
`<member_name>`	(Optional) The name of a step or child pipeline to include in the primary `<pipeline_name>`.
[<existing_member_name>]	(Optional) Use to place a new pipeline member before or after an existing member by reference name in the pipeline.
[<position_#>]	(Optional) Use to place a new pipeline member before or after an existing member by position in the pipeline. Pipeline members cannot be dropped by member name, as the same same step or child pipeline can be used more than once in a parent pipeline.
[as (Condition=(<boolean XSQL expresssion>)), Retries=<# of times to retry member if Condition=false>, RetryInterval=<number of seconds between retries>, OnConditionFalse=<execute step_name\|pipeline_name>, AbortOnConditionFalse=<true\|false>)]	(Optional) The `as Condition=` syntax provides a conditional control structure to change pipeline execution flow. For example, if a boolean condition is not met before executing the pipeline member, the developer can configure the member to halt the parent pipeline execution and execute a different step or pipeline. Additionally, the developer may choose to abort or continue executing the parent pipeline when a conditional check returns false. `Condition` = A custom XSQL expression that evaluates to a single boolean value. For example, “select if count(\*) > 100 then false else true end from delta.example_errors_table” will evaluate to false if the delta.example_errors_table has more than 100 rows. Otherwise the condition evaluates to true and the parent pipeline will continue executing the pipeline member. `Retries` = an integer value. if “Condition” evaluates to false, Connect can re-execute the member n number of times. Default is 0. `RetryInterval` = the number of minutes between each retry. Default is 0. `OnConditionFalse` = the name of the step or pipeline to execute if Condition evaluates to false and all retries have completed. `AbortOnConditionFalse` = true or false. Controls whether to abort or continue execution of the parent pipeline if the Condition expression evaluates to false.
`[as (ContinueOnError=<true\|false>,` `OnError= <step or pipeline to invoke>,` `Finally= <step or pipeline to invoke>)]`	(Optional) These parameters determine how a pipeline behaves if one of its step or child pipeline members throws an error during execution. `ContinueOnError = true or false.` Connect throws exceptions when a pipeline member fails during processing due to unanticipated system conditions. Alternatively, developers may use the `Assert` and `ThrowException` functions to force a pipeline member to throw an exception to trigger a pipeline error. The boolean flags determine if the pipeline should continue or halt processing if one of its members fails. This should probably be set to false in most circumstances. `OnError` = the name of the step or pipeline to execute if a pipeline member throws an error. You might want to send an email to your IT staff if a fatal error was thrown, for example. `Finally` = the name of the step or pipeline to execute after the parent pipeline has completed. Finally will fire when a pipeline completes successfully. It will also fire if a pipeline member throws an error. In other words, Finally will run no matter what happens during pipeline execution.
<new_pipeline_name>	(Optional) The new name for the pipeline object.
`<label_identifier>`	(Optional) The label identifier. Labels allow developers to add descriptive string tags to step and pipeline objects. Labels are added to the metadata returned by the `SHOW PIPELINE` commands. A pipeline can have more than one label.

Details

Steps and pipelines are the core mechanisms for designing ETL processes within Connect.

Examples

screen-shot-2016-09-21-at-11-25-00-am

The step objects shown in the following examples are not stock Connect objects. The alter pipeline commands will fail if a step object does not exist in your environment. The examples demonstrate how to use the different command syntax and parameters.

// Add a step to a pipeline
alter pipeline p_upload_orders add step s_run_validation;

screen-shot-2016-09-21-at-11-25-12-am

// Add a child pipeline to a parent pipeline with if exists
alter pipeline if exists p_upload_orders add pipeline p_clear_staging_tables;

// Add a step between members 1 and 2
alter pipeline p_upload_orders add step s_send_start_email position 1;

// Drop the step or child pipeline found at position 3
alter pipeline p_upload_orders drop position 3;

// Add a child pipeline before step s1_oops
alter pipeline p1 add pipeline p_get_period_name before s1_oops;

// Delete a member from a pipeline. To find the position number of a member, use SHOW PIPELINE <pipeline_name> MEMBERS.
alter pipeline p_upload_orders drop position 2;

// Add a label descriptor
alter pipeline p_upload_orders add label ‘Pipeline downloads Opportunities from SFDC and loads into Incent’;

// Delete a label
alter pipeline p_upload_orders drop label ‘Pipeline downloads Opportunities from SFDC and loads into Incent’;

// Rename a pipeline
alter pipeline p_upload_orders rename p_upload_orders_from_sfdc;

// Compound example with Conditional expression
alter pipeline if exists p_upload_orders
add step s_init_vars
position 3
as (Condition=(Exists(FilePath=’customer_lookup_file.csv’)),
Retries=2,
RetryInterval=10,
OnConditionFalse=s_send_error_email,
AbortOnConditionFalse=true);

// Control how pipeline behaves if a system error or other exception is thrown during execution

alter pipeline p_on_error_test_2 as
(ContinueOnError=false,
OnError=s_send_error_email,
Finally=s_finally_reinit_vars);