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. If the DELAY option is used, the operation is considered aynchronous and the execution of the current pipeline continues.

Calling this operation sets two pipeline variables upon completion: @lastStartExecGuid and @lastStartExecGuid. You can use these variable immediately after a START operation, but not if the DELAY is used; that's because 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'; 
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.

Replace

The REPLACE option can be used to wait for the output of the Read pipeline being called. This option cannot be used with the ASYNC option. This can be useful to execute other pipelines to keep logic centralized and improve reusability. 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 }
    { TIMEOUT N }
;

-- 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
	START 'pipeline_alert' PARAMS 'message="@@rowcount() databases are in recovery mode"&level=critical' ;
END