On this Page

Table of Contents

maxLevel	2
exclude	Older Versions\|Additional Resources\|Related Links\|Related Information

Snap Type:

Write

Description:

This Snap is used to execute multiple Redshift SQL statements sequentially inside of a single BEGIN and END transaction for each input document. If an SQL statement fails, subsequent SQL statements are not executed and error output goes to the error view (if the corresponding option is selected). If all statements are successful, all output results go to the output view. Data can be written to both output and error view if using multiple input documents. Use this Snap to perform updates to Redshift data that would otherwise be done in a Stored Procedure.

Code Block
Select statements that return data results, and transaction sql statements such as begin, end, commit and rollback are not supported.

ETL Transformations & Data Flow

This Snap enables the following ETL operations/flows:

The Snap executes a sequence of multiple Redshift DDL commands in a sequential order as specified in the Executions section and the result will be written to the output view which contains results from individual queries that have been executed from the provided properties. If any submitted query fails, the Snap will throw an exception and rollback changes from prior queries.

Input & Output:

Input: The Snap can have zero to one input document to trigger multi-executions of SQL statements and any necessary field data values if expressions are used in the SQL executions.
Output: The output results will be written to the output view.

Upstream & Downstream Snaps

Expected Upstream Snaps: Upstream Snap is not required - the MultiExecute Snap will execute once if no input view is defined. However, if there is an upstream Snap, it should only produce documents to trigger the MultiExecute Snap. For example, if zero documents are produced by an upstream Snap, the MultiExecute SQL will not execute. If two documents are produced, the MultiExecute will execute twice, once for each input document (the original input document data will be preserved in the output views)
Expected Downstream Snaps: Applicable downstream Snaps to handle the possible Redshift SQL data results emitted on the output and error view can be used.

Note
A BEGIN and END statement are prepended and appended, respectively, to the MultiExecute SQL automatically by the Snap. The result of these statements is added to the output and error views.

Prerequisites:

None

Limitations and Known Issues:

Works in Ultra Task Pipelines.
SELECT statements are not accepted for execution.
Does not support transaction, commit, and rollback operations.

Multiexcerpt include macro

name	Redshift limitation with PostgreSQL driver
templateData	[]
page	Redshift - Execute
addpanel	false

Configurations:

Account & Access

This Snap uses account references created on the Accounts page of SnapLogic Manager to handle access to this endpoint. See Redshift Account for information on setting up this type of account.

Views

Input	This Snap can have at most one input view.
Output	This Snap has at most one output view.
Error	This Snap has at most one error view and produces zero or more documents in the view.

Troubleshooting:

[None]

Settings

Label

Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.

Executions

Required. Specifies the SQL statement to execute on the server.

Examples:

Multiexcerpt include macro

name	ME_DB_Execute_Snaps_Scenario1
page	Azure SQL - Execute

create table stage_table as select * from leads l where l.value not in (select h.value from leads_hist h);
insert into leads_hist select * from stage_table;
drop table stage_table;

Multiexcerpt include macro

name	ME_DB_Execute_Snaps_Scenario2
page	Azure SQL - Execute

Warning

title	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.

Warning

title	Single 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 value	Use
Has no single quotes	Schaum Series	`'`Schaum Series`'`
Contains single quotes	`O'Reilly's Publication`	'`O''Reilly''s Publication`'

Default value: [None]

Multiexcerpt include macro

name	Snap Execution
page	SOAP Execute

Multiexcerpt include macro

name	Execution_Detail_Write
page	SOAP Execute

Troubleshooting

Error	Reason	Resolution
`type "e" does not exist`	This issue occurs due to incompatibilities with the recent upgrade in the Postgres JDBC drivers.	Download the latest 4.1 Amazon Redshift driver here and use this driver in your Redshift Account configuration and retry running the Pipeline.

Examples

Basic Use Case

The following pipeline describes how the Snap functions as a standalone Snap in a pipeline (a Select Snap is used here to display the output preview of the records added as displayed in the second image below):

An example of MultiExecute properties, in this case, the SQL is using expressions:

The successful output after two input documents:

The error output preview:

Typical Snap Configurations

The key configuration of the Snap lies in how you pass the SQL statement to execute a function:

Without Expression: Directly passing the required SQL statements in the Redshift Multi Execute Snap. Literal statements (without SQL parameters or SnapLogic expressions):
- With SQL parameters:

With Expressions
It is possible to pass values to the Redshift Multi Execute Snap using expression language as well. In the example below, the values are passed using a query from an upstream snap and pipeline parameters. Both these methods are demonstrated in detail below:
- Query from an upstream Snap: The Redshift Multi Execute Snap receives values from the upstream JSON Generator Snap.

The Multi Execute Snap executes the SQL statements as passed using the upstream JSON Generator Snap:

The Redshift Select Snap displays the out preview with the added records:

- Pipeline Parameter: Pipeline parameter set to pass the required SQL Statement to the Multi Execute Snap.

In the below Snap Executions settings, the values for the fields are passed using the pipeline parameters.

Advanced Use Case

The pipeline shows how to execute multiple SQL statements to insert records into the table, emp_RS, using the Redshift Multi Execute Snap. We also use the Redshift Select Snap to read the table to which the records are added. Here, we pass the values using the upstream and also dynamically, by defining the pipeline parameters respectively.

1. Edit the JSON Generator Snap and input the upstream data to be passed into the table, emp_RS.

The output preview from the JSON Generator Snap is as below:

2. Set the pipeline parameters to be passed dynamically:

3. In the Redshift - Multi Execute Snap, the SQL statement executions are as below:

4. The output preview of the Redshift - Multi Execute Snap has the status of the executions (result) displayed sequentially between the 'BEGIN' and the 'END' execution transaction:

5. View the table "public"."emp_RS" to which we added the records using the Redshift - Select Snap:

6. Successful execution of the pipeline displays the below output preview in a JSON or a Table format respectively:

Downloads

Multiexcerpt include macro

name	download_instructions
page	OpenAPI

Attachments

patterns	slp, zip

Insert excerpt

	Redshift Snap Pack
	Redshift Snap Pack
nopanel	true

Versions Compared

Old Version 34

New Version Current