Versions Compared

Old Version 65

changes.mady.by.user Shilpa

Saved on

compared with

New Version 66

changes.mady.by.user Shilpa

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

In this article

Table of Contents
maxLevel2
excludeOlder Versions|Additional Resources|Related Links|Related Information

Overview

Snap type:

Write

Description:

This Snap allows you to execute arbitrary Snowflake SQL.

The Snap substitutes the valid JSON paths that are defined in the WHERE clause for queries/statements with the values present in the incoming document. If the incoming document does not carry a substituting value, the document is written to the error view. 

If the Snap executes a SELECT query, it merges the query results into the incoming document and overwrites the values of all existing keys. On the other hand, the Snap writes the original document if there are no results from the query. If an output view is available and an UPDATE/INSERT/MERGE/DELETE statement is executed, the original document that was used to create the statement becomes output with the status of the statement executed.

Expected upstream Snaps: You can provide values upstream to define the dynamic variables used in the execute query. You can use the document generator Snaps, such as JSON Generator, upstream.
 
Expected downstream Snaps: The Snap produces one output document for every record retrieved; hence, any document-processing Snap can be used downstream. 

Note
The Snowflake Execute Snap is for simple DML (SELECT, INSERT, UPDATE, DELETE) type statements. 


Prerequisites:

Insert excerpt
Snowflake - Bulk Load
Snowflake - Bulk Load
nopaneltrue

Info
titleSecurity Prerequisites

You should have the following permissions (but not limited to) in your Snowflake account to execute this Snap:

  • Usage (DB and Schema): Privilege to use database, role, and schema.
  • Create table: Privilege to create a table on the database. role, and schema.

The following commands enable minimum privileges in the Snowflake Console:

Code Block
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 CommandsThe permissions to grant for usage on database and creating tables depend on the queries you provide in this Snap.
Support and limitations:
Note
titleSnowflake 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:
"Statement.getUpdateCount() and PreparedStatement.getUpdateCount() return the number of rows updated by DML statements. For all other types of statements, including queries, they return -1."


Behavior change
Multiexcerpt include macro
nameBehavior change in Database Execute Snaps
pageAzure SQL - Execute
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:


Input

This Snap has at most one document input view. If the input view is defined, the where clause can substitute incoming values for a given expression. 

Output

This Snap has at most one document output view.

Error

This Snap has at most one error view and produces zero or more documents in the view.


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.
Only values can be substituted since it uses prepared statements for execution, which, for example, results in select * from people where address = ?.

Multiexcerpt include macro
nameME_DB_Snaps_Query_Examples
pageOracle - Update

Note

Note: 

  • If '$' is not part of the JSON path, escape it as "\$" so that it can be executed as it is, such as SELECT \$2, \$3 FROM mytable.
    If the character before '$' is alphanumeric, there is no need to escape '$'. For example, SELECT metadata$filename ... 
  • When an escape character is an integral part of an expression-enabled query statement, precede it with another escape character.
    For example, if you enable expression for the SQL statement property, precede the backslash with another backslash when entering such a statement. See the following image:
  • The '$' sign and identifier characters, such as double quotes (“), single quotes ('), or back quotes (`), are reserved characters and should not be used in comments or for purposes other than their originally intended purpose.


Warning
titleSingle quotes in values must be escaped

Any relational database (RDBMS) treats single quotes (') as special symbols. So, single quotes in the data or values passed through a DML query may cause the Snap to fail when the query is executed. Ensure that you pass two consecutive single quotes in place of one within these values to escape the single quote through these queries.

For example:

If String To pass this valueUse
Has no single quotes
Schaum Series
'Schaum Series'
Contains single quotes
O'Reilly's Publication
'O''Reilly''s Publication'



Pass through


Select this checkbox to enable the Snap to pass the input document to the output view under the key named original. This option applies only to the Execute Snaps with SELECT statement.

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
Example: 3

Multiexcerpt include macro
nameretries
pageFile Reader

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
Example: 10

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 Result Query, and the value will be the actual output produced by the SQL query. See the example Snowflake Execute with Use Result Query enabled to know more about this option. 

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 Date Time format in UTC Time Zone: The Snowflake date time data are represented in UTC Time Zone.

  • SnapLogic Date Time format in Regional Time Zone: The Snowflake date time data are represented in the same regional time zone value, as provided in the Snowflake account.

Default value: Default Date Time format in UTC Time Zone

Multiexcerpt macro
nameTimestamp_recommendation


falseRecommendation:

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:

  • Continue to execute queued queries when pipeline is stopped or if it fails
  • Cancel queued queries when pipeline is stopped or if it fails
Note

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

Multiexcerpt include macro
nameSnap Execution
pageSOAP Execute

Multiexcerpt include macro
nameExecution_Detail_Write
pageSOAP Execute

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. 


See Also

https://docs.snowflake.com/en/developer-guide/udf/sql/udf-sql-scalar-functions.html

https://docs.snowflake.com/en/sql-reference/udf-overview.html

https://docs.snowflake.com/en/user-guide-getting-started.html

Insert excerpt
Snowflake Snap Pack
Snowflake Snap Pack
nopaneltrue