Snowflake - Execute
In this article
Overview
Snap type: | Write | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
Description: | This Snap allows you to execute arbitrary Snowflake SQL. The Snowflake Execute Snap is for simple DML (SELECT, INSERT, UPDATE, DELETE) type statements.
| ||||||||||
| Prerequisites: | You should have minimum permissions on the database to execute Snowflake Snaps. To understand if you already have them, you must retrieve the current set of permissions. The following commands enable you to retrieve those permissions. SHOW GRANTS ON DATABASE <database_name> SHOW GRANTS ON SCHEMA <schema_name> SHOW GRANTS TO USER <user_name> Security Prerequisites You should have the following permissions (but not limited to) in your Snowflake account to execute this Snap:
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. | ||||||||||
| Internal SQL Commands | The permissions to grant for usage on database and creating tables depend on the queries you provide in this Snap. | ||||||||||
| Support and limitations: |
Snowflake Execute and Multi-Execute Snaps 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: | ||||||||||
| Behavior change |
If you have any existing Pipelines that are mapped with status key or previous description then those Pipelines will fail. So, you might need to revisit your Pipeline design. | ||||||||||
| Account: | This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Configuring Snowflake Accounts for information on setting up this type of account. | ||||||||||
| Views: |
| ||||||||||
Settings | |||||||||||
Label* | Specify the name for the Snap. You can make the name more specific, especially if your Pipeline has more than one of the same Snap. | ||||||||||
SQL statement* | Specify the Snowflake SQL statement to execute on the server. Document value substitution is performed on literals starting with '$', for example, $people.name is substituted with its value available in the incoming document. In DB Execute Snaps, if the Snowflake SQL statement is not an expression, the JSON path, such as $para, is allowed in the WHERE clause only. If the query statement starts with SELECT (case-insensitive), the Snap regards it as a select-type query and executes once per input document. If not, the Snap regards it as a write-type query and executes in batch mode. This Snap does not allow you to inject Snowflake SQL, for example, select * from people where $columnName = abc. Without using expressions
Using expressions
Caution Using expressions that join strings together to create SQL queries or conditions has a potential SQL injection risk and hence unsafe. Ensure that you understand all implications and risks involved before using concatenation of strings with '=' Expression enabled. Note:
Single quotes in values must be escaped Any relational database (RDBMS) treats single quotes ( For example:
| ||||||||||
Pass through | Select this checkbox to enable the Snap to pass the input document to the output view under the key named Default Value: Selected | ||||||||||
Ignore empty result | Select this checkbox to not write any document to the output view when a SELECT operation does not produce any result. If this checkbox is not selected and the Pass-through checkbox is selected, the input document is passed through to the output view. Default Value: Not selected | ||||||||||
| Number of Retries | Specify the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response. Default Value: 0 No macro found named "retries" on page "File Reader" in space "SD". If you're experiencing issues please see our Troubleshooting Guide. | ||||||||||
| Retry Interval (seconds) | Specify the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception. Default Value: 1 | ||||||||||
| Use Result Query | Select this checkbox to write the query execution result to the Snap's output view after the successful execution. The output of the Snap will be enclosed within the key This option allows users to effectively track the query's execution by clearly indicating the successful execution and the number of records affected, if any, after the execution. | ||||||||||
| Handle Timestamp and Date Time Data | Specify how the Snap must handle timestamp and date time data. The available options are:
Default value: Default Date Time format in UTC Time Zone Recommendation: If you use the Timestamp TZ and Timestamp LTZ in this Snap, we recommend you to use SnapLogic Date Time format in Regional TimeZone to ensure that you get the Timestamp data output of the target table in the same format as in the source table. Source Table
Target Table
| ||||||||||
| Manage Queued Queries | Select an option from the list to determine whether the Snap should continue or cancel the execution of the queued Snowflake Execute SQL queries when you stop the Pipeline. The available options are:
If you select Cancel queued queries when pipeline is stopped or if it fails, the read queries under execution are canceled, whereas the write type of queries under execution are not canceled. Snowflake internally determines which queries are safe to be canceled and cancels those queries. Default value: Continue to execute queued queries when pipeline is stopped or if it fails | ||||||||||
Snap Execution | Select one of the three modes in which the Snap executes. Available options are:
| ||||||||||
Examples
Snowflake Execute with Use Result Query enabled
This example Pipeline demonstrates how to insert data into a table using the Snowflake Execute Snap.
First, we configure the Snowflake Execute Snap as follows. Note that we select the Use Result Query checkbox to view the statement result output.
Upon execution, we see the following output enclosed within the key Result Query.
The following screenshot displays the output preview when we disable the Use Result Query checkbox.
Executing the Snowflake SQL query using the Execute Snap
The following example demonstrates the execution of Snowflake SQL query using the Snowflake Execute Snap.
First, we configure the Execute Snap with this query—select * from "PRASANNA"."ADOBEDATA" , which returns the data from ADOBEDATA. 
Upon successful execution, the Snap displays the following output in its data preview.
Snowflake Execute Snap supports UDFs
User-defined functions (UDFs) created in the Snowflake console can be executed using Snowflake - Execute Snap. In the following example, the SQL statement is defined and then the Snap is executed with that conditions.
First, the Snowflake Execute Snap is used to give the user-defined SQL statement. area_of_circle(3.0) is a UDF here. The Snap settings and the output view are as follows:
Then the Mapper Snap is used to define columns that need to be picked up from the Output of the Snowflake Execute.
