Stored Procedure Executor

5.10 and later

The Stored Procedure executor triggers a Snowflake stored procedure after receiving at least one row of data. The executor can trigger any Snowflake stored procedure that performs a task and does not return tabular data. The executor does not pass return values to the pipeline. When your organization uses deployed Transformer for Snowflake engines, you can use the executor with engine versions 5.10 and later.

Use the Stored Procedure executor to perform a task after it receives data. For example, you might use a Stored Procedure executor to truncate a source table after data is written to pipeline destinations.

Note: Unlike other executors, the Stored Procedure executor does not display on the Executor tab of the Stage Selector dialog box. Instead, to add a Stored Procedure executor to a pipeline, use the Functions tab to select the Snowflake stored procedure that you want to use.

Selecting a stored procedure in the Stage Selector dialog box creates a Stored Procedure executor that is customized based on the selected procedure. The executor displays the name and signature of the stored procedure and provides properties that correspond to the arguments defined in the stored procedure.

When you configure the Stored Procedure executor, you define the values for arguments. All values must be constants.

Prerequisites

Before using a Stored Procedure executor, perform the following tasks as needed:
  1. Create a stored procedure that performs a task.

    Do not create a stored procedure that returns tabular data. The Stored Procedure executor does not pass return data to the pipeline.

    You can use any method available with Snowflake to create the stored procedure. For more information, see the Snowflake documentation.

  2. Ensure that you have a role with access to the stored procedure.

    Use the role to define the Role pipeline property. The pipeline uses the specified role to perform Snowflake-related tasks, including executing the stored procedure. For more information, see the Snowflake documentation.

Defining Arguments

Snowflake stored procedures can include required and optional arguments. All arguments defined in the selected Snowflake stored procedure appear as properties in the resulting Stored Procedure executor. All values defined for argument properties must be constants. Arguments cannot return tabular data.

Argument properties are listed in the same order that the arguments are listed in the Snowflake stored procedure.
Tip: To ensure that you define the argument properties appropriately, consult the Snowflake stored procedure signature or definition. Argument properties are not named in the executor at this time.
For example, the following drop_columns Snowflake stored procedure includes the review and rating arguments:
CREATE OR REPLACE PROCEDURE RATING_CHECK(review VARCHAR, rating VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
AS
BEGIN
  ...
END;

To configure this Stored Procedure executor correctly, you treat the first set of argument properties in the executor like the review argument, and the second set like the rating argument, as follows:

Working with Optional Arguments

When a Snowflake stored procedure includes optional arguments, those arguments appear in the Stored Procedure executor.

When you want to define an optional argument, you must define all arguments, required and optional, that are listed before the optional argument.

For example, say you create a Stored Procedure executor based on a stored procedure that includes one required argument and three optional arguments as follows:
CREATE OR REPLACE PROCEDURE REVIEW_PROCESSING(
  review VARCHAR, 
  review_summary VARCHAR DEFAULT 'none',
  review_sentiment VARCHAR DEFAULT 'none', 
  rating NUMBER(38,0) DEFAULT 0))
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
AS
BEGIN
  ...
END;

If you want to define the required review argument and the optional review_sentiment argument, you must also define the optional review_summary argument.

In the executor, this means that you define the first three arguments. You also remove the last argument, which represents the optional rating argument.

Configuring a Stored Procedure Executor

Configure a Stored Procedure executor to include a Snowflake stored procedure in the pipeline to perform a task.

Unlike other executors, you create a Stored Procedure executor from the Functions tab of the Stage Selector dialog box. This creates a custom Stored Procedure executor based on the selected stored procedure. The Stored Procedure executor does not appear on the Executors tab.

  1. In the pipeline canvas, click the Insert Stage icon or the Add Stage button.
  2. In the Stage Selector, click the Functions tab:
    The Stage Selector dialog box attempts to list all UDFs and stored procedures available to the role defined in the pipeline. To limit the results, you can specify a search string, or select a function type or schema.
    Since they are not supported, stored procedures that return tabular data do not display.
  3. Select the Snowflake stored procedure that you want to use.
    The pipeline canvas displays a Stored Procedure executor based on the selected stored procedure, providing a set of properties for every argument in the stored procedure.
  4. Verify that you selected the appropriate stored procedure.
    The executor provides the following information from the Snowflake stored procedure:
    • Name
    • Signature
    • Description, when available
    Note: You cannot change the stored procedure that the executor uses. If you selected the wrong stored procedure, delete the stage and add a new Stored Procedure executor.
  5. In the Arguments area, define the arguments for the executor.
    For each argument, define the following properties, as needed:
    Table 1. Arguments properties
    Arguments property Description
    Value Value to pass to the argument. The value must be a constant or treated as a constant.
    Treat as Constant Treats the specified value as a constant. Use when the specified value can otherwise be interpreted as a column name or Snowflake expression.
    To define an optional argument, define all arguments up to the optional argument.
    Important: Argument properties are listed in the order that they are defined in the stored procedure. To ensure that you define the properties appropriately, consult the stored procedure signature or definition.
  6. Remove any optional arguments that are not defined.