On this Page
The Snowflake Multi Execute Snap is a Write type Snap that enables you to execute multiple queries as a single atomic unit. This means that. if any query fails in execution, the changes are rolled back. This Snap supports executing all Snowflake queries.
While the Snap does not require an input document, it supports document input and generates document outputs. Snaps that accept or generate documents can be used upstream or downstream of the Snap.
Expected upstream Snaps: The dynamic variables used in the execute query can be defined by providing values upstream. Document-generating Snaps such as JSON Generator can be used in upstream.
Expected downstream Snaps: The Snap will output one document for every record retrieved, hence any document processing Snap can be used downstream.
Security Prerequisites: You should have the following permissions in your Snowflake account to execute this Snap:
Usage (DB and Schema): Privilege to use database, role, and schema.
Create table: Privilege to create table on database. role and schema.
The following commands enable minimum privileges in the Snowflake Console:
grant usage on database <database_name> to role <role_name>; grant usage on schema <database_name>.<schema_name>; grant "CREATE TABLE" on database <database_name> to role <role_name>; grant "CREATE TABLE" on schema <database_name>.<schema_name>; |
For more information on Snowflake privileges, refer to Access Control Privileges.
The permissions to grant for using commands depends on the queries you provide in this Snap.
This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Snowflake Account for information on setting up this type of account.
Input | This Snap has at most one document input view. |
---|---|
Output | This Snap has at most one document output view. |
Error | This Snap has at most one document error view. |
None.
The Snap may break existing Pipelines if the JDBC Driver is updated to a newer version.
With the updated JDBC driver (version 3.12.3), the Snowflake Execute and Multi-Execute Snaps' output displays a Status of "-1" instead of "0" without the Message field upon successfully executing DDL statements. If your Pipelines use these Snaps and downstream Snaps use the Status field's value from these, you must modify the downstream Snaps to proceed on a status value of -1 instead of 0. This change in the Snap behavior follows from the change introduced in the Snowflake JDBC driver in version 3.8.1: |
Label | Required. The name for the Snap. Modify this to be more specific, especially if there are more than one of the same Snap in the pipeline. | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
SQL Statements | Required. Provide the queries to be executed by the Snap. End each query with a semi-colon, except the last query. To add another query, click the + to add a row and there add the query. Default value: None
| |||||||||||
Session auto-commit | Select this to enable the session's auto-commit. This is useful when using DDL queries since the changes made by such queries cannot be rolled back if the Snap/pipeline fails. If deselected, changes made by DML queries can be rolled back or changes made by DML queries after a DDL queries can be rolled back. Default value: Selected | |||||||||||
Use Result Query | Select this property to write the result of each of the query execution, in case of multiple queries, to Snap's output view after the successful execution. The output of the Snap will be enclosed within the key, Result Query and the value will be the actual output produced by the SQL query. The option would allow users to effectively track the query's execution by clearly indicating the successful executions and the number of records affected, if any, after the execution. | |||||||||||
Manage Queued Queries | Select this property to decide whether the Snap should continue or cancel the execution of the queued Snowflake Execute SQL queries when you stop the pipeline.
Default value: Continue to execute queued queries when pipeline is stopped or if it fails | |||||||||||
In the following Pipeline, Snowflake Execute Snap is used to define and execute various UDF queries and the Snowflake Multi Execute Snap is used to give process multiple Snowflake queries in a single transaction.
First, the Snowflake Execute Snap is used to define the user-defined function in the SQL statement. RECURSION_TEST is one of the Scalar JavaScript UDF in Snowflake.
The Snap settings and the Output view are displayed as follows:
Then, the Snowflake Multi Execute Snap is used to execute multiple Snowflake queries as a single transaction for every input document. The Snap settings and the Output view are displayed as follows: