START

Overview

Starts another job pipeline immediately or with a slight delay, and optionally retrieve the data set output of the pipeline (the pipeline called must be a Read pipeline). Unlike TRIGGER operations that starts other pipeline conditionally upon completion, the START operation can appear anywhere in the job pipeline. By default, the operation is considered aynchronous and the execution of the current pipeline continues. To start another pipeline synchronously, use the WAIT or REPLACE option.

Calling this operation sets two pipeline variables upon completion: @lastStartExecGuid and @lastStartExecGuid. You can use these variable immediately after a START operation. Note that unless WAIT or REPLACE is used, a delayed start is not guarantee to actually start if the pipeline is disabled or is already running. The @lastStartExecGuid can be useful to poll pipeline completion programmatically.

START 'another pipeline';  -- start the pipeline async 
PRINT 'Exec guid: @lastStartExecGuid';

If the pipeline being called is already running, this command will automatically attempt to retry the start operation up to 5 times using a linear backoff strategy starting at 1 second delay. This retry operation observes the overall timeout of the script and may be cancelled.

Parameters

If the pipeline called by the START operation has one or more DECLARE statements, you can override the default value of these parameters by using the PARAMS option using an xxx-form-encoded format (ex: p1=100&p2=test). The PARAMS setting supports DataZen functions and pipeline variables (ex: p1=100&p2=#urlencode(This is an = test)&p3=@executionid). If the pipeline being started does not define corresponding DECLARE variables, the parameters are ignored and no error is thrown. Variable names are case-sensitive.

If the parameters are too complex or too large, use the SINK DRIVE operation to first save the data then pass the file name created as one of the parameters so you can use it in the pipeline being called.

Wait

The WAIT option pauses the execution of the current pipeline and continues when the pipeline being called completes. However, the data generated by the pipeline being called is discarded.

Replace

The REPLACE option can be used to wait for the completion of the Read pipeline being called and replace the pipeline data with its output. This can be useful to execute other pipelines to keep logic centralized and improve reusability.

Beware that using REPLACE and SKIP_PREVIEW may yield unexpected results at runtime because the pipeline being called may return a different schema than the current pipeline schema.

For example, the following code calls a pipeline that reads files from a cloud drive:

-- This is how you would call this pipeline 
-- START 'this pipeline' PARAMS 'fileName=*.parquet&container=qa' REPLACE;
-- 
DECLARE PARAM @fileName = '*.txt';
DECLARE PARAM @container = 'dev';
DECLARE PARAM @format = 'RAW';

SELECT * FROM DRIVE [awss3] (@fileName)
WITH 	CONTAINER '@container'
        FORMAT '@format' 
        LIST
;

Syntax

START '...' 
    { PARAMS 'p1=val1&p2=val2...' }
    { SKIP_PREVIEW }
    { DELAY N }
    { < REPLACE | WAIT > }
    { TIMEOUT N }
    { CONTINUE_ON_ERROR }
;

-- Get data from two RSS Feeds and append their outputs
SELECT * FROM HTTP [Rss Feed] (GET /econi.xml);
APPEND SELECT * FROM HTTP [RSS Gov Feed] (GET /budget.xml);

-- Apply an ETL block 
APPLY PIPELINE (

  -- Start error processing if any errors were detected 
  -- DataColumn Expressions work using aggregate operations
  IF (MAX(statusCode) > 200)
  BEGIN
    PRINT 'At least one call failed' LEVEL 'warning';
  	START 'pipeline_notify_failure' ;
  END
  
  -- FILTER out errors, but exit if 0 records remain (break_on_empty)
  APPLY FILTER 'statusCode = 200';
  
  APPLY TX (//item) ON 'payload';

  START 'pipeline_notify_success' DELAY 5 SKIP_PREVIEW;

) BREAK_ON_EMPTY;

--
-- List databases in RECOVERY mode in SQL Server
--
SELECT * FROM DB [sql2017]
(
  SELECT * FROM sys.databases WHERE state_desc IN ('RECOVERING', 'RECOVERY_PENDING', 'RESTORING');
);

-- Start a pipeline that has contains these 2 parameters:
-- DECLARE PARAM @message = '';
-- DECLARE PARAM @level = 'debug';
IF(@@rowcount() > 0)
BEGIN
	PRINT 'Sending alerts asynchronously...';
	START 'pipeline_alert' PARAMS 'message="@@rowcount() databases are in recovery mode"&level=critical' ;
END

--
-- List databases in RECOVERY mode in SQL Server
--
SELECT * FROM DB [sql2017]
(
  SELECT * FROM sys.databases WHERE state_desc IN ('RECOVERING', 'RECOVERY_PENDING', 'RESTORING');
);

-- Start a pipeline that has contains these 2 parameters:
-- DECLARE PARAM @message = '';
-- DECLARE PARAM @level = 'debug';
IF(@@rowcount() > 0)
BEGIN
	PRINT 'Sending alerts and waiting for completion...';
	START 'pipeline_alert' 
		PARAMS 'message="@@rowcount() databases are in recovery mode"&level=critical' 
		WAIT;
	PRINT 'Alerts were sent successfully. Continuing with pipeline processing...';
END