Snowflake Stored Procedure

The Snowflake Stored Procedure transfer object calls a stored procedure in the Snowflake AI Data Cloud. Input parameters, return values, and, if applicable, a result set can be used as destinations or sources within the connection.

Dialog Overview

General Properties

Property	Description
Data Source	All Snowflake connections created in the Plug-ins section are available for selection here. If the desired connection does not yet exist, you can make it available in the Plug-ins section as the “Snowflake AI Data Cloud” plug-in.
Stored Procedure	Stored procedure to be executed.
Columns/Parameters	Input and return parameters of the procedure that are to be connected as sources or destinations to the transfer object.
Query Result Set	If return values are expected in the form of a result set, they can be read using Query Result Set and then made available as a source on the transfer object.

Special Features in the OPC Router

• The OPC Router reads the available parameters of the procedure and makes them available as usable elements in the transfer object.
• When Query Result Set is executed, the OPC Router calls the procedure once to determine the structure of the result set. This call takes place within a transaction, which is subsequently rolled back.
• To determine the structure, the OPC Router calls the procedure with default parameters (NULL; for parameters that do not allow NULL, e.g., 0 for numeric values or an empty string for character strings).

warning

The procedure must be transaction-safe and must not return any errors during the standard call for structure detection.

Example

The following stored procedure writes a telemetry measurement value to the MACHINE_TELEMETRY table and returns a status based on the temperature. The transfer object shown above is configured for this procedure.

CREATE OR REPLACE PROCEDURE PRODUCTION.SP_LOG_TELEMETRY(
    MACHINE_ID  VARCHAR,
    TEMPERATURE FLOAT,
    PRESSURE    FLOAT
)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    INSERT INTO MACHINE_TELEMETRY (MACHINE_ID, TEMPERATURE, PRESSURE, INGESTED_AT)
        VALUES (:MACHINE_ID, :TEMPERATURE, :PRESSURE, CURRENT_TIMESTAMP());

    IF (:TEMPERATURE > 80) THEN
        RETURN 'WARN_HIGH_TEMP';
    ELSE
        RETURN 'OK';
    END IF;
END;
$$;

In the transfer object, you bind the three input parameters (MACHINE_ID, TEMPERATURE, PRESSURE) as targets; the return value is available as a source. The procedure is transaction-safe: During the standard call for structure recognition, only a data record with default values is inserted and then rolled back, and OK is returned.