Redshift - Multi Execute

Overview

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.

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.

Prerequisites

A valid Redshift Account with the required permissions.

Limitations

  • SELECT statements are not accepted for execution.
  • Does not support transaction, commit, and rollback operations.
  • Each SQL statement must be separated by a semicolon (;).
  • Using expressions that join strings together to create SQL queries or conditions has a potential SQL injection risk and is unsafe. Ensure that you understand all implications and risks involved before using concatenation of strings with expression enabled.
  • If you use the PostgreSQL driver (org.postgresql.Driver) with the Redshift Snap Pack, it could result in errors if the data type provided to the Snap does not match the data type in the Redshift table schema. Either use the Redshift driver (com.amazon.redshift.jdbc42.Driver) or use the correct data type in the input document to resolve these errors.

Snap views

Type Description Examples of upstream and downstream Snaps
Input

Document

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).
Output

Applicable downstream Snaps to handle the possible Redshift SQL data results emitted on the output and error view can be used.

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.
Learn more about Error handling.

Examples

Examples for this Snap are coming soon.

Snap settings

Note: Learn about the common controls in the Snap settings dialog.
Field/Field set Description

Label

String

Required.

Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline.

Default value: Redshift - Multi Execute

Example: Redshift - Multi Execute

Executions

String/Expression

Required.

Specifies the SQL statement to execute on the server.

Example:

  • email = '[email protected]' or email = $email
  • emp=$emp
  • 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;
  • "EMPNO=$EMPNO and ENAME=$EMPNAME"
  • "emp='" + $emp + "'"
  • "EMPNO=" + $EMPNO + " and ENAME='" + $EMPNAME+ "'"
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 (') 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]

Snap execution

Dropdown list

Select one of the three modes in which the Snap executes. Available options are:

  • Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.
  • Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.
  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Default value: Execute only

Example: Validate & 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.