In this article
Table of Contents | ||||
---|---|---|---|---|
|
Overview
Snap type: | Write | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Description: | This Snap allows you to execute arbitrary Snowflake SQL.
| |||||||||||||||||||||||
Prerequisites: |
| |||||||||||||||||||||||
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: |
| |||||||||||||||||||||||
Behavior change | Multiexcerpt include macro | | ||||||||||||||||||||||
name | Behavior change in Database Execute Snaps | page | Azure SQL - Execute
Note |
---|
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. |
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.
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*
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 name ME_DB_Snaps_Query_Examples page Oracle - Update
Note |
---|
Note:
|
Warning | |||||||||
---|---|---|---|---|---|---|---|---|---|
| |||||||||
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 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
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 name retries page File Reader
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
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.
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 | ||
---|---|---|
| ||
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 |
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 | ||||
---|---|---|---|---|
|
Multiexcerpt include macro | ||||
---|---|---|---|---|
|
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 | ||||||
---|---|---|---|---|---|---|
|