Execute Custom SQL Statements

Overview

This example demonstrates how to use the Redshift Execute Snap to execute custom SQL statements including DDL and DML operations. The Snap allows you to leverage the database query engine capabilities such as functions, joins, and complex queries that go beyond simple SELECT operations.

Prerequisites

  • Amazon Redshift cluster with appropriate access
  • Valid Redshift account configured in SnapLogic
  • Understanding of SQL syntax and Redshift-specific functions

Pipeline Overview

The pipeline uses the Redshift Execute Snap as a standalone component to execute custom SQL queries. This example demonstrates a query that uses database functions, provides uniqueness, generates different output columns, and includes literal values that cannot be performed with the Redshift Select Snap alone.

Snap Configuration

Redshift Execute Snap - Settings:

  • SQL statement: Custom SQL query with database functions
  • Query type: Auto (automatically detects read or write operation)
  • Pass through: Selected (includes original input document in output)

Output

The Snap outputs documents containing the query results merged with the original input data:

SQL Statement Execution Modes

The Redshift Execute Snap supports two execution modes:

Without Expressions:

  • Direct SQL statement execution
  • JSON path parameters in WHERE clause (e.g., $name)
  • Uses prepared statements with parameter binding
  • Executes in batch mode based on account configuration

With Expressions:

  • SQL statement must be within quotes
  • Expressions outside quotes (e.g., + $tablename +)
  • Bind parameters within quotes (e.g., '$id')
  • Allows dynamic table and column references

Error Handling

When the error view is enabled, processing continues even if some queries fail. Documents that cause errors are routed to the error view with detailed error information.

Use Cases

This pattern is useful for:

  • Executing complex queries with joins and subqueries
  • Running DDL operations (CREATE, ALTER, DROP)
  • Performing database maintenance operations
  • Leveraging Redshift-specific functions and features
  • Dynamic SQL generation based on input parameters

Best Practices

  • Use prepared statements with JSON paths for better performance
  • Be cautious with DDL operations - they can drop databases or lock tables
  • Escape single quotes in string values by using two consecutive quotes
  • Enable the error view to capture and handle query failures
  • Use the Pass through option to preserve input documents in output
  • Avoid string concatenation to prevent SQL injection risks