Table of Contents

Search

  1. Preface
  2. Part 1: Introduction to Amazon Redshift connectors
  3. Part 2: Data Integration with Amazon Redshift V2 Connector
  4. Part 3: Data Integration with Amazon Redshift Connector

Amazon Redshift Connectors

Amazon Redshift Connectors

SQL transformation

SQL transformation

You can use an SQL transformation to push Redshift supported scalar functions to Amazon Redshift.
When you configure
SQL ELT optimization
for a mapping, you can use SQL user-defined functions (UDFs) in a SQL transformation and run queries with the Amazon Redshift target endpoint.
You can use only the SELECT clause SQL statement to push down a function. The following snippet demonstrates the syntax of a simple select SQL query:
SELECT <function_name1>(~Arg~), <function_name2> (~Arg~)...
You must provide the corresponding query in the following format:
select <Redshift_supported_scalar_function1> (~Arg~), <Redshift_supported_scalar_function2> (~Arg~)
You can push a SQL transformation with the following restrictions:
  • You can configure only a SQL query in the SQL transformation. You cannot enable a stored procedure when you configure
    SQL ELT optimization
    to Amazon Redshift.
  • The SQL query must be a simple SELECT statement without 'FROM' and 'WHERE' arguments. The SQL transformation only supports functions with Simple SELECT statement.
  • When you specify a SELECT query, you must also specify the column name and number of columns based on the functions. For example, when you specify the query
    select square(~AGE~), sqrt(~SNAME~)
    , you must specify two output columns for
    AGE
    and
    SNAME
    functions each, otherwise the mapping fails.
  • You can only use a SQL transformation when the SELECT statement is present in the query property. Even if an entire query containing the SELECT statement comes from a parameterized input port, the
    SQL ELT optimization
    fails.
  • If any SQL error occurs, the error is added to the
    SQLError
    field by default. However, when mapping runs in PDO mode, the
    SQLError
    field will remain as Null.
  • The
    NumRowsAffected
    field number records the number of rows affected while computing the output buffer. However, for SQL transformation, the
    NumRowsAffected
    will be 0 since the query runs for all records in a single go and not for each row.
  • Amazon Redshift offers only passive behavior of SQL transformations where the support for dynamic queries are limited.

0 COMMENTS

We’d like to hear from you!